In one of my post regarding performance tuning for Temenos T24 on DB2 I hinted that a hidden column could be used to range partition a table. There have been questions on how to do it and what to use. As always, it depends... ;-) Below I give you some examples of how it could be done, not only for Temenos T24.
I already introduced you to the concept of implicitly hidden columns some time back. DB2 allows to hide and unhide columns, thereby making it possible to have columns not showing up in the default result set. That feature can be used to alter the table layout (database schema) without impacting the application. Here, in the examples below, range partitioning is introduced with the hidden column as foundation for the data ranges.
CREATE TABLE ACCOUNT
(
RECID VARCHAR(10) NOT NULL PRIMARY KEY,
D DATE IMPLICITLY HIDDEN DEFAULT CURRENT DATE,
XMLRECORD XML
)
PARTITION BY RANGE(D)
(STARTING FROM '01.01.2012' ENDING AT '31.12.2014' EVERY (3 MONTHS));
For the ACCOUNT table we have the regular RECID and XMLRECORD columns and then "sneak in" a column D. Because RECID and D won't be updated they come first. The hidden column D has a default value, so that automatically the current date is inserted. The column is also used to define data ranges with each range covering 3 months.
The next example uses a different approach. An identity column ANYID is introduced and for new rows we would cycle through the specified set of values. This would evenly distribute new rows across all ranges.
CREATE TABLE ACCOUNT
(
RECID VARCHAR(10) NOT NULL PRIMARY KEY,
ANYID SMALLINT NOT NULL
GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY
1, MINVALUE 1, MAXVALUE 10,
CYCLE, CACHE 10000, NO ORDER) IMPLICITLY HIDDEN,
XMLRECORD XML INLINE LENGTH 32000
)
PARTITION BY RANGE(ANYID) (STARTING(1) ENDING (10) EVERY 1);
It is worth pointing out that such an approach should always be tested first before moving it into production.