How to generate a reorg job for partitioned objects as one reorg where the size of the partitions is varied using Database Analyzer for Db2
search cancel

How to generate a reorg job for partitioned objects as one reorg where the size of the partitions is varied using Database Analyzer for Db2

book

Article ID: 54456

calendar_today

Updated On:

Products

Database Analyzer for DB2 for z/OS

Issue/Introduction

Database Analyzer for Db2 for z/OS (PDA) provides several methods using PDA model symbolic variables to calculate space allocations used in allocating various work
files needed by the utilities that are generated based on Action Procedures.
The calculated size using these methods reflects the size of all partitions, not just the first partition.

Resolution

In generated reorg jobs via PDA Action Procedures tied to Extract or Execution Procedures for partitioned tablespaces, symbolic variables like ROSYSULD, ROSYSREC,
REORGP contain a value based on partition 1.  If you have a small partition 1 and large partitions 2,3 etc., the generated JCL can have a space parameter which is too
small for the intermediate work files.

Example IC DD statement in model:

#IF(%CALC(%ROSYSREC*%PARTTOT),GT,300)
// SPACE=(TRK,(4500,4500),RLSE),
#ELSE
// SPACE=(TRK,(%CALC(%ROSYSREC*%PARTTOT*15),500),RLSE),
#ENDIF
// DSN=%FLQIC..%SYSID..%DBNAME..%TSNAME..T&LJDAY.&LHR.&LMIN.
#ENDIF

Example partitioned Tablespace

Enter "/" to select action             Tracks %Used XT  Device
--------------------------------------------------------------------
ssid.DSNDBD.dbname.tsname.J0001.A001            15    ?   1  3390
ssid.DSNDBD.dbname.tsname.J0001.A002         26355    ?  25  3390
ssid.DSNDBD.dbname.tsname.J0001.A003         24630    ?  39  3390
ssid.DSNDBD.dbname.tsname.J0001.A004         23385    ?  32  3390
ssid.DSNDBD.dbname.tsname.J0001.A005         28500    ?  28  3390

The %REORGP could be solved by I,SUM(RATS_PAGES_REORG):

The Action Conditions that end up setting %USER1 through %USER4 allow a few functions (SUM, MAX as examples).

Use SUM to get all partition values or MAX to get the MAX and multiply it by the number of partitions.

r11.5 sp2 examples use PDA tables (and support TSP type processing)
   _ (TS) Populate USER1 w/PDA stats column   P | I,SUM(RATS_PAGES_REORG)
 r11.5 sp3 examples changing to RTS
   _ (TS)/(TSP) Set USER1 w/PDA/RTS/DB2 Col   R | I,SUM(TSS.NACTIVE)
   _ (TS)/(TSP) Set USER2 w/PDA/RTS/DB2 Col   R | I,SUM(TSS.SPACE)

Following symbolics : %ROSORT, %ROSYSREC, %ROSYSUT1, %ROSORTWK , %ROSYSULD are mainly for temporary space so they just need to be
reasonably close. Values in terms of 4K pages can be divided by 180 (3390) to get cyl (%CALC).

There is another PDA table column you could use: I,SUM(RATS_SYSUT1). It could be used instead of %ROSYSUT1 and perhaps %ROSORT.

Between these two PDA table columns they should be close enough for the temporary dataset space you require along with a reasonable secondary allocation amount.
Pick one of the two PDA table columns that is the closest to your needs for the other symbolics.