DB2 LOAD logging, copies, ENFORCE, sort, and STATISTICS

LOAD LOG and LOAD LOG NO

LOG applies to the RELOAD phase for SHRLEVEL NONE and for RESUME YES SHRLEVEL CHANGE. REFERENCE jobs always run as LOG NO. YES logs every loaded record (unless the table space is NOT LOGGED, in which case LOAD does not log anyway). NO skips that logging. The speed win is real on large replaces; the cost is recoverability.

COPY-pending means SQL UPDATE/INSERT/DELETE against that space or partition is refused until you take a full image copy (COPY or inline COPYDDN) or otherwise reset the state per IBM’s “Resetting COPY-pending” procedures. LOAD LOG NO without a copy is the classic Monday-morning “why is the table ROCPY?” page. NOCOPYPEND says you understood that and still do not want the flag — for example a scratch table you will not recover, or a space you will COPY in the next step. It will not rescue a space that was already COPY-pending before this LOAD.

1
2
3
4
5
6
7
LOAD DATA INDDN SYSREC
  REPLACE LOG NO NOCOPYPEND
  INTO TABLE TEMP.WORK_STAGE
 
LOAD DATA INDDN SYSREC
  REPLACE LOG NO COPYDDN(SYSCOPY)
  INTO TABLE HR.EMPLOYEE

LOAD COPYDDN and inline copies

You can specify COPYDDN with REPLACE or with RESUME YES SHRLEVEL NONE. The inline copy is a full copy of the table space. If you load individual partitions with INTO TABLE PART, the copy and SYSCOPY records include only those partitions. SHRLEVEL CHANGE cannot take COPYDDN and does not insert SYSCOPY rows. Pair LOG NO with COPYDDN when the table must remain recoverable: you skip the log volume but still have a copy taken at the consistent end of RELOAD.

DD names may be TEMPLATEs defined earlier in SYSIN. If the same name is both a JCL DD and a TEMPLATE, the DD wins. FlashCopy (FCCOPYDDN) needs unique names per piece; include &DSNUM.

1
2
3
4
5
TEMPLATE SCPY DSN HLQ.COPY.&DB..&TS..D&DATE. UNIT SYSALLDA
LOAD DATA INDDN SYSREC
  RESUME YES SHRLEVEL NONE
  COPYDDN(SCPY)
  INTO TABLE HR.PAY_TRAN

LOAD ENFORCE

Check constraints are also evaluated during RELOAD for each row. ENFORCE CONSTRAINTS is the RI pass in the ENFORCE phase: children without parents are removed (discard processing copies them to SYSDISC if you allocated it). Informational referential constraints are never enforced. Parent-before-child load order still matters; a cycle of tables needs ENFORCE NO plus CHECK DATA later, or a carefully ordered set of jobs.

SYSERR (ERRDDN) and SYSMAP (MAPDDN) are required for discard processing and for RI ENFORCE with unique/extended indexes. Defaults are SYSERR and SYSMAP. SHRLEVEL REFERENCE always uses ENFORCE NO — plan a CHECK DATA if the table has RI.

LOAD SORTDEVT and SORTNUM

Index build and RI ENFORCE use an external sort. SORTDEVT names a disk device type acceptable to DFSORT DYNALLOC (never tape). If you omit SORTDEVT, you must supply sort work DD statements yourself. TEMPLATE does not allocate sort work. SORTNUM is 2–255 data sets per sort invocation; omit it to let sort use its default. Three indexes with SORTKEYS and SORTNUM 8 can allocate 24 work data sets. Each consumes above- and below-the-line storage; an oversized SORTNUM can collapse parallel index build to one task. ZPARM IGNSORTN YES ignores SORTNUM entirely.

1
2
3
4
5
LOAD DATA INDDN SYSREC
  REPLACE
  SORTDEVT SYSALLDA SORTNUM 8
  ENFORCE CONSTRAINTS
  INTO TABLE ORD.ORDR_HDR

LOAD STATISTICS

Inline stats avoid an immediate RUNSTATS after LOAD, which matters when the optimizer would otherwise see empty-space statistics on a freshly replaced table. You still cannot specify STATISTICS with SHRLEVEL CHANGE. Collect index stats explicitly if access-path histograms matter; the bare STATISTICS keyword is table space only. INVALIDATECACHE YES/NO controls whether dynamic SQL cached against the object is invalidated when stats change — same idea as RUNSTATS.

1
2
3
4
5
LOAD DATA INDDN SYSREC FORMAT DELIMITED
  REPLACE
  COPYDDN(SCPY)
  STATISTICS TABLE(ALL) INDEX(ALL) UPDATE ALL REPORT YES
  INTO TABLE HR.EMPLOYEE

Recoverability after LOG NO

A LOGGED table space that you LOAD LOG NO is not recoverable to current from the log alone. RECOVER needs an image copy that includes the loaded pages. COPYDDN (or a subsequent COPY utility) writes that copy and registers it in SYSIBM.SYSCOPY. Until that happens, COPY-pending (displayed as ROCPY or similar on DISPLAY DATABASE) blocks SQL updates. Reads may still be allowed depending on the state; do not assume the table is “fine because SELECT works.” NOCOPYPEND skips setting the flag for this execution only. Use it for true scratch tables, or when the next step in the same job is a full COPY you have already coded. Using it on a production recoverable table without a copy is how disaster recovery tests fail.

NOT LOGGED table spaces never log loaded rows; LOG YES does not override that attribute. Their recovery model is already “go back to the last image copy and accept that later changes are gone.” Mixing NOT LOGGED with LOAD LOG NO NOCOPYPEND is consistent; mixing LOGGED with LOG NO and no copy is the trap. RECOVERYDDN is the recovery-site copy pair for dual-site shops; it is not a substitute for COPYDDN at the local site. FlashCopy (FCCOPYDDN) can be faster on disk that supports it; each partition still needs a unique data set name, which is why &DSNUM belongs in the template.

SHRLEVEL CHANGE always logs like INSERT (on a LOGGED space) and forbids LOG NO, COPYDDN, and STATISTICS. After a large CHANGE load you still need a COPY if your recovery strategy expects a recent full copy, plus RUNSTATS because inline STATISTICS was not allowed. REFERENCE always uses LOG NO; the inline copy story for REFERENCE follows IBM’s shadow/switch rules — do not assume COPYDDN from a NONE job ports unchanged.

ENFORCE, discards, and CHECK-pending

Check constraints are evaluated as rows go in during RELOAD. Referential constraints are the later ENFORCE phase when you specified ENFORCE CONSTRAINTS. Violating children are deleted from the table and, if discard processing is active, copied to SYSDISC so you can repair and reload them. SYSERR holds error records; SYSMAP maps table rows back to input records. If you omit those DDs and Db2 needs them, the utility fails instead of silently skipping discards.

ENFORCE NO plus a table that has RI or check constraints sets CHECK-pending (CHKP) unless NOCHECKPEND. CHECK-pending blocks SQL until CHECK DATA (or you reset the state per documented procedures). NOCHECKPEND is the twin of NOCOPYPEND: it does not clear a CHKP that was already on. Parent-before-child still matters if you ENFORCE in the same job as the parent load. Cycles of three tables usually mean ENFORCE NO on all three, then CHECK DATA SCOPE ALL, not a hope that LOAD will sort it out.

Putting the switches together

Recoverable production replace: REPLACE LOG NO COPYDDN(...) STATISTICS ... SHRLEVEL NONE (or REFERENCE if you need readers and can accept ENFORCE NO and shadow restrictions). Scratch pad: REPLACE LOG NO NOCOPYPEND. Online trickle: RESUME YES SHRLEVEL CHANGE (logged, no COPYDDN, no STATISTICS — follow with RUNSTATS/REORG). Parent/child warehouse: ENFORCE CONSTRAINTS and enough sort work, or ENFORCE NO plus CHECK DATA. Never combine LOG NO, no copy, and a table you must RECOVER to current.

Inline STATISTICS TABLE(ALL) INDEX(ALL) UPDATE ALL REPORT YES is the usual “I just replaced the table, do not let the optimizer keep empty-table cardinalities” pattern. UPDATE ACCESSPATH writes the stats the optimizer uses; UPDATE SPACE writes space stats used by utilities and some real-time stats displays; UPDATE NONE with REPORT YES is a dry run. HISTORY copies a row into the statistics history tables the way RUNSTATS HISTORY does. FORCEROLLUP matters on partitioned spaces when some partitions were not touched. INVALIDATECACHE YES drops cached dynamic SQL that might still be using old stats — often what you want after a replace, less often after a tiny RESUME.

SORTDEVT must be a disk UNIT name DFSORT (or DB2SORT) will accept. Tape is invalid. SORTNUM 2 is the documented minimum when you specify it; 255 is the maximum and is almost never wise. Count indexes that will be built in parallel: each sort invocation can allocate SORTNUM work data sets. If IGNSORTN=YES in ZPARM, your SORTNUM is ignored and you should not tune it in every job. When RI ENFORCE needs a sort and you forgot SORTDEVT and sort DDs, the ENFORCE phase abends or returns a DSNU message pointing at sort allocation — that is not a constraint violation, it is JCL.

Explain It Like I'm Five

LOG YES is writing every Lego you add into a diary (the active log). LOG NO skips the diary so you finish faster, but then you need a photograph of the finished model (COPYDDN) or a sign that says “we do not have a photo on purpose” (NOCOPYPEND). ENFORCE is checking that every child brick still has its parent brick. SORTDEVT is borrowing extra tables to sort the bricks. STATISTICS is counting the bricks so the next person (the optimizer) knows how many are in the box.

Exercises

1. Write a LOAD REPLACE that is not COPY-pending when it finishes, using inline copy, not NOCOPYPEND.
2. Explain what DISPLAY DATABASE would show after LOG NO without NOCOPYPEND or COPYDDN.
3. Size SORTNUM for four indexes with SORTKEYS. What goes wrong if you pick 255?
4. Why must a SHRLEVEL REFERENCE REPLACE job run CHECK DATA if the table has foreign keys?
5. Add STATISTICS INDEX(ALL) UPDATE ACCESSPATH to a LOAD and list which RUNSTATS job you can drop that night.