Using Unicenter Database Analyzer to be Proactive and Minimize the Number of REORGs Executed Against an Object

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

Using Unicenter Database Analyzer Proactively

The following example illustrates a proactive approach to determining which DB2 objects are in need of reorganization. When used consistently the proactive approach can limit and ultimately decrease the frequency of REORGs by identifying key recurrent factors.

Many customers already depend on PDA extracts to generate their DB2 reorganization jobs. The following example illustrates how to incorporate a proactive approach into these same extracts so that the underlying reasons for the REORGs are tracked and saved and can later be analyzed.

While it has been common practice to simply schedule reorganizations as access times increase, this new process incorporates a proactive approach by saving the reason(s) for a reorganization every time one is executed. Violating metrics such as clusterratios, extents, faroffpos, pseudo deleted rows, etc are saved into a DB2 table for later analysis. Having this information available, it is possible to determine if a pattern exists. One example could be that a specific tablespace or certain partitions are being selected for reorganization every week because of clusterratio. Now, instead of reorganizing the object every week, one can simply increase TS PCTFREE or FREEPAGE in order to eliminate the need for a reorganization.

Follow these steps to utilize the proactive approach.

  1. Create a table that will hold the reasons for REORG. Here is one example:

    Figure 1

  • Create a model to be used in the PDA Action Procedure(s). Notice the INSERT statement in the following example reflects the columns of the above listed "proactive table". Also notice the SET command correlates with the action conditions in the next set of illustrations.

    Figure 2

  • Modify the Action Procedure(s) within the PDA extract(s) that are generating REORGS. All that is needed is one additional utility code (US = User Application), but it is possible to have a US utility code for every action condition used.

    This is an example of existing action conditions assigned to an Action Procedure:

    Figure 3

    1. Specify utility code ='US'
    2. When prompted, specify the model name just created.

    Figure 4

  • Update the CONDITION TYPE and extend the already selected CONDITIONS with the values to be transferred to the later generated action JCL. The %USERx values will be populated with the specified columns from the PDA statistics table, and since the INSERT statements have these %USERx values specified, the "proactive table" will be populated as specified.

    Specify an "S" on the line next to the "Populate USERx..." Action Condition to include these conditions. If additional details are desired, specify 'H' prior to 'S'.

    Figure 5

    The INSERTS will be executed along with the rest of the utility codes specified in the Action Procedure. Below is a sample of these generated INSERT statements. In order to better view what is generated, the associated Alter and Reorg steps have been removed:

    Figure 6

  • Once this method has been active for a while, it will be possible to start the proactive approach by analyzing the content for patterns.

    Analyzing the matrix below, the following observations might lead to fewer reorganizations and application performance:

    1. Tablespace PTITSMG1 has been selected for reorganization seven times within a short period. One possible solution to avoid frequent reorganizations could be to alter the secondary quantity to be higher or set to -1 using DB2 V8 or higher. To verify the tablespace really is growing, the PDA Query Facility option 9, can be used to look at the historical statistics.
    2. The index PTMG5_GLOBIX_0400 has been selected for reorganization 5 times due to non optimal leaf pages. A possible solution to eliminate some of these reorganizations could be to allocate more freespace to better facilitate INSERTS if this is the cause. This can also be verified by using PDA Query Facility option 9, and view the historical information for the specific index.

Figure 7

Alternative Solution - One Model per Condition.

An alternative solution to having all the conditions defined in one model is to have individual models for every condition.

Follow these steps when using one model for each condition.

  1. Create the necessary models in the highlvl.MODEL library. In this example four index and two tablespace reorg conditions are to be captured, so six models will be needed:

    Figure 8

    Figure 9

    Figure 10

    Figure 11

    Figure 12

    Figure 13

  • Create one action procedure for each model:

    Figure 14

  • Select a condition. Each Action Procedure will host ONE condition reflecting a condition from the Action Procedure used to schedule the REORG. The USER symbolics transfer the actual violating metric value(s) to the model for eventual insertion into the "proactive table".

    Figure 15

  • Once the Action Procedures have been created for every condition, all that is needed is to tie these Action Procedures to the Extract Procedure(s):

    The generated Action JCL will have one SQL insert step for every condition met per object. This procedure is useful when there is a need to specify more than four %USERx for a tablespace or index.

    Figure 16