Db2 LOAD utility load JCL
You can code and submit the JCL on the following pages to execute the load process without invoking the interactive portion of the Load option. The required parameter information and DD statements for File-AID/RDX-specific steps are described following the JCL.
Db2 Load Utility Load JCL (continues on next page)
//* XFRJCREA
//* --------------------------------------------------------------------
//* CREATE:- CREATE TABLES AND INDEXES
//* --------------------------------------------------------------------
//CREATE EXEC PGM=XFREXEC,REGION=8192K,COND=(4,LT),
// PARM=(FR,7,B)
//STEPLIB DD DSN=Combined-File-AID/RDX-load-libraries,DISP=SHR
// DD DSN=Db2-load-library,DISP=SHR
//RDXMLIB DD DSN=File-AID-message-library,DISP=SHR
//EXTRACT DD DSN=Extract file,
// DISP=SHR
//EXTPARMS DD DSN=TSOID01.prefix.PARMS.Dnnnnnn.Tnnnnnn.suffix,
// DISP=SHR
//SYSOUT DD SYSOUT=(*
//RDXSQLCD DD SYSOUT=(*)
//SYSPRINT DD SYSOUT=(*)
//SYSUDUMP DD SYSOUT=(*)
//TPTPRINT DD SYSOUT=(*)
//SYSTERM DD SYSOUT=(*)
//SYSIN DD DSN=TSOID01.prefix.VBCNTL.Dnnnnnn.Tnnnnnn.suffix(Db2CREAT),
DISP=SHR
//*-------------------------------------------------------------------
//* DELTEnnn:- DELETE ALL ROWS FROM TABLE TSOID01.ORDER_TABLE
//*-------------------------------------------------------------------
//DELTEnnn EXEC PGM=XFREXEC,REGION=8192K,COND=(4,LT),
// PARM=(FR,4,B)
//*
//STEPLIB DD DSN=Combined-File-AID/RDX-load-libraries,DISP=SHR
// DD DSN=Db2-load-library,DISP=SHR
//RDXMLIB DD DSN=File-AID-message-library,DISP=SHR
//EXTPARMS DD DSN=TSOID01.prefix.PARMS.Dnnnnnn.T
nnnnnn.suff
ix,
// DISP=SHR
//RDXSQLCD DD SYSOUT=(*)
//SYSOUT DD SYSOUT=(*)
//SYSPRINT DD SYSOUT=(*)
//SYSTERM DD SYSOUT=(*)
//DLETEIN DD DSN=TSOID01.prefix.VBCNTL.Dnnnnnn.Tnnnnnn.suffix(DELTEnnn),
// (DISP=SHR)
//* XFRJSTRT
//*-------------------------------------------------------------------
//* CLEAN1ST: - THIS STEP WILL ENSURE THAT ALL TEMPORARY FILES
//* USED IN THIS JOB ARE DELETED.
//* - THIS ALLOWS FOR KEEPING AND RERUNNING THE JOB EVEN IF
//* SUBSEQUENT STEPS FAIL.
//* - THIS JCL IS ALSO RESTARTABLE AT ANY STEP BY CODING
//* RESTART=XXXXXXXX ON THE JOBCARD. (XXXXXXXX = STEPNAME)
//*-------------------------------------------------------------------
//CLEAN1ST EXEC PGM=IDCAMS,COND=(4,LT),DYNAMNBR=2048
//SYSPRINT DD SYSOUT=*
//SYSIN DD * IDCAMS DELETE STATEMENTS FOLLOW
DELETE File-AID-sort-file
DELETE File-AID-sort-control-file
DELETE File-AID-split-file
DELETE File-AID-check-file
DELETE File-AID-image-copy-file
SET MAXCC=0
SET LASTCC=0
/*
Db2 Load Utility Load JCL (continues on next page)
//* LDDRV:- LOAD DRIVER
//* THE FOLLOWING IS PERFORMED IN THIS STEP.
//* - CHECK USERS AUTHORITY TO RUN ALL THE STEPS.
//* - ENSURE TABLES EXIST.
//* - SPLIT EXTRACT FILE INTO SEPARATE LOAD FILES.
//* --------------------------------------------------------------------
//LDDRV EXEC PGM=XFREXEC,COND=(4,LT),REGION=8192K,
// PARM=(FR,3,B)
//*
//STEPLIB DD DSN=Combined-File-AID/RDX-load-libraries,DISP=SHR
// DD DSN=Db2-load-library,DISP=SHR
//RDXMLIB DD DSN=File-AID-message-library,DISP=SHR
//EXTRACT DD DSN=extract-file,
// DISP=SHR
//EXTPARMS DD DSN=TSOID01.prefix.PARMS.Dnnnnnn.Tnnnnnn.suffix,
// DISP=SHR
//*
//CHCKDATA DD DSN=TSOID01.prefix.CHECK.Dnnnnnn.Tnnnnnn.suffix,
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=27920),
// SPACE=(TRK,(1,1),RLSE),
// UNIT=SYSDA,
// DISP=(NEW,CATLG,DELETE)
//*
//IMAGECPY DD DSN=TSOID01.prefix.IMAGECPY.Dnnnnnn.Tnnnnnn.suffix,
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=27920),
// SPACE=(TRK,(1,1),RLSE),
// UNIT=SYSDA,
// DISP=(NEW,CATLG,DELETE)
//* XFRJSTR1
//SPLITnnn DD DSN=TSOID01.prefix.SPLITnnn.Dnnnnnn.Tnnnnnn.suffi
x,
// DCB=(RECFM=VB,LRECL=302,BLKSIZE=0),
// SPACE=(TRK,(5,1),RLSE),
// UNIT=SYSDA,
// DISP=(NEW,CATLG,DELETE)
//SRTCDnnn DD DSN=TSOID01.prefix.FBCNTL.Dnnnnnn.Tnnnnnn.suffix(SRTCDnnn),
// (DISP=SHR)
//SYSOUT DD SYSOUT=(*)
//RDXSQLCD DD SYSOUT=(*)
//SYSPRINT DD SYSOUT=(*)
//SYSUDUMP DD SYSOUT=(*)
//TPTPRINT DD SYSOUT=(*)
//SYSTERM DD SYSOUT=(*)
//SYSIN DD DSN=TSOID01.prefix.VBCNTL.Dnnnnnn.Tnnnnnn.suffix(Db2LCT),
// (DISP=SHR)
Db2 Load Utility Load JCL (continues on next page)
//*-------------------------------------------------------------------
//* SORTnnn:- SORT INPUT FILE FOR LOAD STEP LOADnnn
//*-------------------------------------------------------------------
//SORTnnn EXEC PGM=SORT,COND=(4,LT)
//*
//STEPLIB DD DSN=SYS1.SORTLIB,
// DISP=SHR
//SYSOUT DD SYSOUT=(*)
//DFSPARM DD DSN=TSOID01.prefix.FBCNTL.Dnnnnnn.Tnnnnnn.suffix(DFSPARM),
// (DISP=SHR)
//$ORTPARM DD DSN=TSOID01.prefix.FBCNTL.Dnnnnnn.Tnnnnnn.suffix($ORTPARM),
// (DISP=SHR)
//SORTIN DD DSN=File-AID-split-file,
// DISP=(OLD,KEEP,KEEP)
//SORTOUT DD DSN=File-AID-sort-file
// DCB=(RECFM=VB,LRECL=302,BLKSIZE=0),
// SPACE=(TRK,(5,1),RLSE),
// UNIT=SYSDA,
// DISP=(NEW,CATLG,DELETE)
//SORTWK01 DD UNIT=SYSDA,
// SPACE=(19069,(00003),,,ROUND),
// DISP=(NEW,DELETE,DELETE)
//SORTWK02 DD UNIT=SYSDA,
// SPACE=(19069,(00003),,,ROUND),
// DISP=(NEW,DELETE,DELETE)
//SORTWK03 DD UNIT=SYSDA,
// SPACE=(19069,(00003),,,ROUND),
// DISP=(NEW,DELETE,DELETE)
//SYSIN DD DSN=TSOID01.prefix.FBCNTL.Dnnnnnn.Tnnnnnn.suffix(SRTCDnnn),
// DISP=SHR
//*-------------------------------------------------------------------
//* LOADnnn:- LOAD STEP FOR TABLE TSOID01.CUSTOMER_TABLE
//*-------------------------------------------------------------------
//LOADnnn EXEC PGM=DSNUTILB,PARM='DSN,TSOID01',
// REGION=8192K,COND=(4,LT)
//*
//STEPLIB DD DSN= Db2-load-library,D
ISP=SHR
//SORTOUT DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//SORTWK01 DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//SORTWK02 DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//SORTWK03 DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//SORTWK04 DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//SYSUT1 DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//SYSERR DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//SYSMAP DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//SYSREC DD DISP=(OLD,KEEP,KEEP),
// DSN=File-AID-sort-file
//COPYnnn DD DSN=TSOID01.prefix.IMAGEnnn.Dnnnnnn.Tnnnnnn.suffix,
// DCB=(RECFM=VB,LRECL=27994,BLKSIZE=27998,DSORG=PS),
// SPACE=(27998,(20,10),RLSE),
// UNIT=SYSDA,VOL=SER=PRD900,
// DISP=(NEW,CATLG,DELETE)
//SYSPRINT DD SYSOUT=(*)
//UTPRINT DD SYSOUT=(*)
//SYSIN DD DSN=TSOID01.prefix.FBCNTL.Dnnnnnn.Tnnnnnn.suffix(LOADnnn),
// (DISP=SHR)
/*
//*-------------------------------------------------------------------
//* CHCKDATA:- RUN CHECK DATA TO CHECK TABLESPACES FOR VIOLATIONS
//* OF REFERENTIAL CONSTRAINTS.
//* - CHECK DATA IS ONLY RUN AGAINST TABLESPACES THAT MAY
//* BE IN CHECK PENDING STATUS.
//* - THE "SCOPE ALL" PARAMETER IS USED.
//*-------------------------------------------------------------------
//CHCKDATA EXEC PGM=DSNUTILB,PARM='DSN,TSOID01',
// REGION=8192K,COND=(4,LT)
//*
//STEPLIB DD DSN=Db2-load-library,DISP=SHR
//SYSPRINT DD SYSOUT=(*)
//UTPRINT DD SYSOUT=(*)
//SORTOUT DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//SORTWK01 DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//SORTWK02 DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//SORTWK03 DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND
//SORTWK04 DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//SYSUT1 DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//SYSERR DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//SYSIN DD DSN=TSOID01.prefix.CHECK.Dnnnnnn.Tnnnnnn.suffix,
// DISP=(OLD,KEEP,KEEP)
//*
Db2 Load Utility Load JCL
//* IMAGECOPY:- THE COPY UTILITY IS RUN AGAINST TABLESPACES THAT HAVE
//* BEEN PLACED IN COPY PENDING STATUS.
//* - TABLESPACES ARE PLACED IN COPY PENDING BY SPECIFYING
//* "LOG NO" DURING THE LOAD PROCESS.
//* - A FULL IMAGECOPY WILL BE MADE.
//*-------------------------------------------------------------------
//IMAGECPY EXEC PGM=DSNUTILB,PARM='DSN,TSOID01',
// REGION=8192K,COND=(4,LT)
//*
//STEPLIB DD DSN=Db2-load-library,DISP=SHR
//SYSPRINT DD SYSOUT=(*)
//UTPRINT DD SYSOUT=(*)
//SORTOUT DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//SORTWK01 DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//SORTWK02 DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//SORTWK03 DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//SORTWK04 DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//SYSUT1 DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//SYSERR DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//COPYnnn DD DSN=TSOID01.prefix.IMAGEnnn.Dnnnnnn.Tnnnnnn.suffix,
// DCB=(RECFM=VB,LRECL=27994,BLKSIZE=27998,DSORG=PS),
// SPACE=(27998,(2,2),RLSE),
// UNIT=SYSDA,
// DISP=(NEW,CATLG,DELETE)
//SYSIN DD DSN=TSOID01.prefix.IMAGECPY.Dnnnnnn.Tnnnnnn.suffix,
// DISP=(OLD,KEEP,KEEP)
//* XFRJLDND
//*-------------------------------------------------------------------
//* CLEANLST: - THIS STEP WILL PERFORM A DELETE OF ALL TEMPORARY
//* FILES USED IN THIS JOB.
//* - KEEPING THE FILES UNTIL ALL STEPS RUN SUCCESSFULLY
//* ALLOWS FOR THE RESTARTING OF THE JCL AT ANY STEP.
//*-------------------------------------------------------------------
//CLEAN1ST EXEC PGM=IDCAMS,COND=(4,LT),DYNAMNBR=2048
//SYSPRINT DD SYSOUT=*
//SYSIN DD * IDCAMS DELETE STATEMENTS FOLLOW
DELETE File-AID-sort-file
DELETE File-AID-sort-control-file
DELETE File-AID-split-file
DELETE File-AID-check-file
DELETE File-AID-image-copy-file
SET MAXCC=0
SET LASTCC=0
/*
//*
//*-------------------------------------------------------------------
//* CLEANCTL: - THIS STEP WILL PERFORM A DELETE OF THE TWO CNTL
//* DATASETS USED BY THIS JOB FOR THE SYSIN DATA.
//* - THE DATASETS ARE ONLY DELETED IF ALL THE PREVIOUS
//* STEPS RUN SUCCESSFULLY; THIS ALLOWS FOR RESTARTING
//* OF THE JOB AT ANY STEP.
//*-------------------------------------------------------------------
//* NOTE! - THIS STEP IS CONDITIONED ON AN OPTION ON THE GLOBAL
//* OPTIONS SCREEN
/*-------------------------------------------------------------------
//CLEANCTL EXEC PGM=IEFBR14,COND=(4,LT)
//*
//DFBPDSE DD DSN=TSOID01.prefix.FBCNTL.Dnnnnnn.Tnnnnnn.suffix,
// DISP=(OLD,DELETE,DELETE)
//DVBPDSE DD DSN=TSOID01.prefix.VBCNTL.Dnnnnnn.Tnnnnnn.suffix,
// DISP=(OLD,DELETE,DELETE)
/*-------------------------------------------------------------------
//CLEANEXT EXEC PGM=IDCAMS,COND=(4,LT),DYNAMNBR=2048 XFRJCLXT
//SYSPRINT DD SYSOUT=*
//SYSIN DD * IDCAMS DELETE STATEMENTS FOLLOW
DELETE extract-parms-file,
SET MAXCC=0
SET LASTCC=0
//*
//* /*XFRJCLXT*/
//
CREATE Step Parameter List and DD Statements
XFREXEC is the File-AID/RDX program name.
The parameter information in the PARM field on the EXEC statement for the CREATE step must have the following format:
EXEC Statement PARM Field—CREATE Step
Parameter | Default | Length | Description |
|---|---|---|---|
Product | FR | 2 | File-AID/RDX |
Option | 7 | 1 | File-AID/RDX option number. |
Operating Mode | B | 1 | B : Batch |
The following DD statements are required for the CREATE step:
DD Statements—CREATE Step
DD Statement | Description |
|---|---|
STEPLIB | File-AID/RDX load libraries (CXVJLOAD and SXVJLOAD); Db2 load library. |
RDXMLIB | Library containing File-AID/RDX messages. |
EXTRACT | File containing the extracted data. |
EXTPARMS | File containing the load parameters (see EXTPARMS File). |
SYSOUT | Output class for error messages and reports. |
RDXSQLCD | Output class for detailed SQL error information from Db2. |
SYSPRINT | Standard output stream. |
SYSTERM | Output class for runtime errors. |
TPTPRINT | Standard output stream for optional diagnostics. |
SYSIN | DDL for tables and indexes to be created (see Db2 Create DDL (member Db2CREAT)). |
Create DDL
Member Db2CREAT in the SYSIN control card PDSE contains the DDL for tables and indexes to be created (see the following figure).
Db2 Create DDL (member Db2CREAT)
--
CREATE TABLE TSOID02.CUSTOMER_TABLE
(CUSTOMER_NUMBER CHAR(6) NOT NULL WITH DEFAULT,
COMPANY_NAME CHAR(30) NOT NULL WITH DEFAULT,
ADDRESS CHAR(30) NOT NULL WITH DEFAULT,
CITY CHAR(25) NOT NULL WITH DEFAULT,
STATE CHAR(2) NOT NULL WITH DEFAULT,
ZIP_CODE CHAR(9) NOT NULL WITH DEFAULT,
COUNTRY CHAR(20) NOT NULL WITH DEFAULT,
AREA_CODE CHAR(3) NOT NULL WITH DEFAULT,
TELEPHONE_NUM CHAR(7) NOT NULL WITH DEFAULT,
CONTACT_NAME CHAR(30) NOT NULL WITH DEFAULT,
CONTACT_TITLE CHAR(30),
CONTACT_ADDR CHAR(30),
CONTACT_CITY CHAR(25),
CONTACT_STATE CHAR(2),
CONTACT_ZIP CHAR(9),
CONTACT_COUNTRY CHAR(10),
CONTACT_AREA_CD CHAR(3),
CONTACT_TELEPHONE CHAR(7),
PRIMARY KEY (CUSTOMER_NUMBER)
) IN DATABASE TSOID01H;
--
CREATE UNIQUE INDEX TSOID02.CUST_IDX
ON TSOID02.CUSTOMER_TABLE
(CUSTOMER_NUMBER ASC)
CLUSTER
SUBPAGES 4
BUFFERPOOL BP0
C
--
--CREATE TABLE/INDEX DDL FOR: TSOID02.CONTACT_TABLE
--
CREATE TABLE TSOID02.CONTACT_TABLE
(CUSTOMER_NUMBER CHAR(6) NOT NULL WITH DEFAULT,
CONTACT_ID CHAR(2) NOT NULL WITH DEFAULT,
CONTACT_NAME CHAR(30) NOT NULL WITH DEFAULT,
TITLE CHAR(30),
CONTACT_CODE CHAR(4) NOT NULL WITH DEFAULT,
ADDRESS CHAR(30) NOT NULL WITH DEFAULT,
CITY CHAR(25) NOT NULL WITH DEFAULT,
STATE CHAR(2) NOT NULL WITH DEFAULT,
ZIP_CODE CHAR(9) NOT NULL WITH DEFAULT,
COUNTRY CHAR(20) NOT NULL WITH DEFAULT,
AREA_CODE CHAR(3) NOT NULL WITH DEFAULT,
TELEPHONE_NUM CHAR(7) NOT NULL WITH DEFAULT,
PRIMARY KEY (CUSTOMER_NUMBER,
CONTACT_ID)
) IN DATABASE TSOID01H;
--
CR
EATE UNIQUE INDEX TSOID02.CONT_IDX
ON TSOID02.CONTACT_TABLE
(CUSTOMER_NUMBER ASC,
CONTACT_ID ASC)
CLUSTER
SUBPAGES 4
BUFFERPOOL BP0
CLOSE YES;
--
--ALTER TO ADD FOREIGN KEYS TO ALL TABLES
--
ALTER TABLE TSOID02.CONTACT_TABLE
ADD FOREIGN KEY
(CUSTOMER_NUMBER)
REFERENCES TSOID02.CUSTOMER_TABLE
ON DELETE CASCADE;
LDDRV Step Parameter List and DD Statements
XFREXEC is the File-AID/RDX program name.
The parameter information in the PARM field in the EXEC statement must have the format as shown in the following table:
EXEC Statement PARM Field—LDDRV Step
Parameter | Default | Length | Description |
|---|---|---|---|
Product | FR | 2 | File-AID/RDX |
Option | 3 | 1 | File-AID/RDX option number. |
Operating Mode | B | 1 | B : Batch |
The following DD statements are required for the LDDRV step:
DD Statements—LDDRV Step
DD Statement | Description |
|---|---|
STEPLIB | File-AID/RDX load libraries (CXVJLOAD and SXVJLOAD); Db2 load library. |
RDXMLIB | Library containing File-AID/RDX messages. |
EXTRACT | File-AID/RDX extract file name. |
EXTPARMS | File containing the load parameters (see EXTPARMS File). |
CHCKDATA | Output file to which the CHECK utility control statements are written. |
IMAGECPY | Output file to which the COPY utility statements are written. |
SPLITnnn | Output file to which File-AID/RDX writes data for a single table from the extract file.
|
SRTCDnnn | Control card used by File-AID/RDX to sort each split file before the data is loaded into a table.
|
SYSOUT | Output class for error messages and reports. |
RDXSQLCD | Output class for detailed SQL error information from Db2. |
SYSPRINT | Standard output stream. |
SYSTERM | Output class for runtime errors. |
TPTPRINT | Standard output stream for optional diagnostics. |
SYSIN | File-AID/RDX’s Load Control Table. Used to verify authorities and identify column mapping details. For more information, see Load Control Table. |
Load Control Table
The Load Control Table (member Db2LCT in the SYSIN control card PDSE) contains information about the source and target tables (see the following figure). It is the input (SYSIN) to the LDDRV step.
Db2 Load Control Table (Db2LCT)
*
*
* SOURCE AND TARGET OBJECTS
* ----------------------------------
SC: TSOID01
ST: CUSTOMER_TABLE
TI: DEL=R DEP=N ROW COUNT= 2001 NNN E REP=N
TC: TSOID02
TT: CUSTOMER_TABLE
*
* TABLE CONTROL NUMBER
CN: 001
*
* NULL CCSID
* TARGET COLUMN NAME DEF SOURCE COLUMN NAME TYPE OFFSET OFFSET LENGTH TARGET SOURCE
*-------------------- --- -------------------- ---- ------ ------ ------ ------ ------
CUSTOMER_NUMBER N CUSTOMER_NUMBER C 16 6
COMPANY_NAME N COMPANY_NAME C 22 30
ADDRESS N ADDRESS C 52 30
CITY N CITY C 82 25
STATE N STATE C 107 2
ZIP_CODE N ZIP_CODE C 109 9
COUNTRY N COUNTRY C 118 20
AREA_CODE N AREA_CODE C 138 3
TELEPHONE_NUM N TELEPHONE_NUM C 141 7
CONTACT_NAME N CONTACT_NAME C 148 30
CONTACT_TITLE N CONTACT_TITLE C 178 0 30
CONTACT_ADDR N CONTACT_ADDR C 208 2 30
CONTACT_CITY N CONTACT_CITY C 238 4 25
CONTACT_STATE N CONTACT_STATE C 263 6 2
CONTACT_ZIP N CONTACT_ZIP C 265 8 9
CONTACT_COUNTRY N CONTACT_COUNTRY C 274 10 10
CONTACT_AREA_CD N CONTACT_AREA_CD C 284 12 3
CONTACT_TELEPHONE N CONTACT_TELEPHONE C 287 14 7
*
*
* SOURCE AND TARGET OBJECTS
* ----------------------------------
SC: TSOID01
ST: CONTACT_TABLE
TI: DEL=R DEP=Y ROW COUNT= 1492 NNN E REP=N
TC: TSOID02
TT: CONTACT_TABLE
*
* TABLE CONTROL NUMBER
CN: 002
*
* NULL CCSID
* TARGET COLUMN NAME DEF SOURCE COLUMN NAME TYPE OFFSET OFFSET LENGTH TARGET SOURCE
*-------------------- --- -------------------- ---- ------ ------ ------ ------ ------
C
USTOMER_NUMBER N CUSTOMER_NUMBER C 2 6
CONTACT_ID N CONTACT_ID C 8 2
CONTACT_NAME N CONTACT_NAME C 10 30
TITLE N TITLE C 40 0 30
CONTACT_CODE N CONTACT_CODE C 70 4
ADDRESS N ADDRESS C 74 30
CITY N CITY C 104 25
STATE N STATE C 129 2
ZIP_CODE N ZIP_CODE C 131 9
COUNTRY N COUNTRY C 140 20
AREA_CODE N AREA_CODE C 160 3
TELEPHONE_NUM N TELEPHONE_NUM C 163 7
Each source/target pair is identified by its table control number and for each, there are two tables. The first table contains the following information:
Load Control Table—Source and Target Table Information
Field Name | Description | |
|---|---|---|
SC | The source creator ID. | |
ST | The source table; the table extracted from. | |
TI | Target Information | |
DEL | For T: tables only. Indicates whether or not rows should be deleted before the load is executed. | |
Y | Delete rows before loading using the DELTEnnn step. | |
N | Do not delete rows before loading. | |
R | Delete rows using LOAD utility’s REPLACE parameter. | |
DEP | For TT: tables only. Indicates whether or not File-AID/RDX should attempt to replace (update) existing rows from the target table with rows from the source table. | |
Y | Yes | |
N | No | |
ROW COUNT | For T: tables only. Identifies the number of rows to be loaded into the target table. | |
N or Y | Represents the D record supplement indicator (see also Product Record — 0). | |
N or Y | Represents the extract IMPLICITLY HIDDEN columns indicator (see also Extract IMPLICITLY HIDDEN columns). | |
N, C or V | Represents the compression indicator (see also Compress). | |
E or U | Represents the encoding indicator (see also Db2 Format). | |
REP=N or Y | Represents the replace matches indicator (see also Replace Matches). | |
TC | The target creator ID. | |
TT | The target table; the table to be loaded. | |
CN | Table control number; corresponds to the Hnnn records in the extract file. | |
The second table contains the following column mapping information:
Load Control Table—Column Mapping Information
Field Name | Description | |
|---|---|---|
Target Column Name | Contains a list of the names of the target columns. This information is taken from the extract file and the Column Mapping screen. If you mapped any target columns to source columns with different names, those mappings are reflected here. | |
Def | Indicates whether the target column will contain its default value or nulls, or whether it is excluded from the load. | |
Y | Yes – The column will contain the default value defined for it. | |
N | No – The column will contain the value of the source column. | |
x | Excluded – The column is excluded from the load. | |
Source Column Name | Contains a list of the names of the source columns. This information is taken from the extract file. | |
Type | Indicates the data type of the target columns. This information is taken from the extract file and the Column Mapping screen. | |
Offset | Indicates the offset of the source data in the extract file. | |
Null Offset | Indicates the offset of null indicators for the source data in the extract file. | |
Length | Indicates the length of the source columns. This information is taken from the extract file and the Column Mapping screen. | |
CCSID Target | Indicates the CCSID for the target column if it is Unicode encoded. | |
CCSID Source | Indicates the CCSID for the source column if it is Unicode encoded. | |
DELTEnnn Step Parameter List and DD Statements
XFREXEC is the File-AID/RDX program name.
The parameter information in the PARM field in the EXEC statement must have the format as shown in the following table:
EXEC Statement PARM Field—DELTEnnn Step
Parameter | Default | Length | Description |
|---|---|---|---|
Product | FR | 2 | File-AID/RDX |
Option | 4 | 1 | File-AID/RDX option number. |
Operating Mode | B | 1 | B : Batch |
The following DD statements are required for the DELTEnnn step:
DD Statements—DELTEnnn Step
DD Statement | Description |
|---|---|
STEPLIB | File-AID/RDX load libraries (CXVJLOAD and SXVJLOAD); Db2 load library. |
RDXMLIB | Library containing File-AID/RDX messages. |
EXTPARMS | File containing the load parameters (see EXTPARMS File). |
RDXSQLCD | Output class for detailed SQL error information from Db2. |
SYSOUT | Output class for error messages and reports. |
SYSPRINT | Standard output stream. |
SYSTERM | Output class for runtime errors. |
TPTPRINT | Standard output stream for optional diagnostics. |
DLETEIN | File that contains the delete data information (member DELTEnnn in the control card PDSE). |
FIXDLnnn
This step uses the standard Db2 REPAIR utility to repair a tablespace due to previous delete step. Refer to the IBM Db2 Command and Utility Reference for a full description of how to set up this step.
SORTnnn Step DD Statements
This step uses your site’s SORT utility. The following DD statements are required for the SORT step:
DD Statements—SORT Step
DD Statement | Description |
|---|---|
STEPLIB | Your site’s SORT library. |
SORTIN | SPLIT file from the LDDRV step. |
SORTOUT | Output from the SORT. |
SYSOUT | Output class for messages and reports. |
SORTWK01 | Work files required by the SORT utility. |
SYSIN | File that will contain the SORT cards generated by the LDDRV step (member SRTCDnnn in the SYSIN control card PDSE). |
ICETOOL Step DD Statements
This optional step uses the ICETOOL utility. The following DD statements are required for the ICETOOL step:
DD Statements—ICETOOL Step
DD Statement | Description |
|---|---|
STEPLIB | Your site’s SORT library. |
DFSPARM | Supplies DFSORT program control statements and EXEC statement PARM options. |
$ORTPARM | Supplies SYNCSORT program control statements and EXEC statement PARM options. |
SYSOUT | Output class for messages and reports. |
TOOLMSG | Messages from the ICETOOL utility. |
DFSMSG | Messages from the individual sorts. |
CnnnCNTL | File that will contain the SORT cards generated by the LDDRV step. Associated with each SnnnIN DD. |
SnnnIN | SPLIT file from the LDDRV step. A unique DD generated for each split file to sort. |
SnnnOUT | Output from the SORT. A unique DD generated for each sort output file. |
SORTWK01 | Work files required by the SORT utility. |
TOOLIN | File that associates each CnnnCNTL, SnnnIN, and SnnnOUT DD for ICETOOL to sort. |
LOADnnn
This step uses the standard Db2 LOAD utility.
The SYSREC DD must reference the OUTPUT file from the corresponding SORT step. Refer to the IBM Db2 Command and Utility Reference for a full description of how to set up this step. The load data is contained in member LOADnnn of the SYSIN control card PDSE.
SYSIN Load Data (member LOADnnn)
REPLACE
RESUME NO
COPYDDN COPYnnn
ENFORCE NO
LOG NO
INTO TABLE TSOID01.CUSTOMER_TABLE
(
CUSTOMER_NUMBER POSITION(17)
CHAR (6)
,
COMPANY_NAME POSITION(23)
CHAR (30)
,
ADDRESS POSITION(53)
CHAR (30)
,
CITY POSITION(83)
CHAR (25)
,
STATE POSITION(108)
CHAR (2)
,
ZIP_CODE POSITION(110)
CHAR (9)
,
COUNTRY POSITION(119)
CHAR (20)
,
AREA_CODE POSITION(139)
CHAR (3)
,
TELEPHONE_NUM POSITION(142)
CHAR (7)
,
CONTACT_NAME POSITION(149)
CHAR (30)
,
CONTACT_TITLE POSITION(179)
CHAR (30)
NULLIF (1:2) = X'FFFF',
CONTACT_ADDR POSITION(209)
CHAR (30)
NULLIF (3:4) = X'FFFF',
CONTACT_CITY POSITION(239)
CHAR (25)
NULLIF (5:6) = X'FFFF',
CONTACT_STATE POSITION(264)
CHAR (2)
NULLIF (7:8) = X'FFFF',
CONTACT_ZIP POSITION(266)
CHAR (9)
NULLIF (9:10) = X'FFFF',
CONTACT_COUNTRY POSITION(275)
CHAR (10)
NULLIF (11:12) = X'FFFF',
CONTACT_AREA_CD POSITION(285)
CHAR (3)
NULLIF (13:14) = X'FFFF',
CONTACT_TELEPHONE POSITION(288)
CHAR (7)
NULLIF (15:16) = X'FFFF'
)
RPAIRnnn
This step uses the standard Db2 REPAIR utility. Refer to the IBM Db2 Command and Utility Reference for a full description of how to set up this step.
CHCKDATA
This step uses the standard Db2 CHECK utility.
The SYSIN DD must reference the same file referenced by the CHCKDATA DD in the LDDRV step. Refer to the IBM Db2 Command and Utility Reference for a full description of how to set up this step.
IMAGECPY
This step uses the standard Db2 COPY utility.
The SYSIN DD must reference the same file referenced by the IMAGECPY DD in the LDDRV step. Refer to the IBM Db2 Command and Utility Reference for a full description of how to set up this step.
COPYnnn
This step uses the Db2 Inline Image Copy utility. Refer to the IBM Db2 Command and Utility Reference for a full description of how to set up this step.
Return Codes
An execution return code (RC) appears in the output listing. For return codes other than 0, an error code and message indicating the specific problem also appear in the listing. In general, the return codes can be summarized as follows:
Return Code | Description |
|---|---|
0 | Job ran to completion without error. |
4 | Job ran to completion without error, warnings issued. |
8 | Job ran with error. Subsequent job steps stopped. |
16 | Severe error, job aborted. |