Optimize Reorganizations of Partitioned Objects with PDA

Document ID : KB000056136
Last Modified Date : 14/02/2018
Show Technical Document Details

Many years ago Unicenter Database Analyzer for DB2 (PDA) introduced a new keyword in the PDA Parmlib with the name: DISABLE_AUTO_IX_RO_SUPP. Before we step into the news, let's have a look at how PDA developed over time when it comes to the topic of generating reorganizations of partitioned objects.

Let's assume a tablespace PART12 with 12 partitions, one partitioned index PART12IX and two non-partitioned indexes (NPI's) PART12NPI1 and PART12NPI2.

Let's also assume we have an Action Procedure where it's specified that ALTER (AL), REORG (RO) must be generated if Clusterratio is below 94, Extents are > 15, or Pseudo-Deleted RIDS > 5%.

Let's also assume that Tablespace partitions 1,4,7 and Index Partitions 4,7,9 and one of the NPI's have hit one or more of these thresholds. The first versions of PDA would generate the following job-steps in the Action JCL:

ALTER		TS	PART12.001
REORG		TS	PART12.001
ALTER		TS	PART12.004
REORG		TS	PART12.004
ALTER		TS	PART12.007		
REORG		TS	PART12.007
ALTER		NPI	PART12NPI2	
REORG		NPI	PART12NPI2
ALTER		IX	PART12IX.004
REORG		IX	PART12IX.004
ALTER		IX	PART12IX.007
REORG		IX	PART12IX.007
ALTER		IX	PART12IX.009
REORG		IX	PART12IX.009

So a total of 14 steps were generated. Many customers quickly noticed that this was not always the optimal way of executing reorganizations. The situations we're talking about, is where a tablespace partition AND the corresponding index partition both are being reorganized, since when you reorganize the tablespace partition, the corresponding index partition is also inherently reorganized and since PDA (at that time) also generated the index partition reorg - this was an unnecessary step.

Enough customers indicated this was a limitation so PDA was enhanced to always automatically suppress the reorganization of all related indexes when the tablespace is reorganized. This included suppressing of NPIs. This caused another problem because when a tablespace partition is reorganized the NPIs are only updated in place to correct the RID values and are not reorganized. This means that ALTERs of NPI sizes would only take place in the DB2 catalog but not physically take place by resizing the underlying datasets.

Then several months later enough customers indicated now that the NPIs being suppressed was more of a problem than having extra index reorganizations. So a new PARMLIB(PDA) keyword was introduced allowing the user to choose to disable the automatic index suppression feature or not using the keyword: DISABLE_AUTO_IX_RO_SUPP (Y or N).

Two different values could be specified. Specifying (Y) resulted in running "the good old way of always generating all index reorganizations". If you instead specify (N), then PDA will remove the indexspace reorganizations where the corresponding tablespace partition was reorganized. In order to make this real effective, PDA will also have to "move" the index ALTER up in front of the tablespace partition reorganization, so this is how the generated Action JCL will look like:

ALTER		TS	PART12.001
ALTER		NPI	PART12NPI2	
-----------------------------------------------------------
REORG		TS	PART12.001
-----------------------------------------------------------
ALTER		TS	PART12.004
ALTER		IX 	PART12IX.004
-----------------------------------------------------------
REORG		TS	PART12.004
-----------------------------------------------------------
ALTER 		TS	PART12.007
ALTER		IX	PART12IX.007
-----------------------------------------------------------		
REORG		TS	PART12.007
-----------------------------------------------------------
ALTER		IX	PART12IX.009
-----------------------------------------------------------
REORG		IX	PART12IX.009

So - we eliminated a lot of job steps, and we got rid of the redundant index partition reorganizations.

As discussed above the PDA feature index reorganization suppress is an all or none approach. The one challenge that still arises was when doing partition reorganizations AND non-partitioned indexes do exist. Again beside from removing the redundant index partition reorganizations, PDA also removes the NPI reorganizations. The NPI(s) might be very defragmented over time due to this, and this might lead to worse performance for SQL accessing this index.

And now we finally came to the purpose of this article. On P01D the PARMLIB(PDA) keyword now has a third possible value (O) - Only disable for NPIs. By specifying (O), PDA will leave the NPI reorganization in the generated Action JCL, and the generated steps will look like this:

ALTER		TS	PART12.001
-----------------------------------------------------------
REORG		TS	PART12.001
-----------------------------------------------------------
ALTER		TS	PART12.004
ALTER		IX 	PART12IX.004
-----------------------------------------------------------
REORG		TS	PART12.004
-----------------------------------------------------------
ALTER 		TS	PART12.007
ALTER		IX	PART12IX.007
-----------------------------------------------------------		
REORG		TS	PART12.007
-----------------------------------------------------------
ALTER		IX	PART12IX.009
-----------------------------------------------------------
REORG		IX	PART12IX.009
-----------------------------------------------------------
ALTER		NPI	PART12NPI2
-----------------------------------------------------------
REORG		NPI	PART12NPI2

In order to update the PARMLIB(PDA), you can either use the CA DB2 Main Menu and chose EP, then you select PDA and you will be walked through all the Parmlib keywords. This is the recommended method every time you upgrade the DB2 tools. In this way you can be sure to see any new Parmlib keywords. Another option is to edit the PARMLIB(PDA) member, and specify (O), as per the example below:

DISABLE_AUTO_IX_RO_SUPP (N) /* Y = Disable auto IX reorg   */
                            /*     suppression feature.    */
                            /* N = Allow the auto IX reorg */
                            /*     suppression feature.    */
                            /*     (Default)               */
                            /* O = Allow the auto IX reorg */
                            /*     suppression EXCEPT for  */
                            /*     NPI(s) on a part TS.    */