Friday, January 10, 2014

Using hidden columns for range partitioning (Temenos T24)

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.

3 comments:

Dick Rietveld said...

Yes, those hidden columns are interesting. I wonder how they get in your way when you try a "load from cursor". Anybody did some experimenting yet?

Henrik Loeser said...

Rick,
can you details what you mean with "get in your way"? In a SELECT you can explicitly list hidden columns, for the LOAD command there are modifiers for generated columns and for hidden columns.

Henrik

Dick Rietveld said...

Suppose you're right. I was thinking about my nice little script in which I can generate a "declare cursur select * from ..." without thinking twice. As soon as I get a client who uses hidden columns my scripts will not work any more..
So, it gets in my way :)

LinkWithin

Related Posts with Thumbnails