Archive old data in a large table in DB2

Question

I have a very large non-partitioned table with ~4,500,000,000 records: ODS.LARGE_TABLE.

The table has stored 3 months of data with ~50,000,000 rows per day, therefore resulting total of ~4,500,000,000 records.

My objective is to archive the records in the oldest month (i.e. Sep) to another new table ODS.LARGE_TABLE_old.

1.) My first approach is to load the old records to ODS.LARGE_TABLE_old, then delete them in ODS.LARGE_TABLE. The loading step is fine but the delete step is problematic, since deleting such many records in this large table will cause transaction log full error.

I knew that one can alter table to not logged initially to avoid this problem, but it seems to be risky in my situation since any interruption of the delete operation will make the table inaccessible and I dont have any backup copy of this large table.

2.) My second approach may sound stupid but it does not involve of delete operation. I first rename ODS.LARGE_TABLE to ODS.LARGE_TABLE_tmp, then create 2 new tables: ODS.LARGE_TABLE and ODS_LARGE_TABLE_old, I load the old records to ODS.LARGE_TABLE_old from ODS.LARGE_TABLE_tmp; load the rest of the records (i.e. Oct & Nov) to ODS.LARGE_TABLE from ODS.LARGE_TABLE_tmp, finally, I drop the table ODS.LARGE_TABLE_tmp.

I think the drawback with this approach is that the 2nd load operation is taking me so much time, since it involves of loading data with 2 months.

My question is that are there any best practices/ways to archive old records from a large table in DB2 LUW? I am finding an efficient way to do so such that I can apply the same approach in all of my large tables.

Thanks in advance.

Answer 1

You may try ADMIN_MOVE_TABLE routine to convert your table to a range partitioning one online. Use COPY_USE_LOAD option to speed up the process and avoid excessive logging.

Answer 2

I've used something similar to your approach:

declare c1 cursor for select ... from ODS.LARGE_TABLE where ts <= ...; -- data to remove
load from c1 of cursor insert into ODS.LARGE_TABLE_old;

create table ODS.LARGE_TABLE_tmp like ODS.LARGE_TABLE;
declare c2 cursor for select ... from ODS.LARGE_TABLE where ts > ...; -- data to keep
load from c2 of cursor insert into ODS.LARGE_TABLE_tmp;

declare c3 cursor for select ... from ODS.LARGE_TABLE_tmp;
load from c2 of cursor replace into ODS.LARGE_TABLE;
drop table ODS.LARGE_TABLE_tmp;

Have you considered changing the table to be range partitioned?