Post-migration verification, Redirected Recovery

In my previous blog, I mentioned about the concern we have whether the data got copied from source to target or not, and also mentioned the 4 ideas/approaches we came up with for the verification.

After internal team discussion, we decided to implement the below approach.

• Take backups of target after data migration is done, and compare source’s COPYPAGESF (from SYSCOPY) with target’s COPYPAGESF (from SYSCOPY) 

This is how I implemented the approach.
This process will be executed after data migration.

1. Create image copies for the Target database.
2. Execute a JCL that has 3 steps

• First step executes a SELECT query on source's SYSIBM.SYSCOPY to get data (based on the ICDATE we used for data migration) from these columns into a PS file.

DBNAME
TSNAME
ICDATE
ICTIME
DSNAME
DSNUM
COPYPAGESF

• Second step executes SELECT query on target's SYSIBM.SYSCOPY to get data (for the image copies created just before starting this process) from the same columns mentioned above into a PS file.
• Third step executes a REXX routine (I coded) that will compare the PS files generated in above  mentioned steps.

This approach is chosen as our backup strategy.

• Reading source’s SYSTABLESPACESTATS data into a personal Table on daily basis, and comparing the data in personal Table with target’s real-time stats data (when needed)

This approach is bit complicated to implement, as we need to get the RTS details at the time of image copies done for the databases.
This approach can be implemented in 2 ways.

1. Schedule a job that extracts the details from SYSTABLESPACESTATS and loads into a user-defined Table. This job will be triggered by the image copies jobs. 

          The main concerns with this way of implementation are

• Elaborate planning as the schedule of image copy jobs has to be changed.
• RTS details may be of not much use for verification if image copies are not SHARELEVEL REFERENCE

    2. In our Mainframes setup, RTS details are externalized every 30 minutes. So, a job can be executed every 25 minutes, which extracts the details from SYSTABLESPACESTATS and loads into a user-defined Table. 

          The main concerns with this way of implementation are

• Time constraint. If the extract job runs long due to low priority in the system, then we may miss the RTS details.
• We may be getting too many details than needed, as RTS details get loaded every 30 minutes.

Checklist for data migration using Redirected Recovery

In many of my previous blogs I mentioned that we (DBA team) use Redirected Recovery of Recovery Expert for z/OS v3.2 for data migrations from one DB2 sub-system to another DB2 sub-system. The data migration (using Redirected Recovery) is done in 3 phases.

      Phase 1: Create Profiles and update Recovery options in Recovery Expert

This is pretty straight-forward process. We add all the Tablespaces in a database needed for data migration to an Application profile. Usually, nothing can go wrong in this phase.

      Phase 2: Execute Build JCL (that builds/generates the data migration JCLs)

In the Build JCL, we provide the following details to build/generate the data migration JCLs.

       a)      Application profile name (having the objects to be migrated)

       b)     Timestamp for PIT recovery 

             (Redirected Recovery can do only PIT recovery to another sub-system)

       c)      PDS name (data migration JCLs will be created in this PDS)

       d)     Plan number for the Recovery (its 29 for Redirected Recovery)

This job can fail for many reasons and the frustrating part is that it’s not easy to find out the reason for job failure. Most of the time we see just the ARYZ018E error message, with list of Objects that can’t be recovered. And, reason why the Objects are recoverable will be not be provided in the job output.

After discussions with Rocket Software tool support team, got the checklist to go through for Build job to be successful. The checklist provides the details of Objects that can lead to failure of Build job. So, these Objects will be excluded from data migration process. With the help of JCL and REXX, I automated the checklist. The JCL will have multiple steps to verify the below.

1. Check whether all TS’ in the Source database has ICTYPE=’F’ entries in SYSCOPY for a given ICDATE. I use this SQL query for verification.

SELECT

   DBNAME

  ,NAME

FROM SYSIBM.SYSTABLESPACE

WHERE

   DBNAME=<dbname> AND

   NAME NOT IN

              (SELECT

                 TSNAME

               FROM SYSIBM.SYSCOPY

               WHERE

                  DBNAME=<dbname> AND

                  ICDATE=<date>)

WITH UR;

2. Check whether all Image copy datasets (of the TS’ got from the above SQL query) are present in ICF catalog. I coded the below REXX routine for this verification.

LIST_PARA:                                                                     

 SAY '*****SYSCOPY ENTRIES NOT IN ICF CATALOG*****'                            

 DO I = 1 TO INP.0                                                              

   IC_DSN = STRIP(INP.I,"B")                                                   

   X = OUTTRAP(LSTC.)                                                          

    ADDRESS TSO "LISTCAT ENT('"IC_DSN"') "                                     

   X=OUTTRAP('OFF')                                                            

   Y = STRIP(WORD(LSTC.1,1),"B")                                               

   IF Y <> 'NONVSAM'                                                            

     THEN SAY IC_DSN NOT FOUND IN ICF CATALOG                                  

 END                                                                           

 SAY '********************************************'                             

RETURN;

3. Check whether any Objects (of TS’ included in the Application profile) are altered.

The CURRENT_VERSION of the TS can change for many reasons. Redirected Recovery can’t migrate data from versioned TS to non-versioned TS. I coded Objects’ structures comparison process to verify this.
Versioning can also be found using the below query.

SELECT

   TS.DBNAME

  ,TS.NAME

  ,TB.NAME

  ,TS.OLDEST_VERSION

  ,TS.CURRENT_VERSION

  ,TB.VERSION

FROM SYSIBM.SYSTABLESPACE TS

     INNER JOIN

SYSIBM.SYSTABLES TB ON

   TS.DBNAME = TB.CREATOR AND

   TS.DBNAME = TB.DBNAME  AND

   TS.NAME = TB.TSNAME

WHERE

   TS.DBNAME='dbname'  AND

   TB.TYPE = 'T'       AND

   TS.CURRENT_VERSION <> TB.VERSION;

4. PIT timestamp used in Build job must be greater than the highest START_RBA for a given DBNAME and ICDATE. I use this SQL query to get the value.

SELECT TIMESTAMP((MAX(START_RBA))||X'0001') + CURRENT TIMEZONE

FROM SYSIBM.SYSCOPY

WHERE

   DBNAME=<DBNAME> AND

   ICDATE=<DATE>

WITH UR;

         Phase 3: Execution of the data migration JCLs

This phase can go smooth if we follow the checklist.

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;