Tag: REORG

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