DB2 z/OS Real Time Statistics (RTS) – NULL initialization made easy
A second RTS query to set this time *all* of your RTS KPIs and counters to enable good DB2 Database Maintenance before a REORG, RUNSTAT or a DB2 Migration for DB2 10 and DB2 11
Following on from last month’s Newsletter (first RTS query) where we inserted any missing data rows into the RTS tables-the next thing that I see on a, sadly, regular basis is NULL values in the RTS columns.
Now when RTS was introduced, way back in DB2 V7, the argument from DB2 Development was “If we do not know the exact count we will set the column to NULL.” I have always strongly disagreed with this approach as whenever you add +1 to NULL you get NULL, whenever you add +1000000 to NULL you get NULL. I said then, and I repeat it now, zero is orders of magnitude better than NULL because when you add +1 to 0 you get +1 and when you add +1000000 to 0 you get +1000000. This enables your DB2 Database Maintenance system to actually *work* and then, after a REORG, the counters are actually 100% correct. I am a firm believer in the “I don’t care if it is 99% inaccurate as long as it *counts*!” methodology, and please remember that a SHRLEVEL CHANGE RUNSTATS does *not* set the TOTALROWS or TOTALENTRIES!
RTS Database Maintenance
The following SQLs in this month’s newsletter will intelligently set *all* of your RTS KPIs and counters to enable good DB2 Database Maintenance. It is written for DB2 10 with a couple of commented out lines for DB2 11. (In the RTS tables, there are only two actual new columns and these are the “info” only columns UPDATESIZE and LASTDATACHANGE in SYSTABLESPACESTATS. The commented out lines just set the UPDATESIZE to zero.)
RTS NULL initialization made easy
Now here are the UPDATE SQLs broken down to be one UPDATE SQL per KPI column and one “mass update” for all of the counter columns:
-- TOTALENTRIES
UPDATE SYSIBM.SYSINDEXSPACESTATS E
SET TOTALENTRIES =
(SELECT CASE A.CARDF
WHEN -1 THEN 0
ELSE A.CARDF
END
FROM SYSIBM.SYSINDEXPART A
,SYSIBM.SYSINDEXES B
WHERE B.DBNAME = E.DBNAME
AND B.INDEXSPACE = E.INDEXSPACE
AND B.CREATOR = A.IXCREATOR
AND B.NAME = A.IXNAME
AND A.PARTITION = E.PARTITION)
WHERE TOTALENTRIES IS NULL
;
-- NLEVELS
UPDATE SYSIBM.SYSINDEXSPACESTATS E
SET NLEVELS =
(SELECT CASE B.NLEVELS
WHEN -1 THEN 1
ELSE B.NLEVELS
END
FROM SYSIBM.SYSINDEXES B
WHERE B.DBNAME = E.DBNAME
AND B.INDEXSPACE = E.INDEXSPACE )
WHERE NLEVELS IS NULL
;
-- NLEAF
UPDATE SYSIBM.SYSINDEXSPACESTATS E
SET NLEAF =
(SELECT CASE B.NLEAF
WHEN -1 THEN 1
ELSE B.NLEAF
END
FROM SYSIBM.SYSINDEXES B
WHERE B.DBNAME = E.DBNAME
AND B.INDEXSPACE = E.INDEXSPACE )
WHERE NLEAF IS NULL
;
-- SPACE: USE SPACE IF SPACEF -1 WATCH OUT FOR OVERFLOW
UPDATE SYSIBM.SYSINDEXSPACESTATS E
SET SPACE =
(SELECT CASE A.SPACEF
WHEN -1 THEN CASE A.SPACE
WHEN 0 THEN 0
ELSE A.SPACE
END
ELSE MAX(MIN(2147483647 , A.SPACEF ) , A.SPACE)
END
FROM SYSIBM.SYSINDEXPART A
,SYSIBM.SYSINDEXES B
WHERE B.DBNAME = E.DBNAME
AND B.INDEXSPACE = E.INDEXSPACE
AND B.CREATOR = A.IXCREATOR
AND B.NAME = A.IXNAME
AND A.PARTITION = E.PARTITION)
WHERE SPACE IS NULL
;
-- EXTENTS: AT LEAST ONE EXTENT
UPDATE SYSIBM.SYSINDEXSPACESTATS E
SET EXTENTS =
(SELECT CASE A.EXTENTS
WHEN -1 THEN 1
ELSE A.EXTENTS
END
FROM SYSIBM.SYSINDEXPART A
,SYSIBM.SYSINDEXES B
WHERE B.DBNAME = E.DBNAME
AND B.INDEXSPACE = E.INDEXSPACE
AND B.CREATOR = A.IXCREATOR
AND B.NAME = A.IXNAME
AND A.PARTITION = E.PARTITION)
WHERE EXTENTS IS NULL
;
-- NPAGES: KPI SO SET TO ZERO IF NULL
UPDATE SYSIBM.SYSINDEXSPACESTATS E
SET NPAGES = COALESCE(NPAGES , 0)
WHERE NPAGES IS NULL
;
-- COUNTERS: SET TO ZERO IF NULL
UPDATE SYSIBM.SYSINDEXSPACESTATS E
SET REORGINSERTS = COALESCE(REORGINSERTS , 0)
, REORGDELETES = COALESCE(REORGDELETES , 0)
, REORGAPPENDINSERT = COALESCE(REORGAPPENDINSERT , 0)
, REORGPSEUDODELETES = COALESCE(REORGPSEUDODELETES , 0)
, REORGMASSDELETE = COALESCE(REORGMASSDELETE , 0)
, REORGLEAFNEAR = COALESCE(REORGLEAFNEAR , 0)
, REORGLEAFFAR = COALESCE(REORGLEAFFAR , 0)
, REORGNUMLEVELS = COALESCE(REORGNUMLEVELS , 0)
, REORGINDEXACCESS = COALESCE(REORGINDEXACCESS , 0)
, STATSINSERTS = COALESCE(STATSINSERTS , 0)
, STATSDELETES = COALESCE(STATSDELETES , 0)
, STATSMASSDELETE = COALESCE(STATSMASSDELETE , 0)
, COPYUPDATEDPAGES = COALESCE(COPYUPDATEDPAGES , 0)
, COPYCHANGES = COALESCE(COPYCHANGES , 0)
WHERE REORGINSERTS IS NULL
OR REORGDELETES IS NULL
OR REORGAPPENDINSERT IS NULL
OR REORGPSEUDODELETES IS NULL
OR REORGMASSDELETE IS NULL
OR REORGLEAFNEAR IS NULL
OR REORGLEAFFAR IS NULL
OR REORGNUMLEVELS IS NULL
OR REORGINDEXACCESS IS NULL
OR STATSINSERTS IS NULL
OR STATSDELETES IS NULL
OR STATSMASSDELETE IS NULL
OR COPYUPDATEDPAGES IS NULL
OR COPYCHANGES IS NULL
;
-- NOTE THAT FOR TABLESPACES DSNDB01.SYSUTILX IS NOT
-- SET AND ANY WORK DATABASE IS ALSO IGNORED
--
-- TOTALROWS
UPDATE SYSIBM.SYSTABLESPACESTATS C
SET TOTALROWS =
(SELECT CASE A.CARDF
WHEN -1 THEN 0
ELSE A.CARDF
END
FROM SYSIBM.SYSTABLEPART A
WHERE A.DBNAME = C.DBNAME
AND A.TSNAME = C.NAME
AND A.PARTITION = C.PARTITION )
WHERE TOTALROWS IS NULL
AND NOT (C.DBNAME = 'DSNDB01' AND C.NAME = 'SYSUTILX')
AND NOT EXISTS (SELECT 1
FROM SYSIBM.SYSDATABASE D
WHERE C.DBNAME = D.NAME
AND D.TYPE = 'W')
;
-- SPACE: SPACE IF SPACEF IS -1 WATCH OUT FOR OVERFLOW
UPDATE SYSIBM.SYSTABLESPACESTATS C
SET SPACE =
(SELECT CASE A.SPACEF
WHEN -1 THEN A.SPACE
ELSE MAX( MIN( 2147483647 , A.SPACEF ) , A.SPACE)
END
FROM SYSIBM.SYSTABLEPART A
WHERE A.DBNAME = C.DBNAME
AND A.TSNAME = C.NAME
AND A.PARTITION = C.PARTITION )
WHERE SPACE IS NULL
AND NOT (C.DBNAME = 'DSNDB01' AND C.NAME = 'SYSUTILX')
AND NOT EXISTS (SELECT 1
FROM SYSIBM.SYSDATABASE D
WHERE C.DBNAME = D.NAME
AND D.TYPE = 'W')
;
-- NACTIVE: SPACE IF SPACEF IS -1 / PGSIZE WATCH OUT FOR OVERFLOW
UPDATE SYSIBM.SYSTABLESPACESTATS C
SET NACTIVE =
(SELECT CASE A.SPACEF
WHEN -1 THEN CASE A.SPACE
WHEN 0 THEN 0
ELSE A.SPACE / B.PGSIZE
END
ELSE MIN( 2147483647 , ( MAX(A.SPACEF , A.SPACE) / B.PGSIZE ) )
END
FROM SYSIBM.SYSTABLEPART A
,SYSIBM.SYSTABLESPACE B
WHERE A.DBNAME = C.DBNAME
AND A.TSNAME = C.NAME
AND A.PARTITION = C.PARTITION
AND A.DBNAME = B.DBNAME
AND A.TSNAME = B.NAME )
WHERE NACTIVE IS NULL
AND NOT (C.DBNAME = 'DSNDB01' AND C.NAME = 'SYSUTILX')
AND NOT EXISTS (SELECT 1
FROM SYSIBM.SYSDATABASE D
WHERE C.DBNAME = D.NAME
AND D.TYPE = 'W')
;
-- EXTENTS: AT LEAST ONE EXTENT
UPDATE SYSIBM.SYSTABLESPACESTATS C
SET EXTENTS =
(SELECT CASE A.EXTENTS
WHEN -1 THEN 1
ELSE A.EXTENTS
END
FROM SYSIBM.SYSTABLEPART A
WHERE A.DBNAME = C.DBNAME
AND A.TSNAME = C.NAME
AND A.PARTITION = C.PARTITION )
WHERE EXTENTS IS NULL
AND NOT (C.DBNAME = 'DSNDB01' AND C.NAME = 'SYSUTILX')
AND NOT EXISTS (SELECT 1
FROM SYSIBM.SYSDATABASE D
WHERE C.DBNAME = D.NAME
AND D.TYPE = 'W')
;
-- NPAGES: KPI SO SET TO ZERO IF NULL
UPDATE SYSIBM.SYSTABLESPACESTATS C
SET NPAGES = COALESCE(NPAGES , 0 )
WHERE NPAGES IS NULL
AND NOT (C.DBNAME = 'DSNDB01' AND C.NAME = 'SYSUTILX')
AND NOT EXISTS (SELECT 1
FROM SYSIBM.SYSDATABASE D
WHERE C.DBNAME = D.NAME
AND D.TYPE = 'W')
;
-- COUNTERS: SET TO ZERO IF NULL
UPDATE SYSIBM.SYSTABLESPACESTATS C
SET REORGINSERTS = COALESCE(REORGINSERTS , 0 )
, REORGDELETES = COALESCE(REORGDELETES , 0 )
, REORGUPDATES = COALESCE(REORGUPDATES , 0 )
, REORGUNCLUSTINS = COALESCE(REORGUNCLUSTINS , 0 )
, REORGDISORGLOB = COALESCE(REORGDISORGLOB , 0 )
, REORGMASSDELETE = COALESCE(REORGMASSDELETE , 0 )
, REORGNEARINDREF = COALESCE(REORGNEARINDREF , 0 )
, REORGFARINDREF = COALESCE(REORGFARINDREF , 0 )
, REORGCLUSTERSENS = COALESCE(REORGCLUSTERSENS , 0 )
, REORGSCANACCESS = COALESCE(REORGSCANACCESS , 0 )
, REORGHASHACCESS = COALESCE(REORGHASHACCESS , 0 )
, STATSINSERTS = COALESCE(STATSINSERTS , 0 )
, STATSDELETES = COALESCE(STATSDELETES , 0 )
, STATSUPDATES = COALESCE(STATSUPDATES , 0 )
, STATSMASSDELETE = COALESCE(STATSMASSDELETE , 0 )
-- , UPDATESIZE = COALESCE(UPDATESIZE , 0 )
, COPYUPDATEDPAGES = COALESCE(COPYUPDATEDPAGES , 0 )
, COPYCHANGES = COALESCE(COPYCHANGES , 0 )
WHERE (REORGINSERTS IS NULL
OR REORGDELETES IS NULL
OR REORGUPDATES IS NULL
OR REORGUNCLUSTINS IS NULL
OR REORGDISORGLOB IS NULL
OR REORGMASSDELETE IS NULL
OR REORGNEARINDREF IS NULL
OR REORGFARINDREF IS NULL
OR REORGCLUSTERSENS IS NULL
OR REORGSCANACCESS IS NULL
OR REORGHASHACCESS IS NULL
OR STATSINSERTS IS NULL
OR STATSDELETES IS NULL
OR STATSUPDATES IS NULL
OR STATSMASSDELETE IS NULL
-- OR UPDATESIZE IS NULL
OR COPYUPDATEDPAGES IS NULL
OR COPYCHANGES IS NULL)
AND NOT (C.DBNAME = 'DSNDB01' AND C.NAME = 'SYSUTILX')
AND NOT EXISTS (SELECT 1
FROM SYSIBM.SYSDATABASE D
WHERE C.DBNAME = D.NAME
AND D.TYPE = 'W')
;
It is a very good idea to run all of these queries on a regular basis…just in case!
I would like to know how many rows this UPDATEd at your shops. Here in the Düsseldorf labs it updated hundreds in a DB2 11 NFM system.
As always, any questions or comments would be most welcome!
TTFN,
Roy Boxwell
