Date roll over functionality

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/developerwor...1_adamache.html

Abhishek Srivastava wrote:
[color=blue]
> 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.[/color]

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

The UNION ALL method is described here:

http://www7b.boulder.ibm.com/dmdd/l...0202zuzarte.pdf

Serge Rielau wrote:[color=blue]
> 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 :-)
>
>[/color]

... 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

Serge Rielau wrote:[color=blue]
> .. and in the full disclosure report of the TPC-C result.[/color]

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