DB2 LOAD field specs, discards and special data

LOAD WHEN — which records belong in which table

A LOAD job can feed several tables from one SYSREC. Each INTO TABLE clause can have a WHEN field-selection criterion. IBM samples load EMP versus DEPT by testing a record type in a positional field. Records that do not satisfy WHEN for that table are not loaded there. If you requested discards, they can be written to the discard data set.

1
2
3
4
5
6
7
8
9
LOAD DATA INDDN SYSREC
  INTO TABLE HR.EMPLOYEE
    WHEN (1:1) = 'E'
    (EMPNO POSITION(2:7) CHAR,
     LASTNAME POSITION(8:22) CHAR)
  INTO TABLE HR.DEPARTMENT
    WHEN (1:1) = 'D'
    (DEPTNO POSITION(2:4) CHAR,
     DEPTNAME POSITION(5:40) CHAR)

Positional WHEN uses start:end column numbers; column 1 is the first byte of the assembled record. Character, hex (X'...'), and graphic constants are the usual comparisons. WHEN SQL/DS='table-name' applies to SQL/DS formatted input. If you omit WHEN, LOAD attempts every record for that INTO TABLE.

Do not put LOB or XML fields in a WHEN criterion when you use FORMAT SPANNED YES. IBM forbids referencing those columns in the field-selection criterion because they sit in the spanned tail of the record.

Field specifications

A field specification names a column (or an IGNOREFIELDS dummy), gives POSITION(start:end) or POSITION(*), and a data type. If you omit POSITION, the field starts one column after the previous field. * means that next-byte start; *+n skips n bytes.

FORMAT UNLOAD, FORMAT INTERNAL, and FORMAT SQL/DS do not allow field specifications—the format already defines every byte. FORMAT DELIMITED uses field names and types without classic POSITION ranges; fields are separated by COLDEL.

If you specify any field for a table, IBM requires a specification for every column that has no default. A NOT NULL column with no default and no field spec terminates the job. Columns you omit receive their default (including identity GENERATED BY DEFAULT if you leave them out).

1
2
3
4
5
6
INTO TABLE HR.EMPLOYEE
  (EMPNO    POSITION(1:6)   CHAR,
   FIRSTNME POSITION(7:18)  CHAR,
   LASTNAME POSITION(19:33) CHAR,
   SALARY   POSITION(34:42) DECIMAL EXTERNAL(9),
   HIREDATE POSITION(43:52) DATE EXTERNAL)

NULLIF and DEFAULTIF

Both attach to a field and test a condition on the input (positional or field-name).

• NULLIF — store NULL. The column must allow nulls. Typical tests: blank, a sentinel like X'FF', or another field’s value
• DEFAULTIF — store the column default when the condition is true

1
2
3
4
5
6
7
8
9
10
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'))

That IBM sample also shows CONTINUEIF: if column 80 is X, concatenate the next physical record before parsing fields. NULLIF and DEFAULTIF are not valid with FORMAT UNLOAD or FORMAT INTERNAL.

Generated columns and identity columns

GENERATED ALWAYS columns are normally computed by Db2. Reloading an UNLOAD of a temporal table or an identity-keyed table needs an explicit override:

• OVERRIDE(IDENTITY) — reload GENERATED ALWAYS identity (also required in some PART loads when identity is in the partitioning index). Alternate keyword IDENTITYOVERRIDE is deprecated
• OVERRIDE(SYSTEMPERIOD) — row-begin / row-end used for a system period. If you list field specs, include both columns; NULLIF and DEFAULTIF are not allowed on them
• OVERRIDE(TRANSID) — transaction-start-ID GENERATED ALWAYS
• OVERRIDE(NONDETERMINISTIC) — expression-generated ALWAYS columns
• OVERRIDE(ROWCHANGE) — GENERATED ALWAYS row-change timestamp

Identity and row-change timestamp columns may appear in the field list only when they are GENERATED BY DEFAULT, unless you use the matching OVERRIDE. UPDMAXASSIGNEDVAL YES (IBM option) can refresh the sequence high-water mark after you reload identity values so later INSERT does not collide.

LOAD XML and LOAD LOBs

XML columns use field type XML, optionally BINARYXML, with CCSID rules (1208 for character XML is common). LOB columns can be:

• In-record — BLOB/CLOB/DBCLOB with a 4-byte length prefix in binary layouts
• File reference — BLOBF, CLOBF, DBCLOBF: the field contains a file name; Db2 reads the LOB from that file
• Spanned — FORMAT SPANNED YES (or VBS input): all LOB and XML fields at the end of the record, typically POSITION(*)

DEFINEAUX YES asks LOAD to define auxiliary LOB/XML objects up front. COPYDDN during LOAD copies the base table space only—not LOB, XML, or index spaces. Plan separate COPY jobs for those. Inline statistics likewise skip LOB/XML spaces.

Discard processing and discard data sets

IBM writes discards in the DISCARD phase. Records can be flagged earlier (RELOAD conversion errors, INDEXVAL unique-key errors, ENFORCE RI/check failures) and only then copied from SYSREC to SYSDISC. Therefore SYSDISC must be a sequential BSAM data set with the same RECFM, LRECL, and BLKSIZE as SYSREC. If you omit DISCARDDN, discards are saved only when a SYSDISC DD is present in the JCL (and partition-level INTO TABLE PART DISCARDDN has its own rules).

DISCARDS integer (0 through 2147483647) is a kill switch. DISCARDS 0 means unlimited. If the maximum is reached, LOAD abends and the discard data set is empty—you do not get a partial discard file to inspect. Raise the limit and restart, or TERM UTILITY. BACKOUT YES (with a non-zero DISCARDS cap) can delete rows already loaded in this execution if a discard would leave the object unavailable.

1
2
3
4
5
6
7
LOAD DATA INDDN SYSREC
     DISCARDDN SYSDISC DISCARDS 50
     ERRDDN SYSERR MAPDDN SYSMAP
  INTO TABLE HR.EMPLOYEE
    WHEN (1:3) = 'EMP'
    (EMPNO POSITION(4:9) CHAR NULLIF(EMPNO=X'000000000000'),
     LASTNAME POSITION(10:24) CHAR)

Explain It Like I'm Five

Imagine pouring LEGO bricks from a big bin into labeled boxes. WHEN is the rule “only red bricks go in the car box.” Field specs are the sorting tray that says “this slot is the wheel, that slot is the door.” NULLIF is “if this sticker is blank, leave the slot empty.” DEFAULTIF is “if you see GG, put the factory default brick there.” Identity and generated columns are bricks the factory usually stamps for you—OVERRIDE lets you keep the old stamp from a previous build. LOBs are huge posters; you either roll them at the end of the box or write “see folder X.” Discards are the reject bin. If too many bricks are wrong, the factory stops and empties the reject bin so you must fix the limit and try again.

Exercises

1. Write WHEN clauses that split a file with record type E versus D into two INTO TABLE blocks.
2. Add NULLIF so a CHAR hire-date of all blanks becomes NULL.
3. Explain why FORMAT INTERNAL cannot use your POSITION list.
4. List three OVERRIDE keywords and which GENERATED ALWAYS columns they cover.
5. If DISCARDS 10 is exceeded, what do you see in SYSDISC, and what are your next operational choices?