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.
Best Answer
You may try ADMIN_MOVE_TABLE routine to convert your table to a range partitioning one online. Use
COPY_USE_LOADoption to speed up the process and avoid excessive logging.