Fast Unload: How to unload from an image copy that has no existing table on the DB2 subsystem.
Article ID: 26057
Updated On:
Products
Issue/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?
Environment
Release:R20
Component: PFU
Cause
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.
Resolution
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 Fast Unload for DB2 for z/os(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.
- 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='0035'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.
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'0035' 53
So now you know that the table is identified by a OBID of X'0035' or 53 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 create table statement as there is no existing table on the DB2 catalog that can be used by the unload. The SEARCH-OBIDS will report the OBIDs present in the image copy dataset. OBID X'nnnn' will identify the table you want to unload.
//SYSIN DD *
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 IGNORE
SORTFLAG ALL
SORTSIZE 4M
SORTNUM 8
ESTIMATED-ROWS 7500000
SQL-ACCESS NONE
SELECT * FROM creator.table OBID X'0035';
The results:
PFU0157I - FASTUNLOAD TASK #01 STARTED - UNLOADING FROM: IMAGECOPY
PFU0207I - FAST UNLOAD WILL USE BSAM ACCESS METHOD TO ACCESS TABLESPACE DATA
PFU0229I - STATISTICS FOR: IMAGECOPY --
UNLOADED FROM DDNAME: SYSIMAG
PFU0232I - COUNT-TABLESPACE PAGES READ: ......................................03
PFU0234I - COUNT-TABLESPACE ROWS PROCESSED: ..................................10
PFU0236I - COUNT-TABLESPACE ROWS FROM UNSELECTED TABLES: .....................00
PFU0237I - COUNT-TABLESPACE ROWS FROM SELECTED TABLES: .......................10
PFU0238I - COUNT-ROWS PASSED TO SELECT#01: ...................................10
PFU0242I - COUNT-ROWS WRITTEN TO SYSREC01 FOR SELECT#01: ....................10
PFU0243I - SYSREC01 SELECTED FROM OBJ: creator.table OBID: 53
PFU0247I - SEARCH-OBIDS SPECIFIED. LIST OF OBIDS FOUND IN TABLESPACE FOLLOWS:
TABLE OBID (HEX) TABLE OBID (DECIMAL)
---------------- --------------------
PFU0248I - X'0035' 53
PFU0242I - COUNT-ROWS WRITTEN TO SYSCTL01 FOR SELECT#01: ....................08
Apart from the DD card referenced 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 can be unloaded in case the table has been dropped to facilitate recovery tasks. 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 and perhaps cause contention on production.
Additional Information
Fast Unload User Guide. Keyword descriptions for SEARCH-OBIDS and DDLDDN. Sample Output Section, Image Copy Unload Using DDLDDN Definitions.
Feedback
