A Good time to check your DB2 Catalog Statistics !
One of my favorite topics is STATISTICS and RUNSTATS.
This month I have a short newsletter involving both of them!
Something jumped right out…
Some time ago we were helping one of our customers to perform an Early Precheck (Going from DB2 9 to 10). To do so, we requested a copy of their entire DB2 production statistics so the optimizer could work here at our labs in Dusseldorf – just like at the customer site.
…as we loaded up DB2 production statistics of a customer
We loaded up the data and I noticed something that just jumped right out…
SELECT TYPE
,FREQUENCYF
,NUMCOLUMNS
,SUBSTR(COLVALUE , 1 , 11) AS COLVALUE
,COLGROUPCOLNO
FROM SYSIBM.SYSCOLDIST
WHERE TBOWNER = 'aaaaaaa'
AND TBNAME = 'bbbbbb'
AND NAME = 'cccccc'
ORDER BY 1 , 3 , 2
;
---------+---------+---------+---------+---------+---------+---------
TYPE FREQUENCYF NUMCOLUMNS COLVALUE COLGROUPCOLNO
---------+---------+---------+---------+---------+---------+---------
F +0.6066539624818615E-02 1 . xxxxxxxxxx ..
F +0.6066539624818615E-02 1 . xxxxxxxxxx
F +0.6287988717751475E-02 1 . xxxxxxxxxx ..
F +0.6287988717751475E-02 1 . xxxxxxxxxx
F +0.8554928116458912E-02 1 . xxxxxxxxxx ..
F +0.8554928116458912E-02 1 . xxxxxxxxxx
F +0.8578238547293950E-02 1 . xxxxxxxxxx ..
F +0.8578238547293950E-02 1 . xxxxxxxxxx
F +0.8852136109605646E-02 1 . xxxxxxxxxx
F +0.8852136109605646E-02 1 . xxxxxxxxxx ..
F +0.1229042465777374E-01 1 . xxxxxxxxxx
F +0.1229042465777374E-01 1 . xxxxxxxxxx ..
F +0.1331608361451540E-01 1 . xxxxxxxxxx ..
F +0.1331608361451540E-01 1 . xxxxxxxxxx
F +0.1342098055327308E-01 1 . xxxxxxxxxx ..
F +0.1342098055327308E-01 1 . xxxxxxxxxx
F +0.1633478440765281E-01 1 . xxxxxxxxxx
F +0.1633478440765281E-01 1 . xxxxxxxxxx ..
F +0.8439891140288000E+00 1 . xxxxxxxxxx ..
F +0.8439891140288000E+00 1 . xxxxxxxxxx
DSNE610I NUMBER OF ROWS DISPLAYED IS 20
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
There were “duplicates”!
This is just one of many examples. Also above, you can see the problem – Notice the data in COLGROUPCOLNO? Sometimes there is a hex value and sometimes not!
Now, believe this or not, all of this goes back to a bug in RUNSTATS in DB2 V8 which got this APAR:
PK33517:
COLGROUPCOLNO ASSOCIATED WITH SINGLE COLUMN CONTAINS A NUMERIC VALUE INSTEAD OF ZERO LENGTH FIELD ACCORDING TO SQL REF
The bug was fixed in DB2 9, but the “bad” data was not automatically cleaned up
As you can see, the bug caused an erroneous value in the COLGROUPCOLNO to be set for single column frequency rows.
The bug was then fixed in DB2 9, but the “bad” data that had been inserted was not automatically cleaned up and, as I hope you all know, the SYSCOLDIST data is never automatically deleted – it is only ever updated. So when the bug was fixed, the low-value or hexadecimal column number in that field was no longer EQUAL to a zero length field, and so an insert was done. Since then these rows have just stayed there…
Query to count these bad rows in the SYSCOLDIST
I wrote a little query just to show the count of how many of these bad rows exist in the SYSCOLDIST:
SELECT COUNT(*) AS BAD_GUYS
FROM SYSIBM.SYSCOLDIST
WHERE NUMCOLUMNS = 1
AND TYPE = 'F'
AND NOT LENGTH(COLGROUPCOLNO) = 0
;
---------+---------+---------+---------+------
BAD_GUYS
---------+---------+---------+---------+------
8680
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
All of these entries should be deleted from SYSCOLDIST to help the optimizer pick the right access paths!
Check your stats !
I first noticed this problem during a test of our Statistics HealthCheck product, which flagged over 1,200 critical problems in the DB2 catalog and, thinking I had broken something, I checked all of the checks and found the above bad data. Now is as good a time as any to check your stats *and* download our Statistics Healthcheck Freeware!!
As usual any questions or comments are welcome,
TTFN Roy Boxwell
Senior Software Architect