In the previous post we discussed how it is possible to extract your Integer values from IBM’s new Db2 table as of Db2 for z/OS V11 called SYSIBM.SYSSTATFEEDBACK. The table verifies if statistics were collected and if the correct statistics were collected.
In Part 2, I would like to cover the format of the RUNSTATS statements that needs to be used when combining the TYPE and REASON columns of records in the SYSIBM.SYSSTATFEEDBACK table. If the RUNSTATS statements are correct, Db2 will delete the row(s) from table SYSIBM.SYSSTATFEEDBACK. If the format is incorrect, the row will remain, and you would need to modify the format or add/remove RUNSTATS parameters.
I have discovered that the following RUNSTATS statements have worked every time when run with the correct TYPE and REASON combination.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
IF TYPE = 'T' and (REASON = 'CONFLICT' or REASON = 'BASIC') THEN DO RUNSTATS TABLESPACE DBNAME . TSNAME TABLE( TBCREATOR . TBNAME ) SHRLEVEL CHANGE UPDATE ALL IF TYPE = 'I' and (REASON = 'CONFLICT' or REASON = 'BASIC') THEN DO RUNSTATS TABLESPACE DBNAME . TSNAME TABLE( TBCREATOR . TBNAME ) INDEX( IXCREATOR . IXNAME ) SHRLEVEL CHANGE UPDATE ALL IF TYPE = 'C' and (REASON = 'CONFLICT' or REASON = 'BASIC') and NUMCOLUMNS = 1 THEN DO RUNSTATS TABLESPACE DBNAME . TSNAME TABLE( TBCREATOR . TBNAME ) COLUMN( COLNAME ) SHRLEVEL CHANGE UPDATE ALL IF TYPE = 'C' and (REASON = 'CONFLICT' or REASON = 'BASIC') and NUMCOLUMNS > 1 THEN DO RUNSTATS TABLESPACE DBNAME . TSNAME TABLE( TBCREATOR . TBNAME ) COLUMN( COLNAME ) COLGROUP ( COL1, COL2, COL3, ....) SHRLEVEL CHANGE UPDATE ALL IF TYPE = 'C' and REASON = 'KEYCARD' THEN DO RUNSTATS TABLESPACE DBNAME . TSNAME TABLE( TBCREATOR . TBNAME ) INDEX( IXCREATOR . IXNAME ) COLGROUP ( COL1, COL2, COL3, ....) SHRLEVEL CHANGE REPORT YES UPDATE ALL HISTORY NONE IF TYPE = 'C' and REASON = 'COMPFFIX' THEN DO RUNSTATS TABLESPACE DBNAME . TSNAME TABLE( TBCREATOR . TBNAME ) COLUMN( COLNAME ) COLGROUP ( COL1, COL2, COL3, ....) SHRLEVEL CHANGE REPORT YES UPDATE ALL HISTORY NONE IF TYPE = 'F' and (REASON = 'CONFLICT' or REASON = 'DEFAULT' or REASON = 'NULLABLE' or REASON = 'LOWCARD') and NUMCOLUMNS = 1 THEN DO RUNSTATS TABLESPACE DBNAME . TSNAME TABLE( TBCREATOR . TBNAME ) COLGROUP( COLNAME ) FREQVAL COUNT 30 SHRLEVEL CHANGE REPORT YES UPDATE ALL HISTORY NONE IF TYPE = 'F' and (REASON = 'CONFLICT' or REASON = 'DEFAULT' or REASON = 'NULLABLE' or REASON = 'LOWCARD') and NUMCOLUMNS > 1 THEN DO RUNSTATS TABLESPACE DBNAME . TSNAME TABLE( TBCREATOR . TBNAME ) COLGROUP ( COL1, COL2, COL3, ....) FREQVAL COUNT 30 SHRLEVEL CHANGE REPORT YES UPDATE ALL HISTORY NONE IF TYPE = 'H' and (REASON = 'RNGPRED' or REASON = 'PARALLEL') THEN DO RUNSTATS TABLESPACE DBNAME . TSNAME TABLE( TBCREATOR . TBNAME ) COLGROUP( COLNAME ) HISTOGRAM SHRLEVEL CHANGE REPORT YES UPDATE ALL |
Many of our smaller tables re-appeared in the SYSIBM.SYSSTATFEEDBACK table with REASON = CONFLICT.
I suspected that the optimizer found that the rows in the table did not match the RUNSTATS and then performed a TABLESPACE scan. This happened with many of our volatile tables where rows were inserted and deleted randomly at any time.
I then ran an ALTER TABLE TBCREATOR . TBNAME VOLATILE ; command on those tables which forced the optimizer to use the index every time to get to the data instead of using the “outdated” RUNSTATS. This solved the problem.
The following SQL can be run to get table row cardinality of each table found in SYSIBM.SYSSTATFEEDBACK:
|
1 2 3 4 5 6 7 8 |
SELECT DISTINCT A.TBCREATOR, A.TBNAME, CAST(FLOAT(B.CARDF) AS INTEGER ) "CARD" , REASON, LASTDATE, NUMCOLUMNS, HEX(COLGROUPCOLNO) "COLGROUPCOLNO" FROM "SYSIBM"."SYSSTATFEEDBACK" A , "SYSIBM"."SYSTABLES" B WHERE B.NAME = A.TBNAME AND B.CREATOR = A.TBCREATOR AND A.TBNAME = ‘TBNAME’ AND A.TBCREATOR = ‘TBCREATOR’ |
