Loading and Unloading Data
There are two third-party utilities for loading and unloading data from DB2 tables that are available for use at Phoenix. These utilities are the "Fast Load" and "Fast Unload" utilities from Computer Associates. These utilities provide better performance and functionality over the native DB2 load and unload utilities/programs.
JCL for Fast Unload
//STEP001 EXEC PGM=PTLDRIVM,PARM='EP=UTLGLCTL/DB0G',
// REGION=6M
//STEPLIB DD DISP=SHR,DSN=PDB2.PLAT.TEST.LOADLIB
// DD DISP=SHR,DSN=SYSD1.DB0G.SDSNLOAD
//PTILIB DD DISP=SHR,DSN=PDB2.PLAT.TEST.LOADLIB
// DD DISP=SHR,DSN=SYSD1.DB0G.SDSNLOAD
//PTIPARM DD DISP=SHR,DSN=PDB2.PLAT.TEST.PARMLIB
//PTIXMSG DD DISP=SHR,DSN=PDB2.PLAT.TEST.XMESSAGE
//SYSREC01 DD DSN=YOUR.UNLOAD.DSN,
// DISP=(NEW,CATLG,DELETE),UNIT=SYSDA,
// SPACE=(CYL,(50,50),RLSE)
//SYSCTL01 DD DSN=YOUR.OUTPUT.PDS(LOADCNTL),DISP=SHR
//PTIMSG DD SYSOUT=*
//PTIIMSG DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//ABNLIGNR DD DUMMY
//SYSOUT DD SYSOUT=*
//SYSIN DD *
FASTUNLOAD LOAD-CONTROL FASTLOAD IO-BUFFERS 60
OUTPUT-FORMAT DSNTIAUL SHRLEVEL IGNORE EXCP YES
INPUT-FORMAT TABLE
DISPLAY-STATUS 100000,INTERVAL
ESTIMATED-ROWS 5000000
SELECT * FROM YOUR.TABLE;
Notes on Using Fast Unload
The unload data will be written to the dataset defined by the SYSREC01 ddname. You do not have to specify DCB information such as LRECL or BLKSIZE; the utility will do that for you and allocate the dataset with the appropriate DCB information. The unload utility will create load syntax for loading the data back into the table. The load syntax will be stored in the PDS member specified by the SYSCTL01 ddname. You can restrict the columns and/or the rows to be unloaded with the SELECT statement, which must be the last line of the Fast Unload syntax in the SYSIN ddname. Please note that the FROM clause in the SQL statement should only reference one table, i.e. no joins. There are many features and options that are provided by the Fast Unload utility.
The examples shown here are the most common options that you may need to use. Please contact your favorite DBA if you would like more information about the other options and features for the Fast Unload utility.
Unloading From an Image Copy Dataset
One of the more powerful features of the Fast Unload utility is its ability to optionally unload data from an image copy dataset instead of directly from the DB2 table itself. This is ideal if you have a need to extract data from your table as it looked at a previous point in time, or if you want to move data from one environment to another, such as production to model office, without directly impacting the production data. Here are two examples of how to unload data from an image copy dataset:
Example 1 (Unloading from the most recent full image copy)
//SYSIN DD *
FASTUNLOAD LOAD-CONTROL FASTLOAD IO-BUFFERS 60
OUTPUT-FORMAT DSNTIAUL SHRLEVEL REFERENCE EXCP YES
INPUT-FORMAT IMAGECOPY LAST-COPY YES
DISPLAY-STATUS 100000,INTERVAL
ESTIMATED-ROWS 5000000
SELECT * FROM DCMSU.AGTHIST ;
In this example, specifying LAST-COPY YES will tell the Fast Unload utility to query the DB2 catalog to determine the dataset name of the most recent full image copy for the table being unloaded. Fast Unload will then dynamically allocate that dataset to the job and perform the unload.
Example 2 (Unloading from a previous full image copy)
//SYSIN DD *
FASTUNLOAD LOAD-CONTROL FASTLOAD IO-BUFFERS 60
OUTPUT-FORMAT DSNTIAUL SHRLEVEL REFERENCE EXCP YES
INPUT-FORMAT IMAGECOPY LAST-COPY NO
DISPLAY-STATUS 100000,INTERVAL
ESTIMATED-ROWS 5000000
SELECT * FROM CRPACCTU.MOVE_MERGE ;
//SYSIMAG DD DSN=YOUR.IMAGE.COPY.DSN,DISP=OLD
In this example, specifying LAST-COPY NO will tell the Fast Unload utility to unload the table from the image copy dataset specified by the SYSIMAG ddname.
JCL for Fast Load
//STEP001 EXEC PGM=PTLDRIVM,PARM='EP=UTLGLCTL/DB0G',
// REGION=6M
//STEPLIB DD DISP=SHR,DSN=PDB2.PLAT.TEST.LOADLIB
// DD DISP=SHR,DSN=SYSD1.DB0G.SDSNLOAD
//PTILIB DD DISP=SHR,DSN=PDB2.PLAT.TEST.LOADLIB
// DD DISP=SHR,DSN=SYSD1.DB0G.SDSNLOAD
//PTIPARM DD DISP=SHR,DSN=PDB2.PLAT.TEST.PARMLIB
//PTIXMSG DD DISP=SHR,DSN=PDB2.PLAT.TEST.XMESSAGE
//PTIMSG DD SYSOUT=*
//PTIIMSG DD SYSOUT=*
//SYSULD DD DSN=YOUR.UNLOAD.DSN,DISP=OLD
//SYSERR DD DSN=YOUR.SYSERR.DSN,DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(5,5),RLSE),UNIT=SYSDA
//SYSUDUMP DD SYSOUT=*
//ABNLIGNR DD DUMMY
//SYSOUT DD SYSOUT=*
//SYSIN DD *
FASTLOAD INDDN SYSULD
INPUT-FORMAT SEQ
OUTPUT-CONTROL BUILD
RESUME NO REPLACE
EBCDIC
INTO TABLE DCMSU.AGTHIST
(
LASTMAINT POSITION( 1: 26 ) TIMESTAMP EXTERNAL ( 26),
SOURCE_CORP POSITION( 27: 29 ) CHAR ( 3) ,
SOURCE_DIV POSITION( 30: 39 ) CHAR ( 10) ,
SOURCE_DEPT POSITION( 40: 49 ) CHAR ( 10) ,
SOURCE_SUB_DEPT POSITION( 50: 59 ) CHAR ( 10) ,
SOURCE_COST_CTR POSITION( 60: 69 ) CHAR ( 10) ,
TARGET_CORP POSITION( 70: 72 ) CHAR ( 3) ,
TARGET_DIV POSITION( 73: 82 ) CHAR ( 10) ,
TARGET_DEPT POSITION( 83: 92 ) CHAR ( 10) ,
TARGET_SUB_DEPT POSITION( 93: 102 ) CHAR ( 10) ,
TARGET_COST_CTR POSITION( 103: 112 ) CHAR ( 10) ,
AXRATIO_IND POSITION( 113: 113 ) CHAR ( 1) ,
GXRATIO_IND POSITION( 114: 114 ) CHAR ( 1) ,
SOURCE_IND POSITION( 115: 115 ) CHAR ( 1) ,
UPDTBY POSITION( 116: 123 ) CHAR ( 8)
)
Notes on Using Fast Load
1. The data to be loaded should be located in the dataset defined by the SYSULD ddname. This should correspond to the dataset defined by the SYSREC01 ddname in the Fast Unload job.
2. The Fast Load syntax, defined by the SYSIN ddname, will be automatically generated by the Fast Unload job (SYSCTL01 ddname). Most likely, the only change you may need to make would be to change the name of the table you are loading. This would primarily need to be done in a situation where you are moving data from one environment to another, such as from production to test, where the physical table names are different (different creator).
3. As with the Fast Unload utility, there are many features and options that are provided by the Fast Unload utility. The examples shown here are the most common options that you may need to use. Please contact your favorite DBA if you would like more information about the other options and features for the Fast Unload utility.
Operational Considerations for Running Fast Unload and Fast Load
Phoenix is only licensed to run the CA DB2 utilities on the R44 CPU. This means that the software is only licensed to run on the MVS3 LPAR. If you attempt to run the Fast Unload or Fast Load job on any other LPAR, you will receive a RACF error, as shown here:
15.39.53 JOB04605 ICH408I USER(TECWRG ) GROUP(TECHSUPT) NAME(GALLAGHER,WILLIAM M ) PTLDRIVM CL(PROGRAM )
INSUFFICIENT ACCESS AUTHORITY
ACCESS INTENT(READ ) ACCESS ALLOWED(NONE )
To ensure that you run on the correct OS/390 LPAR, you will need to include a SYSAFF card in your JCL. This card should appear immediately after your job card, and would look like this:
/*JOBPARM SYSAFF=(MVS3)
Displaying and Terminating CA Utilities
If a Fast Unload or Fast Load job abends, it will leave the utility in a "pending" status. There are ways to restart a failed utility, but often it is much easier to just terminate the utility and try running again after you have determined and resolved the cause of the abend. In order to terminate the utility, you will need to first determine the name of the utility and find out which tablespaces and/or indexes have been placed in restricted status. This can be done with the following JCL:
//STEP001 EXEC PGM=PTLDRIVM,PARM='EP=PTLDB2C/DB0G',
// REGION=6M
//STEPLIB DD DISP=SHR,DSN=PDB2.PLAT.TEST.LOADLIB
// DD DISP=SHR,DSN=SYSD1.DB0G.SDSNLOAD
//PTILIB DD DISP=SHR,DSN=PDB2.PLAT.TEST.LOADLIB
// DD DISP=SHR,DSN=SYSD1.DB0G.SDSNLOAD
//PTIPARM DD DISP=SHR,DSN=PDB2.PLAT.TEST.PARMLIB
//PTIXMSG DD DISP=SHR,DSN=PDB2.PLAT.TEST.XMESSAGE
//PTIMSG DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
-DISPLAY CA (*)
-DISPLAY DATABASE(DB76FAVU) SPACENAM(*) RESTRICT LIMIT(*)
Here is the kind of output you might get from running this job:
-DISPLAY CA (*)
DATABASE = DB76FAVU UTILID = FLUCKIG.BRUNIJL4 UTILITY = LOAD
TABLESPACE = TFAVTI ROW COUNT 0 USERID =
FLUCKIG
TIME ACCESSED = 2001-12-03 09.14.07
PARTITIONS SELECTED 000
******** 1 C A - D B 2 U T I L I T I E S ********
-DISPLAY DATABASE(DB76FAVU) SPACENAM(*) RESTRICT LIMIT(*)
DSNT360I -DB02 ***********************************
DSNT361I -DB02 * DISPLAY DATABASE SUMMARY
* RESTRICTED
DSNT360I -DB02 ***********************************
DSNT362I -DB02 DATABASE = DB76FAVU STATUS = RW
DBD LENGTH = 20180
DSNT397I -DB02
NAME TYPE PART STATUS PHYERRLO PHYERRHI CATALOG PIECE
-------- ---- ---- ------------------ -------- -------- -------- -----
TFAVTI TS UT
IFAVTI1 IX UT
IFAVTI2 IX UT
IFAVTI3 IX UT
******* DISPLAY OF DATABASE DB76FAVU ENDED **********************
DSN9022I -DB02 DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION
In this example, a job that was performing a Fast Load utility for the table contained in the DB76FAVU.TFAVTI tablespace abended. The abend left the tablespace and it's three associated indexes in "UT" (utility) status.
In order to terminate the utility and bring the tablespace and indexes back to a usable state, you would need to replace the "-DISPLAY" commands in the above JCL with the following, and rerun the JCL:
-TERM CA UTILID (FLUCKIG.BRUNIJL4)
-START DATABASE(DB76FAVU) SPACENAM(TFAVTI) ACCESS(RW)
-START DATABASE(DB76FAVU) SPACENAM(IFAVTI1) ACCESS(RW)
-START DATABASE(DB76FAVU) SPACENAM(IFAVTI2) ACCESS(RW)
-START DATABASE(DB76FAVU) SPACENAM(IFAVTI3) ACCESS(RW)
Please note that these steps will only bring the tablespace and indexes back to a reusable state for the Fast Load utility to be rerun. It will not necessarily make the table usable for normal processing as the data that is actually contained in it may have been corrupted by the abended load utility. If for whatever reason you decide that you do not want to rerun the Fast Load utility, you should contact your favorite DBA to request a restore of that tablespace to a previous image copy.
