Other DB2 utilities and related programs

Full and concurrent copies (quick recap)

A full image copy is still the recovery base even when this page is not about COPY syntax. Concurrent copies (COPY CONCURRENT) use DFSMSdss to copy while the space remains available under documented page-size and CI rules. You cannot follow a concurrent copy with incremental COPY, MERGECOPY, or COPYTOCOPY. For day-to-day incrementals, use ordinary sequential COPY FULL NO instead.

Log application is the RECOVER (and REORG CHANGE) phase that reapplies Db2 log records after a restore or while a shadow is catching up. Image copies reduce how much log you must apply. System-level backups and FlashCopy-with-consistency add extra PRELOG / LOGCSR-style phases for uncommitted work—see the RECOVER utility page.

REORG: FASTSWITCH, DISCARD, statistics, and phases

FASTSWITCH

Online REORG (SHRLEVEL REFERENCE or CHANGE) builds shadow VSAM data sets. At SWITCH, FASTSWITCH YES updates the Db2 catalog (and ICF catalog instance) so the new data sets become current without a long copy-back. The old data sets remain as the previous instance until cleanup. FASTSWITCH NO is slower. IBM requires FASTSWITCH (not NO) to materialize pending DDL (pending ALTER). SHRLEVEL NONE does not use this shadow/switch model.

DISCARD

DISCARD FROM TABLE … WHERE on REORG TABLESPACE deletes rows that match the predicate while the space is reorganized. Discards go to a sequential data set (DISCARDDN). This is a bulk-delete technique with a reorg, not the LOAD discard file. Restrict the predicate; a wrong WHERE deletes production rows permanently.

Inline statistics

STATISTICS on REORG, LOAD, or REBUILD collects catalog statistics during the utility so you can skip a separate RUNSTATS. Caution: REORG INDEX with inline statistics updates index statistics only. Table statistics can drift apart. Prefer a table-space RUNSTATS (or STATISTICS on REORG TABLESPACE) after index-only reorgs.

Log phase, switch phase, drain processing

Drain processing uses claim classes. Writers and readers must release claims before SWITCH. Tune DRAIN_WAIT, RETRY, and MAXRO so the last log iteration is short enough that drain succeeds in the batch window. If drain never completes, REORG fails and the original data sets remain current.

1
2
3
4
5
6
7
REORG TABLESPACE HRDB.HRTS
     SHRLEVEL CHANGE
     FASTSWITCH YES
     DISCARD FROM TABLE HR.EMPLOYEE
       WHEN (TERM_DATE < CURRENT DATE - 7 YEARS)
     STATISTICS TABLE(ALL) INDEX(ALL)
     DRAIN_WAIT 30 RETRY 6

RUNSTATS options that change access paths

RUNSTATS writes summary information the optimizer reads at BIND and PREPARE. Column statistics describe one column. Distribution statistics (frequencies and histograms) describe how values clump. Correlation statistics (COLGROUP) describe columns used together.Key cardinality (FIRSTKEYCARD, FULLKEYCARD) describes index keys.

• FREQVAL COUNT n MOST (or LEAST) stores the n most/least frequent values for a column or COLGROUP
• NUMCOLS on INDEX FREQVAL names how many leading index columns to treat as a concatenated group (for example NUMCOLS 2 COUNT 10)
• HISTOGRAM NUMQUANTILES n with COLGROUP (or index prefix columns in the same order) fills SYSCOLDIST-style quantile rows. Not collected for LOB or XML table spaces; mixed-order index keys are not allowed
• TABLESAMPLE SYSTEM AUTO (UTS) samples pages. On multi-table or segmented-not-partitioned spaces, Db2 may fall back to SAMPLE 25. LOBs are not eligible
• Inline RUNSTATS is the STATISTICS keyword on LOAD/REORG/REBUILD
• Statistics profiles (SET PROFILE / USE PROFILE) store a standard COLUMN/COLGROUP/FREQVAL list in the catalog so every RUNSTATS for that table uses the same specification

1
2
3
4
5
6
7
8
9
10
RUNSTATS TABLESPACE HRDB.HRTS
  TABLE(HR.EMPLOYEE)
    COLGROUP(WORKDEPT, JOB)
      FREQVAL COUNT 10 MOST
      HISTOGRAM NUMQUANTILES 20
  INDEX(ALL)
    FREQVAL NUMCOLS 2 COUNT 10
  TABLESAMPLE SYSTEM AUTO
  SHRLEVEL CHANGE
  UPDATE ALL

CHECK DATA, CHECK INDEX, CHECK LOB

Referential integrity checks are CHECK DATA’s job: find child rows with no parent, or other constraint violations, and optionally copy or delete them (SCOPE, FOR EXCEPTION). After RECOVER to a point that is not a common QUIESCE across a table space set, CHECK-pending is expected until CHECK DATA runs.

Index consistency is CHECK INDEX: every index key must match a table row, and vice versa for the keys CHECK INDEX validates. Run it after suspected damage or after RECOVER of a table space without recovering COPY YES indexes to the same point.

XML consistency (and LOB consistency) use CHECK LOB and XML-related CHECK options so auxiliary/XML objects match the base table. CHECK LOB validates LOB table spaces; XML checks catch broken XML document structure versus node-ID indexes.

1
2
3
4
5
CHECK DATA TABLESPACE HRDB.HRTS
     SCOPE ALL
     FOR EXCEPTION IN HR.EMPLOYEE USE HR.EMP_EXC
CHECK INDEX (HR.XEMP1)
CHECK LOB TABLESPACE HRDB.HREMPAUX

MODIFY RECOVERY: delete old image copies and retention

MODIFY RECOVERY deletes rows from SYSIBM.SYSCOPY (and related SYSLGRNX) older than a DATE or AGE you specify. That is how you enforce retention periods: keep 14 days of copies, or keep two full-copy weekends, then delete. Deleting SYSCOPY rows does not always scratch the tape—your operations procedure must uncatalog or expire the data sets too.

Never MODIFY away the only full copy still required for recover-to-current. REPORT RECOVERY before and after. Recovery history that is gone cannot be used even if the tape is in the vault.

1
2
3
4
MODIFY RECOVERY TABLESPACE HRDB.HRTS
     DELETE AGE(*)
* Prefer a real age, for example DELETE AGE(14), after you verify
* REPORT RECOVERY still shows a usable full copy.

QUIESCE write suspension, STOSPACE, CATMAINT

QUIESCE establishes a SYSCOPY ICTYPE Q log point. WRITE YES (typical) forces updated pages to disk so the quiesce point is a true external consistency point. That short write suspension is why QUIESCE can stall busy writers. WRITE NO records a log point without waiting for all writes—faster, weaker guarantee.

STOSPACE updates catalog space-use columns from the DB2 storage groups (how much space STOGROUP volumes actually hold). Space usage and storage statistics in SYSIBM.SYSINDEXPART / SYSTABLEPART SPACE values are not a substitute for RUNSTATS cardinality, but they help capacity planning.

CATMAINT performs catalog maintenance and catalog level migration: IBM’s jobs apply catalog changes when you activate a new function level or migrate a release. Do not invent CATMAINT SYSIN. Use the DSNTIJ* jobs in the Program Directory / Installation Guide.

DSNJU utilities and BSDS processing

The bootstrap data set (BSDS) is the inventory of active and archive logs, checkpoint queue, DDF records, and system-level backup history. Stand-alone DSNJU programs update or print it. They do not run under DSNUTILB.

Log inventory changes (NEWLOG, DELETE, STARTRBA/ENDRBA on archives) are DSNJU003. Many DSNJU003 updates require Db2 to be stopped; Db2 10+ -SET LOG NEWLOG and Db2 13 REMOVELOG cover some active-log changes while Db2 is up. Log print of BSDS contents is DSNJU004; printing actual log records is DSN1LOGP. Log archive of the active log to archive data sets is the -ARCHIVE LOG command (and offload), not a DSNJU program—DSNJU003 only records those archive data sets in the BSDS.

Current IBM Utility Guide chapters document DSNJU003, DSNJU004, and DSNJU008 (plus DSNJLOGF and DSNJCNVU). Load-module names such as DSNJU005 or DSNJU006 may appear in older outlines or local libraries; do not assume they are supported user utilities on Db2 12/13 without checking your SDSNLOAD and the Utility Guide for your release.

1
2
3
4
5
6
//MAP    EXEC PGM=DSNJU004
//STEPLIB  DD DISP=SHR,DSN=DSN.V12.SDSNLOAD
//SYSUT1   DD DISP=SHR,DSN=DB2T.BSDS01
//SYSUT2   DD DISP=SHR,DSN=DB2T.BSDS02
//SYSPRINT DD SYSOUT=*
//SYSIN    DD DUMMY

Other programs: DSNTEP2, DSNTIAUL, DCLGEN, DSNACCOX

• DSNTEP2 — sample dynamic SQL processor; reads SQL from SYSIN, prints results. Runs under IKJEFT01 / DSN RUN. Good for batch SELECT and simple DML
• DSNTEP4 — DSNTEP2 with multi-row fetch / higher performance for wider result sets
• DSNTIAUL — sample unload program (not DSNUTILB UNLOAD). Still common for application extracts; control statements differ from UNLOAD FROM TABLE
• DSNTIAR — assembler routine that formats SQLCA into printable messages for COBOL/PL/I programs
• DCLGEN — declarations generator; builds host-variable structures from a table or view. Run from DB2I or DSN DCLGEN under IKJEFT01
• DSNACCOX — RTS stored procedure recommending COPY, RUNSTATS, REORG, extents, restricted states. Replaced DSNACCOR. Filter with Criteria (SQL WHERE) and QueryType
• DSNACCOC / DSNACCMO — names you may see in older DSNACC* sample or local maintenance tooling. IBM’s current documented RTS advisor is DSNACCOX; confirm any other DSNACC* member against SDSNSAMP for your release before calling it in production

1
2
3
4
5
6
7
8
9
10
11
12
13
14
//TEP2 EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB  DD DISP=SHR,DSN=DSN.V12.SDSNEXIT
//         DD DISP=SHR,DSN=DSN.V12.SDSNLOAD
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN  DD *
  DSN SYSTEM(DB2T)
  RUN PROGRAM(DSNTEP2) PLAN(DSNTEP12) LIB('DSN.RUNLIB.LOAD')
  END
/*
//SYSIN    DD *
  SELECT COUNT(*) FROM SYSIBM.SYSDUMMY1;
/*

Explain It Like I'm Five

COPY takes photos. The utilities on this page are the rest of the school staff. REORG is the janitor who rearranges desks (FASTSWITCH is swapping the whole classroom for a cleaned one in one move; DISCARD throws away leftover lunch boxes). RUNSTATS is counting how many kids sit in each row so the teacher (optimizer) makes a smart seating plan. CHECK is the hall monitor verifying every kid has a matching name tag. MODIFY RECOVERY throws out old photo envelopes past the keep-by date. DSNJU003 is the office clerk who updates the list of diary books (logs). DSNTEP2 is a kid who can run errands (SQL) from a note. DSNACCOX is the nurse who looks at the attendance clipboard and says “this room needs a photo, a cleanup, or a head count.”

Exercises

1. Explain why FASTSWITCH NO blocks pending DDL materialization on REORG SHRLEVEL CHANGE.
2. Write RUNSTATS with COLGROUP, FREQVAL COUNT 10 MOST, HISTOGRAM NUMQUANTILES 15, and TABLESAMPLE SYSTEM AUTO.
3. Draft MODIFY RECOVERY DELETE AGE(14) and list what you would check with REPORT RECOVERY first.
4. Contrast DSNJU004 (print log map) with DSN1LOGP (print log records) in two sentences.
5. Call out when you would use DSNTEP2 versus DSNUTILB UNLOAD versus DSNTIAUL.