Support for archive tables
In Db2 11, Db2 can automatically insert rows that are deleted from one table into a separate table that is called an archive table. Archive tables are useful for managing historical data.
You can specify that you want a table to use an archive table by specifying the new ENABLE ARCHIVE clause on the ALTER TABLE statement. The original table is called an archive-enabled table. You then use a global variable (SYSIBMADM.MOVE_TO_ARCHIVE) to control whether Db2 inserts rows that are deleted from this table into the associated archive table.
When you query an archive-enabled table, you can indicate whether the query considers the rows in the archive table. You also use a global variable (SYSIBMADM.GET_ARCHIVE) to indicate this preference. Therefore, you can easily change the query to include or exclude archive table data without having to update the SQL.
Archive tables provide the following benefits:
- Db2 can manage historical data for you. You do not have to manually move data to a separate table.
- Because rows that are infrequently accessed are stored in a separate table, you can potentially improve the performance of queries against the archive-enabled table.
- You can modify queries to include or exclude archive table data without having to change the SQL statement and prepare the application again. Instead, you can control the scope of the query with a global variable.
- You can store archive tables on a lower-cost device to reduce operating costs.