2018-03 RTS RUNSTATS

 „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.

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:


  1. 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.


  2. Rule Number two
    Externalize when asked, or by timer.


  3. Rule Number three
    No DEFINE NO data.


  4. Rule Number four
    Utilities will always update the RTS unless it cannot – see Rule Number one.


  5. 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

2017-05 Db2 version 12: A little bit too agile?

 

How to handle APPLCOMPAT when it comes to Dynamic SQL?

I have been involved in testing in Db2 version 12 for a while now, and I think we need a discussion about a few features that come in with this “agile” release.

APPLCOMPAT to the rescue?

First up, is the use of APPLCOMPAT – This is now available throughout the SQL, but comes with a few problems. If you have static SQL then all is fine and dandy, as the APPLCOMPAT used is stored in the Db2 Catalog. You can easily refer to it and check which package is using what APPLCOMPAT. The real trouble starts with Dynamic SQL.

Dynamic SQL intro

A quick discourse about Dynamic SQL is now required…

All Dynamic SQL that runs, must run “under the control” of a Package.

This means that we all have loads of empty packages that are just used to run Dynamic SQL. You all have the SYSLHxxx, SYSLNxxx style Packages and probably loads more. These Packages are used for authentication, tracing, and validation of requests from remote users.

Package is the Boss

This is all good, apart from when a new Db2 12 Function Level (from now on FL) is activated. Why? Because the “package is boss” – If the package was bound at FL500 and you activate FL501, then any SQL that executes in that package that attempts to use an FL501 statement fails. If the SQL attempts to SET CURRENT APPLCOMPAT = ‘V12R1M501’ it will, of course, also fail.

REBIND the world?

To “fix” this, you simply have to REBIND the package to the new FL level.

Sounds simple, huh?

Well, what that means is that *all* SQL that uses that package will get the ability to go to FL501 straightaway, unless they are coded with SET CURRENT APPLCOMPAT = ‘V12R1M500’ or which level you would prefer…

We all have that coded in our JAVA programs don’t we?

Dynamic Packages always allocated

So the problem now is – You have hundreds of Packages that you must rebind, but you dare not rebind them! Even worse, is that you probably *cannot* rebind them anyway, as they are permanently allocated and in use!

. Imagine how many Dynamic SQLs are running in your shop?
. Can you flush the DSC and rebind all of your “empty” Packages?
. When can you plan such an outage?

COLLECTIONs can help

One work-around is to have a new COLLECTION, which the empty packages are bound to again. This works great, apart from one tiny little problem… The COLLID must then be set/changed in all of the CLI.INI or API places where it is currently set, or just defaulted to, today! At one of my customer sites that would be over 8,000 files to update!

So now my questions to the readers out there


How do you plan to manage this?

How do you plan to roll-out FL levels?

If using a new collection, how many CLI.INIs etc. must be changed?


 

As usual, if you have any comments or queries please feel free to drop me a line!

TTFN

Roy Boxwell