Bharath's Tech Blog

New approach for data migration using Redirected Recovery

As mentioned in many of my previous blog posts, we use Redirected Recovery of Recovery Expert for migrating data from one Db2 sub-system to other sub-system. 

We are facing some hurdles whenever we do data migration using Redirected Recovery. Some of the major hurdles are

•    TAPE handling: All our Image copy datasets are written to TAPE with STACK YES option. Currently, Redirected Recovery is not able to generate the Recovery JCLs appropriately when the Image Copy datasets are on TAPE. I created RFE 114350 for the product to do upfront analysis on the objects (included for data migration) to ensure all the objects on the same TAPE are included in the same Recovery job.
•    Data verification after migration: There is no simple approach for verifying that the data is copied properly from source database to target database.

 I devised a new approach to overcome the hurdles. The new approach is lengthy in execution but makes the data migration process less painful.

The process will be executed in the below mentioned sequence of steps, whenever a data migration is requested.

1.   Create DISK backups

As Redirected Recovery is unable to handle the TAPE image copies properly (for now), we decided to create DISK backups. 

We may go with DISK backups even after TAPE related problems are resolved as data migration will be faster with DISK backups.

IBM DB2 Automation Tool for z/OS doesn’t have the capability (as of now) to generate COPYTOCOPY…TOCOPY JCLs (I created RFE 114086 for including this capability into the Tool) So, I coded a REXX routine that will build COPYTOCOPY JCLs for us. The REXX routine will build JCLs to create LB copies if there are no LB copies already for the source database, and will build JCLs to create RP copies if there are LB copies on TAPE already for the source database.

2.   Execute Checklist

Checklist is for ensuring all the Tablespaces included in the data migration have image copies. More details about the Checklist can be found in this blog post.

3.   Execute Object structure comparison

We usually have object structure mismatch between source database and target database. So, we execute object structure comparison to find out the objects (Tablespaces, Tables & Indexes) that have different structures. Such objects will be excluded from the data migration process. 

More details about the Checklist can be found in this blog post.

4.   Create Application profiles

Application profiles with the objects needed for data migration are created in the Recovery Expert panels (objects with structure mismatches will be excluded).

5.   Build Recovery JCLs

A JCL with Application profile details is submitted, which will build Recovery JCLs for us.

6.   Execute Recovery JCLs

Recovery JCLs built in above step are executed. These JCLs use the DISK backups (created in step 1) as source. 

Even after excluding the objects with structure mismatches, there are chances of data migration jobs failing due to version mismatches. REPAIR CATALOG utility will be executed on the objects failed due to version mismatches.

7.   Verify data in target database

This is a major concern for us as it’s really difficult to verify data in target database after data migration is complete to make sure all the data is migrated from source database to target database. After discussion with Recovery Expert developers and IBM tech expert Robert Catterall, I came up with the following ideas for post-migration verification.

• Comparing source’s COPYPAGESF (from SYSCOPY) with target’s NACTIVE (from SYSTABLESPACESTATS)

          I coded a SQL query that can compare source database with target database. 

          But, the results are not 100% accurate as COPY utility will not copy empty pages.

WITH

   TSSTAT(NAME, NACTIVE, TYPE) AS

     (SELECT 

         ST.NAME

        ,SUM(ST.NACTIVE)

        ,TS.TYPE

      FROM SYSIBM.SYSTABLESPACESTATS ST

             INNER JOIN

           SYSIBM.SYSTABLESPACE TS ON

               ST.DBNAME = TS.DBNAME AND

               ST.NAME   = TS.NAME 

      WHERE 

         ST.DBNAME = <target db> AND

         TS.NTABLES > 0

      GROUP BY ST.NAME, TS.TYPE)

  ,TSCOPY(NAME, COPYPAGES) AS

     (SELECT 

         TSNAME

        ,SUM(COPYPAGESF) 

      FROM SYSIBM.SYSCOPY

      WHERE 

         DBNAME = <source db> AND

         ICDATE = 171226   AND

         ICTYPE = 'F' AND

         ICBACKUP <> 'LB'

      GROUP BY TSNAME)

SELECT 

   TSSTAT.NAME

  ,TSSTAT.TYPE

  ,TSCOPY.COPYPAGES AS SRC_ICPAGES

  ,TSSTAT.NACTIVE AS TGT_TSPAGES

FROM 

    TSSTAT

     INNER JOIN

    TSCOPY ON 

       TSSTAT.NAME=TSCOPY.NAME

WHERE TSSTAT.NACTIVE <> TSCOPY.COPYPAGES;

• Comparing source’s CARDF (from SYSIBM.SYSTABLES_HIST) with target’s CARDF (from SYSIBM.SYSTABLES after RUNSTATS are done)

          This requires RUNSTATS to be executed on all databases everyday to update stats in History Tables.

• 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)

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

We decided to go with the third idea as that is easy to implement.

8.   Execute RUNSTATS

We just execute regular RUNSTATS JCLs

9.   Create Image copies for target database

We just execute regular image copies (LP)

10. Delete DISK backups

Once verification is done and application team is happy with the data in target database, DISK backups created in step 1 will be deleted.