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