Database Administration: Designing and Changing Db2 13 Objects
What this domain tests
This domain tests the DBA's ability to create, alter, and maintain Db2 13 for z/OS database objects: storage groups, universal table spaces, indexes, large objects, temporal and clone tables, and schema changes that remain online. Expect questions that force a design choice, not just syntax recall.
Core rules and concepts
- Universal table spaces (UTS) are the practical default. A UTS is either partition-by-growth (PBG) or partition-by-range (PBR). PBG suits unpredictable growth; PBR suits range-based purging and partition-level utility work.
- DSSIZE sets the maximum size of each partition or data set. MAXPARTITIONS limits PBG growth and defines the partition count for PBR. Raising MAXPARTITIONS is a PBG operation; PBR grows by adding partitions with new limit keys.
- Page size (4K, 8K, 16K, 32K) affects row length limits and space use. Choose the smallest page size that comfortably holds the row and leaves room for updates.
- Compression, inline LOBs, clone tables, system-period temporal tables, and hash organization are design tools, but each changes utility and recovery behavior. A clone table is exchanged with
EXCHANGE DATA; a temporal table keeps history rows and needs careful space planning. - Schema change often leaves objects in REORG-pending or advisory-REORG status. Check
SYSIBM.SYSTABLESPACEandSYSIBM.SYSINDEXESstatus columns after DDL.
Worked example
A 2 TB order table is purged by month and queried mainly by order date. Choose PBR with a monthly limit key, DSSIZE 4 G, and a partitioning index on order date. Add next month's partition before the load, then run REORG at partition level. Reasoning: range deletes become partition-level operations, utility scope is smaller, and queries can use partition elimination. PBG would avoid limit-key maintenance but would not give clean monthly purges.
Traps that cost marks
Assuming every table space can be converted in place; ignoring page-size limits; treating MAXPARTITIONS as alterable for PBR in the same way as PBG; and forgetting that DDL that changes row format can require REORG before the object is usable.
Key points
- UTS with PBG or PBR is the design baseline.
- DSSIZE and MAXPARTITIONS must be chosen together.
- PBR supports partition elimination and partition-level utilities.
- Check pending statuses after every schema change.
- Clone and temporal tables change recovery and space planning.