REORG a tablespace in DB2 for z/OS

What REORG TABLESPACE does

Over time, inserts, updates, and deletes scatter rows away from clustering sequence and leave empty holes in pages. Queries that once rode a tight clustering index start reading more pages. REORG rebuilds the physical layout: unload (often sorted by clustering key), reload, rebuild indexes, and optionally switch shadow data sets into place for online availability.

REORG is also used to materialize certain pending ALTER changes when SHRLEVEL REFERENCE or CHANGE is specified. That dual role—performance hygiene plus schema materialization—makes it a weekly staple in many shops.

Prerequisites

• REORG privilege (or higher) on the table space
• Enough sort and shadow work space for the object size—online REORG needs room for shadows
• A chosen SHRLEVEL that matches the outage window (or lack of one)
• Image copy / recovery plan understood for your SHRLEVEL and LOG options
• Mapping table requirements satisfied when your CHANGE reorg pattern needs one (per release and options)
• No conflicting utilities on the same target; check DISPLAY UTILITY first

Practice on a training table space first. Online REORG JCL varies by shop templates more than almost any other utility.

Steps

1. Confirm the object needs reorganization

Check real-time statistics, REORG-recommended indicators, and application symptoms (growing getpages, declining cluster ratio). Blind REORGs waste CPU and batch windows.

2. Pick SHRLEVEL

• SHRLEVEL NONE — simplest mental model; plan an outage-style window
• SHRLEVEL REFERENCE — readers continue; writers wait; shadow switch at the end
• SHRLEVEL CHANGE — maximum availability; log apply and brief drain at switch

3. Code REORG with optional inline statistics

1
2
3
4
REORG TABLESPACE TRAINING.EMPTS
     SHRLEVEL REFERENCE
     SORTDATA YES
     STATISTICS TABLE(ALL) INDEX(ALL)

For a partition range on a larger object:

1
2
3
4
5
REORG TABLESPACE TRAINING.EMPTS
     PART 10:20
     SHRLEVEL CHANGE
     SORTDATA YES
     STATISTICS TABLE(ALL) INDEX(ALL)

4. Build utility JCL from site standards

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
//REORGEMP JOB (ACCT),'DB2 REORG',CLASS=A,MSGCLASS=X,
//             NOTIFY=&SYSUID
//JOBLIB   DD DISP=SHR,DSN=DSN.V13R1M0.SDSNEXIT
//         DD DISP=SHR,DSN=DSN.V13R1M0.SDSNLOAD
//* Use your shop PROC for SORTWKnn, copy DDs, and shadows
//REORG    EXEC PGM=DSNUTILB,REGION=0M,
//             PARM='DB2T,REORGEMP'
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN    DD *
  REORG TABLESPACE TRAINING.EMPTS
       SHRLEVEL REFERENCE
       SORTDATA YES
       STATISTICS TABLE(ALL) INDEX(ALL)
/*

Production CHANGE reorgs usually need additional DDs for inline copies, discard/error data sets, and sort work. Copy a proven template from your DBA team rather than inventing every DD on the first try.

5. Submit during the approved window and monitor phases

Typical phase names you will see in SYSPRINT include UTILINIT, UNLOAD, RELOAD, SORT, BUILD, LOGAPPLY (CHANGE), SWITCH, and UTILTERM. Long stalls often mean drain contention or sort pressure—coordinate with application owners.

Verify results

• Utility return code accepted by shop standards (often 0)
• No unexpected RECOVER-pending or REBUILD-pending states
• DISPLAY DATABASE / RTS show healthier organization indicators after the run
• If STATISTICS was coded, catalog STATSTIME updated for the objects
• Spot-check critical SQL elapsed times or getpage counts in a test workload
• Confirm inline copy registration in SYSIBM.SYSCOPY when copies were taken

1
2
3
4
5
6
7
8
9
SELECT NAME, STATSTIME
  FROM SYSIBM.SYSTABLESPACE
 WHERE DBNAME = 'TRAINING'
   AND NAME = 'EMPTS';
 
SELECT NAME, STATSTIME, CLUSTERED
  FROM SYSIBM.SYSINDEXES
 WHERE TBCREATOR = 'TRAINING'
   AND TBNAME = 'EMPLOYEE';

Common errors

Drain timeout / application contention

Online REORG cannot switch while long transactions hold claims. Tune drain wait, retry windows, or schedule when batch writers quiet down.

Sort space shortages

Huge keys and SORTDATA YES need adequate SORTWK capacity. Symptoms include sort utility messages and job abends. Enlarge work files or adjust parallelism per site guidance.

Missing inline copy when required

Certain SHRLEVEL and object combinations require COPYDDN-style inline copies for recoverability. Add the copy DDs your template expects.

Utility conflict

Another LOAD, REORG, or RECOVER owns the object. DISPLAY UTILITY, wait or TERM only with authority and a recovery plan.

Mapping table problems (CHANGE)

Some CHANGE patterns need a valid mapping table. Create it ahead of time following IBM samples for your version.

REORG-pending materialization surprises

A REORG that materializes pending ALTERs can change limit keys or other attributes. Know which pending changes exist before you start.

Explain It Like I'm Five

Picture a bookshelf where people keep shoving books back in the wrong order and leaving gaps. REORG takes all the books off, puts them back in neat order, and tightens the gaps so you can find stories faster. If the library must stay open (SHRLEVEL CHANGE), workers build a second neat shelf in the back, then swap it in quickly at the end. If the library can close for a while (SHRLEVEL NONE), they tidy the one shelf in place during quiet time.

Exercises

1. On a test table space, run REORG SHRLEVEL REFERENCE with STATISTICS and record RC and STATSTIME.
2. Intentionally insert many out-of-order keys, note getpages or cluster indicators, REORG, and compare.
3. List three reasons your shop would choose CHANGE instead of NONE for a critical table.
4. Find your site's standard REORG PROC and identify which DDs are mandatory.
5. Explain when you would still run a standalone RUNSTATS after REORG.