Piecesize Of an Index

JyothsnaNayak · New User Joined: 03 Feb 2010 Posts: 9 Location: India

Can anyone tell me what it means when an index has PIECESIZE as 0?

Thanks,
Jyothsna

dick scherrer · Posted: Mon Sep 24, 2012 6:58 pm

Hello,

At the top of the page is a link to "IBM Manuals". In the db2 sql reference, read about CREATE INDEX. PIECESIZE is described there.

We are Not a manual reading service . . .

gylbharat · Posted: Mon Sep 24, 2012 9:30 pm

I checked the manuals... I dont think we can specify PIECESIZE 0

As per the manual

PIECESIZE integer
Specifies the maximum addressability of each data set for a secondary index. The PIECESIZE clause can only be specified for secondary indexes.

Be aware that when you alter the PIECESIZE value, the index is placed into page set REBUILD-pending (PSRBD) status. The entire index space becomes inaccessible. You must run the REBUILD INDEX or the REORG TABLESPACE utility to remove that status.
The subsequent keyword K, M, or G, indicates the units of the value that is specified in integer.
K
Indicates that the integer value is to be multiplied by 1024 to specify the maximum data set size in bytes. integer must be a power of two between 256 and 67108864.
M
Indicates that the integer value is to be multiplied by 1048576 to specify the maximum data set size in bytes. integer must be a power of two between 1 and 65536.
G
Indicates that the integer value is to be multiplied by 1073741824 to specify the maximum data set size in bytes. integer must be a power of two between 1 and 64.
Table 1 shows the valid values for data set size, which depend on the size of the table space.
Table 1. Valid values of PIECESIZE clauseK units M units G units Size attribute of table space
256K
512K
1024K 1M
2048K 2M
4096K 4M
8192K 8M
16384K 16M
32768K 32M
65536K 64M
131072K 128M
262144K 256M
524288K 512M
1048576K 1024M 1G
2097152K 2048M 2G
4194304K 4096M 4G LARGE, DSSIZE 4G (or greater)
8388608K 8192M 8G DSSIZE 8G (or greater)
16777216K 16384M 16G DSSIZE 16G (or greater)
33554432K 32768M 32G DSSIZE 32G (or greater)
67108864K 65536M 64G DSSIZE 64G

The data set size limit for partitioned table spaces with more than 256 partitions is 4096.

Link - publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z9.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_sql_alterindex.htm

Please correct me if I am wrong?

dbzTHEdinosauer · Posted: Tue Sep 25, 2012 12:04 am

gylbharat · Posted: Tue Sep 25, 2012 8:02 pm

I was just confirming it... May be i could be missing something.

cvishu · Active User Joined: 31 Jul 2007 Posts: 136 Location: india

what does PIECESIZE 0 represent , i do see table indexes that have PICESIZE as 0 in SYSIBM.SYSINDEXES , am not sure what it represents .

We are trying to reduce the DASD usage , and are changing the PIECESIZE which has low values to 2G so that the number of datasets is reduced , but i see some with 0 am not sure what it means