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