LOAD data into DB2 for z/OS

What LOAD does

LOAD operates on a table space. It reads input records, converts them into Db2 rows, writes pages, and maintains indexes defined on the target tables. You choose whether to replace existing data or resume by appending. Optional phases enforce referential constraints, build indexes in parallel, collect inline statistics, and write discarded records for cleanup.

Compared with INSERT, LOAD is built for volume: sorted input, bulk index build, and utility restart logic. Compared with UNLOAD, LOAD is the inbound direction—file to table—while UNLOAD extracts table to file.

Prerequisites

• Target database, table space, and table already created (or create them first)
• LOAD privilege (and related authorities your shop requires) on the objects involved
• Input data set allocated and populated; record format understood (fixed, variable, delimited as coded)
• Work data sets for sort, error, and discard as required by your control statement and site templates
• Clear decision: REPLACE versus RESUME YES, and whether logging and image copies are required after the job
• Knowledge of whether the table space holds one table or many—REPLACE is table-space scoped when specified at the LOAD level

Always practice on a test table space. A mistaken REPLACE on a shared multi-table space can empty tables you did not intend to touch.

Steps

1. Prepare the input file

Align columns in the file with the field list in the LOAD statement. Pad character fields, watch EBCDIC encoding, and confirm numeric and date layouts. A single off-by-one in positions produces conversion errors and discards.

2. Write the LOAD control statement

A minimal append load into a training table looks like this. Adjust names, positions, and DDs to match your data.

1
2
3
4
5
6
7
8
LOAD DATA INDDN SYSREC
     RESUME YES
     LOG YES
INTO TABLE TRAINING.EMPLOYEE
     (EMPNO    POSITION(1)  CHAR(6),
      FIRSTNME POSITION(7)  CHAR(12),
      LASTNAME POSITION(19) CHAR(15),
      DEPTNO   POSITION(34) CHAR(3))

For a full refresh of a dedicated single-table table space, beginners often use REPLACE:

1
2
3
4
5
6
7
8
LOAD DATA INDDN SYSREC
     REPLACE
     LOG YES
INTO TABLE TRAINING.EMPLOYEE
     (EMPNO    POSITION(1)  CHAR(6),
      FIRSTNME POSITION(7)  CHAR(12),
      LASTNAME POSITION(19) CHAR(15),
      DEPTNO   POSITION(34) CHAR(3))

Important REPLACE meanings:

• REPLACE before or at LOAD scope — reset the table space, then load
• PART n REPLACE on INTO TABLE — replace only that partition when coded correctly for partition-level replace
• RESUME YES — keep existing rows; append input
• RESUME NO — require an empty table space (classic empty-before-load pattern)

3. Build DSNUTILB (or template) JCL

Online utilities typically run under DSNUTILB with a SYSIN control statement. Your shop may generate JCL from DB2I Utilities, Automation Tool, or a standard PROC. A teaching skeleton:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
//LOADEMP  JOB (ACCT),'DB2 LOAD',CLASS=A,MSGCLASS=X,
//              NOTIFY=&SYSUID
//JOBLIB   DD DISP=SHR,DSN=DSN.V13R1M0.SDSNEXIT
//         DD DISP=SHR,DSN=DSN.V13R1M0.SDSNLOAD
//LOAD     EXEC PGM=DSNUTILB,REGION=0M,
//             PARM='DB2T,LOADEMP'
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC   DD DISP=SHR,DSN=TRAINING.EMPLOYEE.INPUT
//SYSDISC  DD DSN=TRAINING.EMPLOYEE.DISC,
//             DISP=(NEW,CATLG,DELETE),
//             UNIT=SYSDA,SPACE=(CYL,(5,5),RLSE)
//SYSERR   DD UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(NEW,DELETE)
//SYSMAP   DD UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(NEW,DELETE)
//SORTOUT  DD UNIT=SYSDA,SPACE=(CYL,(10,10)),DISP=(NEW,DELETE)
//* add SORTWKnn per site standards when indexes need sort
//SYSIN    DD *
  LOAD DATA INDDN SYSREC
       RESUME YES
       LOG YES
       ENFORCE CONSTRAINTS
  INTO TABLE TRAINING.EMPLOYEE
       (EMPNO    POSITION(1)  CHAR(6),
        FIRSTNME POSITION(7)  CHAR(12),
        LASTNAME POSITION(19) CHAR(15),
        DEPTNO   POSITION(34) CHAR(3))
/*

The PARM supplies the SSID and a utility ID. Utility IDs must be unique while a utility is active; reuse after successful termination or after you terminate a failed utility per site procedure.

4. Decide logging and copy strategy

LOG YES (default in many examples) logs changes for recovery.LOG NO can speed large loads but often leaves COPY-pending so you must take an image copy before the object is considered recoverable under normal rules. Some options such as NOCOPYPEND exist for specific scenarios—use them only when your recovery procedures explicitly allow it.

5. Submit and monitor

Watch SYSPRINT for phase messages (RELOAD, BUILD, ENFORCE, DISCARD, REPORT). Non-zero return codes demand a full read of the messages, not only the final RC.

Verify results

• Utility return code and absence of unexpected pending states
• SYSPRINT counts: records read, loaded, discarded
• SQL row counts: SELECT COUNT(*) FROM TRAINING.EMPLOYEE versus expected input size minus discards
• Spot-check key rows with SELECT against known input keys
• -DISPLAY DATABASE(...) or catalog queries for COPY/CHECK pending if you used LOG NO or constraint deferral options
• Index availability: queries that use indexes should not hit rebuild-pending surprises

1
2
3
4
5
6
7
SELECT COUNT(*) AS ROW_CNT
  FROM TRAINING.EMPLOYEE;
 
SELECT EMPNO, LASTNAME, DEPTNO
  FROM TRAINING.EMPLOYEE
 WHERE EMPNO IN ('000010','000020','000050')
 ORDER BY EMPNO;

If discards occurred, browse SYSDISC, fix the data or the field map, and LOAD again with RESUME YES for the repaired subset—or correct the full file and REPLACE when that is the approved refresh pattern.

Common errors

Conversion and length errors

Wrong POSITION lengths, invalid packed decimals, or bad date strings send rows to discard or fail the utility. Dump a few input records in hex and compare to the field list.

Duplicate keys / unique index violations

Input contains keys already present (RESUME) or duplicates within the file. Clean the file, use REPLACE when a full refresh is intended, or adjust keys.

Referential constraint violations

Child rows reference missing parents. Load parents first, or stage data and run CHECK DATA when your process defers enforcement. With ENFORCE CONSTRAINTS, violators go to discard.

COPY-pending after LOG NO

Applications may be restricted until you COPY the table space. Schedule image copy immediately after the load window.

Utility ID conflict / stopped utility

A previous LOAD failed and left a utility record. Display utilities, then TERM or restart according to IBM and site rules—never guess on production.

Wrong REPLACE scope

REPLACE emptied more than you expected because multiple tables share the table space. Prefer one table per table space for refreshable tables, or use careful partition-level options.

Explain It Like I'm Five

Imagine a toy box (the table). INSERT is dropping toys in one at a time. LOAD is pouring a big bag of toys into the box. You can either dump out the old toys first (REPLACE) or pour the new toys on top of the old ones (RESUME). If some toys are broken (bad data), LOAD can put them in a reject pile (SYSDISC) so you can fix them later. When you pour very fast without writing everything in the diary (LOG NO), a grown-up may ask you to take a photo of the box (image copy) so you can rebuild it if something goes wrong.

Exercises

1. Create a one-table test table space and LOAD RESUME YES from a ten-row file. Confirm COUNT(*) is 10.
2. Run the same LOAD again with RESUME YES and explain why the row count doubled or why unique indexes rejected duplicates.
3. Introduce one bad date in the file, allocate SYSDISC, and show that nine rows loaded and one discarded.
4. Perform LOAD REPLACE on the test table and verify the final count matches only the latest file.
5. Document your site's required follow-up: COPY, RUNSTATS, or both after production loads.