Contributed by Anja Jessica Paessler
Organizations have long had a need to save old data, and this is becoming more of an urgent must do requirement as a result of recent regulatory and compliance changes. With ever increasing organizational requirements to save historical data, querying and managing tables that contain a large amount of data is a common problem that is becoming increasingly challenging. One way to address the requirements for saving historical data is to move or archive inactive data to a separate archive, which can be on a less expensive platform. DB2 for z/OS provides a basic row-based archiving solution using SQL, referred to as archive transparency.
Here’s how you can benefit from archive transparency:
1. The two table approach
DB2 11 for z/OS provides basic archive and retrieval functions using SQL through a two table approach, with a single table image to users. The table that contains the current data is called an archive-enabled table, and the table that holds the pre-existing rows is called an archive table. DB2 can automatically move rows deleted from an archive-enabled table to the associated archive table.
2. No changes to existing applications
You don’t need to change existing applications to include or exclude archive table data. Instead, you can control the scope of SQL queries with a global variable. Similarly you can control whether deleted rows are moved to the archive table with a global variable rather than modifying the original data change statements.
3. Easy access and management
DB 11 for z/OS provides easy access to both current and archived data. There is no requirement for separate authorization on the archive table for a user that accesses data in the archive table indirectly by referencing the archive-enabled table (retrieval or deletions).
4. Improved performance
One of the benefits of archiving is improved performance for queries that only access active data. Tables become smaller; indexes will have less levels and the overall number of getpages will decrease.
5. Additional considerations when using transparent archiving
Tables can be defined as an archive-enabled table with an associated archive table to store historical data. Defining a table as an archive-enabled table results in package invalidation or dynamic statement cache invalidations that reference the table. Archive transparency is mutually exclusive with temporal tables. A system-period temporal table or application-period temporal table cannot be defined as an archive-enabled table or archive table.
Want to learn more about archive transparency and DB 11 on z/OS? Read the IBM Redbooks publication Managing Ever Increasing Amounts of Data with DB2 for z/OS Using Temporal Data Management, Archive Transparency, and the DB2 Analytics Accelerator.