Empty Table

[alex250374]

Hello world,

(excuse my English) I have to empty a daily a table that has 50,000,000 record and that stores data by "date". Whenever the empties must remain only the last 3 days of data.
With DELETE
(DELETE FROM SCHEMA.TABLE WHERE DATE(AGG_TIMESTAMP) < DATE(CURRENT TIMESTAMP) - 3 DAY;).
The trasaction log are filled and delete fails. Trauncate the table can not be conditioned.

how can I do?

[docdiesel]

Hi,

the easiest and most effective way is to split the table into three and rejoin them as one view:

• Create a table for each day, e.g. mydata20080928
• Create a view as inner join over the three last days tables
• If a new day/table adds, drop the old view and replace it with an updated one.
• Drop the old days tables when not needed any more.

The "drop table" is done within a fraction of the time you need for "delete where ...". This also gives you the possibility to load the next days data (load=no logs, means less i/o and less time) without danger for the already exsiting data.

Regards

Doc Diesel

[alex250374]

Hi,

the easiest and most effective way is to split the table into three and rejoin them as one view:

• Create a table for each day, e.g. mydata20080928
• Create a view as inner join over the three last days tables
• If a new day/table adds, drop the old view and replace it with an updated one.
• Drop the old days tables when not needed any more.

The "drop table" is done within a fraction of the time you need for "delete where ...". This also gives you the possibility to load the next days data (load=no logs, means less i/o and less time) without danger for the already exsiting data.

Regards

Doc Diesel

thanks for your answer Doc Diesel,

but I can not divide the table into 3 parts per requirements associated application that you access.

there's another way ?

[sakumar9]

You can truncate the table. By the way, which DB2 are you running?

As such, there is no command like truncate in DB2. But I am sure you would like to know how it can be done in DB2. For that, I would suggest you to refer to the sample: sqllib/samples/admin_scripts/truncate.db2 script.

This will surely solve your problem.

Regards
-- Sanjay

[sakumar9]

Oh I am sorry, you can truncate the table conditionally.

I would suggest you to use DB2 partitioning features like Range partitioning, MDC etc. This will boost your performance drastically. Just in fractions of seconds, you can actually delete huge data.

Let me know if you need more details regarding this. I would be more than happy to share.

Regards
-- Sanjay

[alex250374]

Oh I am sorry, you can truncate the table conditionally.

I would suggest you to use DB2 partitioning features like Range partitioning, MDC etc. This will boost your performance drastically. Just in fractions of seconds, you can actually delete huge data.

Let me know if you need more details regarding this. I would be more than happy to share.

Regards
-- Sanjay

Hi Sanjay, Thanks for your response,
the partitioning but I had already thought ... how do I partition it for days? and then, as I organize the process that empty table? I run the drop partitions for more than three days old and at the same time they create for the other days? I feel somewhat complex as a solution ...

[sushanth bobby]

There is something called ROTATING PARTITIONS which will delete the data automatically.