Chapter 30 Exercises: Partitioning Strategies

Exercise 30.1: Create a Range-Partitioned Table on z/OS

Objective: Implement range partitioning for a financial transaction table on DB2 for z/OS.

Scenario: Meridian National Bank's compliance team requires a new AUDIT_LOG table that stores audit events for seven years with monthly granularity.

Tasks:

  1. Write the DDL to create a table space AUDITTS in database MERIDIANDB with the following specifications: - DSSIZE 8G - BUFFERPOOL BP32K - SEGSIZE 64 - MAXPARTITIONS 128

  2. Create the MERIDIAN.AUDIT_LOG table with the following columns: - AUDIT_ID (BIGINT, identity, not null) - EVENT_TIMESTAMP (TIMESTAMP, not null, partition key) - USER_ID (VARCHAR(30), not null) - EVENT_TYPE (CHAR(10), not null) - OBJECT_SCHEMA (VARCHAR(128)) - OBJECT_NAME (VARCHAR(128)) - SQL_TEXT (VARCHAR(2000)) - RESULT_CODE (INTEGER)

  3. Define monthly partitions from January 2020 through December 2026 (84 partitions).

  4. Create a DPSI on (USER_ID, EVENT_TIMESTAMP) and another on (EVENT_TYPE, EVENT_TIMESTAMP).

Expected output: Complete DDL statements ready for execution on z/OS.

Exercise 30.2: Create a Range-Partitioned Table on LUW

Objective: Implement the same audit log table on DB2 LUW with ATTACH/DETACH capability.

Tasks:

  1. Create the meridian.audit_log table with equivalent columns using LUW syntax.
  2. Define monthly partitions with explicit STARTING and ENDING clauses.

  3. Create both partitioned and non-partitioned indexes: - Partitioned index on (event_timestamp, user_id) - Non-partitioned index on (user_id)

  4. Write a script to detach the January 2020 partition and attach a new January 2027 partition.

Expected output: DDL and DML scripts with proper SET INTEGRITY handling.

Exercise 30.3: Partition Elimination Verification

Objective: Verify that partition elimination is occurring for critical queries.

Tasks:

  1. Write an EXPLAIN statement for the following query against the monthly-partitioned TRANSACTION_HISTORY: sql SELECT ACCOUNT_ID, SUM(AMOUNT) AS TOTAL FROM MERIDIAN.TRANSACTION_HISTORY WHERE TRANS_DATE BETWEEN '2026-01-01' AND '2026-03-31' GROUP BY ACCOUNT_ID;

  2. Query the PLAN_TABLE (z/OS) or EXPLAIN tables (LUW) to determine: - Which partitions are accessed? - How many partitions are eliminated? - What access method is used within the qualifying partitions?

  3. Rewrite the following query to enable partition elimination (it currently does NOT achieve elimination): sql SELECT * FROM MERIDIAN.TRANSACTION_HISTORY WHERE YEAR(TRANS_DATE) = 2026 AND MONTH(TRANS_DATE) = 1;

  4. Explain why the original query defeats partition elimination and why your rewrite fixes it.

Exercise 30.4: Rolling Window Implementation

Objective: Design and implement a complete rolling window lifecycle for the TRANSACTION_HISTORY table.

Scenario: It is January 1, 2027. The table currently has 84 monthly partitions from January 2020 through December 2026. You need to: - Archive the January 2020 data. - Add a January 2027 partition.

Tasks (z/OS):

  1. Write the UNLOAD utility statement to extract January 2020 data before rotation.
  2. Write the ALTER TABLE ... ROTATE PARTITION statement.
  3. Write the RUNSTATS utility statement for the new (last) partition.
  4. Write the COPY utility statement for the new partition.

Tasks (LUW):

  1. Write the ALTER TABLE ... DETACH PARTITION statement.
  2. Write the EXPORT command to archive the detached table.
  3. Write the DDL to create a staging table for January 2027 data.
  4. Write the ALTER TABLE ... ATTACH PARTITION statement.
  5. Write the SET INTEGRITY statement.
  6. Write the RUNSTATS command for the newly attached partition.

Exercise 30.5: Hash Partitioning and DPF Design

Objective: Design a DPF configuration for Meridian's digital banking analytics workload.

Scenario: The analytics team needs to distribute a CUSTOMER_360 table across four database partitions for parallel query processing.

Tasks:

  1. Create a database partition group with four partitions (0, 1, 2, 3).
  2. Create a table space in this partition group.

  3. Create the CUSTOMER_360 table with the following columns: - CUSTOMER_ID (BIGINT, not null) - CUSTOMER_NAME (VARCHAR(100)) - TOTAL_DEPOSITS (DECIMAL(15,2)) - TOTAL_LOANS (DECIMAL(15,2)) - ACCOUNT_COUNT (INTEGER) - LAST_TRANSACTION_DATE (DATE) - RISK_SCORE (DECIMAL(5,2)) Distribution key: CUSTOMER_ID

  4. Create a TRANSACTION_SUMMARY table distributed by CUSTOMER_ID with monthly range partitioning on SUMMARY_DATE. Explain why using CUSTOMER_ID as the distribution key enables collocated joins with CUSTOMER_360.

  5. Write a query that joins these two tables and explain whether it is a collocated or non-collocated join.

Exercise 30.6: Partition Monitoring and Health Check

Objective: Build a monitoring script that identifies partition imbalances and maintenance needs.

Tasks:

  1. Write a query for z/OS that reports the following for each partition of TRANSACTION_HISTORY: - Partition number - Number of active pages - Allocated space in MB - Percentage of total table space used by this partition - Whether the partition is in REORG-pending status

  2. Write the equivalent query for LUW using SYSCAT.DATAPARTITIONS and MON_GET_TABLE.

  3. Define thresholds for partition imbalance alerts: - Flag any partition that is more than 3x the average partition size. - Flag any partition with fewer than 100 rows (potentially empty or misconfigured).

  4. Write a stored procedure (LUW) that generates an alert report showing imbalanced partitions.

Exercise 30.7: Partitioning Anti-Pattern Analysis

Objective: Identify and resolve partitioning design mistakes.

Scenario: A junior DBA created the following partitioned table:

CREATE TABLE meridian.daily_balances (
    balance_date    DATE NOT NULL,
    account_id      BIGINT NOT NULL,
    closing_balance DECIMAL(15,2),
    opening_balance DECIMAL(15,2)
)
PARTITION BY RANGE (balance_date)
(
    -- Daily partitions for 10 years = 3,650 partitions
    PARTITION d_20170101 STARTING ('2017-01-01') ENDING ('2017-01-01') INCLUSIVE,
    PARTITION d_20170102 STARTING ('2017-01-02') ENDING ('2017-01-02') INCLUSIVE,
    -- ... (3,648 more daily partitions)
    PARTITION d_20261231 STARTING ('2026-12-31') ENDING ('2026-12-31') INCLUSIVE
);

Tasks:

  1. Identify at least three problems with this design.
  2. Propose an improved partitioning scheme with justification.
  3. Write the DDL for the improved design.
  4. Explain how the improved design handles a query like: sql SELECT account_id, closing_balance FROM meridian.daily_balances WHERE balance_date = '2026-03-15';
  5. Calculate the partition elimination benefit for the original vs. improved design for the query: sql SELECT account_id, AVG(closing_balance) FROM meridian.daily_balances WHERE balance_date BETWEEN '2026-01-01' AND '2026-03-31' GROUP BY account_id;