How to unload from an image copy that has no existing table on the DB2 subsystem.

Document ID : KB000026057
Last Modified Date : 14/02/2018
Show Technical Document Details

Introduction:

Is it possible to unload table data from an image copy dataset where the table in question no longer exists on any current subsystem using CA Fast Unload for DB2 for z/OS (PFU)?

 

Background:

A full image copy dataset contains table data that the user would like to use. The image copy may have belonged to a table which has been dropped and so no record of it exists on the subsystem nor any recovery information. The user would like to access this data so that it can be used again. A normal recovery is not possible as no recovery information exists. The detail of the table's structure also is unavailable to the subsystem since this is also removed when a table is dropped. A normal unload can only unload from an existing table.

 

Instructions:

Unload the data with Fast Unload with input DDL and an OBID.

Caveat: The image copy must be a full image copy not an incremental.

Obtain Table Structure

PFU is able to unload data from an image copy or DSN1COPY dataset. Because the table does not exist the user must provide PFU with the details of its structure as it was when it existed. This is done using the DDLDDN parm which refers to a dataset containing a CREATE Table statement.

Obtain OBID

The table data on the image copy is identified with the use of an OBID value on the unload select statement. This may be unknown to the user and may be discovered in two ways.

  1. This information may be obtained with the use of the DSN1PRINT utility:
    //UPRT     EXEC PGM=DSN1PRNT,PARM='PRINT,FORMAT,FULLCOPY'
    //STEPLIB  DD DISP=SHR,DSN=db2.SDSNLOAD
    //         DD DISP=SHR,DSN=db2.SDSNEXIT
    //SYSUT1   DD DSN= image.COPY.dataset,DISP=SHR
    //SYSPRINT DD SYSOUT=*
    In the DSN1PRNT output look for the PGSOBD value which gives the hex value for the OBID of the table on the image copy. Look for something like this on each record of a data page in the DSN1PRNT report: PGSOBD='004F'X

  2. Fast Unload may also be used utilizing the SEARCH-OBID parm.

    The unload will not unload data but will provide a report listing the OBIDs that were found on the image copy both in hex and decimal.

PFU Cards

FASTUNLOAD
INPUT-FORMAT IMAGECOPY
OUTPUT-FORMAT LOAD
SEARCH-OBIDS
SELECT * FROM creator.table;

Resulting PFU Report

PFU0247I - SEARCH-OBIDS SPECIFIED. LIST OF OBIDS FOUND IN TABLESPACE FOLLOWS:

           TABLE OBID (HEX)    TABLE OBID (DECIMAL)
           ----------------    --------------------
PFU0248I -      X'004F'                79

So now you know that the table is identified by a OBID of X'004F' or 79 in decimal. Both of these values can be used by PFU in it's unload cards.

Unload data

The unload cards can be written as below. The DDLDDN card points to a DD that has the table create statement. The SEARCH-OBIDS will report the OBIDs present in the image copy dataset. OBID X'nnnn' will identify the table you want to unload.

FASTUNLOAD
DISCARDS 100
DISPLAY-STATUS 10000
EXCP YES
INPUT-FORMAT IMAGECOPY
DDLDDN INDDL
SEARCH-OBIDS
IO-BUFFERS 50
LOAD-CONTROL FASTLOAD
OUTPUT-FORMAT LOAD
PART-INDEPENDENCE NO
SHRLEVEL REFERENCE
SORTSIZE 4M
SORTNUM 4
SQL-ACCESS NONE
SELECT * FROM creator.table OBID X'004F';

Apart from the DD card referred to by the DDLDDN parm, PFU goes looking for the image copy dataset on the SYSIMAG DD card.

In this way table data from an image copy could be unloaded in case the table has been dropped for recovery purposes. It could also be done as a means of obtaining data from another subsystem without interfering with that other subsystem. An example would be to obtain data from a production image copy for testing rather than unloading the online table where the table on the test system has changed its structure.

 

Additional Information:

Documentation References
Fast Unload User Guide. Keyword descriptions for SEARCH-OBID and DDLDDN. Sample Output Section, Image Copy Unload Using DDLDDN Definitions.