Process to monitor Restricted Tablespace/Index – cont.

Original post for developing a process to monitor Restricted TS/IX can be found here.

Searching for the Dataset having the Restricted TS/IX details can be painful as the monitoring process I developed creates a Dataset every 15 minutes, if the Dataset name doesn’t have any unique identifier.

I made modifications to the REXX routine, so that Restricted TS/IX details can be displayed in the email and text messages. Below is the modified REXX routine I coded.

/*REXX*/

I = 0                                                                          

J = 0                                                                          

                                                                               

CALL READ_INPUT                                                                

CALL INIT_PARA                                                                 

CALL TERM_PARA                                                                 

                                                                               

READ_INPUT:                                                                     

 "EXECIO * DISKR SYSUT1 (STEM INP. FINIS"                                      

 IF RC <> 0 THEN SAY 'INPUT FILE READ FAILED WITH RC =' RC                     

 ELSE NOP                                                                       

RETURN;                                                                        

                                                                               

TERM_PARA:                                                                      

 FREE ALL                                                                      

 "EXECIO 0 DISKR SYSUT1 (FINIS"                                                

 "EXECIO 0 DISKW SYSUT2 (FINIS"                                                 

EXIT 0                                                                         

                                                                               

INIT_PARA:                                                                      

 DO I = 1 TO INP.0                                                             

   DBRES = SUBSTR(INP.I,1,10)                                                  

   DBRES = STRIP(DBRES,"B")                                                    

   IF DBRES = 'DSNT362I' THEN                                                  

    DO                                                                         

      PARSE VAR INP.I AA AB AC '=' AD AE '=' AF                                

      SAY AC '=' AD AE '=' AF                                                  

      PUSH AC '=' AD AE '=' AF                                                 

      "EXECIO 1 DISKW SYSUT2"                                                  

      CALL RESDB                                                               

    END                                                                        

   ELSE NOP                                                                    

 END                                                                            

RETURN;                                                                        

                                                                               

RESDB:                                                                          

 DO J = I + 5 TO INP.0                                                         

   OBJ = STRIP(INP.J,"B")                                                      

   PARSE VAR OBJ AA BB                                                          

   AA = STRIP(AA,"B")                                                          

   IF AA = '*******' THEN LEAVE                                                

   ELSE                                                                         

    DO                                                                         

      SAY OBJ                                                                  

      PUSH OBJ                                                                  

      "EXECIO 1 DISKW SYSUT2"                                                  

    END                                                                        

 END                                                                           

RETURN;          

                                                               

X‘00E4D5D2’ ABEND code

Using Redirected Recovery component of IBM DB2 Recovery Expert for z/OS v3.2, I created a new Tablespace structure in QA region based on Tablespace structure present in Prod region and migrated data from Prod to QA.

After Data migration is done, I tried the following.

è Online RUNSTATS utility on the Target Tablespace.

It failed with the below error message.

DSNU017I    173 12:57:34.10 DSNUGBAC - UTILITY DATA BASE SERVICES MEMORY EXECUTION ABENDED, REASON=X'00E40347' CAUSE=X'00E4D5D2'

è Online REORG utility on the Target Tablespace. It failed with below error messages.

DSNU017I    174 09:20:16.00 DSNUGSAT - UTILITY DATA BASE SERVICES MEMORY EXECUTION ABENDED, REASON=X'00E4D5D2'

DSNU016I    174 09:20:17.38 DSNUGBAC - UTILITY BATCH MEMORY EXECUTION ABENDED, REASON=X'00E40347'

è Finally, unloaded the data from Source Tablespace (in Prod) and loaded the same into Target Tablespace (in QA) using LOAD REPLACE (with inline statistics).

This worked perfectly fine.

X’00E4D5D2’ ABEND code is something new which I haven’t seen earlier. So, created PMR to IBM to find out more details about the ABEND code. Got the below response from IBM tech support.

The 00E4D5D2 is an indication of a sanity check that was introduced by APAR PK98262 and sourced into DB2 10.  This sanity check was introduced to fail a utility if it finds versioned data records on a non-data-versioned table. I'm wondering if you refreshed this object with DSN1COPY from another subsystem.  You would get this error if the object from the source system was versioned and your target system is not versioned.  This can happen when customers want to test data from one system on a different system.  They create the object on the target system so it matches the layout of the source system.  But, the source system object had been versioned over time.  Then when you run the RUNSTATS or REORG and you get the 00E4D5D2. The same thing can happen if you use flash copies to migrate data. To correct this situation you can run a REPAIR VERSIONS.  This utility updates the catalog with the information from the header pages.  After running the REPAIR VERSIONS, if this is the situation, the RUNSTATS or REORG should then run as expected. 

After reading the message, checked the Source Tablespace (in Prod), and found that it’s versioned. Tablespace versioning details can be found using the below SQL query.

SELECT

   DBNAME

  ,NAME

  ,OLDEST_VERSION

  ,CURRENT_VERSION

FROM SYSIBM.SYSTABLESPACE

WHERE

   DBNAME='LPALIF' AND

   NAME='TSABNBNC'

WITH UR;

The output of above query is

You can see that OLDEST_VERSION and CURRENT_VERSION are having different values. As the Source Tablespace is versioned, Target Tablespace is also created as versioned. And, this lead to the failure of RUNSTATS and REORG utilities on the Target Tablespace.

In this is the situation, the REPAIR VERSIONS will resolve the issue.  It updates the DB2 catalog with the information from the header pages of the Tablespace. After running the REPAIR VERSIONS utility, the REORG should run successful.
For more details about REORG for fixing versioning, below web link can be referred.

https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.ugref/src/tpc/db2z_effectsrunningreorgtablespace.html

SQL queries for Storage calculation, in DB2 v10 (NFM) on z/OS

There are multiple ways to calculate Storage at Object-level. I coded some SQL queries which will make the Storage calculation easy and effective.

For process automation of Storage calculation at Database-level, you can refer my earlier blog post.

1.      To get the details of Tablespaces whose Used Pages percentage crossed 80.

SELECT                                                     

  SUBSTR(SPCRPT.DBNAME,1,8)                              AS DBNAME                           

 ,SUBSTR(SPCRPT.NAME,1,10)                               AS TSNAME               

 ,SUBSTR(CHAR(SPCRPT.PART),1,4)                          AS PART          

 ,SUBSTR(CHAR(SPCRPT.TROW),1,10)                         AS TOT_ROWS          

 ,SUBSTR(CHAR(SPCRPT.EXTENTS),1,4)                       AS EXTENTS               

 ,SUBSTR(CHAR(SPCRPT.ALLOC_SPACE_INKB),1,10)             AS ALLOC_SPC_INKB              

 ,SUBSTR(CHAR(SPCRPT.MAX_SPACE_INKB),1,10)               AS MAX_SPC_INKB              

 ,SUBSTR(CHAR(SPCRPT.ALLOC_SPACE_INPAGES),1,10)          AS ALLOC_SPC_INPAGES          

 ,SUBSTR(CHAR(SPCRPT.MAX_SPACE_INKB/SPCRPT.PGSIZE),1,10) AS MAX_SPC_INPAGES                                

 ,SUBSTR(CHAR(ROUND((SPCRPT.ALLOC_SPACE_INPAGES*100)/      

                   ((SPCRPT.MAX_SPACE_INKB/                            

                   (SPCRPT.PGSIZE-((SPCRPT.PGSIZE*SPCRPT.PCTFREE)/100)))

                    -SPCRPT.FREEPAGE),2)),1,4)           AS USED_PAGES_PERC                                  

 ,SUBSTR(CHAR(SPCRPT.ALLOC_SPACE_INTRACKS),1,8)          AS ALLOC_SPC_INTRKS            

 ,SUBSTR(CHAR(SPCRPT.MAX_SPACE_INTRACKS),1,8)            AS MAX_SPC_INTRKS           

 ,SUBSTR(CHAR(ROUND((SPCRPT.ALLOC_SPACE_INTRACKS*100)      

                   /MAX_SPACE_INTRACKS,2)),1,4)          AS USED_TRACKS_PERC

FROM(

    SELECT

      STATS.DBNAME

     ,STATS.NAME

     ,CASE WHEN STATS.PARTITION IS NULL THEN -1

      ELSE STATS.PARTITION

      END AS PART

     ,CASE WHEN STATS.TOTALROWS IS NULL THEN -1

      ELSE STATS.TOTALROWS

      END AS TROW

     ,CASE WHEN STATS.EXTENTS IS NULL THEN -1

      ELSE STATS.EXTENTS

      END AS EXTENTS

     ,CASE WHEN STATS.SPACE IS NULL THEN -1

      ELSE STATS.SPACE

      END AS ALLOC_SPACE_INKB

     ,(STATS.SPACE/48) AS ALLOC_SPACE_INTRACKS

     ,CASE WHEN STATS.NACTIVE IS NULL THEN -1

      ELSE STATS.NACTIVE

      END AS ALLOC_SPACE_INPAGES

     ,TS.PGSIZE

     ,TP.FREEPAGE

     ,TP.PCTFREE

     ,(CASE

        WHEN SUBSTR(TS.DSSIZE,1,1) IN (0,2,3,4,8) AND TS.TYPE=' ' THEN CEILING((64*1024*1024)/48)

        WHEN SUBSTR(TS.DSSIZE,1,2)=16             AND TS.TYPE=' ' THEN CEILING((64*1024*1024)/48)

        WHEN SUBSTR(TS.DSSIZE,1,1)=2  THEN CEILING((2*1024*1024)/48) 

        WHEN SUBSTR(TS.DSSIZE,1,1)=3  THEN CEILING((32*1024*1024)/48)

        WHEN SUBSTR(TS.DSSIZE,1,1)=4  THEN CEILING((4*1024*1024)/48) 

        WHEN SUBSTR(TS.DSSIZE,1,1)=6  THEN CEILING((64*1024*1024)/48)

        WHEN SUBSTR(TS.DSSIZE,1,1)=8  THEN CEILING((8*1024*1024)/48) 

        WHEN SUBSTR(TS.DSSIZE,1,2)=16 THEN CEILING((16*1024*1024)/48)

        ELSE CEILING((4*1024*1024)/48)

      END) AS MAX_SPACE_INTRACKS

     ,(CASE TS.DSSIZE                                     

        WHEN 0 THEN                                       

            CASE WHEN TS.TYPE = 'G' THEN 4194304   

                 WHEN TS.TYPE = 'L' THEN 4194304   

                 WHEN TS.TYPE = 'O' THEN 4194304   

                 WHEN TS.TYPE = 'P' THEN 4194304   

                 WHEN TS.TYPE = 'R' THEN 4194304  

                 WHEN TS.TYPE = ' ' THEN 67108864

                 ELSE                                  

                  CASE WHEN TS.PARTITIONS > 254 THEN         

                       CASE WHEN TS.PGSIZE = 4  THEN  4194304

                            WHEN TS.PGSIZE = 8  THEN  8388608

                            WHEN TS.PGSIZE = 16 THEN 16777216

                            WHEN TS.PGSIZE = 32 THEN 33554432

                       ELSE NULL             

                       END                        

                       WHEN TS.PARTITIONS > 64 THEN 4194304

                       WHEN TS.PARTITIONS > 32 THEN 1048576  

                       WHEN TS.PARTITIONS > 16 THEN 2097152  

                       WHEN TS.PARTITIONS > 0  THEN 4194304  

                       ELSE 2097152                              

                  END                                       

            END

      ELSE TS.DSSIZE      

      END) AS MAX_SPACE_INKB      

    FROM SYSIBM.SYSTABLESPACESTATS STATS

         INNER JOIN

         SYSIBM.SYSTABLESPACE TS

         ON STATS.DBNAME = TS.DBNAME AND

            STATS.NAME = TS.NAME

         INNER JOIN

         SYSIBM.SYSTABLEPART TP

         ON STATS.DBNAME = TP.DBNAME AND

            STATS.NAME = TP.TSNAME AND

            STATS.PARTITION = TP.PARTITION

    WHERE STATS.DBNAME LIKE 'LP%'

       OR STATS.DBNAME LIKE 'ARCH%'

       OR STATS.DBNAME LIKE 'DPOODS%'

       OR STATS.DBNAME LIKE 'DP1%'

       OR STATS.DBNAME LIKE 'DBPROD%'             

) AS SPCRPT

WHERE DECIMAL((SPCRPT.ALLOC_SPACE_INPAGES*100)/((SPCRPT.MAX_SPACE_INKB/(SPCRPT.PGSIZE-((SPCRPT.PGSIZE*5)/100)))-SPCRPT.FREEPAGE),4,2) > 80

ORDER BY 1, 2, 3

WITH UR;

2.       To get the details of Indexes whose Used Pages percentage crossed 80.

SELECT

   SPCRPT.DBNAME

  ,SPCRPT.NAME

  ,SPCRPT.PARTITION

  ,SPCRPT.TOTALENTRIES

  ,SPCRPT.EXTENTS

  ,SPCRPT.ALLOC_SPACE_INKB

  ,SPCRPT.MAX_SPACE_INKB

  ,SPCRPT.ALLOC_SPACE_INPAGES

  ,(SPCRPT.MAX_SPACE_INKB/SPCRPT.PAGE_SIZE) AS MAX_SPACE_INPAGES

  ,DECIMAL((SPCRPT.ALLOC_SPACE_INPAGES*100)/((SPCRPT.MAX_SPACE_INKB/(SPCRPT.PAGE_SIZE-((SPCRPT.PAGE_SIZE*10)/100)))-SPCRPT.FREEPAGE),6,2) AS USED_PAGES_PERC

  ,SPCRPT.ALLOC_SPACE_INTRACKS, (SPCRPT.MAX_SPACE_INKB/48) AS MAX_SPACE_INTRACKS

FROM(

     SELECT

        STATS.DBNAME, STATS.NAME, STATS.PARTITION, STATS.TOTALENTRIES, STATS.EXTENTS,

        STATS.SPACE AS ALLOC_SPACE_INKB, (STATS.SPACE/48) AS ALLOC_SPACE_INTRACKS,

        STATS.NACTIVE AS ALLOC_SPACE_INPAGES, IX.PGSIZE, IP.FREEPAGE,

        CASE

          WHEN IX.PIECESIZE=0  THEN MIN(TS.DSSIZE,4294967296/(MIN(4096,4294967296/(TS.DSSIZE/TS.PGSIZE)))*IX.PGSIZE)

          WHEN IX.PIECESIZE<>0 THEN 32*IX.PIECESIZE

          ELSE IX.PIECESIZE

        END AS MAX_SPACE_INKB,

        CASE

          WHEN IX.PGSIZE=4096 THEN 4

          ELSE IX.PGSIZE

        END AS PAGE_SIZE

     FROM SYSIBM.SYSINDEXSPACESTATS STATS

           INNER JOIN

        SYSIBM.SYSINDEXES IX ON

            STATS.DBNAME = IX.DBNAME AND

            STATS.NAME = IX.NAME

           INNER JOIN

        SYSIBM.SYSINDEXPART IP ON

            STATS.DBNAME = IP.IXCREATOR AND

            STATS.NAME = IP.IXNAME AND

            STATS.PARTITION = IP.PARTITION

           INNER JOIN

        SYSIBM.SYSTABLESPACE TS ON

            STATS.DBNAME = TS.DBNAME AND

            STATS.PSID = TS.PSID

     WHERE STATS.DBNAME LIKE 'LP%'

        OR STATS.DBNAME LIKE 'ARCH%'

        OR STATS.DBNAME LIKE 'DPOODS%'

        OR STATS.DBNAME LIKE 'DP1%'

        OR STATS.DBNAME LIKE 'DBPROD%'             

) AS SPCRPT

WHERE DECIMAL((SPCRPT.ALLOC_SPACE_INPAGES*100)/((SPCRPT.MAX_SPACE_INKB/(SPCRPT.PAGE_SIZE-((SPCRPT.PAGE_SIZE*10)/100)))-SPCRPT.FREEPAGE),6,2) > 80

ORDER BY 1, 2, 3

WITH UR;

3.       To get the Storage details of all Tablespaces, grouped by Database.

SELECT                                                

  SPCRPT.DBNAME

 ,SUM(SPCRPT.USED_SPACE_INMB)        AS USED_SPACE_ALLTS_MEGABYTES

 ,SUM(SPCRPT.ALLOC_SPACE_INMB)       AS ALLOC_SPACE_ALLTS_MEGABYTES

 ,SUM(SPCRPT.TOTAL_AVAIL_SPACE_INGB) AS MAX_SPACE_ALLTS_GROW_GIGABYTES

FROM(

     SELECT

      STATS.DBNAME

     ,((STATS.DATASIZE/1024)/1024) AS USED_SPACE_INMB

     ,(STATS.SPACE/1024)           AS ALLOC_SPACE_INMB

     ,((CASE TS.DSSIZE                                     

        WHEN 0 THEN                                       

            CASE WHEN TS.TYPE = 'G' THEN 4194304   

                 WHEN TS.TYPE = 'L' THEN 4194304   

                 WHEN TS.TYPE = 'O' THEN 4194304   

                 WHEN TS.TYPE = 'P' THEN 4194304   

                 WHEN TS.TYPE = 'R' THEN 4194304  

                 WHEN TS.TYPE = ' ' THEN 67108864

                 ELSE                                  

                  CASE WHEN TS.PARTITIONS > 254 THEN         

                       CASE WHEN TS.PGSIZE = 4  THEN  4194304

                            WHEN TS.PGSIZE = 8  THEN  8388608

                            WHEN TS.PGSIZE = 16 THEN 16777216

                            WHEN TS.PGSIZE = 32 THEN 33554432

                       ELSE NULL             

                       END                        

                       WHEN TS.PARTITIONS > 64 THEN 4194304

                       WHEN TS.PARTITIONS > 32 THEN 1048576  

                       WHEN TS.PARTITIONS > 16 THEN 2097152  

                       WHEN TS.PARTITIONS > 0  THEN 4194304  

                       ELSE 2097152                              

                  END                                       

            END

      ELSE TS.DSSIZE      

      END)/1024)/1024 AS TOTAL_AVAIL_SPACE_INGB      

     FROM SYSIBM.SYSTABLESPACESTATS STATS

         INNER JOIN

         SYSIBM.SYSTABLESPACE TS

         ON STATS.DBNAME = TS.DBNAME AND

            STATS.NAME = TS.NAME

     WHERE STATS.DBNAME LIKE 'LP%'            

) AS SPCRPT

GROUP BY SPCRPT.DBNAME

WITH UR;

4.       To get the Storage details of all Indexes, grouped by Database.

SELECT

  SPCRPT.DBNAME

 ,SUM(SPCRPT.ALLOC_SPACE_INMB)       AS ALLOC_SPACE_ALLTS_MEGABYTES

 ,SUM(SPCRPT.TOTAL_AVAIL_SPACE_INMB) AS MAX_SPACE_ALLTS_GROW_MEGABYTES

FROM(

     SELECT

        STATS.DBNAME

       ,(STATS.SPACE/1024) AS ALLOC_SPACE_INMB

       ,(CASE

          WHEN IX.PIECESIZE=0  THEN MIN(TS.DSSIZE,4294967296/(MIN(4096,4294967296/(TS.DSSIZE/TS.PGSIZE)))*IX.PGSIZE)

          WHEN IX.PIECESIZE<>0 THEN 32*IX.PIECESIZE

          ELSE IX.PIECESIZE

         END)/1024 AS TOTAL_AVAIL_SPACE_INMB

     FROM SYSIBM.SYSINDEXSPACESTATS STATS

            INNER JOIN

          SYSIBM.SYSINDEXES IX ON

              STATS.DBNAME = IX.DBNAME AND

              STATS.NAME = IX.NAME

            INNER JOIN

          SYSIBM.SYSTABLESPACE TS ON

              STATS.DBNAME = TS.DBNAME AND

              STATS.PSID = TS.PSID

     WHERE STATS.DBNAME LIKE 'LP%'

) AS SPCRPT

GROUP BY SPCRPT.DBNAME

WITH UR;