Creating an archive table

You can create an archive table to manage historical data for an existing table. An archive table stores deleted rows from another table. That base table is called an archive-enabled table.

Before you begin

Check that the table for which you want to create an archive table meets the requirements that are specified in the description of the ENABLE ARCHIVE clause in ALTER TABLE statement.

About this task

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 built-in global variable.
  • You can store archive tables on a lower-cost device to reduce operating costs.

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.

Procedure

To create an archive table:

  1. Create a table with the same columns as the table for which you want to archive data.
    For a complete list of requirements for archive tables, see the information about the ENABLE ARCHIVE clause in ALTER TABLE statement.
  2. Designate the original table as an archive-enabled table by issuing an ALTER TABLE statement with the ENABLE ARCHIVE clause. In that clause, specify the table that you created in the previous step as the archive table.
  3. If you want rows to be automatically archived, set the built-in global variable SYSIBMADM.MOVE_TO_ARCHIVE to Y or E.
    When this built-in global variable is set to Y or E, Db2 automatically moves deleted rows to the archive table.
  4. If you want to remove the relationship between the archive-enabled table and the archive table, issue the ALTER TABLE statement for the archive-enabled table and specify the DISABLE ARCHIVE clause.
    Both tables will still exist, but the relationship is removed.