DB2 REORG TABLESPACE utility

What REORG TABLESPACE does

IBM’s Utility Guide describes REORG TABLESPACE as the utility that reorganizes a table space, partition, or range of partitions to reclaim fragmented space and improve access performance. You can also run it to materialize pending definition changes.

• REORG TABLESPACE — all data and all indexes for the space
• REORG TABLESPACE PART n — data for that partition and the matching partitions of partitioned indexes
• REORG TABLESPACE PART n:m — a contiguous range of partitions

SCOPE PENDING restricts the job to objects (or partitions) already in REORG-pending or advisory REORG-pending. That is the usual choice after an ALTER that left AREOR.

There is no REORG TABLE online utility on Db2 for z/OS. You reorganize the table space that holds the table. In a classic segmented space with several tables, every table in that space is part of the same REORG. Universal table spaces (partition-by-range and partition-by-growth) hold one table, so “reorg the table” and “reorg the table space” mean the same object.

Use DSNACCOX (or your shop’s REORG advisor) against catalog statistics rather than guessing. Do not REORG an object while another data-sharing member holds retained locks or has long-running non-committing applications on it.

Authorization

For a user table space you need one of: REORG privilege on the database, DBADM or DBCTRL on that database (or DSNDB04 for implicit databases), SYSCTRL, SYSADM, or DATAACCESS. STATISTICS on the job also needs STATS privilege. SHRLEVEL CHANGE with a user-created mapping table needs DELETE, INSERT, and UPDATE on that mapping table. FlashCopy options need authority to run DFSMSdss COPY.

A starter control statement

1
2
3
4
5
6
7
8
9
10
11
//REORG   EXEC PGM=DSNUTILB,PARM='DB2A,HRREORG'
//STEPLIB  DD DISP=SHR,DSN=DSN.SDSNLOAD
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSIN    DD *
  REORG TABLESPACE DBHR.TSEMP
    SHRLEVEL CHANGE
    STATISTICS
    COPYDDN SYSCOPY
    SORTDEVT SYSDA
/*

Inline STATISTICS avoids a separate RUNSTATS after the REORG. Inline copies (COPYDDN, or automatic copies with SHRLEVEL REFERENCE/CHANGE) leave you recoverable without a follow-on COPY. Specify SORTDEVT so sort work data sets can be allocated dynamically.

SHRLEVEL

SHRLEVEL CHANGE is what operations usually mean by “online REORG”. Applications keep inserting while REORG builds a shadow, then the LOG phase catches up, then SWITCH swaps names. Drain failures at the end are the classic timeout: long readers that never commit, or overlapping claims. Options such as DRAIN ALL, FORCE READERS / FORCE ALL on the last RETRY, MAXRO, RETRY, and DEADLINE exist to finish the SWITCH. FASTSWITCH YES (not allowed on catalog/directory) uses a faster SWITCH than IDCAMS RENAME.

Mapping tables

SHRLEVEL CHANGE must map each original RID to the RID in the shadow so log records that still name old RIDs can be applied. Historically you created a mapping table and unique index yourself (columns such as TYPE, ORID, NRID, LRSN — the RID lengths grew with extended RBA/LRSN). Current Db2 can create the mapping table implicitly. ZPARM REORG_MAPPING_DATABASE names a database for those objects; blank means an implicitly defined database. A dedicated mapping database avoids catalog contention when many REORG jobs run at once.

If you still create the mapping table, the utility ID needs INSERT, UPDATE, and DELETE on it.

SORT during REORG

If a clustering index exists and you do not specify SORTDATA NO, UNLOAD sorts rows into clustering sequence. NOSYSREC pipes rows in memory to RELOAD; otherwise they land on SYSREC. Index keys are sorted again before BUILD. Parallel index build folds SORT and BUILD into SORTBLD.

On a partition-level REORG, nonpartitioned secondary indexes (NPSIs) are rebuilt from keys of the parts in scope plus keys from parts outside the scope. SORTNPSI YES or AUTO (or ZPARM REORG_PART_SORT_NPSI) sorts those outside keys with the in-scope keys instead of the older BUILD2-style approach.

Reorganization phases

DISPLAY UTILITY shows the current phase. A LOB table space uses UTILINIT, REORGLOB (unload to shadow for SHRLEVEL REFERENCE), SWITCH, UTILTERM. You cannot restart a LOB REORG in REORGLOB. Take a full image copy before SHRLEVEL NONE on a LOB space defined LOG NO; SHRLEVEL REFERENCE requires an inline image copy.

REORG and pending DDL

Many ALTER TABLE / TABLESPACE changes are pending: they sit in SYSPENDINGDDL and the object is AREOR until a REORG materializes them. Run REORG TABLESPACE SHRLEVEL REFERENCE or CHANGE. Do not specify FASTSWITCH NO. SHRLEVEL NONE does not materialize those changes.

Pending limit-key changes also materialize with REFERENCE or CHANGE. Pending changes to an index are not all cleared by a table-space REORG in every case — REORG INDEX materializes pending changes that apply to that index. Empty PBG partitions are not dropped by this materializing REORG.

1
REORG TABLESPACE DB1.TS1 SHRLEVEL REFERENCE

Compression, copies, and other effects

If the space is COMPRESS YES, reload compresses the data. KEEPDICTIONARY reuses the current dictionary; otherwise REORG builds a new one. With DATA CAPTURE CHANGES, an existing dictionary is written to the log. COMPRESS_USED in SYSTABLEPART records which dictionary type was used.

REORG TABLESPACE always allocates new Db2-managed data sets unless you specify REUSE. That is how REORG can also encrypt or decrypt with z/OS DFSMS data set encryption: new data sets pick up the current key label.

You cannot REORG DSNDB07. INITCDDS YES is a special data-sharing GDPS path that copies compression dictionaries into the CDDS without reorganizing.

Explain It Like I'm Five

Imagine a toy box where cars were dumped in randomly. REORG TABLESPACE dumps everything out, lines the cars up the way the label on the box says (clustering), and puts them back so there are no weird empty pockets. SHRLEVEL NONE means nobody else may play while you refill the box. REFERENCE means friends can look but not add toys until you swap in the neat box. CHANGE means friends keep playing with the old box while you build a neat twin; a mapping notebook remembers which car moved where so you can copy their last-minute moves, then you swap boxes in one quick moment.

Exercises

1. Write a REORG TABLESPACE job for one partition of a PBR space with SHRLEVEL CHANGE, STATISTICS, and COPYDDN.
2. Explain why SCOPE PENDING is a good default after an ALTER that left AREOR.
3. List the phases you expect for SHRLEVEL NONE versus SHRLEVEL CHANGE.
4. Ask your DBA whether mapping tables are user-created or implicit, and which REORG_MAPPING_DATABASE value the subsystem uses.
5. Predict whether SHRLEVEL NONE will apply a pending MAXPARTITIONS change (it will not).