UNLOAD data from DB2 for z/OS

What UNLOAD does

UNLOAD reads table data and writes sequential output. Operations teams use it for environment refreshes, vendor feeds, conversions, and “give me a file that LOAD can put back.” Unlike a casual SELECT in SPUFI, UNLOAD runs as an online utility with explicit concurrency controls, partition limits, and SYSPRINT accounting.

You specify a TABLESPACE (and optional database), optional PART ranges, and one or more FROM TABLE clauses. Output goes to a DD such as SYSREC or a name you supply with UNLDDN.

Prerequisites

• Authority to run UNLOAD on the target table space / tables
• Correct database and table space names (not DSNDB01 or DSNDB07 as sources)
• Output data set sized for the extract—underallocating is a classic abend cause
• A chosen SHRLEVEL: NONE, REFERENCE, or CHANGE based on availability needs
• Agreement on encoding and format so a later LOAD can consume the file
• For copy-based unload, a usable full image copy data set and matching control options

Steps

1. Choose concurrency with SHRLEVEL

• SHRLEVEL NONE — most restrictive; use when you need a quiet extract and can take the availability hit
• SHRLEVEL REFERENCE — readers OK; updates blocked during the unload of the target objects
• SHRLEVEL CHANGE — allows concurrent writers; extract reflects committed rows as the utility reads, so it is not a single frozen instant

Beginners practicing on test data can start with REFERENCE. Production 24×7 tables often need CHANGE plus clear documentation of consistency expectations.

2. Code the UNLOAD statement

1
2
3
4
UNLOAD TABLESPACE TRAINING.EMPTS
        FROM TABLE TRAINING.EMPLOYEE
        SHRLEVEL REFERENCE
        UNLDDN SYSREC

Add PART when you only need some partitions:

1
2
3
4
5
UNLOAD TABLESPACE TRAINING.EMPTS
        PART 1:4
        FROM TABLE TRAINING.EMPLOYEE
        SHRLEVEL CHANGE
        UNLDDN SYSREC

3. Build the utility JCL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
//UNLDEMP  JOB (ACCT),'DB2 UNLOAD',CLASS=A,MSGCLASS=X,
//              NOTIFY=&SYSUID
//JOBLIB   DD DISP=SHR,DSN=DSN.V13R1M0.SDSNEXIT
//         DD DISP=SHR,DSN=DSN.V13R1M0.SDSNLOAD
//UNLOAD   EXEC PGM=DSNUTILB,REGION=0M,
//             PARM='DB2T,UNLDEMP'
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC   DD DSN=TRAINING.EMPLOYEE.UNLOAD,
//             DISP=(NEW,CATLG,DELETE),
//             UNIT=SYSDA,SPACE=(CYL,(50,20),RLSE),
//             DCB=(RECFM=FB,LRECL=80,BLKSIZE=0)
//SYSIN    DD *
  UNLOAD TABLESPACE TRAINING.EMPTS
         FROM TABLE TRAINING.EMPLOYEE
         SHRLEVEL REFERENCE
         UNLDDN SYSREC
/*

Match LRECL/RECFM to the unload format your statement produces. Site templates often set these for you—do not invent DCB values that truncate rows.

4. Submit and read SYSPRINT

Confirm the utility ID, the objects processed, and the number of rows unloaded. Save the SYSPRINT with the output file; auditors and reload jobs both need that pair.

Verify results

• Utility return code 0 (or understood warnings)
• Row count in SYSPRINT matches expectations for the PART/FROM TABLE filters
• Compare to SQL: SELECT COUNT(*) FROM TRAINING.EMPLOYEE when SHRLEVEL and filters allow a fair comparison
• Browse the first and last records of the output file for format sanity
• Optionally LOAD into a shadow test table and compare counts and key samples

1
2
3
4
5
6
SELECT COUNT(*) AS LIVE_ROWS
  FROM TRAINING.EMPLOYEE;
 
-- After a test LOAD of the unload file into TRAINING.EMPLOYEE_SHADOW:
SELECT COUNT(*) AS SHADOW_ROWS
  FROM TRAINING.EMPLOYEE_SHADOW;

Common errors

Output data set too small

B37/D37 style abends or utility failures when SYSREC fills. Increase SPACE primary and secondary quantities; use RLSE when appropriate.

Wrong table space or table name

Catalog typos yield object-not-found style utility messages. Query SYSTABLESPACE / SYSTABLES before the window.

Incompatible concurrent utilities

Some utilities cannot run with UNLOAD on the same target depending on SHRLEVEL. Display active utilities and drain conflicting work first.

Format mismatch on reload

LOAD field positions do not match what UNLOAD wrote. Keep unload/load control statements together and test on a shadow table before production cutover.

Unexpected row counts under SHRLEVEL CHANGE

Writers continued during the extract. If you need a frozen picture, use REFERENCE/NONE, unload from a QUIESCE + COPY, or unload from an image copy taken at a known point.

LOB / XML table space restrictions

Base UNLOAD has restrictions around LOB and XML table spaces as sources—check IBM documentation for your release before planning extracts of those objects.

Explain It Like I'm Five

UNLOAD is pouring the toys out of the toy box into a big bag so you can carry them to another room. SHRLEVEL REFERENCE means friends can look at the toys while you pour, but nobody adds or removes toys until you finish. SHRLEVEL CHANGE means friends can still add and remove toys while you pour, so the bag might not match a photo taken at the start. When the bag is full, you check that the number of toys makes sense before you travel.

Exercises

1. UNLOAD a small training table with SHRLEVEL REFERENCE and record the SYSPRINT row count.
2. Compare that count to SELECT COUNT(*). Explain any difference.
3. UNLOAD only PART 1 of a partitioned test table and verify the count matches that partition.
4. LOAD the unload file into a shadow table and spot-check five keys.
5. Write a short note in your runbook: when your shop prefers CHANGE vs REFERENCE for nightly extracts.