Bharath's Tech Blog: July 2016

Jul 23, 2016

Virtual Indexes for Query performance optimization, in DB2 v10 for z/OS

The table DSN_VIRTUAL_INDEXES, enables Optimization Tools to test the effect of creation and/or dropping of Indices on the performance of SQL queries.

Optimizer tools like Data Studio Query Optimizer or Dell SQL optimizer will make an entries into DSN_VIRTUAL_INDEXES Table when trying to optimize the SQL query performance. It’s not advised to make entries into the Table manually.

We don’t have any SQL query optimizer tool installed, and I wanted to test whether an Index creation on the Table will improve the SQL query performance. For that, I did the following.

Step 1: I created DSN_VIRTUAL_INDEXES Table.

DDL for DSN_VIRTUAL_INDEXES Table can be found in SDSNSAMP library. Below is the DDL I used for the creation of Table.

CREATE TABLE RFO7936.DSN_VIRTUAL_INDEXES (

TBCREATOR VARCHAR(128) FOR MIXED DATA NOT NULL,

TBNAME VARCHAR(128) FOR MIXED DATA NOT NULL,

IXCREATOR VARCHAR(128) FOR MIXED DATA NOT NULL,

IXNAME VARCHAR(128) FOR MIXED DATA NOT NULL,

ENABLE CHARACTER(1) FOR MIXED DATA NOT NULL,

MODE CHARACTER(1) FOR MIXED DATA NOT NULL,

UNIQUERULE CHARACTER(1) FOR MIXED DATA NOT NULL,

COLCOUNT SMALLINT NOT NULL,

CLUSTERING CHARACTER(1) FOR MIXED DATA NOT NULL,

NLEAF INTEGER NOT NULL,

NLEVELS SMALLINT NOT NULL,

INDEXTYPE CHARACTER(1) FOR MIXED DATA NOT NULL WITH DEFAULT,

PGSIZE SMALLINT NOT NULL,

FIRSTKEYCARDF DOUBLE NOT NULL DEFAULT -1,

FULLKEYCARDF DOUBLE NOT NULL DEFAULT -1,

CLUSTERRATIOF DOUBLE NOT NULL DEFAULT -1,

PADDED CHARACTER(1) FOR MIXED DATA NOT NULL WITH DEFAULT,

COLNO1 SMALLINT WITH DEFAULT NULL,

ORDERING1 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO2 SMALLINT WITH DEFAULT NULL,

ORDERING2 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO3 SMALLINT WITH DEFAULT NULL,

ORDERING3 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO4 SMALLINT WITH DEFAULT NULL,

ORDERING4 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO5 SMALLINT WITH DEFAULT NULL,

ORDERING5 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO6 SMALLINT WITH DEFAULT NULL,

ORDERING6 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO7 SMALLINT WITH DEFAULT NULL,

ORDERING7 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO8 SMALLINT WITH DEFAULT NULL,

ORDERING8 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO9 SMALLINT WITH DEFAULT NULL,

ORDERING9 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO10 SMALLINT WITH DEFAULT NULL,

ORDERING10 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO11 SMALLINT WITH DEFAULT NULL,

ORDERING11 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO12 SMALLINT WITH DEFAULT NULL,

ORDERING12 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO13 SMALLINT WITH DEFAULT NULL,

ORDERING13 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO14 SMALLINT WITH DEFAULT NULL,

ORDERING14 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO15 SMALLINT WITH DEFAULT NULL,

ORDERING15 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO16 SMALLINT WITH DEFAULT NULL,

ORDERING16 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO17 SMALLINT WITH DEFAULT NULL,

ORDERING17 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO18 SMALLINT WITH DEFAULT NULL,

ORDERING18 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO19 SMALLINT WITH DEFAULT NULL,

ORDERING19 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO20 SMALLINT WITH DEFAULT NULL,

ORDERING20 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO21 SMALLINT WITH DEFAULT NULL,

ORDERING21 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO22 SMALLINT WITH DEFAULT NULL,

ORDERING22 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO23 SMALLINT WITH DEFAULT NULL,

ORDERING23 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO24 SMALLINT WITH DEFAULT NULL,

ORDERING24 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO25 SMALLINT WITH DEFAULT NULL,

ORDERING25 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO26 SMALLINT WITH DEFAULT NULL,

ORDERING26 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO27 SMALLINT WITH DEFAULT NULL,

ORDERING27 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO28 SMALLINT WITH DEFAULT NULL,

ORDERING28 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO29 SMALLINT WITH DEFAULT NULL,

ORDERING29 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO30 SMALLINT WITH DEFAULT NULL,

ORDERING30 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO31 SMALLINT WITH DEFAULT NULL,

ORDERING31 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO32 SMALLINT WITH DEFAULT NULL,

ORDERING32 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO33 SMALLINT WITH DEFAULT NULL,

ORDERING33 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO34 SMALLINT WITH DEFAULT NULL,

ORDERING34 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO35 SMALLINT WITH DEFAULT NULL,

ORDERING35 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO36 SMALLINT WITH DEFAULT NULL,

ORDERING36 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO37 SMALLINT WITH DEFAULT NULL,

ORDERING37 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO38 SMALLINT WITH DEFAULT NULL,

ORDERING38 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO39 SMALLINT WITH DEFAULT NULL,

ORDERING39 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO40 SMALLINT WITH DEFAULT NULL,

ORDERING40 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO41 SMALLINT WITH DEFAULT NULL,

ORDERING41 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO42 SMALLINT WITH DEFAULT NULL,

ORDERING42 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO43 SMALLINT WITH DEFAULT NULL,

ORDERING43 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO44 SMALLINT WITH DEFAULT NULL,

ORDERING44 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO45 SMALLINT WITH DEFAULT NULL,

ORDERING45 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO46 SMALLINT WITH DEFAULT NULL,

ORDERING46 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO47 SMALLINT WITH DEFAULT NULL,

ORDERING47 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO48 SMALLINT WITH DEFAULT NULL,

ORDERING48 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO49 SMALLINT WITH DEFAULT NULL,

ORDERING49 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO50 SMALLINT WITH DEFAULT NULL,

ORDERING50 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO51 SMALLINT WITH DEFAULT NULL,

ORDERING51 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO52 SMALLINT WITH DEFAULT NULL,

ORDERING52 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO53 SMALLINT WITH DEFAULT NULL,

ORDERING53 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO54 SMALLINT WITH DEFAULT NULL,

ORDERING54 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO55 SMALLINT WITH DEFAULT NULL,

ORDERING55 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO56 SMALLINT WITH DEFAULT NULL,

ORDERING56 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO57 SMALLINT WITH DEFAULT NULL,

ORDERING57 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO58 SMALLINT WITH DEFAULT NULL,

ORDERING58 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO59 SMALLINT WITH DEFAULT NULL,

ORDERING59 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO60 SMALLINT WITH DEFAULT NULL,

ORDERING60 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO61 SMALLINT WITH DEFAULT NULL,

ORDERING61 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO62 SMALLINT WITH DEFAULT NULL,

ORDERING62 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO63 SMALLINT WITH DEFAULT NULL,

ORDERING63 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL,

COLNO64 SMALLINT WITH DEFAULT NULL,

ORDERING64 CHARACTER(1) FOR MIXED DATA WITH DEFAULT NULL

)

IN LDARFO.VIRIDX

AUDIT NONE

DATA CAPTURE NONE

NOT VOLATILE

APPEND NO;

Step 2: I made the following entry into the DSN_VIRTUAL_INDEXES Table.

TBCREATOR	LDARFO
TBNAME	PAEMPLOYEE
IXCREATOR	RFO7936
IXNAME	VIR_IDX
ENABLE	Y
MODE	C
UNIQUERULE	D
COLCOUNT	1
CLUSTERING	N
NLEAF	384715
NLEVELS	-1
INDEXTYPE	2
PGSIZE	4
FIRSTKEYCARDF	384715
FULLKEYCARDF	384715
CLUSTERRATIOF	-1
PADDED	Y
COLNO1	36
ORDERING1	A

Above entry says that, I want to test whether creation of a non-unique Index on 36^th column of PAEMPLOYEE Table will improve query performance or not.

Step 3: I executed the following EXPLAIN statement in SPUFI.

EXPLAIN PLAN SET QUERYNO = 2 FOR

SELECT * FROM LDARFO.PAEMPLOYEE WHERE SECURITY_NBR='300668'

Step 4: Once the statement mentioned in Step 3 is executed, we can query PLAN_TABLE to find out whether the Optimizer has considered the Virtual Index for SQL query optimization.

Below entry is found in PLAN_TABLE, which shows that the Virtual Index is considered by the Optimizer.

We have to make sure that the statistics values for the virtual index are valid. Having something like -1 for the number of leaf pages will not help Optimizer in considering the Virtual Index for the optimization.

As long as you have 'Y' in the ENABLE column for the Index in DSN_VIRTUAL_INDEXES, and the Index definition is valid, it should be considered by the Optimizer when access path selection is performed for a query targeting the underlying table.

Note: The above explained is a simple scenario, but can follow the same process for different complex scenarios.

Jul 18, 2016

Row Change TIMESTAMP

DBAs are sometimes asked by the Application teams to help them in finding out the row/s updated Timestamp in a DB2 Table.

Row/s updated Timestamp can be found in multiple ways.

1. UPDATE Trigger

More details about Triggers can be found in http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/sqlref/src/tpc/db2z_sql_createtrigger.html

2. Temporal Tables

More details about the Temporal Tables can be found in

http://www.ibm.com/developerworks/data/library/techarticle/dm-1410temporal-tables-db2zos/index.html

3. ROW CHANGE expression

More details about the ROW CHANGE expression can be found in

http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/sqlref/src/tpc/db2z_rowchangeexpression.html

I found ROW CHANGE expression easy to implement, compared to other approaches. Below mentioned testing has been done to find out row updated Timestamp using ROW CHANGE expression.

Step 1:

CREATE TABLE LPARFO.TEST1

(SNO INTEGER NOT NULL WITH DEFAULT

,ID CHAR(10) NOT NULL WITH DEFAULT

,ROWCHANGE TIMESTAMP NOT NULL GENERATED FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP)

IN DATABASE LPARFO;

Step 2:

INSERT INTO LPARFO.TEST1(SNO,ID) VALUES(1,'RFO7936');

INSERT INTO LPARFO.TEST1(SNO,ID) VALUES(2,'rfo7936');

Step 3:

UPDATE LPARFO.TEST1 SET SNO=3 WHERE SNO=1;

Step 4:

SELECT ROW CHANGE TIMESTAMP FOR LPARFO.TEST1 FROM LPARFO.TEST1;

Jul 11, 2016

Process Automation for Index Compression

In our DB2 for z/OS setup, all Tablespaces are compressed but not the Indexes and Page Size for all Indexes is 4K.

I developed a Process Automation for implementing Index Compression, and this process will be scheduled to be executed on half-yearly basis.

Below are some details related to Index Compression.

è There’s no hardware assist.

An Index is compressed only on disk.

è No dictionary is used or created.

a. With no dictionary, there’s no need to run the REORG/LOAD prior to actually compressing Index data. When Compression is turned on for an Index, Key and RID Compression begins immediately.

b. If an Index is altered with COMPRESS NO, that Index will be placed in REBUILD-pending (RBDP) or Pageset REBUILD-pending (PSRBD) state depending on the Index type.

c. REBUILD INDEX or REORG can be used to remove the Restrictive status.

è Only Leaf Pages are compressed (no Row-Level Compression).

However, even the non-Leaf Pages are read into the I/O work area and then copied (instead of being expanded, since they aren't compressed) into the Buffer Pool.

è A Compressed Index Page will be 4K on disk. When brought into the Buffer Pool, it’s expanded to 8K, 16K or 32K (based on the chosen PGSIZE); when moved back to disk, the Page is compressed.

The effectiveness of Index Compression is higher depending on the BUFFERPOOL size chosen.

è Unlike Data Compression, Log records for Index Keys are not compressed and Image Copies for Indexes are not compressed.

However, BSAM Compression can be used to compress Index image Copies on DASD, and Tape controllers are capable of compressing all data sets on Tape.

Below are the pre-requisites for the Index Compression.

1. For a Compressed Index, the Buffer Pool can’t be a 4K Buffer Pool. The Buffer Pool size must be 8 KB, 16 KB, or 32 KB.

2. PGSIZE has to be more than 4K, to avoid increase in Index Levels (in Compressed Index) and Page Splits (are expensive in a Data-Sharing environment).

Below are the steps involved in the Process Automation I developed.

Step 1: To get the VSAMs of the Indexes suitable for Compression.

Approach: SQL Query

Process: I coded a SQL query with Common-Table Expressions (CTE) to get the VSAMs details.

The SQL does the following.

1. Get the DBNAME, IXNAME, SUM(SPACE) details from

SYSIBM.SYSINDEXSPACESTATS for the Indexes having SUM(SPACE) > 10 GB

2. Get the DBNAME, IXNAME, SUM(SPACE) details from

SYSIBM.SYSINDEXSPACESTATS for the Indexes having SUM(SPACE) > 5 GB

3. Get the DBNAME, IXNAME, SUM(SPACE) details from

SYSIBM.SYSINDEXSPACESTATS for the Indexes having SUM(SPACE) > 1 GB

4. Get the DBNAME, IXNAME, SUM(SPACE) details from

SYSIBM.SYSINDEXSPACESTATS for all the Indexes

5. Calculate the Space Savings after Compression for all Indexes with

SUM(SPACE) > 10 GB

6. Calculate the Space Savings after Compression for all Indexes with

SUM(SPACE) > 5 GB

7. Calculate the Space Savings after Compression for all Indexes with

SUM(SPACE) > 1 GB

8. Calculate the Space Savings after Compression for all Indexes

9. Find the wow factor for the each category of Indexes.

wow factor is a value assigned for each category of Indexes based on some Mathematical calculations.

10. Find out the category of Indexes having the highest wow factor value.

11. Get the Partition Number for the category of Indexes having highest wow

factor value.

12. Generate the VSAM details for the category of Indexes having the highest wow factor value.

Step 2: To build & execute the DSN1COMP JCL for the VSAMs got in the Step1.

Approach: REXX code & JCL

I coded the below REXX routine that will build the DSN1COMP JCL for the all VSAMs generated in Step 1.

/*REXX*/

I = 0

J = 1

CALL READ_INP

CALL JCL_HEADER

CALL BUILD_JCL

CALL TERM_PARA

READ_INP:

"EXECIO * DISKR SYSUT1 (STEM REC. FINIS"

IF RC <> 0 THEN SAY 'INPUT FILE READ FAILED WITH RC =' RC

ELSE NOP

RETURN;

TERM_PARA:

FREE ALL

"EXECIO 0 DISKR SYSUT1 (FINIS"

"EXECIO 0 DISKW SYSUT2 (FINIS"

EXIT 0

JCL_HEADER:

X = '//PDBCOMP1 JOB (0000,00000000),'DSN1COMP','

SAY X

PUSH X

"EXECIO 1 DISKW SYSUT2"

X = '// CLASS=E,MSGCLASS=X,NOTIFY=&SYSUID,REGION=0M'

SAY X

PUSH X

"EXECIO 1 DISKW SYSUT2"

X = '//*******************************************************************'

SAY X

PUSH X

"EXECIO 1 DISKW SYSUT2"

X = '//JOBLIB DD DSN=EGP1X.SDSNEXIT,DISP=SHR'

SAY X

PUSH X

"EXECIO 1 DISKW SYSUT2"

X = '// DD DSN=DSNEGP1.SDSNLOAD,DISP=SHR'

SAY X

PUSH X

"EXECIO 1 DISKW SYSUT2"

X = '//*******************************************************************'

SAY X

PUSH X

"EXECIO 1 DISKW SYSUT2"

RETURN;

BUILD_JCL:

DO I = 1 TO REC.0

AA = STRIP(REC.I,"B")

CALL DSN1COMP_PARA

J = J + 1

END

RETURN;

DSN1COMP_PARA:

X = '//COMP'J' EXEC PGM=DSN1COMP'

SAY X

PUSH X

"EXECIO 1 DISKW SYSUT2"

X = '//SYSPRINT DD SYSOUT=*'

SAY X

PUSH X

"EXECIO 1 DISKW SYSUT2"

X = '//SYSABEND DD SYSOUT=*'

SAY X

PUSH X

"EXECIO 1 DISKW SYSUT2"

X = '//SYSUT1 DD DISP=SHR,DSN='AA''

SAY X

PUSH X

"EXECIO 1 DISKW SYSUT2"

X = '//*******************************************************************'

SAY X

PUSH X

"EXECIO 1 DISKW SYSUT2"

RETURN;

I coded the below JCL for executing the REXX routine.

The input to the JCL is the output of SQL query output generated in Step 1, and the output is the DSN1COMP JCL built by the REXX routine.

The DSN1COMP JCL will be submitted automatically.

//PDBIX001 JOB (0000,00000000),'REXX JCL',

// CLASS=E,MSGCLASS=X,NOTIFY=&SYSUID,REGION=0M

//STEP001 EXEC PGM=IKJEFT01,PARM='DSN1JCL'

//SYSPROC DD DISP=SHR,DSN=RFO7936.PROCAUTO.IXCOMP

//SYSTSPRT DD SYSOUT=*

//SYSOUT DD SYSOUT=*

//SYSTSIN DD DUMMY

//SYSUT1 DD DISP=SHR,DSN=RFO7936.IXVSAM.OUTPUT

//SYSUT2 DD SYSOUT=(E,INTRDR)

//*

Step3: To process the output generated in Step2 and insert the details into a DB2 Table.

Approach: REXX code

I coded the below REXX routine for parsing the DSN1COMP output and load the details into a DB2 Table.

/*REXX*/

I = 0

J = 0

EOF = 0

CALL READ_INPUT

CALL CONNECT_DB2

CALL PROCESS_PARA

CALL TERM_PARA

READ_INPUT:

"EXECIO * DISKR SYSUT1 (STEM REC. FINIS"

IF RC <> 0 THEN SAY 'INPUT FILE READ FAILED WITH RC =' RC

ELSE NOP

RETURN;

CONNECT_DB2:

ADDRESS TSO "SUBCOM DSNREXX"

IF RC <> 0 THEN DO

S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')

IF S_RC <> 0 THEN

SAY 'PROBLEM IN CONNECTING TO DB2'

END

ADDRESS DSNREXX

"CONNECT" EGP1

IF SQLCODE <> 0 THEN CALL SQLCA

ELSE NOP

RETURN;

TERM_PARA:

ADDRESS DSNREXX "DISCONNECT"

S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')

FREE ALL

"EXECIO 0 DISKR SYSUT1 (FINIS"

"EXECIO 0 DISKW SYSUT2 (FINIS"

EXIT 0

PROCESS_PARA:

DO I = 1 TO REC.0

A = STRIP(REC.I,"B")

PARSE VAR A DSN TXT

DSN = STRIP(DSN,"B")

IF DSN = 'DSN1998I' THEN

PARSE VAR TXT TX1 TX2 TX3 TX4 TX5

PARSE VAR TX4 AA '.' BB '.' CC '.' DD '.' EE '.' FF

DB = SUBSTR(STRIP(CC,"B"),1,8)

IX = SUBSTR(STRIP(DD,"B"),1,10)

PT = SUBSTR(STRIP(FF,"B"),2,3)

CALL DSN1COMP_DET

SAY DB IX PT REP1 REP2

/* PUSH DB IX PT REP1 REP2

"EXECIO 1 DISKW SYSUT2"*/

END

ELSE NOP

END

RETURN;

DSN1COMP_DET:

J = I

J = J + 8

B = STRIP(REC.J,"B")

PARSE VAR B LPC TXT1

IF TXT1 = 'REQUESTED LEAF LIMIT NOT REACHED' THEN

J = J + 1

B = STRIP(REC.J,"B")

PARSE VAR B LPC TXT1

IF TXT1 = 'EMPTY - NO ESTIMATE POSSIBLE' THEN RETURN

ELSE CALL COMP_DET

END

ELSE CALL COMP_DET

RETURN;

COMP_DET:

LPC = STRIP(LPC,"B")

LF_PG = SPACE(TRANSLATE(LPC,'',','),0)

DO UNTIL LENGTH(LF_PG) = 12

LF_PG = LF_PG||' '

END

J = J + 3

C = STRIP(REC.J,"B")

PARSE VAR C IXSIZ TXT2

IXSIZ = STRIP(IXSIZ,"B")

IX_SZ_KB = SPACE(TRANSLATE(IXSIZ,'',','),0)

DO UNTIL LENGTH(IX_SZ_KB) = 12

IX_SZ_KB = IX_SZ_KB||' '

END

J = J + 1

C = STRIP(REC.J,"B")

PARSE VAR C IXCOMP TXT2

IXCOMP = STRIP(IXCOMP,"B")

IX_COMP_SZ_KB = SPACE(TRANSLATE(IXCOMP,'',','),0)

DO UNTIL LENGTH(IX_COMP_SZ_KB) = 12

IX_COMP_SZ_KB = IX_COMP_SZ_KB||' '

END

J = J + 7

D = STRIP(REC.J,"B")

PARSE VAR D PG8 TXT3

PG8 = STRIP(PG8,"B")

PG8_SAV = PG8

IF LENGTH(PG8_SAV) = 1 THEN PG8_SAV = '0'||PG8_SAV

J = J + 3

E = STRIP(REC.J,"B")

PARSE VAR E BUF8 TXT4

IF BUF8 = 'No' THEN DO

BUF8_WAS = 00

J = J + 5

END

ELSE DO

IF LENGTH(BUF8) = 1 THEN BUF8_WAS = '0'||BUF8

ELSE BUF8_WAS = BUF8

J = J + 6

END

F = STRIP(REC.J,"B")

PARSE VAR F PG16 TXT5

PG16 = STRIP(PG16,"B")

PG16_SAV = PG16

IF LENGTH(PG16_SAV) = 1 THEN PG16_SAV = '0'||PG16_SAV

J = J + 3

G = STRIP(REC.J,"B")

PARSE VAR G BUF16 TXT6

IF BUF16 = 'No' THEN DO

BUF16_WAS = 00

J = J + 5

END

ELSE DO

BUF16_WAS = BUF16

IF LENGTH(BUF16_WAS) = 1 THEN BUF16_WAS = '0'||BUF16_WAS

J = J + 6

END

H = STRIP(REC.J,"B")

PARSE VAR H PG32 TXT7

PG32 = STRIP(PG32,"B")

PG32_SAV = PG32

IF LENGTH(PG32_SAV) = 1 THEN PG32_SAV = '0'||PG32_SAV

J = J + 3

K = STRIP(REC.J,"B")

PARSE VAR K BUF32 TXT8

IF BUF32 = 'No' THEN BUF32_WAS = 00

ELSE BUF32_WAS = BUF32

IF LENGTH(BUF32_WAS) = 1 THEN BUF32_WAS = '0'||BUF32_WAS

REP1= LF_PG IX_SZ_KB IX_COMP_SZ_KB PG8_SAV BUF8_WAS

REP2= PG16_SAV BUF16_WAS PG32_SAV BUF32_WAS

SQLSTMT="INSERT INTO LPARFO.IXCOMP VALUES",

"('"DB"','"IX"',"PT","LF_PG","IX_SZ_KB",",

""IX_COMP_SZ_KB","PG8_SAV",",

""BUF8_WAS","PG16_SAV","BUF16_WAS","PG32_SAV","BUF32_WAS",",

"CURRENT TIMESTAMP);"

ADDRESS DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"

ADDRESS DSNREXX "EXECSQL EXECUTE S1"

RETURN;

SQLCA:

SAY "SQLCODE = " SQLCODE

SAY "SQLSTATE = " SQLSTATE

SAY "SQLERRMC = " SQLERRMC

EXIT

Step4: To get the apt PAGESIZE for the Indexes chosen for the Compression.

Approach: SQL Query

I coded the SQL query with Common-Table Expressions (CTE) for getting the new Page Size for the Indexes to be compressed. Page Size details will help in determining the corresponding Buffer Pools.

WITH

IXAWE1

(IDXCRE

,IDXNAME

,IDXPART

,RED_8K

,BUF_WAS_8K

,RED_16K

,BUF_WAS_16K

,RED_32K

,BUF_WAS_32K) AS

(SELECT

IDX_CREATOR

,IDX_NAME

,IDX_PART

,(SELECT IDX_PGSIZ_REDUC_PCT_8K*0.01 FROM SYSIBM.SYSDUMMY1)

,(SELECT CONCAT('-',(IDX_BUFSPC_WASTE_PCT_8K*0.01)) FROM SYSIBM.SYSDUMMY1)

,(SELECT IDX_PGSIZ_REDUC_PCT_16K*0.01 FROM SYSIBM.SYSDUMMY1)

,(SELECT CONCAT('-',(IDX_BUFSPC_WASTE_PCT_16K*0.01)) FROM SYSIBM.SYSDUMMY1)

,(SELECT IDX_PGSIZ_REDUC_PCT_32K*0.01 FROM SYSIBM.SYSDUMMY1)

,(SELECT CONCAT('-',(IDX_BUFSPC_WASTE_PCT_32K*0.01)) FROM SYSIBM.SYSDUMMY1)

FROM LPARFO.IXCOMP)

,IXAWE2

(IXCRE

,IXNAME

,IXPART

,PGSIZ_FOR_COMP) AS

(SELECT

IDX_CREATOR

,IDX_NAME

,IDX_PART

,CASE

WHEN ((RED_8K+BUF_WAS_8K) > (RED_16K+BUF_WAS_16K)) AND ((RED_8K+BUF_WAS_8K) > (RED_32K+BUF_WAS_32K)) THEN '8K'

WHEN ((RED_16K+BUF_WAS_16K) > (RED_8K+BUF_WAS_8K)) AND ((RED_16K+BUF_WAS_16K) > (RED_32K+BUF_WAS_32K)) THEN '16K'

WHEN ((RED_32K+BUF_WAS_32K) > (RED_8K+BUF_WAS_8K)) AND ((RED_32K+BUF_WAS_32K) > (RED_16K+BUF_WAS_16K)) THEN '32K'

END

FROM

LPARFO.IXCOMP

INNER JOIN

IXAWE1 ON

IDX_CREATOR=IDXCRE AND

IDX_NAME=IDXNAME AND

IDX_PART=IDXPART)

SELECT * FROM IXAWE2;

Step5: ALTER INDEX statements will be generated.

Step6: ALTER INDEX statements will be executed.