Optimize Reorganizations of Partitioned Objects with Database Analyzer for Db2
Article ID: 56136
Updated On:
Products
Issue/Introduction
Database Analyzer for Db2 for z/OS (PDA) reorg optimization for partitioned objects.
Resolution
Database Analyzer for Db2 for z/OS (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 Db2 tools 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. */
Feedback
