Bereitgestellt von Rolf Loeben, IBM Deutschland GmbH:

Thanks to Patric Gannon from DB2 for S/390 Porting Center and Services for this piece of work.

The DB2 Piecesize Gets No Respect

The DB2 Piecesize is one of those forgotten parameters in DB2 that doesn't seem to get the recognition and attention it deserves.  It's kind of like the Rodney Dangerfield of DB2  parameters.  Sneaking in on Version 5 of DB2, its benefits and uses are still widely not understood.

The piecesize is an optional parameter on the CREATE INDEX and ALTER INDEX statement.  You can specify a maximum size for each data set (piece) of a non-partitioning index.  By specifying a  smaller size, you can have more pieces and therefore greater control over where to place those pieces. Placing the pieces on separate DASD (and preferably controllers) can reduce I/O contention for SQL operations  that access non-partitioning indexes during read or update processing.  A benchmark delivered at the time of Version 5 showed a 70% reduction in processing time for a batch job that inserted 75,000 rows.  This was achieved by splitting the non-partitioned index into 10 pieces and spreading them on 10 separate disks.

The elapsed time improvement may be even more significant when multiple tasks (inserts, updates, or deletes) are accessing the non-partitioned index.  I have seen long running parallel batch jobs  drastically reduced by utilizing DB2 Piecesize to spread the I/O's over several volumes.  Table 1 depicts two non-partitioned indexes on two separate DASD volumes. Table 2 is an RMF monitor report on DASD response time.  Note that volume RT0079 and RT0089 are experiencing 592 ms and 102 ms response time respectively. Upon drill down it is discovered that the datasets in Table 1 are the cause of the elongated DASD response time. By merely breaking these datasets into 8 "pieces" on separate DASD a 15 parallel batch process was reduced from 1 hr 15 min to under 50 min.  An average total savings of 375 minutes (25 min * 15).

Table 1
Command - Enter "/" to select action                 Tracks %Used XT Device Volume
--------------------------------------------------------------------------------------
         RTI.DSNDBD.DBPMP001.RTRX1079.I0001.A001      28035    ?   2 3390    RT0089
         RTI.DSNDBD.DBPMP001.RTRX1JIC.I0001.A001      18285    ?   1 3390    RT0079

Table 2
                   MIG= 6109 CPU= 89/ 89 UIC=254 PFR=   0   System= P14 Total
 17:02:14 I=53%  DEV             ACTV RESP IOSQ ---DELAY--- PEND DISC CONN %D %D
 STG GRP VOLSER NUM   MX  LCU   RATE TIME TIME DPB CUB DB TIME TIME TIME UT RV
 SGPMESMS RT0079 2D0A      007C 21.44  592 561 0.0 0.0 0.0  0.8 26.9  3.164 0
 SGPMESMS RT0045 2DC6      007F 6.428  157 135 0.0 0.0 0.0  0.9 17.7  3.814 0
 SGPMESMS RT0051 2DCC      007F 3.515  124 101 0.0 0.1 0.0  1.2 19.6  2.58  0
 SGPMESMS RT0059 2DD4      007F 1.751  116   85 0.0 0.0 0.0  0.8 27.1  2.75  0
 SGPMESMS RT0089 2D16      007C 20.57  102   82 0.0 0.0 0.0  0.9 16.4  2.840 0

So how does one correctly implement DB2 Piecesize? This has been a source of confusion as witnessed by the following customer question:
Q. I need some clarification on coding PIECESIZE for Non-partitioned indexes (NPI). I have altered an NPI as follows:
ALTER INDEX BSHD.IXCST2 PRIQTY 750000 SECQTY 70000 PIECESIZE 131072 K;
I have run a  REBUILD on the index and have found that DB2 created four pieces for this index.  It appears that the sum of the primary allocation for all four pieces is equal to four times the specified primary quantity.  Since piecesize is defined as 131072 K (which is 128 MB) and primary quantity is defined as 750000 K (which amounts to approximately 750 MB), I would think that up to 6 pieces at 128 MB each would be  allocated.  When they look at the DASD four the four pieces that are created they see:

ISPF 3.4 display:
- Enter "/" to select action               Tracks %Used XT Device
----------------------------------------------------------------------
 DB2X.DSNDBD.BSHD00.IXCST2.I0001.A001        15630   ?  1  3390
 DB2X.DSNDBD.BSHD00.IXCST2.I0001.A002        15630   ?  1  3390
 DB2X.DSNDBD.BSHD00.IXCST2.I0001.A003        15630   ?  1  3390
 DB2X.DSNDBD.BSHD00.IXCST2.I0001.A004        15630   ?  1  3390

Which, if my calculations are correct, add up to about 3 GB total, or about 750 MB per piece.  Does this make sense? Are we missing a step?

A.   The problem is due to specifying a PRIQTY value that is greater than their PIECESIZE value.  Since each piece will max out at the PIECESIZE value, any PRIQTY specified that is greater will be wasted space.  So, the preferred DB2 command would be:
ALTER INDEX  BSHD.IXCST2 PRIQTY 131072 SECQTY 0 PIECESIZE 131072 K ;
     This would (after Rebuild Index) create 6 datasets piecesize if the data occupied a PRIQTY of 750000 previously. In contrast, you could also code the following:

ALTER INDEX  BSHD.IXCST2 PRIQTY 32768 SECQTY 16384 PIECESIZE 131072 K ;

Using SECQTY will allow each data set (piece) to have up to 6 extensions before a new dataset/piece is used 128k = 32k + (6*16k).  I recommend, however, that secqty not be used with piecesize if  at all possible. This avoids creating secondary extents when a new piece would be sufficient.

To choose a PIECESIZE value, divide the size of the non-partitioning index by the number of data sets that you want. For example, to ensure that you have 5 data sets for the non-partitioning index and  your non-partitioning index is 10 MB (and not likely to grow much), specify PIECESIZE 2M. If your non-partitioning index is likely to grow, choose a large value.  Keep in   mind that 32 pieces is the limit  if the underlying table space is not defined as LARGE  (or as DSSIZE 4G or greater) and that the limit is 254 if the table space is defined as LARGE (or as DSSIZE 4G or greater).

Non-partitioning indexes that were created on LARGE tablespaces in Version 5 and migrated to Version 6 can have only 128 pieces.

Keep your PIECESIZE value in mind when you are choosing values for primary and secondary quantities. Ideally, the value of your primary quantity and secondary quantities should be evenly divisible  into PIECESIZE to avoid wasting space.

One might assume that hand placement of the non-partitioned index datasets is necessary.   In a small DB2 subsystem (<250 datasets) without a large DASD SMS pool - yes.  I prefer, however, to let SMS handle the allocations for me. If you have a decent (> 50 volumes) SMS DASD pool for DB2 then I have found that SMS does a fine job at spreading datasets around.  With crafted ACS routines (the logic for SMS allocations) by the storage management folks it is even easier.  A quick check can find if the pieces are on the same DASD following allocation.  T e REUSE parameter may be used for any subsequent Reorgs of the non-partitioned index to ensure the dataset stays in place.

Table 3. Valid values of the PIECESIZE clause                    K units
254 K
512 K
1024 K
2048 K
4096 K
8192 K
16384 K
32768 K
65536 K
131072 K
262144 K
524288 K
1048576 K
2097152 K
4194304 K
8388608 K
16777216 K
33554432 K
67108864 K

As only a specification of the maximum amount of data that a "Piece" can hold and not the actual allocation of storage, PIECESIZE has no effect on primary and secondary space  allocation.  The default for piece size is 2 G (2 GB) for indexes that are backed by table spaces that were created without the LARGE or DSSIZE option, and 4 G (4 GB) for indexes that are backed by table spaces  that were created with the LARGE or DSSIZE option.

As a side note: If you don't have good naming standards and proper ACS routines, a new DB2 PTF (UQ43986) came out June 22, 2000 for DB2 Version 6 which supports the 'DATACLASS' option for tablespaces  and indexes during the define cluster of stogroup data sets. DSNZPARM has been changed by adding 2 new keywords, SMSDCFL and SMSDCIX.  This will allows the user to specify the SMS data class names for tablespaces and indexes. The SMSDCFL is the SMS data class name keyword for all table space data sets and the SMSDCIX is the SMS data class name for all index space data sets.  These 2 new keywords are optional. If either one is specified, all corresponding table space and/or index space data sets will be allocated as a SMS-managed DB2 stogroup defined VSAM LDSs. If you are using SMS and DB2 storage groups, you can use the system parameters SMSDCFL and SMSDCIX to ensure that table spaces and indexes go on separate volumes.

Of course, with IBM Shark DASD and its Parallel Access Volumes (PAV) many I/O headaches disappear and the DB2 Piecesize becomes less important.  In the meantime, until you get your Shark, take a  look at your DB2 DASD response time and determine if the DB2 Piecesize will help you.

created by: Patrick Gannon on 09-Mar-01

Haben Sie noch Anregungen zu diesem Beitrag?