Fast Unload: How to Unload data from non-current Image Copy GDG's produced on another SSID.
Article ID: 195430
Updated On:
Products
Issue/Introduction
Image copies are internal DB2 format backup datasets produced by a utility like 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 that 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 : 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 Fast Unload(PFU) 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 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 Fast Unload with a DDLDDN card to inform it what the format of the table is as below. Remove any Batch processor dot commands such as .SYNC or .AUTH 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 provide a report of existing OBID's using the SYSIN card SEARCH-OBIDS during unload if the OBID is unknown.
This is a sample of the report 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 searching for OBID's see:
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 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 Fast Load and Fast Unload User Guide.
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.
Feedback
