SQLUTB LOAD is designed to work with LOAD cards generated by DSNUTILB UNLOAD on the mainframe as well as LOAD cards generated by SQLUTB UNLOAD itself. For example, the following UNLOAD control statements generate valid LOAD statements:
UNLOAD DATA FROM TABLE HCOSQL.CUSTOMER
UNLOAD TABLESPACE HCOSQL.HCOTS FROM TABLE HCOSQL.EMPLOYEE
Important: SQLUTB LOAD requires that you modify the LOAD control statement created on the mainframe to include either the REPLACE YES or the RESUME YES option. If neither is included, the JCL job terminates with an abend.
Executing SQLUTB LOAD
To use SQLUTB LOAD, either create a JCL application project or directly submit JCL using the JES CONTROL feature in
Enterprise Server. The JCL syntax must be z/OS DB2 compatible.
The following is an example of JCL using a LOAD card generated on the mainframe:
//DSNCUSTM JOB
//DSNUTILB EXEC PGM=HCOUTILB,PARM='HCOD,SH '
//*
//SYSPRINT DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//STPRIN01 DD SYSOUT=*
//IDIOFF DD DUMMY
//*
//SYSUT1 DD DISP=MOD,DSN=SH.DSNUTILB.SYSUT1
//SORTOUT DD DISP=MOD,DSN=SH.DSNUTILB.SYSOUT
//SYSMAP DD DISP=MOD,DSN=SH.DSNUTILB.SYSMAP
//SYSERR DD DISP=MOD,DSN=SH.DSNUTILB.SYSERR
//SYSREC DD DISP=SHR,DSN=SH.HCOSQL.CUSTOMRS
//SYSIN DD *
LOAD DATA INDDN SYSREC LOG NO RESUME NO REPLACE
EBCDIC CCSID(00037,00000,00000)
INTO TABLE
"HCOSQL".
"CUSTOMRS"
WHEN(00001:00002) = X'0028'
NUMRECS 242100
( "C_ID"
POSITION( 00003:00006) INTEGER
, "C_D_ID"
POSITION( 00007:00008) SMALLINT
, "C_W_ID"
POSITION( 00009:00010) SMALLINT
, "C_FIRST"
POSITION( 00012:00029) VARCHAR
NULLIF(00011)=X'FF'
, "C_MIDDLE"
POSITION( 00031:00032) CHAR(00002)
NULLIF(00030)=X'FF'
, "C_LAST"
POSITION( 00034:00051) VARCHAR
NULLIF(00033)=X'FF'
, "C_STREET_1"
POSITION( 00053:00074) VARCHAR
NULLIF(00052)=X'FF'
, "C_STREET_2"
POSITION( 00076:00097) VARCHAR
NULLIF(00075)=X'FF'
, "C_CITY"
POSITION( 00099:00120) VARCHAR
NULLIF(00098)=X'FF'
, "C_STATE"
POSITION( 00122:00123) CHAR(00002)
NULLIF(00121)=X'FF'
, "C_ZIP"
POSITION( 00125:00133) CHAR(00009)
NULLIF(00124)=X'FF'
, "C_PHONE"
POSITION( 00135:00150) CHAR(00016)
NULLIF(00134)=X'FF'
, "C_SINCE"
POSITION( 00152:00177) TIMESTAMP EXTERNAL
NULLIF(00151)=X'FF'
, "C_CREDIT"
POSITION( 00179:00180) CHAR(00002)
NULLIF(00178)=X'FF'
, "C_CREDIT_LIM"
POSITION( 00182:00188) DECIMAL
NULLIF(00181)=X'FF'
, "C_DISCOUNT"
POSITION( 00190:00192) DECIMAL
NULLIF(00189)=X'FF'
, "C_BALANCE"
POSITION( 00194:00200) DECIMAL
NULLIF(00193)=X'FF'
, "C_YTD_PAYMENT"
POSITION( 00202:00208) DECIMAL
NULLIF(00201)=X'FF'
, "C_PAYMENT_CNT"
POSITION( 00210:00211) SMALLINT
NULLIF(00209)=X'FF'
, "C_DELIVERY_CNT"
POSITION( 00213:00214) SMALLINT
NULLIF(00212)=X'FF'
, "C_DATA"
POSITION( 00216:00717) VARCHAR
NULLIF(00215)=X'FF'
)
/*
//
Where HCOD is the ODBC XAR ID for your database connection, SQLUTB has been established as an alias for program HCOUTILB, and SH.HCOSQL.CUSTOMRS is the cataloged data file mapped to SYSREC.
LOAD Options
- Standard LOAD Options
-
- DATA
- Syntactically optional.
- REPLACE YES
- REPLACE YES is mutually exclusive to RESUME YES. Either REPLACE YES or RESUME YES is required.
If neither REPLACE YES nor RESUME YES are present, warnings are generated based on the target table having an empty status.
- DISCARDDN
- This option behaves differently from DB2 for z/OS in handling unique index. The utility does not check for duplicates in the input data set, but instead forwards all data to the database. If duplicate records exist in the data set, only the first instance is loaded into the target table, assuming that no existing record in the table has the same index.
- LOG NO|YES
- This option is syntactically accepted, but ignored, as LOG YES is the only supported behavior.
- RESUME YES
- RESUME YES is mutually exclusive to REPLACE YES. Either RESUME YES or REPLACE YES is required.
If neither RESUME YES nor REPLACE YES are present, warnings are generated based on the target table having an empty status.
- Ignored, but Syntactically Recognized LOAD Options
-
- CCSID
- CONTINUE IF
- COPYDDN
- DISCARDS
- ENFORCE
- ERRDDN
- FLOAT (S390/IEEE)
- FORMAT-SPEC
- FREQVAL
- HISTORY
- INCURSOR
- INDEX
- KEEPDICTIONARY
- KEYCARD
- MAPDDN
- NOCOPYPEND
- NOSUBS
- PREFORMAT
- RECOVERYDDN
- REUSE
- SHRLEVEL
- SORTDEVT
- SORTKEY
- SORTNUMS
- STATISTICS
- WORKDDN
- Unsupported LOAD Options
-
- COPYDICTIONARY
- DECFLOAT ROUNDMODE-SPEC
- IDENTITYOVERRIDE
- INDEXDEFER
- PRESORTED
Specifications
- INTO TABLE Specifications
- The INTO TABLE specifications have a number of options.
- INTO TABLE Supported Options
-
- WHEN
- NUMRECS
- FIELD SPECIFICATION
- PART (for DB2 LUW only)
- Ignored, but Syntactially Recognized INTO TABLE Options
-
- IGNOREFIELDS
- PART (for all DBMSs but DB2 LUW)
- LOAD Specification
-
- INDDN SYSREC (or ddname)
- EBCDIC/ASCII/UNICODE
Note: Unlike the mainframe, SQLUTB LOAD does not default to EBCDIC, but defaults to ASCII.
- FIELD Specification
-
- Supported SQL Data Types for FIELD Specification
-
- INTEGER, BIGINT, SMALLINT
- FLOAT, REAL
- DECIMAL
- CHAR, VARCHAR (UP TO VARCHAR(20000))
- GRAPHIC, VARGRAPHIC
- DATE, TIME, TIMESTAMP
- BINARY, VARBINARY
- Unsupported SQL Data Types for FIELD Specification
-
- DECFLOAT
- GRAPHIC
- VARGRAPHIC
- ROWID
- BLOB
- CLOB
- DBCLOB
- XML
Important: SQLUTB LOAD commit behavior can be configured to issue one commit or multiple commits. See
MBDT Configuration Utility for details.
Note: To ensure proper parsing of SYSIN, do not include the tab character (ASCII decimal character code 9; EBCDIC decimal character code 5) in its contents. When parsed, the tab character could cause spurious, unintended errors.
