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 rebuilding

Your new assignment is to build a shadow of an application that runs on a non-data sharing system for use in auditing. At the target site, some new indexes are created to facilitate the audit. The audit application begins at the end of the year.

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

Important

This example uses REBUILD INDEX, which allows you to have different source and target indexes.

You can also use this technique when you want to create a different set of indexes for your target spaces on the same Db2 system or at the same site.

To move an application to another subsystem with index rebuilding

  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.

    The 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 Db2 logging messages 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. (See Creating-a-consistent-database-for-query-and-review.) 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 the previous step. 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.

    Important

    AFRACCT is partitioned, but the OUTCOPY ASCODED option is used to make a copy of 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)
    //SYSIN    DD *
     OPTIONS OUTCOPY ASCODED
     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)
        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 these 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 or tables that affect their internal structure. The AFRTRAN table space must remain partitioned and use the same key. You may change or add to the other indexes used because you are rebuilding them on the target Db2.

    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 target 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 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, and tables on the target system. They need to be consistent in structure with the source system. (BMC AMI Recover provides migration checking when INCOPY is used.)

    The partitioned index needs to be the same, but the other indexes can be deleted or changed. You can also create new indexes. In this example, you add a new index, AFRTRANX, as shown in the following figure.

    CREATE DATABASE AFRAUDIT;
    CREATE TABLESPACE AFRHOLD1 IN AFRAUDIT USING STOGROUP AFRTEST;
    CREATE TABLESPACE AFRHOLD2 IN AFRAUDIT USING STOGROUP AFRTEST;
    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 AFRTRANX ON AFRTRAN (CLERKID)
           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 and set up translation.On the target system, obtain the object IDs that you need for translation. You can do this by using SQL statements to query the catalog. The example SQL statements and 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 |         6 |
     2_| AFRHIST  |       336 |        12 |
     3_| AFRSUMM  |       336 |        10 |
     4_| AFRTRAN  |       336 |         8 |
       +----------------------------------+

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

       +--------------------------------+
       |        NAME        |   OBID    |
       +--------------------------------+
     1_| AFRACCT            |        13 |
     2_| AFRTRAN            |        14 |
     3_| AFRSUMM            |        15 |
     4_| AFRHIST            |        16 |
       +--------------------------------+

  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 with BMC AMI Recover. 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.

    //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.DSNEXIT
    //          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)
    //SORTOUT   DD DUMMY
    //SYSIN     DD *
    OPTIONS OUTCOPY ASCODED
     RECOVER  TABLESPACE AFRAUDIT.AFRACCT
         OBIDXLAT RESET DBID(321,336)   PSID(2,6)
             OBID(9,13)
               INCOPY
                 FULL DSNAME AFR.ACCTCPY1
                   SHRLEVEL REFERENCE
                   INVOLUME 311778
                   INDEVT CART
                OUTCOPY YES  OUTCOPYDDN(ACCTCPY)
                TOCOPY LASTCOPY
     RECOVER  TABLESPACE AFRAUDIT.AFRTRAN
         OBIDXLAT RESET DBID(321,336)   PSID(4,8)
             OBID(10,14)
               INCOPY
                 FULL DSNAME AFR.TRANCPY1
                   SHRLEVEL REFERENCE
                   INVOLUME 311763
                   INDEVT CART
               OUTCOPY YES OUTCOPYDDN(TRANCPY)
               TOCOPY LASTCOPY
      RECOVER  TABLESPACE AFRAUDIT.AFRSUMM
          OBIDXLAT RESET DBID(321,336)   PSID(6,10)
              OBID(11,15)
                INCOPY
                  FULL DSNAME AFR.SUMMCPY1
                    SHRLEVEL REFERENCE
                    INVOLUME 311733
                    INDEVT CART
                OUTCOPY YES OUTCOPYDDN(SUMMCPY)
                TOCOPY LASTCOPY
     RECOVER  TABLESPACE AFRAUDIT.AFRHIST
         OBIDXLAT RESET DBID(321,336)   PSID(8,12)
             OBID(12,16)
               INCOPY
                 FULL DSNAME AFR.HISTCPY1
                     SHRLEVEL REFERENCE
                     INVOLUME 311722
                     INDEVT CART
                OUTCOPY YES OUTCOPYDDN(HISTCPY)
                TOCOPY LASTCOPY
     REBUILD INDEX(ALL)
             TABLESPACE AFRAUDIT.AFRACCT
                 NOWORKDDN
     REBUILD INDEX(ALL)
             TABLESPACE AFRAUDIT.AFRTRAN
                 NOWORKDDN
     REBUILD INDEX(ALL)
             TABLESPACE AFRAUDIT.AFRSUMM
                 NOWORKDDN
     REBUILD INDEX(ALL)
             TABLESPACE AFRAUDIT.AFRHIST
                 NOWORKDDN
  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*