Table range partitioning has been available in DB2 z/OS since the early days of that product, and received a significant refresh in functionality in DB2 z/OS v8, but did not become available in DB2 LUW until DB2 LUW v9.1.
Table range partitioning is a data organization scheme in which table data is stored across multiple physical partitions (or ranges) based on the value in one of more table columns. Each data partition can be stored in separate tablespaces, or in the same tablespace, as desired. Table partitioning can improve performance of queries by eliminating large amounts of I/O for range based queries as only the partitions containing data in scope of the queries will need to be accessed.
One significant difference between the implementation of table range partitioning in DB2 LUW as opposed to DB2 z/OS is that in DB2 LUW, partitions can be named, and they can be attached or detached from tables as needed. This allows for easy roll-in or roll-out of data from a partitioned table via ALTER TABLE statements with either the ATTACH PARITION or DETACH PARTITION options.
The DETATCH PARITION option of an ALTER TABLE statement allows for a named partition of a table to be detached into a separate, standalone table. The separate table can then be dropped if the data is to be deleted or destroyed; it can be attached to another partitioned table that is acting as a history or archive table; or the data can be refreshed and then reattached back to the original table as a new partition containing new data.
Example of detaching and re-attaching a table partition:
Let’s say we have a table named SALES that is partitioned by year and month. We want to remove all the data for June 2012 (which resides in named partition JUN12) and replace it with data for June 2013 in a new partition. To accomplish this, we would perform the following steps:
1) Remove the June 2012 data by detaching it to a separate table named TEMPSALES with the following statement:
ALTER TABLE SALES
DETACH PARTITION JUN12 INTO TEMPSALES;
2) Replace the data in the TEMPSALES table with the new data for June 2013 with the following command:
LOAD FROM jun2013.txt OF DEL REPLACE INTO TEMPSALES;
3) Attach the TEMPSALES table, which contains the new data, to the SALES table as a new partition by executing the following statement:
ALTER TABLE SALES
ATTACH PARTITION JUN13
STARTING ‘2013-06-01’ ENDING ‘2013-06-30’
FROM TEMPSALES;
4) At this point, the new data is not yet accessible until a SET INTEGRITY statement is executed to update the indexes for the SALES table to include the new partition. This can be done with the following statement:
SET INTEGRITY FOR SALES
ALLOW WRITE ACCESS
IMMEDIATE CHECKED;
