Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
IBM introduced a new Db2 table with Db2 for z/OS V11 called SYSIBM.SYSSTATFEEDBACK. The table verifies if statistics were collected and if the correct statistics were collected.
Instead of rehashing the information already covered in the IBM online magazine concerning the new Db2 table SYSIBM.SYSSTATFEEDBACK written by Willie Favero, which you can find here, I would like to explain how you can use a User-Defined Function to get the integer column numbers from the COLGROUPCOLNO field in the new table.
The COLGROUPCOLNO values are stored in a VARCHAR field. The User Defined Function (UDF) converts the Hex values to Integer values, which is the tables column number.
I would advise that you read the article mentioned above first to get a broader understanding of what information the SYSIBM.SYSSTATFEEDBACK table stores and how you can use it to optimise your environment with updated statistics.
When running a SELECT on the SYSIBM.SYSSTATFEEDBACK table, the COLGROUPCOLNO column values are usually displayed as periods (in red) below in Figure 1.
Figure 1:
1 2 3 4 5 6 7 8 9 | ********************************* Top of Data ******************************** TBCREATOR TBNAME IXCREATOR IXNAME COLNAME NUMCOLUMNS COLGROUPCOLNO --------- ------- --------- ------ ---------- ---------- ------------- TEST TMMP016 AIMPRM_PCT 1 TEST TMMP016 PDTLNE_CDE 2 . . |
When using the HEX function in your Select (HEX(COLGROUPCOLNO)) you will find the hex values displayed for each column number as shown below.
Figure 2:
1 2 3 4 5 6 7 8 9 10 11 | ********************************* Top of Data ******************************** TBCREATOR TBNAME IXCREATOR IXNAME COLNAME NUMCOLUMNS COLGROUPCOLNO * * * * * * * --------- ------- --------- ------ ---------- ---------- ------------- TEST TMMP016 AIMPRM_PCT 1 TEST TMMP016 PDTLNE_CDE 2 00100011 |
As you can see in Figure 2 above the field value of COLGROUPCOLNO is shown as 00100011.
These are the column numbers to be used in your Runstats statement.
You will notice that the value in the NUMCOLUMNS field will tie in with the number of columns displayed in the COLGROUPCOLNO, NUMCOLUMNS = 2.
The hex values convert as follows: 0010 converts to the integer value 16 and 0011 converts to integer value 17, in other words COLNO 16 and COLNO 17 of the table.
In order to have the column numbers’ hex values converted to integer (COLNO in SYSIBM.SYSCOLUMNS), in Db2, you need a Function for the conversion.
The function, HEX2INT, can be found here. Simply search for HEX2INT.
The one displayed below has been tailored to use a WLM on z/OS. Yours might require further tailoring based on your environments ZPARMs.
Figure 3:
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 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 | --#SET TERMINATOR ` SET CURRENT SQLID='qualifier'` CREATE FUNCTION dba.HEX2INT à dba can be any name (STR VARCHAR(8)) RETURNS INTEGER SPECIFIC HEX2INT DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL <-- Could also be READS SQL DATA WLM ENVIRONMENT FOR DEBUG MODE WLMENV <--change WLM QUALIFIER qualifier <-- Change Qualifier PACKAGE OWNER qualifier <-- Change Qualifier BEGIN NOT ATOMIC DECLARE RES INTEGER DEFAULT 0 ; DECLARE POS INTEGER DEFAULT 1 ; DECLARE NIBBLE CHAR( 1) ; WHILE POS <= LENGTH(STR) DO SET NIBBLE = SUBSTR(STR, POS, 1) ; SET RES = BITOR(CASE WHEN BITAND(RES, 134217728) != 0 THEN BITOR(16 * BITANDNOT(RES, 134217728), -2147483648) ELSE 16 * RES END, CASE NIBBLE WHEN '0' THEN 0 WHEN '1' THEN 1 WHEN '2' THEN 2 WHEN '3' THEN 3 WHEN '4' THEN 4 WHEN '5' THEN 5 WHEN '6' THEN 6 WHEN '7' THEN 7 WHEN '8' THEN 8 WHEN '9' THEN 9 WHEN 'A' THEN 10 WHEN 'a' THEN 10 WHEN 'B' THEN 11 WHEN 'b' THEN 11 WHEN 'C' THEN 12 WHEN 'c' THEN 12 WHEN 'D' THEN 13 WHEN 'd' THEN 13 WHEN 'E' THEN 14 WHEN 'e' THEN 14 WHEN 'F' THEN 15 WHEN 'f' THEN 15 ELSE RAISE_ERROR('78000', 'NOT A HEX STRING') END), POS = POS + 1 ; END WHILE ; RETURN RES ; END ` --#SET TERMINATOR ; COMMIT; |
Once the UDF has been created you should be able to do a Select as follows (the SELECT statement has been edited to remove the other 47 COL statements) to display the column numbers in the COLGROUPCOLNO.
Figure 4:
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 | SELECT TBCREATOR, TBNAME, COLNAME, NUMCOLUMNS, HEX(COLGROUPCOLNO) "COLGROUPCOLNO", CASE WHEN CHAR(NUMCOLUMNS) > 1 THEN DBA.HEX2INT(HEX(SUBSTR(COLGROUPCOLNO,1,2))) ←Note length of 2 ELSE 0 END AS COL1, CASE WHEN CHAR(NUMCOLUMNS) >= 2 THEN DBA.HEX2INT(HEX(SUBSTR(COLGROUPCOLNO,3,2))) ←Note increments of 2 ELSE 0 END AS COL2, CASE WHEN CHAR(NUMCOLUMNS) >= 3 THEN DBA.HEX2INT(HEX(SUBSTR(COLGROUPCOLNO,5,2))) ←Note increments of 2 ELSE 0 END AS COL3 FROM "SYSIBM"."SYSSTATFEEDBACK" WHERE TBNAME = 'TMMP016' |
Figure 5:
1 2 3 4 5 6 7 | TBCREATOR TBNAME COLNAME NUMCOLUMNS COLGROUPCOLNO COL1 COL2 --------- ------- ---------- ---------- ------------- ----------- ----------- TEST TMMP016 AIMPRM_PCT 1 0 0 TEST TMMP016 PDTLNE_CDE 2 00100011 16 17 |
The non-zero values of COL1 and COL2 in the second row of Figure 5, displays the columns numbers of the Table needing updated statistics. The column values of the 2 rows selected in this example from SYSIBM.SYSFEEDBACK are as follows shown in Figure 6:
Figure 6:
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 | Row 1 TBCREATOR = TEST TBNAME = TMPP016 COLNAME = AIMPRM_PCT NUMCOLUMNS = 1 COLGROUPCOLNO = TYPE = F DBNAME = DMMP01 TSNAME = SMMPPD16 REASON = DEFAULT LASTDATE = 28.09.2018 Row 2 TBCREATOR = TEST TBNAME = TMPP016 COLNAME = PDTLNE_CDE NUMCOLUMNS = 2 COLGROUPCOLNO = 00100011 TYPE = C DBNAME = DMMP01 TSNAME = SMMPPD16 REASON = COMPFFIX LASTDATE = 26.09.2018 |
The TYPE and the REASON columns will determine what your RUNSTATS statement should look like when complete.
Row 1:
Type = F determines that the table lacks Frequency (FREQVAL) statistics.
Reason = DEFAULT determines that a predicate references a value that is probably a default value.
Row1’s RUNSTATS statement should look as follows:
1 2 3 4 5 6 7 8 9 | RUNSTATS TABLESPACE DMMP01.SMMPPD16 TABLE(TEST.TMMP016) COLGROUP(AIMPRM_PCT) FREQVAL COUNT 30 SHRLEVEL CHANGE REPORT YES UPDATE ALL HISTORY NONE |
Row 2:
Type = C determines that the table lacks Cardinality statistics
Reason = COMPFFIX determines that Multi-column cardinality statistics are needed for an index compound filter factor.
And Row2’s RUNSTATS statement should look as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 | RUNSTATS TABLESPACE DMMP01.SMMPPD16 TABLE(TEST.TMMP016) COLUMN(PDTLNE_CDE) COLGROUP(PDTLNE_CDE ß Column 16 and 17 ,MVTTYP_CDE ) SHRLEVEL CHANGE REPORT YES UPDATE ALL HISTORY NONE |
Remember that you need to follow up your RUNSTATS with a REBIND of your packages affected.
You can find the second article of series below.
“The SYSIBM.SYSSTATFEEDBACK table introduced in Db2 for z/OS V11 Part 2”
Cool, Thanks Ian.