Archive-enabled tables and archive tables

If you have a table that contains a significant amount of historical data that is not often referenced, consider creating archive tables. An archive table is a table that stores older rows from another table.

The original table is called an archive-enabled table. Db2 can automatically store rows that are deleted from an archive-enabled table in an associated archive table. When you query an archive-enabled table, you can specify whether you want those queries to include data from the archive table.

Archive tables have the following advantages:

  • 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.

To create an archive table, follow the instructions in Creating an archive table. When you create an archive table, use the SYSIBMADM.MOVE_TO_ARCHIVE built-in global variable to specify whether Db2 is to automatically archive any rows that are deleted from the archive-enabled table.

When you query an archive-enabled table, you can specify whether you want the query to consider rows in the archive table. You do not have to modify the SQL. Instead, you can control the scope of the query by using the SYSIBMADM.GET_ARCHIVE built-in global variable and the ARCHIVESENSITIVE bind option. To retrieve data from an archive able, set SYSIBMADM.GET_ARCHIVE to Y and bind the plan or package with ARCHIVESENSITIVE(YES).

The ARCHIVESENSITIVE bind option has no affect on the SYSIBMADM.MOVE_TO_ARCHIVE value.

You can later remove the relationship between the archive-enabled table and the archive table. To remove this relationship, issue the ALTER TABLE statement for the archive-enabled table and specify the DISABLE ARCHIVE clause. Both tables still exist, but the relationship is removed.