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)?
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.
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.
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.
- 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
- 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.
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.
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.
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.
Fast Unload User Guide. Keyword descriptions for SEARCH-OBID and DDLDDN. Sample Output Section, Image Copy Unload Using DDLDDN Definitions.