How can I use BMC Unload Plus for Db2 Utility to unload data from an image copy generated at a Db2 Subsystem, when running the Unload Plus job at another Subsystem, for example, to replicate Production data in a Test environment?

You can use BMC Unload Plus for Db2 Utility to unload data from an Image Copy generated at a Db2 Subsystem, when running the Unload Plus job at a different Db2 Subsystem, for example, to Replicate Production data in a Test environment,

These are the steps of a procedure that you can use:

1.- Extract the HDDL of the Source Tablespace that you want to unload data from - You can use BMC Catalog Manager for Db2 HDDL command, executed against the Source Base Tablespace.

2.- Transmit or copy the DDL script containing the SQL CREATE statements of the Tablespace and Table you want to unload data from, to the Target system.

3.- Edit the transmitted DDL to comply with the standards of the Target environment and execute it to create the Target Tablespace and Table.

Important Note:

• This table in the Target environment will be used in the SELECT statement of the UNLOAD PLUS Utility job to match with the image copy structure, therefore, the execution of the UNLOAD PLUS job will not impact the Source environment (Production) as it will be executed in the Target environment.

4.- Generate a Full Image Copy either with IBM Copy or BMC AMI Copy for Db2, on a Shared Dasd unit, or on a Tape unit that can be accessed from both the Source Db2 Subsystem and the Target Db2 Subsystem.

5.- Generate a BMC Unload Plus for Db2 Utility JCL in the Target Db2 Subsystem, pointing to the Image Copy taken in step 4 above, allocated to your JCL like in this example:

//IMAGECOP DD DISP=SHR,
//         DSN=prefix.IC.dbname.tsname.Pnnn.Dicdate

6.- SInce the Image Copy that is used as Input to Unload Plus in the Target Subsystem was generated in a different Db2 Subsystem, you need to specify three keywords in the Unload Plus job:

• INFILE ddname - You can specify the INFILE option to use a copy or a data set, other than the Db2 table space data set as the source of the input data.
• OBID n - OBID of the rows contained in the Image Copy, from the Source Table in the Source environment
• ESTROWS n - Number of estimated rows that exist in the Image Copy for the Source Table in the Source environment.

This is a sample SYSIN DD Statement containing the UNLOAD command and the three keywords explained above:

UNLOAD
    INFILE IMAGECOP
    UNICODE
    CNTLCARDS DB2LOAD CNTLDDN SYSPUNCH
    FIXEDVARCHAR YES
    ORDER YES
    SORTNUM 32
    SELECT *
    FROM Creator.Name (OBID id)
    ESTROWS nnnn

Where:

• INFILE clause is specified to indicate that the Input to the BMC Unload process is not a 'live' Tablespace in Db2 but an Image Copy.
• IMAGECOP is the name of the DD in JCL where the Image Copy file is allocated.
• Creator.Name is a Table in the Target Db2 Subsystem created with the DDL extracted from the Source Subsystem - it needs to have an identical structure (TS and Table) as the Source Table.
• OBID id - Is the OBID of the Source Table in the Source Db2 Subsystem - you specify this parameter to have Unload Plus search for rows with this Id in the input Image Copy.
• ESTROWS nnnn is specified to allow Unload Plus to calculate the size of the data sets that are produced during dynamic allocation; ESTROWS, LIMIT, or SPACE command options are required when DIRECT NO is in effect, or you specify INFILE, like in this case, as it is explained in this URL to Product documentation: https://docs.bmc.com/docs/adu13100/estrows-1071490512.html