Cleaning a partitioned table

[RonvdP]

We have a DB2-table in a datawarehouse. It contains history for 24 months. Every month contains between 10 and 12 million rows. Monthly the oldest month in the table is cleaned. So , november 2008 the data from november 2006 is cleaned, deleted.
The table is partitoned, but we are not allowed to use rotating partitions in prodcutions.
How do we clean the table?
First we unload the whole table and then we reload the table with all the data without the month that must be deleted. This takes a long time.
Does anyone have a better solution, for instance just emptying the oldest partition? How do I do that?

 

[fredericofonseca]

depends on your OS and your DB2 version.

Version 9 (for Unix/Windows/Linux) have a ALTER TABLE DETACH PARTITION partition-name INTO table-name1 that will allow you to do that easily. The new table can then be dropped.

From the manual.
ALTER TABLE DETACH PARTITION partition-name INTO table-name1

Detaches the data partition partition-name from the altered table, and uses the data partition to create a new table named table-name1. The data partition is logically attached to the new table without any data movement. The specified data partition cannot be the last remaining partition of the table being altered

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Truusvlugindewind]

the table is partitioned" is not enough info. On what column? How are partitions devided over tablespaces.
Things like that we need to know before we can answer your question.