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.

Creating a consistent database for query and review

Every evening at midnight, a version of an order and shipping database must be migrated to a query database. To create management reports, you must be able to execute long-running queries on the query database without interfering with online transactions.

Orders and shipping data are added by the online transactions. These transactions cannot interfere with the consistency of a series of queries against the query database that remains static while you create the management reports.

In this example, the databases are defined on the same Db2 subsystem. Your challenge is to create a consistent database for query and review (see the following figure).

GUID-6DB62187-619A-450B-81E1-B376F33E52C3-low.png

Important

If you have the BMC AMI Change Manager for Db2 product, you can use it to automate this entire process. Change Manager generates the JCL and the syntax.

To create a consistent database for query and review

  1. Obtain data definitions.Obtain the definitions of the database, table spaces, and tables from the online application. These are the source objects. Also, obtain definitions for the indexes if you want to create them.

    Important

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

    The DDL for this example is shown in the following figure.

    CREATE DATABASE AFRORDER;
    CREATE TABLESPACE AFRORDER IN AFRORDER USING STOGROUP PROD1;
    CREATE TABLESPACE AFRSHIPS IN AFRORDER USING STOGROUP PROD1;
    CREATE TABLE AFRORDER   (
                            ORDERNUM INT
                            ITEMCODE INT
                            PRICE    DECIMAL (5,2)
                            QUANTITY SMALLINT
                            TOTAL    DECIMAL (7,2)
                             )
           IN AFRORDER.AFRORDER;
    CREATE TABLE AFRSHIPS   (
                            ORDERNUM    INT
                            DATEORDERED DATE
                            DATESHIPPED DATE
                            )
           IN AFRORDER.AFRSHIPS;
    CREATE UNIQUE INDEX AFRORDI ON AFRORDER (ORDERNUM)
       USING STOGROUP PROD1;
    CREATE UNIQUE INDEX AFRSHPI ON AFRSHIPS (ORDERNUM)
       USING STOGROUP PROD1;

  2. Create the database.Modify the DDL for the source objects to change the names so that table spaces and tables are distinctly defined. You might want to create a new database as shown in the following figure. Also, consider other possible changes such as the STOGROUP and index type. The following figure shows the DDL for the target objects.

    CREATE DATABASE AFRSUMOR;
    CREATE TABLESPACE AFRSUMOR IN AFRSUMOR USING STOGROUP MIS1;
    CREATE TABLESPACE AFRSUMSH IN AFRSUMOR USING STOGROUP MIS1;
    CREATE TABLE AFRSUMORDER ( ORDERNUM INT
                               ITEMCODE INT
                               PRICE DECIMAL (5,2)
                               QUANTITY SMALLINT
                               TOTAL DECIMAL (7,2))
       IN AFRSUMOR.AFRSUMOR;
    CREATE TABLE AFRSUMSHIPS ( ORDERNUM INT
                               DATEORDERED DATE
                               DATESHIPPED DATE )
       IN AFRSUMOR.AFRSUMSH;
    CREATE UNIQUE INDEX AFRORDSI ON AFRSUMORDER (ORDERNUM)
       USING STOGROUP MIS1;
    CREATE UNIQUE INDEX AFRSHPSI ON AFRSUMSHIPS (ORDERNUM)
       USING STOGROUP MIS1;

  3. Obtain internal IDs.You need the internal Db2 OBIDs for the source and target objects to set up the migration. You can use SQL to obtain these identifiers from the Db2 system catalog. See the following figure for an example.

    Important

    You can accept the default values for the source DBID and PSID. If the table space contains only one table, you can also accept the default values for the source OBIDs. BMC AMI Recover extracts this information from the catalog. These IDs are in decimal. Because a new database is created, all of the IDs except for the DBID are the same in this example. They are included in the migration job for documentation purposes.

     SELECT NAME, DBID, PSID FROM SYSIBM.SYSTABLESPACE
     WHERE NAME = 'AFRORDER' OR NAME = 'AFRSUMOR' OR NAME = 'AFRSHIPS' OR NAME = 'AFRSUMSH';

      +-----------------------------------+
      |   NAME    |   DBID    |   PSID    |
      +-----------------------------------+
     1_| AFRORDER |       352 |         2 |
     2_| AFRSHIPS |       352 |         7 |
     3_| AFRSUMOR |       449 |         2 |
     4_| AFRSUMSH |       449 |         7 |
      +-----------------------------------+

     SELECT CREATOR, NAME, INDEXTYPE, DBID, ISOBID FROM SYSIBM.SYSINDEXES
     WHERE NAME = 'AFRORDI' OR NAME = 'AFRSHPI' OR NAME = 'AFRORDSI' OR NAME = 'AFRSHPSI';

      +----------------------------------------------------------+
      | CREATOR   |   NAME   | INDEXTYPE |   DBID    |   ISOBID  |
      +---------------------------------------------=------------+
     1_| OWNERID  | AFRORDI  |    2      |       352 |         5 |
     2_| OWNERID  | AFRORDSI |    2      |       352 |         5 |
     3_| OWNERID  | AFRSHPI  |    2      |       449 |        10 |
     4_| OWNERID  | AFRSHPSI |    2      |       449 |        10 |
      +----------------------------------------------------------+

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

      +---------------------------------+
      |        NAME         |   OBID    |
      +---------------------------------+
     1_| AFRORDER           |         3 |
     2_| AFRSHIPS           |         8 |
      +---------------------------------+

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

      +---------------------------------+
      |        NAME         |   OBID    |
      +---------------------------------+
     1_| AFRSUMORDER        |         3 |
     2_| AFRSUMSHIPS        |         8 |
      +---------------------------------+

  4. Quiesce the source objects.Issue a QUIESCE command on the source table spaces. Example JCL and output are shown in the following figures.

    //QUIESCE EXEC DSNUPROC,SYSTEM=DBAN,UID='AFRRESM1',
    // UTPROC='',REGION=4M,
    // LIB='SYS2.DB2.PROD.DSNLOAD'
    //DSNUPROC.SYSPRINT DD SYSOUT=*
    //DSNUPROC.SYSIN DD *
         QUIESCE TABLESPACE AFRORDER.AFRORDER
                 TABLESPACE AFRORDER.AFRSHIPS
    DSNU000I   DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = LSBRESM1
    DSNU050I   DSNUGUTC - QUIESCE TABLESPACE AFRORDER.AFRORDER TABLESPACE AFRORDER.AFRSHIPS
    DSNU477I *DSNP DSNUQUIA - QUIESCE SUCCESSFUL FOR TABLESPACE AFRORDER.AFRORDER
    DSNU477I *DSNP DSNUQUIA - QUIESCE SUCCESSFUL FOR TABLESPACE AFRORDER.AFRSHIPS
    DSNU474I *DSNP DSNUQUIA - QUIESCE AT RBA 0003D5B9A3C2 AND AT LRSN 0003D5B9A3C2
    DSNU475I   DSNUQUIB - QUIESCE UTILITY COMPLETE, ELAPSED TIME= 00:00:00
    DSNU010I   DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=0

    Important

    • This quiesce will be the end point (TORBA/TOLOGPOINT) of the BMC AMI Recover request. Note the LRSN.
    • At this point, the source objects can begin to be updated by other transactions.
    • If image copies (even SHRLEVEL CHANGE) can be made just before this quiesce point, less information from the log will be needed to accomplish the migration. As a result, the migration will be completed more quickly.

  5. Stop the target objects.Issue a STOP command against the database or table space where the query image will be built.

    Example

    -STOP DATABASE (AFRSUMOR)

  6. Run BMC AMI Recover to create the target image.In this step, you migrate the image to the other spaces using the BMC AMI Recover INDEPENDENT OUTSPACE and OBIDXLAT options.

    The data sets that are to receive the migrated data were created when the data definition was done for the target table spaces and index spaces. These data sets are specified in the syntax with their VSAM cluster names.

    In the example shown in the following figure, the IDs from Step 3 are used to define the objects required. Note that TORBA (or TOLOGPOINT) LASTQUIESCE is used. If other quiesce points might be created by other activities, use TORBA (or TOLOGPOINT) X’0003D5B9A3C2’--the LRSN from the QUIESCE operation.

    Important

    This step does not affect the source table spaces or indexes.

    You can use the RECOVER INDEX command to recover the indexes from image copies and logs if copies of the indexes have been made with BMC AMI Copy for Db2 , DSN1COPY, or the IBM COPY utility.

    //RECINDEP 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
    //SORTOUT  DD DUMMY
    //SYSIN    DD *
      OPTIONS INDEXLOG YES
      RECOVER TABLESPACE AFRORDER.AFRORDER
          OBIDXLAT DBID(352,449) PSID (2,2) OBID (3,3)
          INDEP OUTSPACE MODEL
                DBANCAT.DSNDBC.AFRSUMOR.AFRSUMOR.I0001.A001
          TORBA LASTQUIESCE
      RECOVER INDEX OWNERID.AFRORDI
          OBIDXLAT DBID(352,449) PSID (5,5) OBID (3,3)
          INDEP OUTSPACE MODEL
                DBANCAT.DSNDBC.AFRSUMOR.AFRORDSI.I0001.A001
          TORBA LASTQUIESCE
      RECOVER INDEX OWNERID.AFRSHPI
          OBIDXLAT DBID(352,449) PSID (10,10) OBID (8,8)
          INDEP OUTSPACE MODEL
                DBANCAT.DSNDBC.AFRSUMOR.AFRSHPSI.I0001.A001
          TORBA LASTQUIESCE
      RECOVER TABLESPACE AFRORDER.AFRSHIPS
          OBIDXLAT DBID(352,449) PSID (7,7) OBID (8,8)
          INDEP OUTSPACE MODEL
                DBANCAT.DSNDBC.AFRSUMOR.AFRSUMSH.I0001.A001
          TORBA LASTQUIESCE

  7. Start the target objects.Issue a START command to start the target objects. They are now ready for queries.

    Example

    -START DATABASE (AFRSUMOR)

  8. Allow queries.Queries such as the one shown in the following figure (which requires a table space scan) can be completed without interfering with the online processing. Because data will not be changing on this image, the result of the query will be consistent with other queries made until the data is refreshed again.

    SELECT COUNT(*), SUM(TOTAL)
    FROM     AFR.AFRSUMORDER ORD,
             AFR.AFRSUMSHIPS SHIP
    WHERE    (
             DAYS(DATESHIPPED) > DAYS(DATEORDERED) + 2 OR   
             DAYS(DATEORDERED) + 2 < DAYS(CURRENT DATE) AND   
             DATESHIPPED IS NULL
             )
    AND ORD.ORDERNUM = SHIP.ORDERNUM;



      +--------------------------------------+
      |                 |                    |
      +--------------------------------------+
    1_|              2  |            888.50  |
      +--------------------------------------+

Related topic

Migrating-data



 

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