Adding Range Partitions to a Near-Full PBR Table Space
Your shop runs Db2 13 for z/OS with a 4K-page PBR table space SALES.FACT on TRANS_DATE. It has MAXPARTITIONS 240, DSSIZE 8G, 240 partitions, and the last partition is defined ENDING AT ('2025-12-31') with no MAXVALUE. Growth is 20 million rows and 9 GB per month; partition 240 holds only 11 days of data and has reached 92% of DSSIZE. The batch window is 3 hours, but a full REORG of the 900 GB table space takes 9 hours. The table has a clustering index on TRANS_DATE and a LOB column in an auxiliary table space. Applications use static SQL and expect partition elimination. The business wants 24 months of rolling data online, with older partitions archived. You must add 2026 and 2027 partitions without an outage, while keeping the clustering index and LOB auxiliaries consistent. The storage team can allocate new volumes only during the weekend.
1. What immediate risks does the 92%-full last partition create, and what DDL sequence would you use to add 2026 and 2027 partitions while minimizing outage?
Read the worked answer
The 92% figure is not just a capacity warning. A partition that reaches DSSIZE can fail inserts with a resource-unavailable condition, and because the last partition ends at 2025-12-31 rather than MAXVALUE, there is no catch-all range for rows after that date. That means inserts for January 2026 can fail even before the partition is physically full.
Start by checking MAXPARTITIONS and the LOB table space partition limits. If more partitions are needed, use ALTER TABLESPACE to raise MAXPARTITIONS online. Then add ranges with ALTER TABLE ADD PARTITION ... ENDING AT (...), adding enough future ranges in one or a few DDL operations rather than one month at a time. Adding partitions is a pending definition change, so plan a REORG TABLESPACE ... MATERIALIZE to make the new ranges usable. Do not assume the new partitions are immediately available to applications. Because a full REORG does not fit, materialize in smaller partition groups and use SHRLEVEL CHANGE where the drain can be tolerated. Take an inline copy or a separate image copy so the new partitions are recoverable.
2. How do you handle the LOB auxiliary table spaces and the indexes when the new partitions are added?
Read the worked answer
Treat the base table, LOB auxiliary objects, and indexes as one consistency unit. Check SYSIBM.SYSTABLEPART and the LOB table space definitions for MAXPARTITIONS and DSSIZE before the DDL; if the LOB table space cannot accept as many partitions as the base table, raise its limit first. Adding a base-table partition normally creates the corresponding LOB auxiliary structure, but you must verify that the LOB table space has enough room and that its partitions are materialized consistently.
For indexes, the partitioning index gains new partitions automatically, but any non-partitioning index is a separate object and may need a rebuild. If a non-partitioning index exists on a 900 GB table, a full rebuild can defeat the outage goal, so review whether it is still needed. After the DDL and materialization, check for pending states and run consistency checks. Include the new partitions in the copy strategy before applications write to them.
3. If the materialization REORG cannot fit in the 3-hour window, what phased plan would you propose?
Read the worked answer
Separate the work by urgency and by partition state. First add and materialize only the partitions needed to accept new data, such as 2026 and the immediately following ranges. Schedule those materialization REORGs as partition-level jobs, not a full-table REORG, and use SHRLEVEL CHANGE for the active ranges. For the older read-only ranges, use SHRLEVEL REFERENCE in off-peak windows or after archiving, because availability is less critical there. Do not let a single 200-partition REORG hold drains across the whole table space.
Build in lead time: add future ranges 12 to 18 months ahead so materialization can be split across several weekends. Monitor log consumption, copy data set allocation, and drain waits. If the business cannot tolerate any active-range drain, use online schema change facilities and test the materialization against a clone first.