„Breaking the rules is often fun, although we cannot condone it. But breaking the rules of Real Time Statistics (RTS) in Db2 12 can really land you in hot water.
We provide two queries that may give you a Get Out of Jail Free Card – at least as far as RTS is concerned…”
In this short newsletter, I wish to briefly discuss a change in behavior of the Real Time Statistics (RTS) tables that I have now seen at customer sites. I am not too happy about it! Before we start, a quick resumé of the RTS is required.
In this short newsletter, I wish to briefly discuss a change in behavior of the Real Time Statistics (RTS) tables that I have now seen at customer sites. I am not too happy about it! Before we start, a quick resumé of the RTS is required.
The Arrival of RTS
The Real Time Statistics were basically created by Dr Jim Teng in Db2 V7 and have always obeyed a few quite simple rules.
Real Time Statistics (RTS) rules:
- Rule Number one
If in doubt set to NULL.
This might read a bit odd but the idea from Jim was that if any value is not 100% known then the column must be set to the NULL value. - Rule Number two
Externalize when asked, or by timer. - Rule Number three
No DEFINE NO data. - Rule Number four
Utilities will always update the RTS unless it cannot – see Rule Number one. - Rule Number five
SQL updates the counters unless they cannot – Think Mass Delete in a multi-table tablespace. Totalrows cannot be updated.
Rules are made to be broken
A couple of years into RTS usage and the clamor for changing various insert values got too big, so IBM enhanced the RTS so that on creation the REORGLASTTIME got set to the created timestamp and all the counter columns got set to zero instead of NULL. Now this was a good change as adding 1,000,000 to NULL you get NULL, whereas adding 1,000,000 to zero means you get 1,000,000 which is obviously better for working out whether or not you need a utility to run. The REORGLASTTIME was also accepted as when you create a nice empty object or you insert one row into an object, then by definition, it is in a perfectly reorganized state!
RTS rule one broken
Mass delete always caused problems, as mentioned earlier, so IBM then made a “half way” fix for the INDEX statistics in Db2 9 by zeroing the TOTALENTRIES when there is a Mass Delete as Db2 knows that the index is now empty.
(PM34730: RTS SYSINDEXSPACESTATS TOTALENTRIES INCORRECT AFTER MASS DELETE.)Of course it did not update the TOTALROWS, as it didn’t “know” how many rows were mass deleted or truncated. This causes “drift” between the TS and IX statistics, but is only a minor annoyance.
Db2 12 breaks rule one
Now in Db2 12 Rule one has been broken again. Not that much of a break, but still not brilliant! What they have done, is change the STATSLASTTIME to now also be, nearly, the created TS – as if creating an object sets the runstats columns to valid data!
Naturally, the Db2 Catalog is still all -1’s. This makes generating utilities based on the RTS a little bit tricky, as you cannot trust the STATSLASTTIME to now ever actually be the time a RUNSTATS utility really ran – which was the *whole*, and only, purpose of this column! If only they had set the STATSLASTTIME to ‘0001-01-01-00.00.00.00.000000’ then all would be well…
Who woke the dogs up? (Or: Who let the dogs out? I.e. American jargon)
PI79234: SYSIBM.SYSTABLESPACESTATS.STATSINSERTS IS NOT UPDATED BY RTS EXTERNALIZATION SINCE OBJECT IS CREATED. Is the APAR (PTF UI48494) that caused me all the trouble.
Where’s the beef?
Well, the problem is, if you are generating RUNSTATS based on the RTS – and who isn’t these days? – Then you are probably using this column. Now it *looks* like a RUNSTATS has been run at least once.
This is false and can lead to the not running of RUNSTATS when it most definitely should have been run. Check all your home-grown RUNSTATS checkers. Remember DSNACCOX is also a little bit broken, as it uses these predicates:
(STATSLASTTIME IS NULL OR STATSLASTTIME<LOADRLASTTIME OR STATSLASTTIME<REORGLASTTIME OR STATSLASTTIME< latest PROFILE_UPDATE for the table space1 OR
Make sure you do not use the STATSLASTTIME, but instead join across to the SYSINDEXPART or SYSTABLEPART and pull out the STATSTIME column. This data is still ok and not fake news!
The scope of the problem
To find out the scope of the problem at your site, or even if you have this problem, you can run these two queries which simply list out all the objects that have, according to the Db2 Catalog, never been RUNSTATted, but according to the RTS they have been RUNSTATted:
Query 1
-- SELECT LIST OF TABLESPACES THAT ACCORDING TO DB2 CATALOG HAVE NOT -- BEEN RUNSTATTED BUT ACCORDING TO RTS HAVE BEEN. -- IGNORE DSNDB01 AS NO RUNSTATS FOR DIRECTORY OBJECTS -- IGNORE WORK DEFINED DATABASES AS NO RUNSTATS FOR THESE -- ONLY CHECK FOR TABLE TYPES H,M,P,R AND T -- DISTINCT IT FOR MULTI TABLE TABLESPACES SELECT DISTINCT A.DBNAME, A.NAME, A.PARTITION ,A.REORGLASTTIME, A.STATSLASTTIME ,B.STATSTIME, B.CREATEDTS FROM SYSIBM.SYSTABLESPACESTATS A ,SYSIBM.SYSTABLEPART B ,SYSIBM.SYSDATABASE C ,SYSIBM.SYSTABLES D WHERE B.STATSTIME = TIMESTAMP('0001-01-01-00.00.00.000000') AND NOT COALESCE(A.STATSLASTTIME ,TIMESTAMP('0001-01-01-00.00.00.000000')) = TIMESTAMP('0001-01-01-00.00.00.000000') AND NOT A.DBNAME = 'DSNDB01' AND A.DBNAME = C.NAME AND NOT C.TYPE = 'W' AND A.DBNAME = B.DBNAME AND A.NAME = B.TSNAME AND A.PARTITION = B.PARTITION AND A.DBNAME = D.DBNAME AND A.NAME = D.TSNAME AND D.TYPE IN ('H' , 'M' , 'P' , 'R' , 'T') ORDER BY 1 , 2 , 3 ;
Query 2
-- SELECT LIST OF INDEXSPACES THAT ACCORDING TO DB2 CATALOG HAVE NOT -- BEEN RUNSTATTED BUT ACCORDING TO RTS HAVE BEEN. -- IGNORE DSNDB01 AS NO RUNSTATS FOR DIRECTORY OBJECTS -- IGNORE HASH INDEXES AS NO RUNSTATS ALLOWED -- ONLY CHECK FOR TABLE TYPES H,M,P,R AND T SELECT A.DBNAME, A.INDEXSPACE, A.PARTITION ,A.REORGLASTTIME, A.STATSLASTTIME ,B.STATSTIME, B.CREATEDTS FROM SYSIBM.SYSINDEXSPACESTATS A ,SYSIBM.SYSINDEXPART B ,SYSIBM.SYSINDEXES C ,SYSIBM.SYSTABLES D WHERE B.STATSTIME = TIMESTAMP('0001-01-01-00.00.00.000000') AND NOT COALESCE(A.STATSLASTTIME ,TIMESTAMP('0001-01-01-00.00.00.000000')) = TIMESTAMP('0001-01-01-00.00.00.000000') AND NOT A.DBNAME = 'DSNDB01' AND A.CREATOR = B.IXCREATOR AND A.NAME = B.IXNAME AND A.PARTITION = B.PARTITION AND A.CREATOR = C.CREATOR AND A.NAME = C.NAME AND NOT C.HASH = 'Y' AND C.TBCREATOR = D.CREATOR AND C.TBNAME = D.NAME AND D.TYPE IN ('H' , 'M' , 'P' , 'R' , 'T') ORDER BY 1 , 2 , 3 - ;
You can run these in any version of Db2 you like, but you will only get results from a Db2 12 system with this APAR applied and you have created an index or a tablespace. When I run them here on my Db2 10 and 11 systems I get zero rows back and on my test Db2 12 FL501 system I get 172 rows.
The good news is…
Our product for helping you all get “agile” in Db2 12, CDDC – ContinuousDelivery DeploymentCheck, detects and reports these data constellations as does our Statistics Health Check, of course.
I would be very interested to hear your opinions about this “little change in behavior”. Have you come across this at your site?
As always, any questions or comments would be most welcome!
TTFN,
Roy Boxwell