SQLUTBxx LOAD

Executing SQLUTBxx LOAD

To use SQLUTBxx LOAD, either create a JCL application project or directly submit JCL using the JES CONTROL feature in Enterprise Server. The JCL syntax must be DB2 compatible.

The following is an example of JCL using a LOAD card generated on the mainframe:

//DSNCUSTM JOB
//DSNUPROC 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 connection to a SQL Server database, SQLUTB32 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 not effective; however the syntax is recognized.

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

Ignored, but Syntactially Recognized INTO TABLE Options

• IGNOREFIELDS
• PART

LOAD Specification

• INDDN SYSREC (or ddname)
• EBCDIC/ASCII/UNICODE

Note: Unlike the mainframe, SQLUTBxx LOAD does not default to the EBCDIC character code set. With SQLUTBxx LOAD, you must specify EBCDIC, ASCII, or UNICODE.

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

Unsupported SQL Data Types for FIELD Specification

• BINARY
• VARBINARY
• DECFLOAT
• ROWID
• BLOB
• CLOB
• DBCLOB
• XML