Limited supportBMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see BMC AMI Recover for Db2 13.1.

Moving an application to another subsystem with index recovery

For use in auditing, you need to build a shadow of an application that runs on a non-data sharing system. The audit application begins at the end of the year.

Important

In this example, RECOVER INDEX is used; therefore, the source and target indexes must be the same.

The table spaces and index spaces for the shadow application are on another Db2 subsystem that is on another computer system with no shared DASD.

To move an application to another subsystem with index recovery

  1. Obtain an RBA for a consistent view of the source table spaces.The table spaces for the application are AFRACCT, AFRTRAN, AFRSUMM, and AFRHIST.

    These table spaces are in database AFRFIN. They need to be at a consistent point for the migration. The point at which logging starts after Db2 goes down normally, flushes all buffers, handles all transactions, and comes up again, can be used for a point of consistency. A sample of messages from Db2 logging is shown in the following figure.

    DSNR004I > RESTART...UR STATUS COUNTS
    IN COMMIT=0, INDOUBT=0, INFLIGHT=0, IN ABORT=0
    DSNR005I > RESTART...COUNTS AFTER FORWARD RECOVERY
    IN COMMIT=0, INDOUBT=0
    DSNR006I > RESTART...COUNTS AFTER BACKWARD RECOVERY
     .
     .
     .
    DSNJ099I > LOG RECORDING TO COMMENCE WITH
     STARTRBA=0004D176C05C

    Important

    Rather than having to determine the RBA for the last shutdown, in this example the option TORBA LASTSHUTDOWN is used to allow BMC AMI Recover to select the last shutdown RBA. This technique is used because the system is taken down for year-end synchronization. Another technique for establishing a point of consistency is to issue a QUIESCE command for all of the table spaces in one statement. You could also use the point of consistency from an ARCHIVE LOG MODE(QUIESCE) command.

  2. Create migration images.Run BMC AMI Recover to create migration images. The syntax is completely described in Command-and-syntax-reference.

    In the example JCL shown in the following figure, you create migration images of the table spaces at the point of the pause of the Db2 subsystem that you captured in step 1. These data sets are identical in format to an image copy or DSN1COPY of the spaces. However, you do not register these copies because they are going to be shipped to the audit system. Also, because they are going to a different computer system, you do not catalog these copies with the operating system.

    If you want to make registered copies, see Making-copies-from-the-log-and-earlier-copies. You may combine these operations.

    Important

    AFRACCT is partitioned, but the OUTCOPY ASCODED option is used to make a copy that is for all of the partitions.

    //RECOUTC1 EXEC PGM=AFRMAIN,REGION=0M,
    // PARM='DBAN,AFRAQDN,NEW,MSGLEVEL(1)'
    //STEPLIB  DD DISP=SHR,DSN=product.libraries
    //         DD DISP=SHR,DSN=DB2.DSNEXIT
    //         DD DISP=SHR,DSN=DB2.DSNLOAD
    //SYSPICK  DD SYSOUT=*
    //ACCTCPY  DD DSN=AFR.ACCTCPY1,
    // UNIT=CART,
    // DISP=(NEW,KEEP)
    //TRANCPY  DD DSN=AFR.TRANCPY1,
    // UNIT=CART,
    // DISP=(NEW,KEEP)
    //SUMMCPY  DD DSN=AFR.SUMMCPY1,
    // UNIT=CART,
    // DISP=(NEW,KEEP)
    //HISTCPY  DD DSN=AFR.HISTCPY1,
    // UNIT=CART,
    // DISP=(NEW,KEEP)
    //ACCICPY  DD DSN=AFR.ACCICPY1,
    // UNIT=CART,
    // DISP=(,KEEP)
    //TRNICPY  DD DSN=AFR.TRNICPY1,
    // UNIT=CART,
    // DISP=(,KEEP)
    //SUMICPY  DD DSN=AFR.SUMICPY1,
    // UNIT=CART,
    // DISP=(,KEEP)
    //HISICPY  DD DSN=AFR.HISICPY1,
    // UNIT=CART,
    // DISP=(,KEEP)
    //SYSIN    DD *
     OPTIONS OUTCOPY ASCODED INDEXLOG YES
     RECOVER  TABLESPACE AFRFIN.AFRACCT
                    OUTCOPY ONLY REGISTER NONE OUTCOPYDDN(ACCTCPY)
              TABLESPACE AFRFIN.AFRTRAN
                    OUTCOPY ONLY REGISTER NONE OUTCOPYDDN(TRANCPY)
              TABLESPACE AFRFIN.AFRSUMM
                    OUTCOPY ONLY REGISTER NONE OUTCOPYDDN(SUMMCPY)
              TABLESPACE AFRFIN.AFRHIST
                    OUTCOPY ONLY REGISTER NONE OUTCOPYDDN(HISTCPY)
              INDEX OWNERID.AFRACCTI
                    OUTCOPY ONLY REGISTER NONE OUTCOPYDDN(ACCICPY)
              INDEX OWNERID.AFRTRANI
                    OUTCOPY ONLY REGISTER NONE OUTCOPYDDN(TRNICPY)
              INDEX OWNERID.AFRSUMMI
                    OUTCOPY ONLY REGISTER NONE OUTCOPYDDN(SUMICPY)
              INDEX OWNERID.AFRHISTI
                    OUTCOPY ONLY REGISTER NONE OUTCOPYDDN(HISICPY)
        TORBA LASTSHUTDOWN

  3. Obtain data definitions.Obtain the definitions of the database, table spaces, and tables for which you want to create the audit application for querying. These are the source objects. Also, obtain definitions for the indexes if you want to create them in the audit application.

    Important

    See Defining-target-objects-for-migration for more information.

    You cannot change any of the characteristics of the table space, tables, or indexes that affect their internal structure. The AFRTRAN table space must remain partitioned and use the same key. If you want to change or add to the indexes used, you must use the technique shown in Moving-an-application-to-another-subsystem-with-index-rebuilding.

    For this example, you would obtain the DDL shown in the following figure.

    CREATE DATABASE AFRFIN;
    CREATE TABLESPACE AFRACCT  IN AFRFIN   USING STOGROUP AFRTEST
                      NUMPARTS 3;
    CREATE TABLESPACE AFRTRAN  IN AFRFIN   USING STOGROUP AFRTEST;
    CREATE TABLESPACE AFRSUMM  IN AFRFIN   USING STOGROUP AFRTEST;
    CREATE TABLESPACE AFRHIST  IN AFRFIN   USING STOGROUP AFRTEST;
    CREATE TABLE AFRACCT    (
                             ACCTNO     INT
                            ,ACCTDESC   CHAR(30)
                            ,ACCTTYPE   CHAR(10)
                            ,BALANCE    DECIMAL (10,2)
                            )
           IN AFRFIN.AFRACCT;
    CREATE TABLE AFRTRAN    (
                             ACCTNO     INT
                            ,TRANSDATE  DATE
                            ,TRANSDESC  CHAR(40)
                            ,CLERKID    CHAR(4)
                            ,TOTAL      DECIMAL (10,2)
                            )
           IN AFRFIN.AFRTRAN;
    CREATE TABLE AFRSUMM     (
                              ACCTNO     INT
                             ,REPCAT     CHAR (40)
                             ,SUMMCAT    CHAR(40)
                             ,SUMMDATE   DATE
                             ,TOTAL      DECIMAL (10,2)
                             )
           IN AFRFIN.AFRSUMM;
    CREATE TABLE AFRHIST     (
                              ACCTNO     INT
                             ,TRANSDATE  DATE
                             ,TRANSDESC  CHAR(40)
                             ,CLERKID    CHAR(4)
                             ,TOTAL      DECIMAL (10,2)
                             )
           IN AFRFIN.AFRHIST;
    CREATE INDEX AFRACCTI ON AFRACCT (ACCTNO)
                  USING STOGROUP AFRTEST
         CLUSTER (
                  PART 1 VALUES(30000)
                 ,PART 2 VALUES(60000)
                 ,PART 3 VALUES(99999)
                 );
    CREATE UNIQUE INDEX AFRTRANI ON AFRTRAN (ACCTNO)
                  USING STOGROUP AFRTEST;
    CREATE UNIQUE INDEX AFRSUMMI ON AFRSUMM
                  (ACCTNO,REPCAT,SUMMCAT,SUMMDATE)
                  USING STOGROUP AFRTEST;
    CREATE INDEX AFRHISTI ON AFRHIST (ACCTNO)
                  USING STOGROUP AFRTEST;

  4. Obtain object IDs for the source objects.On the source system, obtain the object IDs. You can do this by using SQL statements to query the catalog or by using some other tool. The example SQL statements and the output are shown in the following figure.

    Important

    You can use the default values for the source DBID and PSID. If the table space contains only one table, you can also use the default values for the source OBIDs. BMC AMI Recover extracts this information from the image copy.

    SELECT NAME, DBID, PSID FROM SYSIBM.SYSTABLESPACE WHERE
           NAME = 'AFRTRAN' OR NAME = 'AFRACCT' OR
           NAME = 'AFRHIST' OR NAME = 'AFRSUMM';


       +----------------------------------+
       |   NAME   |   DBID    |   PSID    |
       +----------------------------------+
     1_| AFRACCT  |       321 |         2 |
     2_| AFRHIST  |       321 |         8 |
     3_| AFRSUMM  |       321 |         6 |
     4_| AFRTRAN  |       321 |         4 |
       +----------------------------------+

    SELECT CREATOR, NAME, INDEXTYPE, DBID, ISOBID, OBID FROM SYSIBM.SYSINDEXES WHERE
           NAME = 'AFRACCTI' OR NAME = 'AFRTRANI' OR
           NAME = 'AFRSUMMI' OR NAME = 'AFRHISTI';

       +-----------------------------------------------------------------+
       | CREATOR  |   NAME   | INDEXTYPE |   DBID    |   ISOBID  | OBID  |
       +-----------------------------------------------------------------+
     1_| OWNERID  | AFRACCTI |     2     |       321 |        14 |   13  |
     2_| OWNERID  | AFRHISTI |     2     |       321 |        20 |   19  |
     3_| OWNERID  | AFRSUMMI |     2     |       321 |        18 |   17  |
     4_| OWNERID  | AFRTRANI |     2     |       321 |        16 |   15  |
       +-----------------------------------------------------------------+

    SELECT NAME, OBID FROM SYSIBM.SYSTABLES WHERE DBNAME = 'AFRFIN';

       +--------------------------------+
       |        NAME        |   OBID    |
       +--------------------------------+
     1_| AFRACCT            |         9 |
     2_| AFRTRAN            |        10 |
     3_| AFRSUMM            |        11 |
     4_| AFRHIST            |        12 |
       +--------------------------------+

  5. Create objects on the target system.Create the database, table spaces, tables, and indexes on the target system using the data definitions in the following figure. They need to be consistent in structure with the source system. (BMC AMI Recover provides migration checking when INCOPY is used.)

    CREATE DATABASE AFRAUDIT;
    CREATE TABLESPACE AFRACCT  IN AFRAUDIT USING STOGROUP AFRTEST
                      NUMPARTS 3;
    CREATE TABLESPACE AFRTRAN  IN AFRAUDIT USING STOGROUP AFRTEST;
    CREATE TABLESPACE AFRSUMM  IN AFRAUDIT USING STOGROUP AFRTEST;
    CREATE TABLESPACE AFRHIST  IN AFRAUDIT USING STOGROUP AFRTEST;
    CREATE TABLE AFRACCT     (
                              ACCTNO     INT
                             ,ACCTDESC   CHAR(30)
                             ,ACCTTYPE   CHAR(10)
                             ,BALANCE    DECIMAL (10,2)
                             )
           IN AFRAUDIT.AFRACCT;
    CREATE TABLE AFRTRAN     (
                              ACCTNO     INT
                             ,TRANSDATE  DATE
                             ,TRANSDESC  CHAR(40)
                             ,CLERKID    CHAR(4)
                             ,TOTAL      DECIMAL (10,2)
                             )
           IN AFRAUDIT.AFRTRAN;
    CREATE TABLE AFRSUMM     (
                              ACCTNO     INT
                             ,REPCAT     CHAR (40)
                             ,SUMMCAT    CHAR(40)
                             ,SUMMDATE   DATE
                             ,TOTAL      DECIMAL (10,2)
                             )
           IN AFRAUDIT.AFRSUMM;
    CREATE TABLE AFRHIST     (
                              ACCTNO     INT
                             ,TRANSDATE  DATE
                             ,TRANSDESC  CHAR(40)
                             ,CLERKID    CHAR(4)
                             ,TOTAL      DECIMAL (10,2)
                             )
           IN AFRAUDIT.AFRHIST;


    CREATE INDEX AFRACCTI ON AFRACCT (ACCTNO)
                  USING STOGROUP AFRTEST
          CLUSTER (
                   PART 1 VALUES(30000)
                  ,PART 2 VALUES(60000)
                  ,PART 3 VALUES(99999)
                  );
    CREATE UNIQUE INDEX AFRTRANI ON AFRTRAN (ACCTNO)
                  USING STOGROUP AFRTEST;
    CREATE UNIQUE INDEX AFRSUMMI ON AFRSUMM
                  (ACCTNO,REPCAT,SUMMCAT,SUMMDATE)
                  USING STOGROUP AFRTEST;
    CREATE INDEX AFRHISTI ON AFRHIST (ACCTNO)
                  USING STOGROUP AFRTEST;

  6. Obtain IDs on the target system.On the target system, obtain the object IDs needed for translation. You can do this by using SQL statements to query the Db2 system catalog. The example SQL statements and the output are shown in the following figure.

    Important

    You can use the default values for the target DBID and PSID. If the table space contains only one table, you can also use the default values for the target OBIDs. BMC AMI Recover extracts this information from the catalog.

    SELECT NAME, DBID, PSID FROM SYSIBM.SYSTABLESPACE WHERE
              NAME = 'AFRTRAN' OR NAME = 'AFRACCT' OR
              NAME = 'AFRHIST' OR NAME = 'AFRSUMM';


       +----------------------------------+
       |   NAME   |   DBID    |   PSID    |
       +----------------------------------+
     1_| AFRACCT  |       336 |         2 |
     2_| AFRHIST  |       336 |         8 |
     3_| AFRSUMM  |       336 |         6 |
     4_| AFRTRAN  |       336 |         4 |
       +----------------------------------+

    SELECT CREATOR, NAME, INDEXTYPE, DBID, ISOBID, OBID FROM SYSIBM.SYSINDEXES WHERE
              NAME = 'AFRACCTI' OR NAME = 'AFRTRANI' OR
              NAME = 'AFRSUMMI' OR NAME = 'AFRHISTI';

       +-----------------------------------------------------------------+
       | CREATOR  |   NAME   | INDEXTYPE |   DBID    |   ISOBID  | OBID  |
       +-----------------------------------------------------------------+
     1_| OWNERID  | AFRACCTI |     2     |       336 |        14 |   13  |
     2_| OWNERID  | AFRHISTI |     2     |       336 |        20 |   19  |
     3_| OWNERID  | AFRSUMMI |     2     |       336 |        18 |   17  |
     4_| OWNERID  | AFRTRANI |     2     |       336 |        16 |   15  |
       +-----------------------------------------------------------------+

    SELECT NAME, OBID FROM SYSIBM.SYSTABLES WHERE DBNAME = 'AFRAUDIT';

       +--------------------------------+
       |        NAME        |   OBID    |
       +--------------------------------+
     1_| AFRACCT            |         9 |
     2_| AFRTRAN            |        10 |
     3_| AFRSUMM            |        11 |
     4_| AFRHIST            |        12 |
       +--------------------------------+

  7. Recover with INCOPY and ID translation.The tapes from the source system are delivered at the target system. You use the external label information to complete the JCL to create data for the target. The ID translation is built from the data in step 4 and step 6. Output copies are made to allow a normal recovery on the audit system, if required.

    Important

    BMC AMI Recover provides migration checking when the INCOPY option is used, and performs header checking and provides messages (BMC40470, BMC40471, and BMC40472) to indicate incompatible migrations. Some examples of incompatible migrations are:

    • Migration of a partitioned table space into a nonpartitioned table space
    • Migration of a segmented table space into a table space with a different segment size
    • Migration into a table space with a different page size

    Example JCL for this process is shown in the following figure.

    Important

    To illustrate that you do not need to specify the DBIDs, OBIDs, and PSIDs for a single table table space with INCOPY, this example JCL does not include them.

    //RECOUTC1 EXEC PGM=AFRMAIN,REGION=0M,
    // PARM='DBAJ,AFRAQDN,NEW,MSGLEVEL(1),,RDB2STAT(YES)'
    //STEPLIB  DD DISP=SHR,DSN=product.libraries
    //         DD DISP=SHR,DSN=DB2.DSNLOAD
    //SYSPICK   DD SYSOUT=*
    //ACCTCPY   DD DSN=AFR.ACCTCPYA,
    // UNIT=SYSDA,SPACE=(CYL,(10,10)),
    // DISP=(NEW,CATLG)
    //TRANCPY   DD DSN=AFR.TRANCPYA,
    // UNIT=SYSDA,SPACE=(CYL,(10,10)),
    // DISP=(NEW,CATLG)
    //SUMMCPY   DD DSN=AFR.SUMMCPYA,
    // UNIT=SYSDA,SPACE=(CYL,(10,10)),
    // DISP=(NEW,CATLG)
    //HISTCPY   DD DSN=AFR.HISTCPYA,
    // UNIT=SYSDA,SPACE=(CYL,(10,10)),
    // DISP=(NEW,CATLG)
    //SYSIN     DD *
    OPTIONS OUTCOPY ASCODED INDEXLOG YES
     RECOVER  TABLESPACE AFRAUDIT.AFRACCT
         OBIDXLAT RESET
               INCOPY
                 FULL DSNAME AFR.ACCTCPY1
                   SHRLEVEL REFERENCE
                   INVOLUME 311778
                   INDEVT CART
               OUTCOPY YES  OUTCOPYDDN(ACCTCPY)
               TOCOPY LASTCOPY
     RECOVER  TABLESPACE AFRAUDIT.AFRTRAN
         OBIDXLAT RESET  
               INCOPY
                 FULL DSNAME AFR.TRANCPY1
                   SHRLEVEL REFERENCE
                   INVOLUME 311763
                   INDEVT CART
               OUTCOPY YES OUTCOPYDDN(TRANCPY)
               TOCOPY LASTCOPY
     RECOVER  TABLESPACE AFRAUDIT.AFRSUMM
         OBIDXLAT RESET  
               INCOPY
                 FULL DSNAME AFR.SUMMCPY1
                   SHRLEVEL REFERENCE
                   INVOLUME 311733
                   INDEVT CART
               OUTCOPY YES OUTCOPYDDN(SUMMCPY)
               TOCOPY LASTCOPY
     RECOVER  TABLESPACE AFRAUDIT.AFRHIST
         OBIDXLAT RESET
               INCOPY
                 FULL DSNAME AFR.HISTCPY1
                   SHRLEVEL REFERENCE
                   INVOLUME 311722
                   INDEVT CART
               OUTCOPY YES OUTCOPYDDN(HISTCPY)
               TOCOPY LASTCOPY
     RECOVER INDEX OWNERID.AFRACCTI
         OBIDXLAT RESET  
               INCOPY
                 FULL DSNAME AFR.ACCICPY1
                   INVOLUME 311744
                   INDEVT CART
               TOCOPY LASTCOPY
     RECOVER INDEX OWNERID.AFRTRANI
         OBIDXLAT RESET  
               INCOPY
                 FULL DSNAME AFR.TRNICPY1
                   INVOLUME 311744
                   INDEVT CART
               TOCOPY LASTCOPY
     RECOVER INDEX OWNERID.AFRSUMMI
         OBIDXLAT RESET  
               INCOPY
                 FULL DSNAME AFR.SUMICPY1
                   INVOLUME 311744
                   INDEVT CART
               TOCOPY LASTCOPY
     RECOVER INDEX OWNERID.AFRHISTI
         OBIDXLAT RESET  
               INCOPY
                 FULL DSNAME AFR.HISICPY1
                   INVOLUME 311744
                   INDEVT CART
               TOCOPY LASTCOPY

    Important

    For an index, if you code an OBID clause, you must code two OBID clauses, one for the index and one for the table on which the index is built. When INCOPY is specified for an index, you may omit both OBID clauses, in which case the source OBID for the index and the table is taken from the image copy. If you do not omit the ID clauses, you must code both the target and source values for both OBID clauses.

  8. Allow processing on the audit system.The audit system is now ready for use.

Related topic

Migrating-data



 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*