This assumes implementation of table-based partitioning that provides for reuse of purged partitions. Such a scheme must be organized and coordinated with backup and recovery processes. Rotation of partitions will result in the physical partition numbers (corresponding to the physical data sets underlying the design) to no longer match the logical partition numbers. This is detailed in the IBM manuals.
Another database design relates to insert hot spots in transactional tables that are frequently referenced. Here, you want to avoid insert hot spots by spreading new rows across the table space or partition rather than clustering them at the end of the pageset, while still maintaining a partitioning scheme that allows for data purging based on age.
You can accomplish this using table-based partitioning, where the partitioning key is a surrogate key whose value is randomly determined at the time of insert. Another option is using an insert trigger. The effect is to spread row inserts evenly across partitions. Try to ensure that data access paths aren’t affected by this scheme. One possible danger involves Data Partitioned Secondary Indexes (DPSIs), which SQL uses to access the table. Without including a predicate specifying the partition key, SQL may cause DB2 to scan each partition of the DPSI to find qualifying rows. Because of this issue the DBA usually implements the aforementioned scheme without DPSIs.
Input Hot Spots
Schemes that distribute rows to avoid hot spots tend to increase synchronous I/Os while decreasing the ability of DB2 to perform prefetch operations. Data availability may be a problem if transactions (commit-to-commit) are long since many pages will be locked. You should design transactions to have relatively short units of recovery. Such short transactions will have a higher proportion of commits over time than longer transactions, so the amount of work done at commit (such as externalizing log records) may affect system performance.
One additional advantage of relatively short transactions is that they hold claims on pagesets for shorter periods, allowing utilities to drain these claims and to process without failing.
Partitioning where rows are inserted at end-of-partition will be friendlier to SQL statements accessing blocks of rows with similar key values. Data availability improves as such rows physically inserted on the same page will limit total pages locked. Throughput is also relatively fast because inserts won’t compete with each other.
Data Purge Issues
You can implement a rotating partition scheme to purge or archive old data. Assuming you have no need to archive data, a partitioning scheme based on the purge criteria (usually date) works well. In our example where we partitioned a table by month, we can easily empty the oldest partition using load utility statements, such as the following, having no input data:
LOAD REPLACE ... PART <partition number>