ISPF PANEL Interface REXX to calculate Space-usage, Capacity etc. of DB2 Tables & Indexes and formulas been used.

This article is about an ISPF PANEL Interface REXX program developed to calculate some important properties like Space-usage, Capacity, Peicesize:0, Dssize:0, MaxPieces, %Full etc., for DB2 Tables & Indexes by automating mundane space calculations considering a lot of parameters/formulas/logics/cases each time for a given object.

User Interface ISPF Panel can be invoked by a TSO command (for example TSO DBAT03).

Features:

1. Capability of checking entire or specific objects in the subsystem which are close to their max capacity via %FULL panel input field.
2. User can pass input objects at DB.TS panel input field supports wildcarding, flexibility to use INPUT DSN panel input field in case of random input objects names.
3. As the RTS info is available only at part level for partition objects, report processing at object level produces the RTS space usage at object level.
4. Maximum no: of Pieces a NPI can have (which is not recorded in any system table and has to be calculated manually) is formulated and reported in the output for every index.
5. In case of DSSSIZE 0 or PIECESIZE 0 recorded in system tables, formulates and derives them to a proper value for table and index respectively.
6. Has the capability/speed of scanning around 25k objects in a subsystem less than 3 secs.

Different ways of invoking this REXX process called DBAT03:

• Panel Mode : TSO DBAT03
• Online Mode : TSO DBAT03 ssid dbname.tsname
• Batch?Mode : %DBAT03 ssid dbname.tsname 75 'venkat.output' batch

Sample output report of tables & indexes under different types of tablespaces:

Segmented

Classic Partitioned (Object Level)

UTS Partition By Range (Object Level)

UTS Partition By Growth

LOB

Below is the help menu panel, can be invoked by user pressing PF1 to know about the description of the panel input fields.

Here comes the info on formulas embedded in the Rexx program.

Ever observed the value '0' recorded against piecesize and dssize columns of system catalog tables - sysindexes / systablespace / systablepart and if so, how to resolve them to a proper value ?

Resolving Partitioned Index PIECESIZE = 0 :

Deriving PIECESIZE:0 to the proper value depends on DSSIZE & PGSIZE of Tablespace and PGSIZE of the Index.

FORMULA:
PIECESIZE = MIN((TSDSSIZE/2**20) , (2**12)/MIN(4096,2**12/((TSDSSIZE/2**20)/TSPGSIZE)) * (IXPGSIZE/1024)) * 2**20

TSDSSIZE? IN KB
TSPGSIZE? IN KB
IXPGSIZE? IN? B
PIECESIZE IN KB        

Matrix:

***************PARTITION INDEX PIECESIZE:0 DETERMINATION MATRIX*************
--TABLESPACE-- -------INDEX-------
DSSIZE? PGSIZE? ? ? ? PGSIZE? ? ??
? ? ? ? ? ? ? ? 4K? ?8K? ?16K? 32K
? ? ? ? ? ? ? ?---- ---- ---- ----
? ? 1G? ? ? 4K? ?1G? ?1G? ?1G? ?1G
? ? 1G? ? ? 8K? ?1G? ?1G? ?1G? ?1G
? ? 1G? ? ?16K? ?1G? ?1G? ?1G? ?1G
? ? 1G? ? ?32K? ?1G? ?1G? ?1G? ?1G
? ? 2G? ? ? 4K? ?2G? ?2G? ?2G? ?2G
? ? 2G? ? ? 8K? ?2G? ?2G? ?2G? ?2G
? ? 2G? ? ?16K? ?2G? ?2G? ?2G? ?2G
? ? 2G? ? ?32K? ?2G? ?2G? ?2G? ?2G
? ? 4G? ? ? 4K? ?4G? ?4G? ?4G? ?4G
? ? 4G? ? ? 8K? ?4G? ?4G? ?4G? ?4G
? ? 4G? ? ?16K? ?4G? ?4G? ?4G? ?4G
? ? 4G? ? ?32K? ?4G? ?4G? ?4G? ?4G
? ? 8G? ? ? 4K? ?8G? ?8G? ?8G? ?8G
? ? 8G? ? ? 8K? ?4G? ?8G? ?8G? ?8G
? ? 8G? ? ?16K? ?4G? ?8G? ?8G? ?8G
? ? 8G? ? ?32K? ?4G? ?8G? ?8G? ?8G
? ?16G? ? ? 4K? 16G? 16G? 16G? 16G
? ?16G? ? ? 8K? ?8G? 16G? 16G? 16G
? ?16G? ? ?16K? ?4G? ?8G? 16G? 16G
? ?16G? ? ?32K? ?4G? ?8G? 16G? 16G
? ?32G? ? ? 4K? 32G? 32G? 32G? 32G
? ?32G? ? ? 8K? 16G? 32G? 32G? 32G
? ?32G? ? ?16K? ?8G? 16G? 32G? 32G
? ?32G? ? ?32K? ?4G? ?8G? 16G? 32G
? ?64G? ? ? 4K? 64G? 64G? 64G? 64G
? ?64G? ? ? 8K? 32G? 64G? 64G? 64G
? ?64G? ? ?16K? 16G? 32G? 64G? 64G
? ?64G? ? ?32K? ?8G? 16G? 32G? 64G
? 128G? ? ? 4K 128G 128G 128G 128G
? 128G? ? ? 8K? 64G 128G 128G 128G
? 128G? ? ?16K? 32G? 64G 128G 128G
? 128G? ? ?32K? 16G? 32G? 64G 128G
? 256G? ? ? 4K 256G 256G 256G 256G
? 256G? ? ? 8K 128G 256G 256G 256G
? 256G? ? ?16K? 64G 128G 256G 256G
? 256G? ? ?32K? 32G? 64G 128G 256G

*        

How many pieces a Non-Partitioned Index (NPI) can use ?

Max number of pieces for NPI depends on whether tablespace under which it is defined is: Non-Partitioned (or) Partitioned without DSSIZE clause specified (or) Partitioned with DSSIZE clause specified.

For NPI on Non-Partitioned tablespace (or) Partitioned tablespace created without DSSIZE clause specification : MAXPCS = 32

For NPI on Partitioned tablespace that is created with DSSIZE clause specification : MAXPCS=THEMAXPT (Theoretical maximum number of partitions tablespace can have)

Further THEMAXPT depends on DSSIZE and PGSIZE of tablespace.

FORMULAE
THEORITICAL_MAX_PARTITIONS = MIN( 4096 , 4096/(DSSIZE/(PGSIZE*2**20)) )
DSSIZE IN KB
PGSIZE IN KB        

Matrix:

******************THEORETICAL MAX NUMBER OF PARTITIONS*****************
? ? ? ? ? ? ? ? ? ?-------------------------? ? ? ? ? ? ?
? ? ? ------------|? ? ? ?PGSIZE(KB)? ? ? ? |? ? ? ? ? ??
? ? ?| DSSIZE(KB) |? ?4? ? ?8? ? 16? ? 32? ?|? ? ?MAXSIZE
? ? ?| ---------- | ----- ----- ----- ----- |? ? ?-------
1G? ?|? ? 1048576 |? 4096? 4096? 4096? 4096 |? ? ? ? ? 4T
2G? ?|? ? 2097152 |? 4096? 4096? 4096? 4096 |? ? ? ? ? 8T
4G? ?|? ? 4194304 |? 4096? 4096? 4096? 4096 |? ? ? ? ?16T
8G? ?|? ? 8388608 |? 2048? 4096? 4096? 4096 |? ? ? ? ?32T
16G? |? ?16777216 |? 1024? 2048? 4096? 4096 |? ? ? ? ?64T
32G? |? ?33554432 |? ?512? 1024? 2048? 4096 |? ? ? ? 128T
64G? |? ?67108864 |? ?256? ?512? 1024? 2048 |? ? ? ? 128T
128G |? 134217728 |? ?128? ?256? ?512? 1024 |? ? ? ? 128T
256G |? 268435456 |? ? 64? ?128? ?256? ?512 |? ? ? ? 128T
? ? ? -------------        

Resolving DSSIZE = 0 for Classic Partitioned Tablespaces :

Deriving DSSIZE:0 to the proper value depends on NUMPARTS & PGSIZE of partitioned Tablespace.

Rexx Logic:
/* SIMPLE & SEGMENTED */
IF TSSEGSIZE >= 0 & TSPARTITIONS = 0 & TSDSSIZE = 0 & TSTYPE = ''
? THEN TSDSSIZE = 2*2**20
/* LOB */
ELSE IF TSTYPE = 'O' & TSDSSIZE = 0 THEN TSDSSIZE = 4*2**20
/* CLASSIC PARTITIONED */
ELSE IF TSDSSIZE = 0 THEN
DO
?IF TSPARTITIONS >= 1? ?& TSPARTITIONS <= 16? THEN TSDSSIZE = 4*2**20
?IF TSPARTITIONS >= 17? & TSPARTITIONS <= 32? THEN TSDSSIZE = 2*2**20
?IF TSPARTITIONS >= 33? & TSPARTITIONS <= 64? THEN TSDSSIZE = 1*2**20
?IF TSPARTITIONS >= 65? & TSPARTITIONS <= 254 THEN TSDSSIZE = 4*2**20
?IF TSPARTITIONS >? 254 & TSPGSIZE? ? ? = 4? ?THEN TSDSSIZE = 4*2**20
?IF TSPARTITIONS >? 254 & TSPGSIZE? ? ? = 8? ?THEN TSDSSIZE = 8*2**20
?IF TSPARTITIONS >? 254 & TSPGSIZE? ? ? = 16? THEN TSDSSIZE = 16*2**20
?IF TSPARTITIONS >? 254 & TSPGSIZE? ? ? = 32? THEN TSDSSIZE = 32*2**20
END? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?        

Matrix:

***************PARTITION TABLE DSSIZE:0 DETERMINATION MATRIX**************
---------- ------ ----------
PARTITIONS PGSIZE? ?DSSIZE
? ? ? ? ? ? ?KB? ? ? ?KB
---------- ------ ----------
1? - 16? ? ? ? ? ? ? 4194304 4G
17 - 32? ? ? ? ? ? ? 2097152 2G
33 - 64? ? ? ? ? ? ? 1048576 1G
65 - 254? ? ? ? ? ? ?4194304 4G
>=255? ? ? ? ? ?4? ? 4194304 4G
>=255? ? ? ? ? ?8? ? 8388608 8G
>=255? ? ? ? ? 16? ?16777216 16G
>=255? ? ? ? ? 32? ?33554432 32G
---------- ------ ----------
FORMULAE:
BASED ON - PARTITIONS IF 1P-254P | PARTITIONS & PGSIZE IF >=255P        

All of the above Rexx Formulas / Logics / Determination Matrices were researched & documented so far in my DBA life and i hope this information can be helpful for some DB2 users who are in search for the same.

All the best !

Thanks,
Kajuluri Venkata Ashok,
z/OS Mainframe DB2 DBA.