We are able to run the reorg/runstats on the following tables and indexes in our environment successfully but even after the successful completion of re-org/runstats on the tables and indexs, when we try to check the same via REORGCHK command, again it shows that the tables needs to be reorged.

Could you pl. provide if there is an alternate way to do the re-org/runstats to solve this issue?



$ db2 get connection state



Database Connection State



Connection state = Connectable and Connected

Connection mode = SHARE

Local database alias = H02695I1

Database name = H02695I1

Hostname =

Service name =



The Following snapshot refers -- Doing the Reorgchk for Sony I1 database and Listed down the tables that requires reorg/runstats.



$ db2 "CALL REORGCHK_TB_STATS('T','ALL')" | grep '*' | awk '{print "REORG TABLE " $1 "." $2 ";\n" "RUNSTATS ON TABLE " $1 "." $2 ";"}'

REORG TABLE HREDADM.PS_H_DEPT1_TBL;

RUNSTATS ON TABLE HREDADM.PS_H_DEPT1_TBL;

REORG TABLE HREDADM.PS_H_DEPT_TMP;

RUNSTATS ON TABLE HREDADM.PS_H_DEPT_TMP;

REORG TABLE HREDADM.PS_H_DLT_EE_ATTRIB;

RUNSTATS ON TABLE HREDADM.PS_H_DLT_EE_ATTRIB;

REORG TABLE HREDADM.PS_H_DLT_ROLEUSER;

RUNSTATS ON TABLE HREDADM.PS_H_DLT_ROLEUSER;

REORG TABLE HREDADM.PS_H_EE_ATTRIB;

RUNSTATS ON TABLE HREDADM.PS_H_EE_ATTRIB;

REORG TABLE HREDADM.PS_H_EE_ATTRIB_ALL;

RUNSTATS ON TABLE HREDADM.PS_H_EE_ATTRIB_ALL;

REORG TABLE HREDADM.PS_H_MASSINSRT_TMP;

RUNSTATS ON TABLE HREDADM.PS_H_MASSINSRT_TMP;

REORG TABLE HREDADM.PS_H_STG_EE_ATTRIB;

RUNSTATS ON TABLE HREDADM.PS_H_STG_EE_ATTRIB;

REORG TABLE HREDADM.PS_H_TREE_FLAT;

RUNSTATS ON TABLE HREDADM.PS_H_TREE_FLAT;




Below snap shows the successful completion of reorg for table & indexes and runstats for the table HREDADM.PS_H_DEPT1_TBL





$ db2 reorg table HREDADM.PS_H_DEPT1_TBL

DB20000I The REORG command completed successfully.

$ db2 reorg indexes all for table HREDADM.PS_H_DEPT1_TBL

DB20000I The REORG command completed successfully.

$ db2 runstats on table HREDADM.PS_H_DEPT1_TBL with distribution and detailed indexes all

DB20000I The RUNSTATS command completed successfully.


Below snap shows the reorgchk analysis after runnig the reorg / runstats for the table



$ db2 reorgchk update statistics on table HREDADM.PS_H_DEPT1_TBL



Doing RUNSTATS ....





Table statistics:



F1: 100 * OVERFLOW / CARD < 5

F2: 100 * (Effective Space Utilization of Data Pages) > 70

F3: 100 * (Required Pages / Total Pages) > 80



SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG

----------------------------------------------------------------------------------------

Table: HREDADM.PS_H_DEPT1_TBL

HREDADM PS_H_DEPT1_TBL 48295 0 9668 9668 - 27479856 0 70 100 -*-

----------------------------------------------------------------------------------------



Index statistics:



F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80

F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((NLEAF - NUM EMPTY LEAFS -1) * (INDEXPAGESIZE - 96) > MIN(50, (100- PCTFREE))

F6: (100 - PCTFREE) * ( FLOOR[ (100 - min(10, pctfree)) / 100 * (indexPageSize - 96) / (ISIZE + 12) ] ** (NLEVELS - 2) ) * (indexPageSize - 96) / (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) < 100

F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) < 20

F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20



SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS F4 F5 F6 F7 F8 REORG

-------------------------------------------------------------------------------------------------

Table: HREDADM.PS_H_DEPT1_TBL

HREDADM PS0H_DEPT1_TBL 48295 779 0 3 49 0 48295 68 90 7 0 0 *----

HREDADM PS1H_DEPT1_TBL 48295 417 0 3 22 0 48295 96 89 25 0 0 -----

HREDADM PS2H_DEPT1_TBL 48295 444 0 3 24 0 48295 100 89 22 0 0 -----

HREDADM PS3H_DEPT1_TBL 48295 514 0 3 29 0 48295 88 89 17 0 0 -----

HREDADM PS4H_DEPT1_TBL 48295 514 0 3 29 0 48295 98 89 17 0 0 -----

HREDADM PS_H_DEPT1_TBL 48295 378 0 3 19 0 48295 100 89 30 0 0 -----

-------------------------------------------------------------------------------------------------

CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary

for indexes that are not in the same sequence as the base table. When multiple

indexes are defined on a table, one or more indexes may be flagged as needing

REORG. Specify the most important index for REORG sequencing.



Tables defined using the ORGANIZE BY clause and the corresponding dimension

indexes have a '*' suffix to their names. The cardinality of a dimension index

is equal to the Active blocks statistic of the table.



Thanks,

Nambi

What is the PCTFREE for the table? Check the PCTFREE Column in syscat.tables

Hi,



'PS_H_DEPT1_TBL' 20
'PS_H_DEPT_TMP' 20
'PS_H_DLT_EE_ATTRIB' -1
'PS_H_DLT_ROLEUSER' 20
'PS_H_EE_ATTRIB' 10
'PS_H_EE_ATTRIB_ALL' '-1'
'PS_H_MASSINSRT_TMP' '-1'
'PS_H_STG_EE_ATTRIB' '-1'
'PS_H_TREE_FLAT' '-1'

Hi,
This is the PCTFRee values for the tables.

'PS_H_DEPT1_TBL' 20
'PS_H_DEPT_TMP' 20
'PS_H_DLT_EE_ATTRIB' -1
'PS_H_DLT_ROLEUSER' 20
'PS_H_EE_ATTRIB' 10
'PS_H_EE_ATTRIB_ALL' '-1'
'PS_H_MASSINSRT_TMP' '-1'
'PS_H_STG_EE_ATTRIB' '-1'
'PS_H_TREE_FLAT' '-1'

Another reason I have seen for the * in F2 is the row width. Say, you have a row with width of 2500 in a 4K tablespace, the utilization will be less than 70%. Obviously REORG cannot 'solve' this problem.

For a * in F4, I wouldn't worry. You cannot have a table clustered on more than one index. In your case, it is PS_H_DEPT1_TBL and therefore PS0H_DEPT1_TBL can be ignored.


HTH

Sathyaram