Wednesday , May 29 2024

The SYSIBM.SYSSTATFEEDBACK table introduced in Db2 for z/OS V11


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:


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:


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:


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:

The Select statement will display the COLUMNS needed for the Runstats as follows:


Figure 5:


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:


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:


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:


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


About Ian Sampson

One comment

  1. Cool, Thanks Ian.

Leave a Reply

Your email address will not be published. Required fields are marked *