Clients are facing increased requirements to maintain historical data and even a history of all data changes. Some of these requirements are expressed in the form of government regulations where auditing and compliance inquiries require point in time analysis of data at any time in the past 10 years. Other clients have recognized the need to maintain a full change history in order to analyze trends, develop predictive models, and perform statistical analysis such as regression analysis. Many customers have created their own complex ETL processes, landing zones, and data repositories simply to maintain a complete change history of data originating from DB2 for System z. This complexity can be greatly reduced by leveraging time based data management (temporal) support within DB2 for z/OS and the DB2 Analytics Accelerator. DB2 for z/OS temporal support allows you to insert, update, delete, and query/snapshot data on demand in the past, the present, and future while keeping a complete history of what you knew and when you knew it.
When temporal versioning support is added on a DB2 for z/OS table, DB2 automatically tracks when changes (Inserts/Updates/Deletes) are made to the state of a table, automatically preserves deleted records in a temporal table with period timestamps, automatically maintains period timestamps for inserted records, allows for partitioning and archiving of the temporal data, and makes the data available online for analysis. Please see the presentation Temporal And IDAA.pptxView Details. DB2 for z/OS supports three kinds of temporal tables:
• System-period temporal table (STT) with SYSTEM_TIME period: DB2 transparently keeps a history of old rows that have been updated or deleted over time. With new constructs in the SQL language standard, users can go back in time and query the database at any chosen point in the past
• Application-period temporal table (ATT) with BUSINESS_TIME period: Applications supply dates or timestamps to describe the business validity of their data
• Bi-temporal table (BTT) with both ATT and STT features combined: Bi-temporal tables combine all the capabilities of system-period and application-period temporal tables. This combination enables applications to manage the business validity of their data while DB2 keeps a full history of any updates and deletes.
What makes temporal tables different from regular base tables is the period concept. A period is represented by a pair of date/time columns in DB2. The start column stores the start time of the period, and the end column stores the end time of the period. DB2 supports two kinds of periods: SYSTEM_TIME period and BUSINESS_TIME period.
• SYSTEM_TIME, sometimes referred to as "transaction time", tracks when changes (Inserts/Updates/Deletes) are made to the state of a table, such as when an insurance policy is modified or a loan is created. The SYSTEM_TIME period columns are populated and controlled by the system.
• BUSINESS_TIME, sometimes referred to as "valid time" or "application time", tracks the effective dates of certain business conditions, such as the terms of an insurance policy or the interest rate of a loan. The BUSINESS_TIME period columns are populated and controlled by the application.
With the DB2 Analytics Accelerator v5.1, the DB2 Analytics Accelerator has added support for the DB2 for z/OS v11 temporal table SYSTEM_TIME period (BUSINESS_TIME was supported starting in DB2 for z/OS v11 and the DB2 Analytics Accelerator v4 PTF3). In addition to being able to query active and temporal data on the DB2 Analytics Accelerator, temporal data can be archived to the DB2 Analytics Accelerator. Range partitioning the temporal table on the SYSTEM_END timestamp guarantees that all of its partitions except for the most recent partition are not changing, and thus can be archived onto the DB2 Analytics Accelerator. The DB2 Analytics Accelerator includes an online archiving functionality called the High Performance Storage Saver (HPSS). Using this functionality to move historical data into the DB2 Analytics Accelerator reduces the data volume in the DB2 for z/OS table; this also means less administration and less indexes. By employing the HPSS, both active and online historical data are always available to the user for analysis and auditing purposes-with near zero latency for online historical data queries. The DB2 for z/OS optimizer manages traffic and directs any queries that touch archived data to the DB2 Analytics Accelerator, while queries against strictly non-archived data follow the DB2 Analytics Accelerator's usual query routing criteria. This means there will be near zero CPU consumption associated with the analysis of the historical data.
Temporal table versioning support in DB2 + Analytics Accelerator HPSS archiving + Accelerator Only Tables can all be used to support regulatory compliance requirements and reporting. Versioning support helps maintain a history of changes to the base table(s). Archiving allows users to move historical partitions of the temporal history table into the accelerator, thus freeing up storage. Temporal queries can be used to satisfy regulatory requests for snapshots, and those queries can be run and results landed all within the accelerator.
The following Redbook discusses temporal support in DB2 for z/OS and the DB2 Analytics Accelerator in great detail.