2016-02 Real Time Statistics (RTS) Revisited – Information missing (part 1)

Easy Real Time Statistics (RTS) data initialization from DB2 9 to DB2 11:

Special query to run before a REORG, RUNSTAT or a DB2 Migration. How many RTS rows did you find?

 

Hands up who knows nothing about the RTS? Good, all hands are down! I had an interesting experience the other day with one of my customers as they are in the process of doing the big bang “REORG the world” to get from a six byte RBA/LRSN to a 10 Byte RBA/LRSN due to problems with data cloning in a mixed DB2 release Environment.

 

RTS Database Maintenance

They use the RTS to drive the creation of REORG, RUNSTAT, and COPY utilities as this is the modern and correct way to go, right? Well, they ended up with a bunch of objects that refused to REORG. I looked high and low for *any* reason as to why they would be excluded from processing and found none. Well, actually, I lie – there was one, and that was the fact that the candidate list was based upon a SELECT from the RTS tables and then joining to the DB2 Catalog to refine the data and then finally generating the required REORG jobs.

 

RTS data missing

It was noticed that these tablespaces were either empty or very small and it was seen that they did not even *exist* in the RTS! Now cast your mind way way way back to DB2 V7 when the RTS were introduced as an “optional” feature. I wrote a little SQL INSERT to populate the RTS for any missing elements as the IBM way of populating the RTS was to “REORG the world” (remember those halcyon days?) Anyway these days, about 11 years later, it is *always* assumed that:

– The RTS data exists
– The RTS data is correct (mainly!)
– RTS data initialization made easy

So, to save you all from trying to find my SQL from those days, here’s the DB2 9 and above version which you can, perhaps must, run to make sure you have no “bodies in the cellar” like my customer did!

 

RTS data initialization made easy

So, to save you all from trying to find my SQL from those days, here’s the DB2 9 and above version which you can, perhaps must, run to make sure you have no “bodies in the cellar” like my customer did!

 

------------------------------------------------------------------------
-- THESE TWO QUERIES WILL FILL THE RTS TABLES SYSIBM.SYSTABLESPACESTATS
-- AND SYSIBM.SYSINDEXSPACESTATS WITH DEFAULT AND, WHEN POSSIBLE,     --
-- WITH CATALOG DATA FOR MISSING ENTRIES                              --
-- (OBJECTS FOUND IN THE CATALOG BUT NOT IN RTS TABLES)               --
------------------------------------------------------------------------
-- LOCK TABLE SYSIBM.SYSTABLESPACESTATS IN EXCLUSIVE MODE ;         

INSERT INTO SYSIBM.SYSTABLESPACESTATS
 (UPDATESTATSTIME,NACTIVE,EXTENTS)
 ,LOADRLASTTIME
 ,REORGLASTTIME,REORGINSERTS,REORGDELETES,REORGUPDATES,REORGUNCLUSTINS
 ,REORGDISORGLOB,REORGMASSDELETE,REORGNEARINDREF,REORGFARINDREF
 ,STATSLASTTIME,STATSINSERTS,STATSDELETES,STATSUPDATES,STATSMASSDELETE
 ,COPYLASTTIME,COPYUPDATEDPAGES,COPYCHANGES
 ,IBMREQD
 ,DBID,PSID,PARTITION,INSTANCE,SPACE,TOTALROWS 
 ,DBNAME,NAME)
 SELECT CURRENT TIMESTAMP 
 ,CASE A.SPACEF 
  WHEN -1 THEN CASE A.SPACE 
               WHEN 0 THEN NULL 
               ELSE A.SPACE / B.PGSIZE 
               END
  ELSE MIN( 2147483647 , ( MAX(A.SPACEF , A.SPACE) / B.PGSIZE ) )
  END
 ,CASE A.EXTENTS
  WHEN -1 THEN NULL
  ELSE A.EXTENTS
  END
  ,TIMESTAMP('0001-01-01-00.00.00.000000')
  ,TIMESTAMP('0001-01-01-00.00.00.000000'), 0 , 0 , 0 , 0   
  , 0 , 0 , 0 , 0  
  ,CASE 
    WHEN A.STATSTIME = TIMESTAMP('0001-01-01-00.00.00.000000') 
    THEN A.STATSTIME 
    WHEN A.STATSTIME < A.CREATEDTS THEN 
                       TIMESTAMP('0001-01-01-00.00.00.000000')
    ELSE A.STATSTIME
    END
  , 0 , 0 , 0 , 0 
  ,TIMESTAMP('0001-01-01-00.00.00.000000'), 0 , 0   
  , 'N'   
  ,B.DBID,B.PSID,A.PARTITION,B.INSTANCE  
  ,CASE A.SPACEF 
    WHEN -1 THEN CASE A.SPACE 
                 WHEN 0  THEN NULL  
                 ELSE A.SPACE 
                 END 
    ELSE MAX( MIN( 2147483647 , A.SPACEF ) , A.SPACE)
    END 
   ,CASE A.CARDF 
    WHEN -1 THEN NULL  
    ELSE A.CARDF 
    END 
   ,A.DBNAME,A.TSNAME  
    FROM SYSIBM.SYSTABLEPART  A 
        ,SYSIBM.SYSTABLESPACE B 
    WHERE NOT EXISTS (SELECT C.*  
                     FROM SYSIBM.SYSTABLESPACESTATS C 
                     WHERE A.DBNAME = C.DBNAME 
                       AND A.TSNAME = C.NAME  
                       AND A.PARTITION = C.PARTITION)    
     AND NOT A.SPACE  = -1   
     AND A.DBNAME     = B.DBNAME   
     AND A.TSNAME     = B.NAME    
  ; 
COMMIT ;
-- LOCK TABLE SYSIBM.SYSINDEXSPACESTATS IN EXCLUSIVE MODE ;
INSERT INTO SYSIBM.SYSINDEXSPACESTATS
 (UPDATESTATSTIME
 ,NLEVELS,NLEAF,NACTIVE,SPACE,EXTENTS
 ,LOADRLASTTIME
 ,REBUILDLASTTIME
 ,REORGLASTTIME,REORGINSERTS,REORGDELETES,REORGAPPENDINSERT
 ,REORGPSEUDODELETES,REORGMASSDELETE,REORGLEAFNEAR,REORGLEAFFAR
 ,REORGNUMLEVELS
 ,STATSLASTTIME,STATSINSERTS,STATSDELETES,STATSMASSDELETE
 ,COPYLASTTIME,COPYUPDATEDPAGES,COPYCHANGES
 ,IBMREQD
 ,DBID,ISOBID,PSID,PARTITION,INSTANCE
 ,TOTALENTRIES,DBNAME,NAME,CREATOR,INDEXSPACE)
  SELECT CURRENT TIMESTAMP
 ,CASE B.NLEVELS
   WHEN -1 THEN NULL
   ELSE B.NLEVELS
   END
  ,CASE B.NLEAF
   WHEN -1 THEN NULL
   ELSE B.NLEAF
   END
  ,CASE A.SPACEF
   WHEN -1 THEN CASE A.SPACE
                WHEN 0  THEN NULL
                ELSE A.SPACE / B.PGSIZE
                END
  ELSE MIN( 2147483647 , ( MAX(A.SPACEF , A.SPACE) / B.PGSIZE ) )
  END
 ,CASE A.SPACEF
  WHEN -1 THEN CASE A.SPACE
               WHEN 0  THEN NULL
               ELSE A.SPACE
               END
  ELSE MAX( MIN( 2147483647 , A.SPACEF ) , A.SPACE)
  END
 ,CASE A.EXTENTS
   WHEN -1 THEN NULL
   ELSE A.EXTENTS
   END
 ,TIMESTAMP('0001-01-01-00.00.00.000000')
 ,TIMESTAMP('0001-01-01-00.00.00.000000')
 ,TIMESTAMP('0001-01-01-00.00.00.000000'), 0 , 0 , 0
 , 0 , 0 , 0 , 0 , 0
 ,CASE
  WHEN A.STATSTIME = TIMESTAMP('0001-01-01-00.00.00.000000')
  THEN A.STATSTIME
  WHEN A.STATSTIME < A.CREATEDTS THEN
                     TIMESTAMP('0001-01-01-00.00.00.000000')
  ELSE A.STATSTIME
  END
 , 0 , 0 , 0
 ,TIMESTAMP('0001-01-01-00.00.00.000000'), 0 , 0
 , 'N'
 ,B.DBID,B.ISOBID, C.PSID
 ,A.PARTITION,C.INSTANCE
 ,CASE A.CARDF
  WHEN -1 THEN NULL
  ELSE A.CARDF
  END
 ,B.DBNAME,B.NAME,B.CREATOR,B.INDEXSPACE
  FROM SYSIBM.SYSINDEXPART  A
      ,SYSIBM.SYSINDEXES    B
      ,SYSIBM.SYSTABLESPACE C
      ,SYSIBM.SYSTABLES     D
 WHERE NOT EXISTS (SELECT E.*
                   FROM SYSIBM.SYSINDEXSPACESTATS E
                   WHERE B.DBNAME = E.DBNAME
                     AND B.INDEXSPACE = E.INDEXSPACE
                     AND A.PARTITION  = E.PARTITION)
   AND B.CREATOR    = A.IXCREATOR
   AND B.NAME       = A.IXNAME
   AND NOT A.SPACE  = -1
   AND B.TBCREATOR  = D.CREATOR
   AND B.TBNAME     = D.NAME
   AND D.DBNAME     = C.DBNAME
   AND D.TSNAME     = C.NAME
;
COMMIT ;

 

It may even be a good idea to run these two queries on a regular basis… just in case!

I would like to know how many rows these queries INSERTed at your shops – Here in Düsseldorf, in the labs, it found two TS’s and three IX’s in a DB2 11 NFM system.

As always, any questions or comments would be most welcome!

TTFN,

Roy Boxwell