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
DO
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
DO
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.
No comments:
Post a Comment