Sample LOAD control statements
Use the sample control statements as models for developing your own LOAD control statements.
Specifying field positions
The following LOAD statement specifies that the utility is to load the records from the data set that is defined by the SYSREC DD statement into table DSN8810.DEPT. SYSREC is the default input data set.
Each POSITION clause specifies the location of a field in the input record. In this example, LOAD accepts the input and interprets it as follows:
- The first 3 bytes of each record are loaded into the DEPTNO column of the table.
- The next 36 bytes, including trailing blanks, are loaded into the DEPTNAME column.
If this input column were defined as VARCHAR(36), the input data would need to contain a 2-byte binary length field preceding the data. This binary field would begin at position 4.
- The next three fields are loaded into columns that are defined as CHAR(6), CHAR(3), and CHAR(16).
The RESUME YES clause specifies that the table space does not need to be empty; new records are added to the end of the table.
LOAD DATA
RESUME YES
INTO TABLE DSN8C10.DEPT
(DEPTNO POSITION (1:3) CHAR(3),
DEPTNAME POSITION (4:39) CHAR(36),
MGRNO POSITION (40:45) CHAR(6),
ADMRDEPT POSITION (46:48) CHAR(3),
LOCATION POSITION (49:64) CHAR(16))The following example shows the input to the preceding LOAD job.
A00SPIFFY COMPUTER SERVICE DIV. 000010A00USIBMSTODB21
B01PLANNING 000020A00USIBMSTODB21
C01INFORMATION CENTER 000030A00USIBMSTODB21
D01DEVELOPMENT CENTER A00USIBMSTODB21The following table shows the result of executing the statement SELECT * FROM DSN8C10.DEPT after the preceding input records are loaded.
| DEPTNO | DEPTNAME | MGRNO | ADMRDEPT | LOCATION |
|---|---|---|---|---|
| A00 | SPIFFY COMPUTER SERVICE DIV. | 000010 | A00 | USIBMSTODB21 |
| B01 | PLANNING | 000020 | A00 | USIBMSTODB21 |
| C01 | INFORMATION CENTER | 000030 | A00 | USIBMSTODB21 |
| D01 | DEVELOPMENT CENTER | A00 | USIBMSTODB21 |
Replacing data in a given partition
The following control statement specifies that data from the data set that is defined by the SYSREC DD statement is to be loaded into the first partition of table DSN8810.DEPT. The default input data set is SYSREC. The REPLACE option indicates that the input data is to replace only the specified partition. If the REPLACE option was specified before the PART option, REPLACE would indicate that entire table space is to be replaced, and the data is to be loaded into the specified partition. Note that the keyword DATA does not need to be specified.
LOAD
INTO TABLE DSN8C10.DEPT PART 1 REPLACELoading selected records into multiple tables
The following LOAD statement specifies that the utility is to load certain data from the EMPLDS input data set into tables DSN8C10.EMP, SMITH.EMPEMPL, and DSN8810.DEPT. The input data set is identified by the INDDN option. The WHEN clauses indicate which records are to be loaded into each table. For the EMP and DEPT tables, the utility is to load only records that begin with the string LKA. For the EMPEMPL table, the utility is to load only records that begin with the string ABC. The RESUME YES option indicates that the table space does not need to be empty for the LOAD job to proceed. The new rows are added to the end of the tables. This example assumes that the first two tables being loaded have exactly the same format, and that the input data matches that format; therefore, no field specifications are needed for those two INTO TABLE clauses. The third table has a different format, so field specifications are required and are supplied in the example.
The three tables being loaded each contain a different number of records. To improve the sizing of the sort work data sets that the LOAD utility requires, the number of records being loaded into each table is specified on the NUMRECS keyword for each table.
The POSITION clauses specify the location of the fields in the input data for the DEPT table. For each source record that is to be loaded into the DEPT table:
- The characters in positions 7 - 9 are loaded into the DEPTNO column.
- The characters in positions 10 - 35 are loaded into the DEPTNAME column.
- The characters in positions 36 - 41 are loaded into the MGRNO column.
- The characters in positions 42 - 44 are loaded into the ADMRDEPT column.
LOAD DATA INDDN EMPLDS
RESUME YES
INTO TABLE DSN8C10.EMP
NUMRECS 100000
WHEN (1:3)='LKA'
INTO TABLE SMITH.EMPEMPL
NUMRECS 100
WHEN (1:3)='ABC'
INTO TABLE DSN8C10.DEPT
NUMRECS 500
WHEN (1:3)='LKA'
(DEPTNO POSITION (7:9) CHAR,
DEPTNAME POSITION (10:35) CHAR,
MGRNO POSITION (36:41) CHAR,
ADMRDEPT POSITION (42:44) CHAR)Loading data of different data types
The following LOAD statement specifies that the utility is to load data from the SYSRECPJ input data set into table DSN8C10.PROJ. The input data set is identified by the INDDN option. Assume that the table space that contains table DSN8C10.PROJ is currently empty.
For each input record, data is loaded into the specified columns (that is, PROJNO, PROJNAME, DEPTNO, and so on) to form a table row. Any other PROJ columns that are not specified in the LOAD control statement are set to the default value.
The POSITION clauses define the starting positions of the fields in the input data set. The ending positions of the fields in the input data set are implicitly defined either by the length specification of the data type (CHAR length) or the length specification of the external numeric data type (LENGTH).
The numeric data that is represented in SQL constant format (EXTERNAL format) is converted to the correct internal format by the LOAD process and placed in the indicated column names. The two dates (PRSTDATE and PRENDATE) are assumed to be represented by eight digits and two separator characters, as in the USA format (for example, 11/15/2006). The length of the date fields is given as 10 explicitly, although in many cases, the default is the same value.
LOAD DATA INDDN(SYSRECPJ)
INTO TABLE DSN8C10.PROJ
(PROJNO POSITION (1) CHAR(6),
PROJNAME POSITION (8) CHAR(22),
DEPTNO POSITION (31) CHAR(3),
RESPEMP POSITION (35) CHAR(6),
PRSTAFF POSITION (42) DECIMAL EXTERNAL(5),
PRSTDATE POSITION (48) DATE EXTERNAL(10),
PRENDATE POSITION (59) DATE EXTERNAL(10),
MAJPROJ POSITION (70) CHAR(6))Loading data in delimited file format
The control statement specifies that data in delimited format is to be loaded into the specified columns (FILENO, DATE1, TIME1, and TIMESTMP) in table TBQB0103. The FORMAT DELIMITED option indicates that the data is in delimited format. The data is to be loaded from the SYSREC data set, which is the default.
The COLDEL option indicates that the column delimiter is a comma (,). The CHARDEL option indicates that the character string delimiter is a double quotation mark ("). The DECPT option indicates that the decimal point character is a period (.). You are not required to explicitly specify these particular characters, because they are all defaults.
//*
//STEP3 EXEC DSNUPROC,UID='JUQBU101.LOAD2',TIME=1440,
// UTPROC='',
// SYSTEM='SSTR'
//SYSERR DD DSN=JUQBU101.LOAD2.STEP3.SYSERR,
// DISP=(MOD,DELETE,CATLG),UNIT=SYSDA,
// SPACE=(4096,(20,20),,,ROUND)
//SYSDISC DD DSN=JUQBU101.LOAD2.STEP3.SYSDISC,
// DISP=(MOD,DELETE,CATLG),UNIT=SYSDA,
// SPACE=(4096,(20,20),,,ROUND)
//SYSMAP DD DSN=JUQBU101.LOAD2.STEP3.SYSMAP,
// DISP=(MOD,DELETE,CATLG),UNIT=SYSDA,
// SPACE=(4096,(20,20),,,ROUND)
//SYSUT1 DD DSN=JUQBU101.LOAD2.STEP3.SYSUT1,
// DISP=(MOD,DELETE,CATLG),UNIT=SYSDA,
// SPACE=(4096,(20,20),,,ROUND)
//UTPRINT DD SYSOUT=*
//SORTOUT DD DSN=JUQBU101.LOAD2.STEP3.SORTOUT,
// DISP=(MOD,DELETE,CATLG),UNIT=SYSDA,
// SPACE=(4096,(20,20),,,ROUND)
//SYSIN DD *
LOAD DATA
FORMAT DELIMITED COLDEL ',' CHARDEL '"' DECPT '.'
INTO TABLE TBQB0103
(FILENO CHAR,
DATE1 DATE EXTERNAL,
TIME1 TIME EXTERNAL,
TIMESTMP TIMESTAMP EXTERNAL)
/*
//SYSREC DD *
"001", 2000-02-16, 00.00.00, 2000-02-16-00.00.00.0000
"002", 2001-04-17, 06.30.00, 2001-04-17-06.30.00.2000
"003", 2002-06-18, 12.30.59, 2002-06-18-12.30.59.4000
"004", 1991-08-19, 18.59.30, 1991-08-19-18.59.30.8000
"005", 2000-12-20, 24.00.00, 2000-12-20-24.00.00.0000
/*Concatenating multiple input records
The following LOAD statement specifies that data from the SYSRECOV input data set is to be loaded into table DSN8C10.TOPTVAL. The input data set is identified by the INDDN option. The table space that contains the TOPTVAL table is currently empty.
Some of the data that is to be loaded into a single row spans more than one
input record. In this situation, an X in column 72 indicates that the input record contains fields
that are to be loaded into the same row as the fields in the next input record. In the LOAD control
statement, CONTINUEIF(72:72)='X' indicates that LOAD is
to concatenate any input records that have an X in column 72 with the next record before loading the
data.
For each assembled input record (that is, after the concatenation), fields are loaded into the DSN8C10.TOPTVAL table columns (that is, MAJSYS, ACTION, OBJECT …, DSPINDEX) to form a table row. Any columns that are not specified in the LOAD control statement are set to the default value.
The POSITION clauses define the starting positions of the fields in the assembled input records. Starting positions are numbered from the first column of the internally assembled input record, not from the start of the input records in the sequential data set. The ending positions of the fields are implicitly defined by the length specification of the data type (CHAR length).
No conversions are required to load the input character strings into their designated columns, which are also defined to be fixed-length character strings. However, because columns INFOTXT, HELPTXT, and PFKTXT are defined as 79 characters in length and the strings that are being loaded are 71 characters in length, those strings are padded with blanks as they are loaded.
LOAD DATA INDDN(SYSRECOV) CONTINUEIF(72:72)='X'
INTO TABLE DSN8C10.TOPTVAL
(MAJSYS POSITION (2) CHAR(1),
ACTION POSITION (4) CHAR(1),
OBJECT POSITION (6) CHAR(2),
SRCHCRIT POSITION (9) CHAR(2),
SCRTYPE POSITION (12) CHAR(1),
HEADTXT POSITION (80) CHAR(50),
SELTXT POSITION (159) CHAR(50),
INFOTXT POSITION (238) CHAR(71),
HELPTXT POSITION (317) CHAR(71),
PFKTXT POSITION (396) CHAR(71),
DSPINDEX POSITION (475) CHAR(2))Loading null values
The following LOAD statement specifies that data from the SYSRECST data set is to be loaded into the specified columns in table SYSIBM.SYSSTRINGS. The input data set is identified by the INDDN option. The NULLIF option for the ERRORBYTE and SUBBYTE columns specifies that if the input field contains a blank, LOAD is to place a null value in the indicated column for that particular row. The DEFAULTIF option for the TRANSTAB column indicates that the utility is to load the default value for this column if the input field value is GG. The CONTINUEIF option indicates that LOAD is to concatenate any input records that have an X in column 80 with the next record before loading the data.
LOAD DATA INDDN(SYSRECST) CONTINUEIF(80:80)='X' RESUME(YES)
INTO TABLE SYSIBM.SYSSTRINGS
(INCCSID POSITION( 1) INTEGER EXTERNAL(5),
OUTCCSID POSITION( 7) INTEGER EXTERNAL(5),
TRANSTYPE POSITION( 13) CHAR(2),
ERRORBYTE POSITION( 16) CHAR(1) NULLIF(ERRORBYTE=' '),
SUBBYTE POSITION( 18) CHAR(1) NULLIF(SUBBYTE=' '),
TRANSPROC POSITION( 20) CHAR(8),
IBMREQD POSITION( 29) CHAR(1),
TRANSTAB POSITION( 31) CHAR(256) DEFAULTIF(TRANSTYPE='GG'))Enforcing referential constraints when loading data
The following LOAD statement specifies that data from the SYSREC input data set is to be loaded into table DSN8C10.PROJ. The default input data set is SYSREC. The table space that contains the PROJ table is not empty. RESUME YES indicates that the records are to be added to the end of the table.
The ENFORCE CONSTRAINTS option indicates that LOAD is to enforce referential constraints on the data that is being added. This option is also the default. All violations are reported in the output. All records causing these violations are not loaded and placed in the SYSDISC data set, which is the default data set for discarded records.
The CONTINUEIF option indicates that before loading the data LOAD is to concatenate any input records that have an X in column 72 with the next record.
LOAD DATA INDDN(SYSREC) CONTINUEIF(72:72)='X'
RESUME YES
ENFORCE CONSTRAINTS
INTO TABLE DSN8C10.PROJ
(PROJNO POSITION (1) CHAR (6),
PROJNAME POSITION (8) VARCHAR,
DEPTNO POSITION (33) CHAR (3),
RESPEMP POSITION (37) CHAR (6),
PRSTAFF POSITION (44) DECIMAL EXTERNAL (5),
PRSTDATE POSITION (50) DATE EXTERNAL,
PRENDATE POSITION (61) DATE EXTERNAL,
MAJPROJ POSITION (80) CHAR (6) NULLIF(MAJPROJ=' '))Loading data without enforcing referential constraints
The following LOAD statement specifies that data from the SYSRECAC input data set is to be loaded into table DSN8810.ACT. The INDDN option identifies the input data set.
ENFORCE NO indicates that the LOAD utility is not to enforce referential constraints and places the table in CHECK-pending status. Use this option if you are loading data into several tables that are related in such a way that the referential constraints cannot be checked until all tables are loaded. For example, a column in table A depends on a column in table B; a column in table B depends on a column in table C; and a column in table C depends on a column in table A.
The POSITION clauses define the starting positions of the fields in the input data set. The ending positions of the fields in the input data set are implicitly defined by the length specification of the data type (CHAR length). In this case, the characters in positions 1 through 3 are loaded into the ACTNO column, the characters in positions 5 through 10 are loaded into the ACTKWD column, and the characters in position 13 onward are loaded into the ACTDESC column. Because the ACTDESC column is of type VARCHAR, the input data needs to contain a 2-byte binary field that contains the length of the character field. This binary field begins at position 13.
//STEP1 EXEC DSNUPROC,UID='IUIQU2UB.LOAD',
// UTPROC='',
// SYSTEM='DSN'
//SYSRECAC DD DSN=IUIQU2UB.LOAD.DATA,DISP=SHR,VOL=SER=SCR03,
// UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SYSUT1 DD DSN=IUIQU2UB.LOAD.STEP1.SYSUT1,
// DISP=(MOD,DELETE,CATLG),
// UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SORTOUT DD DSN=IUIQU2UB.LOAD.STEP1.SORTOUT,
// DISP=(MOD,DELETE,CATLG),
// UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SYSIN DD *
LOAD DATA INDDN(SYSRECAC) RESUME YES
INTO TABLE DSN8C10.ACT
(ACTNO POSITION(1) INTEGER EXTERNAL(3),
ACTKWD POSITION(5) CHAR(6),
ACTDESC POSITION(13) VARCHAR)
ENFORCE NO
//*Loading data and using a parallel index build
The following LOAD statement specifies that data from the SYSREC input data set is to be loaded into table DSN8810.DEPT. Assume that 22,000 rows need to be loaded into table DSN8C10.DEPT, which has three indexes. In this example, the SORTKEYS option is used to improve performance by forcing a parallel index build. The SORTKEYS option specifies 66,000 as an estimate of the number keys to sort in parallel during the SORTBLD phase. (This estimate was computed by using the calculation that is described in Improving LOAD performance.) Because more than one index needs to be built, LOAD builds the indexes in parallel.
The SORTDEVT and SORTNUM keywords specify that the sort program is to dynamically allocate the required data sets. If sufficient virtual storage resources are available, one utility subtask pair is started to build each index. This example does not require UTPRINnn DD statements because it uses DSNUPROC to invoke utility processing, which includes a DD statement that allocates UTPRINT to SYSOUT.
The CONTINUEIF option indicates that, before loading the data, LOAD is to concatenate any input records that have a plus sign (+) in column 79 and a plus sign (+) in column 80 with the next record.
//SAMPJOB JOB …
//STEP1 EXEC DSNUPROC,UID='SAMPJOB.LOAD',UTPROC='',SYSTEM='DSN'
//SORTOUT DD DSN=SAMPJOB.LOAD.STEP1.SORTOUT,DISP=(MOD,DELETE,CATLG),
// UNIT=SYSDA,SPACE=(CYL,(10,20),,,ROUND)
//SYSUT1 DD DSN=SAMPJOB.LOAD.STEP1.SYSUT1,DISP=(MOD,DELETE,CATLG),
// UNIT=SYSDA,SPACE=(CYL,(10,20),,,ROUND)
//SYSERR DD DSN=SAMPJOB.LOAD.STEP1.SYSERR,DISP=(MOD,DELETE,CATLG),
// UNIT=SYSDA,SPACE=(2000,(20,20),,,ROUND)
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=2400)
//SYSMAP DD DSN=SAMPJOB.LOAD.STEP1.SYSMAP,DISP=(MOD,DELETE,CATLG),
// UNIT=SYSDA,SPACE=(2000,(20,20),,,ROUND),
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=2400)
//SYSREC DSN=SAMPJOB.TEMP.DATA,DISP=SHR,UNIT=SYSDA
//SYSIN DD *
LOAD DATA REPLACE INDDN SYSREC CONTINUEIF(79:80)='++'
SORTKEYS 66000 SORTDEVT SYSDA SORTNUM 3
INTO TABLE DSN8C10.DEPT
/*Creating inline copies using the REPLACE option
The following LOAD statement specifies that the utility is to load data from the SYSREC data set into the specified columns of table ADMF001.TB0S3902.
COPYDDN(COPYT1) indicates that LOAD is to create inline copies and write the primary image copy to the data set that is defined by the COPYT1 template. This template is defined in one of the preceding TEMPLATE control statements.
To create an inline copy, you can specify the REPLACE option, which indicates that any data in the table space is to be replaced.
CONTINUEIF(79:80)='++' indicates that, before loading the data,
LOAD is to concatenate any input records that have a plus sign (+) in column 79 and a plus sign (+)
in column 80 with the next record.
The ERRDDN(ERRDDN) and MAPDDN(MAP) options indicate that information about errors is to be written to the data sets that are defined by the ERRDDN and MAP templates. DISCARDDN(DISCARD) specifies that discarded records (those that violate referential constraints) are to be written to the data set that is defined by the DISCARD template. WORKDDN(UT1,OUT) specifies the temporary work files for sort input and output; LOAD is to use the data set that is defined by the UT1 template for sort input and the data set that is defined by the OUT template for sort output.
//STEP1 EXEC DSNUPROC,UID='JUOSU339.LOAD1',TIME=1440,
// UTPROC='',
// SYSTEM='SSTR'
//SYSREC DD DSN=CUST.FM.CINT135.DATA,DISP=SHR,VOL=SER=FORDMD,
// UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SYSIN DD *
TEMPLATE ERRDDN UNIT(SYSDA)
DSN(JUOSU339.T&TI..&ST..ERRDDN)
SPACE(50,10) TRK
TEMPLATE UT1 UNIT(SYSDA)
DSN(JUOSU339.T&TI..&ST..SYSUT1)
SPACE(50,10) TRK
TEMPLATE OUT UNIT(SYSDA)
DSN(JUOSU339.T&TI..&ST..SYSOUT)
SPACE(50,10) TRK
TEMPLATE MAP UNIT(SYSDA)
DSN(JUOSU339.T&TI..&ST..SYSMAP)
SPACE(50,10) TRK
TEMPLATE DISCARD UNIT(SYSDA)
DSN(JUOSU339.T&TI..&ST..DISCARD)
SPACE(50,10) TRK
TEMPLATE COPYT1
UNIT(SYSDA)
DSN(JUOSU339.COPY1.STEP1.&SN..COPY&LR.&PB.)
DISP(MOD,CATLG,CATLG)
SPACE(60,30) TRK
LOAD DATA INDDN SYSREC REPLACE
CONTINUEIF(79:80)='++'
COPYDDN(COPYT1)
ERRDDN(ERRDDN)
WORKDDN(UT1,OUT)
MAPDDN(MAP)
DISCARDDN(DISCARD)
INTO TABLE
ADMF001.TBOS3902
( ID_PARTITION POSITION(1) CHAR(1),
CD_PLANT POSITION(2) CHAR(5),
NO_PART_BASE POSITION(7) CHAR(9),
NO_PART_PREFIX POSITION(16) CHAR(7),
NO_PART_SUFFIX POSITION(23) CHAR(8),
NO_PART_CONTROL POSITION(31) CHAR(3),
DT_TRANS_EFFECTIVE POSITION(34) DATE EXTERNAL(10),
CD_INV_TRANSACTION POSITION(44) CHAR(3),
TS_PROCESS POSITION(47) TIMESTAMP EXTERNAL(26),
QT_INV_TRANSACTION POSITION(73) INTEGER,
CD_UNIT_MEAS_USAGE POSITION(77) CHAR(2),
CD_USER_ID POSITION(79) CHAR(7),
NO_DEPT POSITION(86) CHAR(4),
NO_WORK_CENTER POSITION(90) CHAR(6))
/* 
Creating inline copies while serializing at the partition level
To create an inline image copy while serializing at the partition level, issue LOAD with COPYDDN (or RECOVERYDDN) at the statement level while specifying resume-spec at the INTO TABLE PART level, as shown in the following example. The resulting inline copy includes pages only for partitions that are specified in the statement.
TEMPLATE SCPY DISP(NEW,CATLG,CATLG)
DSN MYHLQ.&DB..&TS..P&PA.
LOAD DATA SHRLEVEL NONE
FORMAT DELIMITED COPYDDN(SCPY)
INTO TABLE MY.TABLEA PART 1 RESUME YES
INDDN SREC1
(“ID” POSITION(*) INTEGER
,“NAME” POSITION(*) VARCHAR
)
INTO TABLE MY.TABLEA PART 2 RESUME YES
INDDN SREC2
(“ID” POSITION(*) INTEGER
,“NAME” POSITION(*) VARCHAR
)
/*
Collecting statistics
This example is similar to the previous example, except that the STATISTICS option and other related options have been added so that during the LOAD job, Db2 also gathers statistics for the table space. Gathering these statistics eliminates the need to run the RUNSTATS utility after completing the LOAD operation.
The TABLE, COLUMN, and INDEX options specify that information is to be gathered for columns QT_INV_TRANSACTION, NO_DEPT, NO_PART_PREFIX, DT_TRANS_EFFECTIVE and index ID0S3902 for table TB0S3902. SAMPLE 53 indicates that LOAD is to sample 53% of the rows when gathering statistics on non-leading-indexed columns of an index or non-indexed columns. For the index, statistics on all of the distinct values in all of the key column combinations are collected by default. FREQVAL NUMCOLS 4 COUNT 20 indicates that 20 frequent values are to be collected on the concatenation of the first four key columns.
REPORT YES indicates that the statistics are to be sent to SYSPRINT as output. UPDATE ALL and HISTORY ALL indicate that all collected statistics are to be updated in the catalog and catalog history tables.
//STEP1 EXEC DSNUPROC,UID='JUOSU339.LOAD1',TIME=1440,
// UTPROC='',
// SYSTEM='SSTR'
//SYSREC DD DSN=CUST.FM.CINT135.DATA,DISP=SHR,VOL=SER=FORDMD,
// UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SYSIN DD *
TEMPLATE ERRDDN UNIT(SYSDA)
DSN(JUOSU339.T&TI..&ST..ERRDDN)
SPACE(50,10) TRK
TEMPLATE UT1 UNIT(SYSDA)
DSN(JUOSU339.T&TI..&ST..SYSUT1)
SPACE(50,10) TRK
TEMPLATE OUT UNIT(SYSDA)
DSN(JUOSU339.T&TI..&ST..SYSOUT)
SPACE(50,10) TRK
TEMPLATE MAP UNIT(SYSDA)
DSN(JUOSU339.T&TI..&ST..SYSMAP)
SPACE(50,10) TRK
TEMPLATE DISCARD UNIT(SYSDA)
DSN(JUOSU339.T&TI..&ST..DISCARD)
SPACE(50,10) TRK
TEMPLATE COPYT1
UNIT(SYSDA)
DSN(JUOSU339.COPY1.STEP1.&SN..COPY&LR.&PB.)
DISP(MOD,CATLG,CATLG)
SPACE(60,30) TRK
LOAD DATA INDDN SYSREC REPLACE
CONTINUEIF(79:80)='++'
COPYDDN(COPYT1)
STATISTICS
TABLE (TBOS3902) SAMPLE 53
COLUMN (QT_INV_TRANSACTION,
NO_DEPT,
NO_PART_PREFIX,
DT_TRANS_EFFECTIVE)
INDEX (IDOS3902
FREQVAL NUMCOLS 4 COUNT 20)
REPORT YES UPDATE ALL HISTORY ALL
ERRDDN(ERRDDN)
WORKDDN(UT1,OUT)
MAPDDN(MAP)
DISCARDDN(DISCARD)
INTO TABLE
ADMF001.TBOS3902
( ID_PARTITION POSITION(1) CHAR(1),
CD_PLANT POSITION(2) CHAR(5),
NO_PART_BASE POSITION(7) CHAR(9),
NO_PART_PREFIX POSITION(16) CHAR(7),
NO_PART_SUFFIX POSITION(23) CHAR(8),
NO_PART_CONTROL POSITION(31) CHAR(3),
DT_TRANS_EFFECTIVE POSITION(34) DATE EXTERNAL(10),
CD_INV_TRANSACTION POSITION(44) CHAR(3),
TS_PROCESS POSITION(47) TIMESTAMP EXTERNAL(26),
QT_INV_TRANSACTION POSITION(73) INTEGER,
CD_UNIT_MEAS_USAGE POSITION(77) CHAR(2),
CD_USER_ID POSITION(79) CHAR(7),
NO_DEPT POSITION(86) CHAR(4),
NO_WORK_CENTER POSITION(90) CHAR(6))
/* Loading Unicode data
The following control statement specifies that Unicode data from the REC1 input data set is to be loaded into table ADMF001.TBMG0301. The UNICODE option specifies the type of input data. Only data that satisfies the condition that is specified in the WHEN clause is to be loaded. The CCSID option specifies the three coded character set identifiers for the input file: one for SBCS data, one for mixed data, and one for DBCS data. LOG YES indicates that logging is to occur during the LOAD job.
LOAD DATA INDDN REC1 LOG YES REPLACE
UNICODE CCSID(00367,01208,01200)
INTO TABLE "ADMF001 "."TBMG0301"
WHEN(00004:00005 = X'0003') Loading data from multiple input data sets by using partition parallelism
The following LOAD statement contains a series of INTO TABLE statements that specify which data is to be loaded into which partitions of table DBA01.TBLX3303. For each INTO TABLE statement:
- Data is to be loaded into the partition that is identified by the PART option. For example, the first INTO TABLE statement specifies that data is to be loaded into the first partition of table DBA01.TBLX3303.
- Data is to be loaded from the data set that is identified by the INDDN option. For example, the data from the PART1 data set is to be loaded into the first partition.
- Any discarded rows are to be written to the data set that is specified by the DISCARDDN option. For example, rows that are discarded during the loading of data from the PART1 data set are written to the DISC1 data set.
- The data is loaded into the specified columns (EMPNO, LASTNAME, and SALARY).
LOAD uses partition parallelism to load the data into these partitions.
The TEMPLATE utility control statement defines the data set naming convention for the data set that is to be dynamically allocated during the following LOAD job. The name of the template is ERR3. The ERRDDN option in the LOAD statement specifies that any errors are to be written to the data set that is defined by this ERR3 template.
TEMPLATE ERR3
DSN &UT..&JO..&ST..ERR3&MO.&DAY.
UNIT SYSDA DISP(NEW,CATLG,CATLG)
LOAD DATA
REPLACE
ERRDDN ERR3
INTO TABLE DBA01.TBLX3303
PART 1
INDDN PART1
DISCARDDN DISC1
(EMPNO POSITION(1) CHAR(6),
LASTNAME POSITION(8) VARCHAR(15),
SALARY POSITION(25) DECIMAL(9,2))
.
.
.
INTO TABLE DBA01.TBLX3303
PART 5
INDDN PART5
DISCARDDN DISC5
(EMPNO POSITION(1) CHAR(6),
LASTNAME POSITION(8) VARCHAR(15),
SALARY POSITION(25) DECIMAL(9,2))
/* Loading data from another table in the same system by using a declared cursor
The following LOAD control statement specifies that all rows that are identified by cursor C1 are to be loaded into table MYEMP. The INCURSOR option is used to specify cursor C1, which is defined in the EXEC SQL utility control statement. Cursor C1 points to the rows that are returned by executing the statement SELECT * FROM DSN8810.EMP. In this example, the column names in table DSN8810.EMP are the same as the column names in table MYEMP. Note that the cursor cannot be defined on the same table into which Db2 is to load the data.
EXEC SQL
DECLARE C1 CURSOR FOR SELECT * FROM DSN8810.EMP
ENDEXEC
LOAD DATA
INCURSOR(C1)
REPLACE
INTO TABLE MYEMP
STATISTICSLoading data partitions in parallel from a remote site by using a declared cursor
The following LOAD statement specifies that for each specified partition of table MYEMPP, the rows that are identified by the specified cursor are to be loaded. In each INTO TABLE statement, the PART option specifies the partition number, and the INCURSOR option specifies the cursor. For example, the rows that are identified by cursor C1 are to be loaded into the first partition. The data for each partition is loaded in parallel.
Each cursor is defined in a separate EXEC SQL utility control statement and points to the rows that are returned by executing the specified SELECT statement. These SELECT statement are being executed on a table at a remote server, so the three-part name is used to identify the table. In this example, the column names in table CHICAGO.DSN8810.EMP are the same as the column names in table MYEMPP.
The four partitions being loaded each contain a different number of records. To improve the sizing of the sort work data sets that the LOAD utility requires, the number of records being loaded into each partition is specified on the NUMRECS keyword for each table.
EXEC SQL
DECLARE C1 CURSOR FOR SELECT * FROM CHICAGO.DSN8810.EMP
WHERE EMPNO <= '099999'
ENDEXEC
EXEC SQL
DECLARE C2 CURSOR FOR SELECT * FROM CHICAGO.DSN8810.EMP
WHERE EMPNO > '099999' AND EMPNO <= '199999'
ENDEXEC
EXEC SQL
DECLARE C3 CURSOR FOR SELECT * FROM CHICAGO.DSN8810.EMP
WHERE EMPNO > '199999' AND EMPNO <= '299999'
ENDEXEC
EXEC SQL
DECLARE C4 CURSOR FOR SELECT * FROM CHICAGO.DSN8810.EMP
WHERE EMPNO > '299999' AND EMPNO <= '999999'
ENDEXEC
LOAD DATA
INTO TABLE MYEMPP PART 1 REPLACE INCURSOR(C1) NUMRECS 10000
INTO TABLE MYEMPP PART 2 REPLACE INCURSOR(C2) NUMRECS 50000
INTO TABLE MYEMPP PART 3 REPLACE INCURSOR(C3) NUMRECS 100000
INTO TABLE MYEMPP PART 4 REPLACE INCURSOR(C4) NUMRECS 50000Loading LOB data from a file
The following LOAD statement specifies that data from 000130DSN!10.SDSNIVPD(DSN8R130) is to be loaded into the MY_EMP_PHOTO_RESUME table. The characters in positions 1 through 6 are loaded into the EMPNO column, and the characters starting from position 7 are to be loaded into the RESUME column. CLOBF indicates that the characters in position 7 are the name of a file from which a CLOB is to be loaded.
REPLACE indicates that the new data will replace any existing data. Although no logging is to be done, as indicated by the LOG NO option, the table space is not to be set in CHECK-pending state, because NOCOPYPEND is specified.
SORTKEYS 1 indicates that one index key is to be sorted.
//*****************************************************************
//* LOAD LOB from file
//*****************************************************************
//LOADIT EXEC DSNUPROC,UID='LOADIT',TIME=1440,
// UTPROC='',
// SYSTEM='DSN'
//SYSREC DD*
000130DSN!10.SDSNIVPD(DSN8R130)
//SYSUT1 DD DSN=SYSADM.LOAD.SYSUT1,DISP=(MOD,DELETE,CATLG),
// UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SORTOUT DD DSN=SYSADM.LOAD.SORTOUT,DISP=(MOD,DELETE,CATLG),
// UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SYSIN DD *
LOAD DATA
REPLACE LOG NO NOCOPYPEND
SORTKEYS 1
INTO TABLE MY_EMP_PHOTO_RESUME
(EMPNO POSITION(1:6) CHAR(6),
RESUME POSITION(7:31) CHAR CLOBF)Loading with parallel subtasks
The following LOAD statement specifies that the utility is to use multiple parallel subtasks, as indicated by the PARALLEL keyword. Because no value is specified with the PARALLEL keyword, Db2 determines the optimal degree of parallelism. This use of parallelism can potentially reduce the elapsed time that is required for loading large amounts of data.LOAD DATA
PARALLEL
RESUME YES
SHRLEVEL NONE
INDDN INPUT1
EBCDIC
CONTINUEIF(80:80)='-'
INTO TABLE SCTX1300.TB_HISTORY_PART
(HISTORY_DAILY POSITION(1:11) INT EXTERNAL,
HISTORY_ROWNUM POSITION(13:23) INT EXTERNAL,
HISTORY_CUSTOMER_ID POSITION(25:35) INT EXTERNAL,
HISTORY_CUSTOMER_ACCOUNT_ID POSITION(37:50) DECIMAL EXTERNAL,
HISTORY_CUSTOMER_DISTRICT_ID POSITION(52:53) CHAR,
HISTORY_CUSTOMER_WAREHOUSE_ID POSITION(55:64) CHAR,
HISTORY_DISTRICT_ID POSITION(66:67) CHAR,
HISTORY_TRANSACTION_ID POSITION(69:70) CHAR,
HISTORY_WAREHOUSE_ID POSITION(72:81) CHAR,
HISTORY_DATE POSITION(83:108) TIMESTAMP EXTERNAL,
HISTORY_AMOUNT POSITION(110:126) DECIMAL EXTERNAL,
HISTORY_STATUS POSITION(128:144) VARCHAR,
HISTORY_DATA POSITION(3874:3899) VARCHAR) Preserving timestamp values when loading row change timestamp columns that are defined as GENERATED ALWAYS
Suppose that you unloaded data from a table with a row change timestamp column and subsequently want to reload the data into a table that is defined as follows:
CREATE TABLE MYDB.MYTB
(TYPE CHAR(1) NOT NULL,
CHANGE TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL GENERATED ALWAYS
FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
)
IN MYDB.MYTS;
Notice that the row change timestamp column is defined as GENERATED ALWAYS. If you want to preserve the timestamp values in the unload data set when you load the data into this table, specify the OVERRIDE(ROWCHANGE) option as shown in the following example LOAD statement:
LOAD DATA INDDN SYSREC OVERRIDE(ROWCHANGE)
INTO TABLE
"MYDB"."MYTB"
NUMRECS 10
("TYPE"
POSITION(00001:00001) CHAR(1)
"CHANGE"
POSITION(00004:00029) TIMESTAMP EXTERNAL(26)
)
Loading multiple input data sets
The following LOAD job loads data from three input data sets that are defined by templates TSREC1, TSREC2, and TSREC3. These data sets are dynamically concatenated, and then the data is loaded into the table.
TEMPLATE SU1 DISP(MOD,CATLG,CATLG) DSN 'E44753.&DB..&SN..&UQ..SU1'
TEMPLATE CPY DISP(MOD,CATLG,CATLG) DSN 'E44753.&DB..&SN..&UQ..SCO'
TEMPLATE SO1 DISP(MOD,CATLG,CATLG) DSN 'E44753.&DB..&SN..&UQ..SO1'
TEMPLATE ERR DISP(MOD,CATLG,CATLG) DSN 'E44753.&DB..&SN..&UQ..ERR'
TEMPLATE MAP DISP(MOD,CATLG,CATLG) DSN 'E44753.&DB..&SN..&UQ..MAP'
TEMPLATE DSC DISP(MOD,CATLG,CATLG) DSN 'E44753.&DB..&SN..&UQ..SDI'
TEMPLATE TSREC1 DSN('E44753.DB1.TS1.P00001.SYSREC')
TEMPLATE TSREC2 DSN(‘E44753.DB1.TS1.P00002.SYSREC')
TEMPLATE TSREC3 DSN('E44753.DB1.TS1.P00003.SYSREC')
LOAD DATA INDDN (TSREC1,TSREC2,TSREC3)
RESUME YES LOG YES
SORTDEVT SYSDA SORTNUM 99
WORKDDN(SU1,SO1) ERRDDN(ERR) DISCARDDN(DSC)
INTO TABLE TI01AN.OIGT0055
WHEN(00001:00002) = X'0003'
NUMRECS 8534 Loading multiple input data sets into partitions
The following LOAD job loads data from several input data sets into each partition. For example, the input data sets that are defined by templates TSREC1 and TSREC4 are loaded into partition 1.
TEMPLATE SU1 DISP(MOD,CATLG,CATLG) DSN 'E44753.&DB..&SN..&UQ..SU1'
TEMPLATE CPY DISP(MOD,CATLG,CATLG) DSN 'E44753.&DB..&SN..&UQ..SCO'
TEMPLATE SO1 DISP(MOD,CATLG,CATLG) DSN 'E44753.&DB..&SN..&UQ..SO1'
TEMPLATE ERR DISP(MOD,CATLG,CATLG) DSN 'E44753.&DB..&SN..&UQ..ERR'
TEMPLATE MAP DISP(MOD,CATLG,CATLG) DSN 'E44753.&DB..&SN..&UQ..MAP'
TEMPLATE DSC1 DISP(MOD,CATLG,CATLG) DSN 'E44753.&DB..&SN..&UQ..SDI1'
TEMPLATE DSC2 DISP(MOD,CATLG,CATLG) DSN 'E44753.&DB..&SN..&UQ..SDI2'
TEMPLATE DSC3 DISP(MOD,CATLG,CATLG) DSN 'E44753.&DB..&SN..&UQ..SDI3'
TEMPLATE TSREC1 DSN('E44753.DBE44753.TSE44753.P00001.SYSREC')
TEMPLATE TSREC2 DSN('E44753.DBE44753.TSE44753.P00002.SYSREC')
TEMPLATE TSREC3 DSN('E44753.DBE44753.TSE44753.P00003.SYSREC')
TEMPLATE TSREC4 DSN('E44753.DBE44753.T2E44753.P00001.SYSREC')
TEMPLATE TSREC5 DSN('E44753.DBE44753.T2E44753.P00002.SYSREC')
TEMPLATE TSREC6 DSN('E44753.DBE44753.T2E44753.P00003.SYSREC')
LOAD DATA
RESUME YES LOG YES
SORTDEVT SYSDA SORTNUM 99
WORKDDN(SU1,SO1) ERRDDN(ERR)
INTO TABLE TBE44753 PART 1 INDDN (TSREC1,TSREC4) DISCARDDN(DSC1)
NUMRECS 600
( C1 POSITION(00004:00007) INTEGER NULLIF(00003)=X'FF')
INTO TABLE TBE44753 PART 2 INDDN (TSREC2,TSREC5) DISCARDDN(DSC2)
NUMRECS 600
( C1 POSITION(00004:00007) INTEGER NULLIF(00003)=X'FF')
INTO TABLE TBE44753 PART 3 INDDN (TSREC3,TSREC6) DISCARDDN(DSC3)
NUMRECS 600
( C1 POSITION(00004:00007) INTEGER NULLIF(00003)=X'FF')
Defining DEFINE NO auxiliary objects at the start of utility execution
In the following LOAD statement, DEFINEAUX YES specifies that any target auxiliary objects with the DEFINE NO attribute are to be defined at the start of LOAD execution, regardless of whether these objects are populated by LOAD.
LOAD INDDN SYSREC RESUME NO REPLACE DEFINEAUX YES
INTO TABLE MYEMPLoading default values for columns
In the following LOAD statement, the INTO TABLE clause contains two DEFAULTIF specifications to indicate when the default column value is to be loaded. For column C1, the default value is to be loaded if the input value is blank. For column C2, the default value is to be loaded if the input value is not blank. For column C3®, the default value is to be loaded if the value in position 5 is not blank.
LOAD DATA INDDN SYSREC
FORMAT DELIMITED COLDEL ',' CHARDEL '"' DECPT '.'
EBCDIC CCSID(00037,00000,00000)
INTO TABLE SYSADM.TB1
IGNOREFIELDS YES
( C1 POSITION(*) DEFAULTIF(C1= '')
, PARTNO POSITION(*)
, C2 POSITION(*) DEFAULTIF(C2<>'')
, C3 POSITION(*) DEFAULTIF((5:5) <> '')
)Similarly, the DEFAULTIF specifications in the following example indicate that if a conversion error occurs when loading a value into columns C1 or C2, the default column value is loaded instead.
LOAD DATA INDDN SYSREC
FORMAT DELIMITED COLDEL ',' CHARDEL '"' DECPT '.'
EBCDIC CCSID(00037,00000,00000)
INTO TABLE SYSADM.TB1
IGNOREFIELDS YES
( C1 POSITION(*) DEFAULTIF(CONV_ERROR)
,C2 POSITION(*) DEFAULTIF(CONV_ERROR)
)