CA Fast Unload for DB2 for Z/OS: How to Unload data from non-current Image Copy GDG's produced on another SSID.

book

Article ID: 195430

calendar_today

Updated On:

Products

CA Fast Unload for DB2 for z/OS CA Fast Load for DB2 for z/OS

Issue/Introduction

Image copies are internal DB2 format backup datasets produced by a utility like CA Quick Copy or IBM Copy. This Image Copy Dataset can only be used by another Utility like a Recovery or Unload Utility. A GDG is a Generation Data Group which is a dataset storage structure which is often used for the storage of image copies. Multiple prior versions or generations of the dataset can be stored without any name change in the JCL.

The Data stored in an Image Copy Dataset can be used to populate another target database and this is often done to obtain production data for testing purposes without hindering the operations of the online production database.

Environment

DB2 for Z/OS

Release R19, R20

Resolution

Fast Unload is able to unload data from an Image Copy stored on a GDG made on another environment as long as some particular settings are used.

First, start with the standard JCL used by Fast Unload to unload a table. See the CA Fast Unload User Guide for samples in the Sample Jobs.

Identify the input Dataset

Consider this GDG structure:

hlq.CPY
hlq.CPY.G0001V00 <----- two generations old is the target data
hlq.CPY.G0002V00
hlq.CPY.G0003V00

In the CA Fast Unload JCL make a specific reference to the image copy dataset available like this with the SYSIMAG DD card:

    //SYSIMAG DD DSN=hlq.CPY(-2), DISP=OLD 

This one is referring to a GDG two generations old so the job would obtain hlq.CPY.G0001V00. In the SYSIN Unload Cards the input format is also specified:

    INPUT-FORMAT IMAGECOPY 

Identify the Table and its Format

The Unload should select the table like this in the SYSIN cards as normal:

    SELECT * FROM authid.TABLE; 

If the table name exists on this subsystem already and its identical to the one which is on the image copy in terms of the format then the local catalog definition will be used by Fast Unload to establish the format.

If the Table does not exist on the local subsystem then provide CA Fast Unload with a DDLDDN card to tell it what the format of the table is as below. Remove any CA Batch processor dot commands such as .SYNC from this DDL. 

    //INDDL DD DSN=DDL.DATASET(DDL), DISP=OLD 

And in the Fast Unload SYSIN cards make reference to the DD thus:

    DDLDDN INDDL 

If there are data records from more than one table on the image copy then the specific records to be unloaded must be identified with an OBID. In this case 28 is the decimal OBID of the data records on the image copy. The NEWOBID specifies the OBID of the target table to be loaded. 

    SELECT * FROM authid.table_name
       OBID 28 NEWOBID 47;

PFU can report what the OBID is with the SYSIN card SEARCH-OBIDS during unload if it is unknown.

This is what you can expect to see in the unload SYSOUT:

PFU0247I - SEARCH-OBIDS SPECIFIED.  LIST OF OBIDS FOUND IN TABLESPACE FOLLOWS:
           TABLE OBID (HEX)    TABLE OBID (DECIMAL)
           ----------------    --------------------
PFU0248I -     X'001C'                   28

For more on this searching of OBID's see:

Article Id: 26057CA Fast Unload for Db2 for z/os: How to unload from an image copy that has no existing table on the DB2 subsystem.

https://knowledge.broadcom.com/external/article?articleId=26057

Identify the Format of Data to Unload

When Fast Unload unloads data it can be unloaded in various formats. In this document  only OUTPUT-FORMAT (LOAD) and OUTPUT-FORMAT (EXTERNAL) will be discussed.

When the format is LOAD then data is not the only thing that is unloaded. The data is tagged with the OBID of the table and the type of tablespace that it was stored on. The NEWOBID keyword can be used to specify a new OBID to tag the unloaded data records if required.

When the format is OUTPUT-FORMAT (EXTERNAL) then only data is unloaded.

Note that data that is an EXTERNAL format will require conversion when it's loaded to the target so there is a performance overhead involved compared to LOAD format.

Sample JCL unloading an Image Copy GDG (-2 generation) of table data, and reassigning an OBID 47 to the unloaded data.. The Table does not exist on the source subsystem so table DDL is referenced using the DDLDDN DD card. 

//SYSREC01    DD   DSN=hlq.SYSREC01,
// DISP=(,CATLG),SPACE=(CYL,(920,5)),UNIT=SYSDA
//SYSCTL01    DD   DSN=hlq.SYSCTL01,
// DISP=(,CATLG),SPACE=(CYL,(1,1)),UNIT=SYSDA
//SYSIMAG DD    DSN=hlq.CPY(-2),
// DISP=OLD
//INDDL   DD    DSN=hlq.ddl(tableddl),
// DISP=OLD
//ST01MSG   DD   SYSOUT=*
//SYSIN   DD  *
FASTUNLOAD
DISCARDS 100
DISPLAY-STATUS 10000
EXCP YES
INPUT-FORMAT IMAGECOPY
INDDN SYSIMAG
SEARCH-OBIDS
ZIIP NO
IO-BUFFERS 50
DDLDDN INDDL
LOAD-CONTROL FASTLOAD
OUTPUT-FORMAT LOAD
PART-INDEPENDENCE NO
SHRLEVEL IGNORE
SORTSIZE 4M
SORTNUM 4
SQL-ACCESS  NONE
SELECT * FROM authid.table_name
       OBID 28 NEWOBID 47;

Loading the Data with CA Fast Load for DB2 for Z/OS

If the data is unloaded in OUTPUT-FORMAT (LOAD) then when the data is loaded the OBID of the receiving table must match the OBID in the data or rejects will occur. Also the Type of Tablespace that the receiving table is created on must match with the source tablespace or a load error will occur.

Data can be loaded into a table where the OBID does not match by specifying the OBID of the SOURCE table data. This is the OBID used by the NEWOBID in the FAST LOAD sysin cards thus: 47 is the decimal OBID tagged on the input dataset records in this sample.

    INTO TABLE authid.TABLE1 OBID 47

When the data is loaded to the target table the data is automatically translated to the OBID of the table being loaded.

If the data is unloaded with OUTPUT-FORMAT (EXTERNAL) then the data is treated as just data with no special identification attributes. The Format of the data must be reflected in the load cards which will match the input data as for data that came from somewhere else.

Sample JCL of the load of the previously unloaded data into a new table of the same structure but different name where the decimal OBID of the target table is 47. An image copy is taken during the job. 

//SYSMAP    DD   DSN=hlq.SYSMAP,
// DISP=(,CATLG),SPACE=(CYL,(5,5)),UNIT=SYSDA
//SYSDISC   DD   DSN=hlq.SYSDISC,
// DISP=(,CATLG),SPACE=(CYL,(5,5)),UNIT=SYSDA
//SYSERR    DD   DSN=hlq.SYSERR,
// DISP=(,CATLG),SPACE=(CYL,(5,5)),UNIT=SYSDA
//SYSULD    DD   DSN=hlq.SYSREC01,
//        DISP=OLD
//*
//SYSCP101  DD   DSN=hlq.SYSCP101.COPY,
// DISP=(,CATLG),SPACE=(4096,(1,5)),UNIT=SYSDA
//ST01MSG   DD   SYSOUT=*
//SYSIN   DD  *
FASTLOAD
STARTUP-ACCESS RESET
ALLMSGS YES
COPY-BUFFERS 10
SPACE-DEFN YES
DISPLAY-STATUS 10000
ESTIMATED-ROWS 7500000
INPUT-FORMAT UNLOAD
IO-BUFFERS 50
MAXTASKS 1
NONLEAF-PCTFREE LEAF
OUTPUT-CONTROL BUILD
EBCDIC  CCSID(1027,5035,4396)
UNLOAD CONTINUE
QUICKCOPY COPY01(Y,SYSCP101,,LP)
RECLUSTER NO SORT-CLINDX
SORTDEVT DASD
SORTFLAG ALL
SORTSIZE 4M
SORTNUM 8
STOP-LIMIT 1
UPDATE-CATSTATS ALL
VSAM-BUFFERS 96
RESUME NO
INTO TABLE authid.table1 OBID 47

Additional Information

Refer to the CA Fast Load and CA Fast Unload User Guides for DB2 for Z/OS.

For Fast Unload check the keyword descriptions for OUTPUT-FORMAT, OBID, NEWOBID and DDLDDN. There is also a section that is about "Unload from an Image Copy Data Set" in the chapter called "Sample Jobs". Also read about the SYSIMAG DD.

For Fast Load check the keyword descriptions for OBID and INPUT-FORMAT.