Date roll over functionality

[Abhishek Srivastava]

Hello All,

I am very new to DB2. I am studying the feasibility of porting my
appliction to DB2.

Our appliction keeps 45 days of data. the data for the 46th day is to
be purged.

Currently we have created fragmentation of the table based on date.
every day we drop the fragment of 46th day.

Can someone advice me how will I create the same kind of fragmentation
logic on db2. Or some other efficient way of purging data for the 46th
day?

Also, can I create data fragements based on two fields? So that I have
fragments o n on field called status (which is used as a criteria in
most sql queries) and date (used for data purging)?

thanks for your help and patience.

regards,
Abhishek.

[Blair Adamache]

Have a look at MDC (Multi-dimensional clustering) in the manuals.
Another approach is to use free space options, and alter them to keep
the data clustered on disk. That approach is discussed briefly here:

http://www-106.ibm.com/developerwork..._adamache.html

Abhishek Srivastava wrote:

Hello All,

I am very new to DB2. I am studying the feasibility of porting my
appliction to DB2.

Our appliction keeps 45 days of data. the data for the 46th day is to
be purged.

Currently we have created fragmentation of the table based on date.
every day we drop the fragment of 46th day.

Can someone advice me how will I create the same kind of fragmentation
logic on db2. Or some other efficient way of purging data for the 46th
day?

Also, can I create data fragements based on two fields? So that I have
fragments o n on field called status (which is used as a criteria in
most sql queries) and date (used for data purging)?

thanks for your help and patience.

regards,
Abhishek.

[Serge Rielau]

You can use a partitioning view using UNION ALL.
Each base table holds 1 day.
I woudl propose using an integer presentation for the date column (not
null!) then add a check constraint for each table forcing the day.
You can insert, update and select from teh view. If you did it right the
plans will look very much like a normal select from any base table.
Performance impact will be minimal.
To roll-out data:
ALTER TABLE <basetabx> DROP CONSTRAINT chkdatex;
ALTER TABLE <basetabx> ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
ALTER TABLE <basetabx> ADD CONSTRAINT chkdatex CHECK (date = 20031209);
commit;

done :-)
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Blair Adamache]

The UNION ALL method is described here:

http://www7b.boulder.ibm.com/dmdd/li...202zuzarte.pdf

Serge Rielau wrote:

You can use a partitioning view using UNION ALL.
Each base table holds 1 day.
I woudl propose using an integer presentation for the date column (not
null!) then add a check constraint for each table forcing the day.
You can insert, update and select from teh view. If you did it right the
plans will look very much like a normal select from any base table.
Performance impact will be minimal.
To roll-out data:
ALTER TABLE <basetabx> DROP CONSTRAINT chkdatex;
ALTER TABLE <basetabx> ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
ALTER TABLE <basetabx> ADD CONSTRAINT chkdatex CHECK (date = 20031209);
commit;

done :-)

[Serge Rielau]

... and in the full disclosure report of the TPC-C result. So we know for
fact that it flies.
In terms of performance it is well within the range of what you'd expect
from true range partitioning as known in Informix or DB2 z/OS.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark Townsend]

Serge Rielau wrote:

.. and in the full disclosure report of the TPC-C result.

Funny - I didn't know the TPC-C required archiving of the 46th day of
data or any other date roll over type functionality ?