DB2 LOAD utility basics: REPLACE, RESUME, INTO, PART, SHRLEVEL

How LOAD thinks about a table space

LOAD is a table-space utility. Even when INTO TABLE names one table, Db2 still needs authority for every table in that space. For nonpartitioned spaces, other tables in the space are unavailable during a drained load (SHRLEVEL NONE or REFERENCE). Partitioned spaces make the loaded partitions unavailable the same way, except SHRLEVEL CHANGE. CREATE/DROP/ALTER on another space in the same database can contend. That is why shops put one important table in its own partitioned space before they automate LOAD REPLACE.

Input is usually a BSAM sequential file (INDDN, default SYSREC), optionally FORMAT DELIMITED, or INCURSOR after EXEC SQL DECLARE CURSOR (cross loader). Field specs, WHEN, NULLIF, discards, XML, and LOBs are the following LOAD page. Encrypted input data sets are accepted; LOAD REPLACE can also participate in encrypting Db2-managed data sets with DFSMS.

1
2
3
LOAD DATA INDDN SYSREC
  REPLACE
  INTO TABLE HR.EMPLOYEE

LOAD INTO TABLE

INTO TABLE identifies which table receives records. You can have more than one INTO TABLE in one LOAD when the input contains more than one record type (WHEN clauses pick the type). DATA after LOAD is optional noise in many samples. Position-based or delimited fields follow in parentheses; if you omit field specs, LOAD expects an internal format (often from UNLOAD). Always name the table you mean — unqualified names use the utility auth ID’s qualifier, which is a classic “loaded the wrong HR.EMPLOYEE” incident.

LOAD REPLACE

Table-space REPLACE deletes and redefines STOGROUP data sets unless you also specify REUSE (keep the data set, reset it). You must hold LOAD on all tables in the space or the utility errors. REPLACE cannot run on a table space in the window after a point-in-time RECOVER that still needs REORG to materialize pending definition changes. Converting page format on a base with LOAD REPLACE does not convert associated versioned XML spaces. Clones: you cannot CONVERT page format with a clone in place.

Do not combine statement-level REPLACE with PART n REPLACE. Pick one grain.

LOAD RESUME, RESUME YES, RESUME NO

On nonsegmented spaces, RESUME YES does not reuse holes from deleted or dropped rows; REPLACE is more efficient there if you meant to start clean. If you specify RESUME at table-space level, you cannot repeat RESUME on a PART clause. PART integer RESUME is the partition-level twin: same YES/NO idea, but only that partition, and better concurrency with other partition jobs.

1
2
3
4
5
6
LOAD DATA INDDN SYSREC
  RESUME YES
  INTO TABLE HR.PAY_TRAN
 
LOAD DATA INDDN SYSREC
  INTO TABLE HR.PAY_TRAN PART 4 RESUME YES

LOAD PART and REPLACE PART

PART integer on INTO TABLE targets a physical partition. REPLACE after PART replaces only that partition’s data; REPLACE before PART (statement level) would have already meant the whole space — illegal together. Loading one partition still requires the partitioning key in the input to belong to that partition or the row is discarded or the job fails depending on options.

A common pattern: nightly LOAD DATA INTO TABLE T PART n REPLACE for the partition that just closed, leaving other partitions online as much as drains allow. Statement-level LOAD REPLACE PART without the INTO PART clause is not the right reading — always put PART on INTO TABLE.

1
2
LOAD DATA INDDN SYSREC
  INTO TABLE HR.PAY_TRAN PART 7 REPLACE

LOAD SHRLEVEL

SHRLEVEL NONE is the batch window load: drain, RELOAD, build indexes, enforce constraints, done. SHRLEVEL REFERENCE REPLACE is the “readers stay, then a short switch” pattern using a shadow data set; if the drain for SWITCH fails, RC 8. SHRLEVEL CHANGE is online insert-style load: no SYSCOPY row, no inline copy keywords, identity CACHE helps, XML lock escalation is disabled, and DataPropagator can see log records if DATA CAPTURE CHANGES is on. After a large CHANGE load, IBM recommends RUNSTATS SHRLEVEL CHANGE UPDATE SPACE and a conditional REORG because clustering suffers.

BACKOUT YES (with a non-zero DISCARDS limit) rolls back the rows this LOAD inserted if discards exceed the limit, leaving the object available — only with RESUME YES SHRLEVEL NONE, not with REPLACE or INCURSOR.

REUSE, KEEPDICTIONARY, identity, and what LOAD will not do

REUSE keeps the existing STOGROUP-defined data sets instead of deleting and redefining them on REPLACE. That can save VCAT catalog work and preserve SMS attributes you already like. If the data set is too small for the new data, LOAD still needs to extend it; REUSE is not a size guarantee. KEEPDICTIONARY keeps a compression dictionary on REPLACE instead of building a new one. That is faster when the new data looks like the old data; it is the wrong choice after a wholesale format change because compression ratio collapses. Without KEEPDICTIONARY, LOAD builds a dictionary from the incoming rows when the space is defined COMPRESS YES.

Identity columns and row change timestamps follow LOAD rules that differ from INSERT: you can supply values (IDENTITYOVERRIDE / OVERRIDE) or let Db2 generate them, depending on GENERATED ALWAYS versus BY DEFAULT and the LOAD keywords. Getting this wrong either rejects the input or duplicates keys you thought were unique. ROWID columns similarly need the right override if you are reloading a table that other tables reference by ROWID.

Clone tables, some LOB and XML table spaces, and certain pending ALTER states block SHRLEVEL REFERENCE. You cannot LOAD a view. Temporary tables are not LOAD targets. Archive-enabled and temporal tables have extra rules: you load the base the way the temporal tutorials describe, not by pointing SYSREC at the history table and hoping. Encrypted input is fine; LOAD does not decrypt your business data for you — the file must already be in the format field specs expect.

Triggers and referential integrity are the other behavioural gap versus INSERT. Drained LOAD (NONE/REFERENCE) does not fire insert triggers the way SQL does; SHRLEVEL CHANGE does, which is why shops that replicate with triggers or DataPropagator sometimes accept the slower online load. Informational RI is never enforced. Real RI is ENFORCE CONSTRAINTS on NONE, forced ENFORCE NO on REFERENCE, and insert-time checking on CHANGE. Plan CHECK DATA when you skip ENFORCE.

Phases you will DISPLAY

• RELOAD — read input, insert pages, check check-constraints per row, write index/FK sort records; inline copies if COPYDDN
• SORT / BUILD or SORTBLD — build indexes; SORTBLD is parallel index build
• INDEXVAL — unique index problems from SYSERR
• ENFORCE — referential constraints (not informational RI)
• DISCARD / REPORT — copy bad input to SYSDISC; print summary
• SWITCH — REFERENCE REPLACE only: flip shadow to real

SHRLEVEL CHANGE does not perform SORT, BUILD, SORTBLD, INDEXVAL, or ENFORCE in that classic way; concurrency and compatibility rules differ.

FORMAT INTERNAL (UNLOAD format) skips field specs and is the fastest reload of a table you just unloaded from the same Db2. FORMAT DELIMITED is CSV-style with a delimiter and optional DECPT and COLDEL options. Default positional format needs the start and end columns in the INTO TABLE field list. INCURSOR is the cross loader: EXEC SQL DECLARE cursor FOR SELECT ..., then LOAD INCURSOR cursor-name. It cannot combine with several drained-load keywords (IBM lists INCURSOR as invalid with SHRLEVEL CHANGE among others). DISCARDS n stops the job after n rejected records; BACKOUT YES then rolls back a RESUME YES SHRLEVEL NONE load so the table is left as it was. Default discard limit behaviour is “no limit” unless you set one — a bad file can load millions of junk rows before anyone notices.

Authorization is LOAD on every table in the table space, or DBADM on the database, or SYSADM. Loading a child table does not grant you the right to skip ENFORCE. Loading into a partitioned space by PART still needs the same table authority; PART is not a security boundary. If the input CCSID does not match the table, specify UNICODE, EBCDIC, or ASCII on the LOAD statement (and CCSID integers when required) rather than hoping the bytes look like characters on your terminal.

Explain It Like I'm Five

INTO TABLE is which toy box. PART is which drawer. REPLACE dumps the drawer on the floor then pours in new toys. RESUME YES dumps new toys on top of the old ones. RESUME NO refuses to play unless the drawer is already empty. SHRLEVEL NONE locks the bedroom door. REFERENCE lets siblings look but not dump toys until a quick swap of boxes. CHANGE lets everyone keep playing while you tuck toys into gaps — the room gets messy, so you tidy (REORG) later.

Exercises

1. Write three SYSIN statements for the same file: REPLACE whole space, RESUME YES, PART 2 REPLACE. Explain who is locked out in each case with SHRLEVEL NONE.
2. Why does LOAD REPLACE on a two-table segmented space scare application owners?
3. List five keywords illegal with SHRLEVEL CHANGE and why COPYDDN is among them.
4. DISPLAY UTILITY during a sandbox LOAD and name the phase. Match it to the list above.
5. Design a weekend job: readers needed, full replace of a partitioned table without LOBs. Which SHRLEVEL and REPLACE grain?