The DB2 UNLOAD utility

UNLOAD TABLESPACE and FROM TABLE

The usual shape names the table space, then one or more tables:

1
2
3
4
UNLOAD TABLESPACE HRDB.HRTS
        PUNCHDDN SYSPUNCH
        UNLDDN SYSREC
  FROM TABLE HR.EMPLOYEE

TABLESPACE database.tablespace is required when you use PART or when you do not use the DATA keyword. The table space must not be a LOB or XML table space (you unload the base table; LOB/XML values come along according to field options). Database default is DSNDB04 if omitted. DSNDB01 and DSNDB07 are not valid.

FROM TABLE selects which table in a multi-table space (or a subset of columns). You can list several FROM TABLE clauses to unload EMP and DEPT from the same space into one SYSREC, typically with HEADER constants so generated LOAD WHEN clauses can split them again.

UNLOAD DATA FROM TABLE … (DATA keyword) is mutually exclusive with TABLESPACE, PART, and LIST. Use it when you identify tables only, without naming the space.

Output data sets: UNLDDN and PUNCHDDN

• UNLDDN — unload rows. Default DD SYSREC
• PUNCHDDN — generated LOAD statements. Default DD SYSPUNCH. If you omit PUNCHDDN, LOAD statements are not generated

JCL still needs SYSIN and SYSPRINT. Multiple table spaces need TEMPLATEs so each space gets its own SYSREC and SYSPUNCH; a single DD would overwrite statements. LIST listdef drives that pattern with pattern-matching LISTDEF.

FROMCOPY — unload an image copy

FROMCOPY data-set-name reads SYSCOPY’s DSNAME instead of the live VSAM linear data set. Use it to extract data as of last night’s copy without touching online pages. FROMCOPYDDN concatenates several copy data sets under one DD (pieces). FROMVOLUME CATALOG or a volser plus FROMSEQNO disambiguate non-cataloged tape copies using SYSCOPY DSVOLSER and FILESEQNO.

1
2
3
4
UNLOAD TABLESPACE HRDB.HRTS
        FROMCOPY HR.FULLCOPY.HRTS.D20260813
        PUNCHDDN SYSPUNCH
        UNLDDN SYSREC

FROMCOPY cannot be combined with SPANNED YES. You are unloading whatever that copy contains—SHRLEVEL CHANGE copies may include in-flight pages.

SHRLEVEL

SHRLEVEL CHANGE takes ISOLATION CS by default; specify UR for uncommitted read, and SKIP LOCKED DATA to skip locked rows instead of waiting. REGISTER YES/NO relates to whether the unload is registered for replication-style consumers—check the Utility Guide if you use those products.

Partitions

PART integer or PART int1:int2 unloads one partition or a contiguous range. Without PART, the whole space goes to one UNLDDN data set (unless partition parallelism with a TEMPLATE that includes &PART). You cannot specify PART with LIST; put PARTLEVEL in the LISTDEF instead.

1
2
3
UNLOAD TABLESPACE HRDB.HRTS PART 3:5
        UNLDDN SYSREC
  FROM TABLE HR.EMPLOYEE

Predicates: WHEN and SAMPLE

In FROM TABLE you can add a WHEN clause that looks like a SQL predicate on column names (IBM documents parentheses around the condition). Only qualifying rows are written. SAMPLE decimal then samples a percentage of those qualified rows (0–100, precision ddd.dddd).

1
2
3
4
UNLOAD TABLESPACE HRDB.HRTS UNLDDN SYSREC
  FROM TABLE HR.EMPLOYEE
    SAMPLE 10
    WHEN (WORKDEPT = 'A00')

HEADER CONST 'xyz' plants a constant in the output so generated LOAD WHEN (1:n)='xyz' can reload the correct table from a mixed SYSREC. HEADER NONE omits that WHEN on the punched LOAD—dangerous if several tables share one file.

Output formats and delimiters

FORMAT DELIMITED uses COLDEL (default comma), CHARDEL (default quote), and DECPT (default period). On EBCDIC, comma/quote/period are not the ASCII X'2C' values—use hex COLDEL if the receiver is a PC CSV. NOPAD is the default for variable columns with DELIMITED. EBCDIC, ASCII, UNICODE, and CCSID clauses convert output; DELIMITED plus UNICODE forces UTF-8 CCSID 1208.

XML, LOB, and “discards”

Unload XML with field type XML or BINARYXML. LOB columns can be written in-record (length prefix 2 bytes if SPANNED NO, 4 bytes if SPANNED YES) or via file-reference variables. SPANNED YES is the supported path when the assembled record would exceed 32 KB. Do not combine SPANNED YES with DELIMITED, FROMCOPY, or LIST (LIST ignores SPANNED YES).

UNLOAD has no SYSDISC. Conversion failures skip the record and count toward MAXERR (default 1). At the limit, message DSNU1219 and RC 8. Some LOB/XML file-reference errors terminate regardless of MAXERR. Filter with WHEN rather than expecting a discard file.

1
2
3
4
5
6
7
8
9
10
11
12
13
//UNLOAD EXEC DSNUPROC,SYSTEM=DB2T,UID='HR.UNLD.EMP'
//SYSREC   DD DSN=HR.EMP.UNLOAD,DISP=(NEW,CATLG,DELETE),
//            UNIT=SYSDA,SPACE=(CYL,(50,10),RLSE)
//SYSPUNCH DD DSN=HR.EMP.PUNCH,DISP=(NEW,CATLG,DELETE),
//            UNIT=SYSDA,SPACE=(TRK,(5,5),RLSE)
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
  UNLOAD TABLESPACE HRDB.HRTS
         PUNCHDDN SYSPUNCH UNLDDN SYSREC
         SHRLEVEL REFERENCE
         MAXERR 50
    FROM TABLE HR.EMPLOYEE
/*

Explain It Like I'm Five

UNLOAD is photocopying the toys in a classroom onto a roll of paper (SYSREC). SYSPUNCH is the instruction sheet that says how to put the toys back (LOAD). FROM TABLE is “only photograph the blue bin.” WHEN is “only if the toy is a car.” FROMCOPY is “photograph yesterday’s class photo instead of today’s room.” DELIMITED is writing a list with commas so a PC can read it. MAXERR is “stop after this many blurry photos.” There is no reject box like LOAD’s SYSDISC—blurry photos are just skipped until you hit the limit.

Exercises

1. Write UNLOAD for one table with PUNCHDDN and UNLDDN, then identify which JCL DDs you need.
2. Add PART 1 and explain what happens to other partitions.
3. Unload from SYSCOPY using FROMCOPY; list the SYSCOPY columns you would use to pick DSNAME and FILESEQNO.
4. Write a DELIMITED unload intended for a Windows CSV consumer (think CCSID / COLDEL hex).
5. Explain two reasons a row might be missing from SYSREC even though it exists in the table.