Chapter 30 Quiz: Partitioning Strategies

Multiple Choice Questions

Question 1

On DB2 for z/OS, what does the ENDING AT clause in a partition definition specify?

A. The first value that belongs to the next partition B. The highest value that the partition can contain (the limit key) C. The date when the partition will be automatically dropped D. The maximum number of rows the partition can hold

Answer: B. The ENDING AT clause specifies the limit key — the highest value the partition can contain. Any row with a partitioning key value less than or equal to this value (and greater than the previous partition's limit key) is placed in this partition.

Question 2

Which statement about hash partitioning on DB2 LUW is TRUE?

A. Hash partitioning supports partition elimination for range queries B. Hash partitioning requires a date column as the partition key C. Hash partitioning distributes rows evenly but only supports partition elimination for equality predicates D. Hash partitioning is available on both z/OS and LUW platforms

Answer: C. Hash partitioning uses a hash function to distribute rows evenly across partitions. The optimizer can eliminate partitions only when an equality predicate is specified on the hash key — range predicates cannot be used for elimination because the hash function does not preserve ordering.

Question 3

What is the primary advantage of using ALTER TABLE ... DETACH PARTITION instead of DELETE FROM to remove old data?

A. DETACH generates detailed audit logs for compliance B. DETACH is a metadata-only operation that avoids log overhead and index maintenance C. DETACH automatically creates a backup of the deleted data D. DETACH preserves row-level locks for concurrent readers

Answer: B. Detaching a partition is a metadata operation that completes in seconds regardless of data volume. It generates no log records for individual row deletions and requires no per-row index maintenance. The DELETE approach would generate log records for every deleted row and trigger index updates for each deletion.

Question 4

In a DPF environment, what is a "collocated join"?

A. A join that occurs entirely in the buffer pool without disk I/O B. A join between two tables distributed on the same key where matching rows reside on the same partition C. A join between two tables that share the same table space D. A join that uses a hash join algorithm instead of nested loop

Answer: B. A collocated join occurs when two tables are distributed on the same distribution key and joined on that key. Because matching rows are guaranteed to reside on the same database partition, no data needs to be transferred between partitions — the join is performed locally on each partition.

Question 5

Which query pattern DEFEATS partition elimination on a table partitioned by TRANS_DATE?

A. WHERE TRANS_DATE = '2026-01-15' B. WHERE TRANS_DATE BETWEEN '2026-01-01' AND '2026-03-31' C. WHERE YEAR(TRANS_DATE) = 2026 D. WHERE TRANS_DATE >= '2026-01-01' AND TRANS_DATE < '2026-04-01'

Answer: C. Applying a function (YEAR()) to the partition key column prevents the optimizer from mapping the predicate to specific partition boundaries. The optimizer would need to evaluate YEAR(TRANS_DATE) = 2026 for every row rather than determining partition ranges at compile time. Options A, B, and D all allow partition elimination.

Question 6

On z/OS, what is the key difference between DPSI and NPSI?

A. DPSI is faster for all queries; NPSI is deprecated B. DPSI is partitioned like the table data, enabling partition independence; NPSI is a single global index C. DPSI only supports unique indexes; NPSI supports non-unique indexes D. DPSI requires more disk space than NPSI for the same data

Answer: B. A Data-Partitioned Secondary Index (DPSI) is physically partitioned to match the table's partitions, one index partition per data partition. This enables partition-level operations (REORG, RECOVER) on both data and index partitions independently. An NPSI is a single, global index — faster for single-row lookups without the partition key but it breaks partition independence.

Question 7

What happens after you ATTACH a partition on DB2 LUW?

A. The data is immediately available for all queries with no further action B. The table enters SET INTEGRITY PENDING state and requires validation C. All indexes are automatically rebuilt during the ATTACH operation D. The attached data is copied into the partitioned table's storage

Answer: B. After an ATTACH operation, the table is placed in SET INTEGRITY PENDING state. You must run SET INTEGRITY FOR <table> IMMEDIATE CHECKED to validate the attached data against check constraints and to update indexes. Until SET INTEGRITY is run, the table is not fully accessible.

Question 8

For Meridian National Bank's TRANSACTION_HISTORY table with 15 million rows per month and a 7-year retention period, which partitioning granularity is most appropriate?

A. Daily — for maximum partition elimination B. Monthly — balanced partition count and management overhead C. Yearly — simplest to manage D. Quarterly — compromise between daily and yearly

Answer: B. Monthly partitioning produces 84 partitions for a 7-year window — well within DB2's limits and small enough for efficient partition-level maintenance. Daily would produce 2,555 partitions (excessive), yearly would produce only 7 partitions (too few for effective elimination on monthly queries), and quarterly would produce 28 partitions (acceptable but less aligned with the monthly query pattern).

Question 9

When using ROTATE PARTITION FIRST TO LAST on z/OS, what happens to the data in the first partition?

A. The data is moved to the last partition position B. The data is archived to a separate table automatically C. The data is discarded — the partition is emptied D. The data remains in place but the partition number changes

Answer: C. The ROTATE PARTITION FIRST TO LAST operation empties the first partition and repositions it at the end with a new limit key. The data in the first partition is discarded. If you need to preserve the data, you must unload or copy it before executing the ROTATE.

Question 10

Which z/OS utility command performs a REORG on only partition 84 of table space TSHIST?

A. REORG TABLESPACE MERIDIANDB.TSHIST PARTITION 84 B. REORG TABLESPACE MERIDIANDB.TSHIST PART 84 C. REORG TABLE MERIDIAN.TRANSACTION_HISTORY PART 84 D. REORG INDEX ALL ON MERIDIANDB.TSHIST PART 84

Answer: B. The z/OS REORG utility operates at the table space level and uses the PART keyword (not PARTITION) to specify a single partition. The correct syntax is REORG TABLESPACE <dbname>.<tsname> PART <number>.

Short Answer Questions

Question 11

Explain why including the partition key in the primary key constraint is important for partitioned tables. What happens if the primary key does not include the partition key?

Answer: If the primary key does not include the partition key, the unique index enforcing the primary key must be a global (non-partitioned) index — an NPSI on z/OS or a non-partitioned index on LUW. This global index breaks partition independence: partition-level REORG, RECOVER, or DETACH operations must also maintain the global index. Including the partition key in the primary key allows the unique index to be partitioned, preserving full partition independence.

Question 12

A developer reports that a query against TRANSACTION_HISTORY is scanning all 84 partitions even though it filters on the partition key. The query is:

SELECT * FROM MERIDIAN.TRANSACTION_HISTORY
WHERE CAST(TRANS_DATE AS VARCHAR(10)) = '2026-01-15';

Why is partition elimination not working, and how would you fix it?

Answer: The CAST function applied to the partition key column (TRANS_DATE) prevents the optimizer from mapping the predicate to specific partition boundaries. Any function applied to the partition key column defeats partition elimination. The fix is to remove the function and use a direct comparison: WHERE TRANS_DATE = '2026-01-15'. If the application requires string comparison, the conversion should be applied to the literal, not the column: WHERE TRANS_DATE = DATE('2026-01-15').

Question 13

Describe the trade-off between using DPSI and NPSI for a secondary index on ACCOUNT_ID for the TRANSACTION_HISTORY table partitioned by TRANS_DATE.

Answer: A DPSI on ACCOUNT_ID preserves partition independence — each partition has its own index partition, enabling independent REORG, RECOVER, and COPY operations. However, a query filtering only on ACCOUNT_ID (without TRANS_DATE) must probe every index partition (multi-probe scan), which is slower than a single index lookup. An NPSI on ACCOUNT_ID provides fast single-probe lookups for any ACCOUNT_ID query but breaks partition independence — any partition-level operation on the data requires corresponding maintenance on the global NPSI. The choice depends on whether operational independence (DPSI) or query performance for non-partition-key lookups (NPSI) is more important for the workload.

Question 14

Why is loading data into a staging table and then ATTACHing it as a partition more efficient than LOADing directly into a partitioned table on LUW?

Answer: When loading into a staging table, the LOAD utility only needs to build indexes defined on the staging table (which typically has no indexes or minimal indexes). After attaching the staging table as a partition, the SET INTEGRITY ... IMMEDIATE CHECKED phase builds only the index entries for the newly attached data in the context of the partitioned table's indexes. This avoids the overhead of maintaining the full partitioned table's index structure during the bulk load, and the staging table load can run without any locking impact on the partitioned table.