Temporal Support of Db2 for z/OS – Part I

Temporal Auditing Enhancements in Db2 12 Continuous Delivery

 Xiaohong Fu, Db2 for z/OS Development, Silicon Valley Lab, IBM

 Abstract

This article series discuss the major Db2 temporal enhancements delivered in Db2 12 for z/OS and Db2 for z/OS Continuous Delivery (CD), e.g., temporal logical transaction, temporal RI, and replication and temporal query enhancements on temporal auditing. These capabilities provide great opportunities to streamline users’ time-based data designs for overall deployment and performance. The paper also provides a brief roadmap of major temporal support across Db2 10, 11, 12, and 12 continuous delivery and will position Db2 for z/OS users to exploit the powerful features available at different migration stages. As time goes by, sections may be added to address typical or common feedback and questions of Db2 temporal users.

Background and Roadmap of Db2 for z/OS Temporal Support

The temporal features in the Db2® for z/OS product (hereafter referred to as "Db2") enable Db2 applications to accurately track information and data changes over time and provide an efficient and cost-effective way to address auditing and compliance requirements. What makes temporal tables different from regular base tables is the period concept. In relational database system, a period is represented by a pair of columns: 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, which is controlled by the Db2 system, tracks information and data changes over transaction time, hence it is also known as “transaction time”. Business time is sometimes referred to as “valid time” or “application time”, which is controlled by the application. For example, you can indicate the business validity of data by assigning a pair of date or timestamp values to the business time period of a row to indicate when the information is deemed valid in the real world.

Associated with the periods, there are three types of temporal tables in Db2:

• System-period temporal table (STT) with system time period

An STT is also called a transaction time table. Db2 adopts a two-table design: the current table stores the most current    versions of the data, and the history table stores the historical versions of the data. For STTs, Db2 transparently keeps a history of updated and deleted rows over time -- a complete history of "what you knew" and "when you knew it". With time travel query (the query with period specification) supported by Db2, the user can "go back in time" and query the database as of any chosen point in the past. This is based on system timestamps that Db2 assigns internally to manage system time.

• Application-period temporal table (ATT) with business time period

An ATT is also known as a valid time table. Db2 supports ATTs to track and manage the business validity of application data. Using standardized SQL syntax, you can easily insert, update, delete, and query data in the past, present, or future. Besides temporal query, Db2 also provides temporal update and temporal delete (the update and delete with period clause) to allow a user to update or delete the data given a portion of time.

• Bitemporal table (BTT) with both system time and business time periods

 A BTT combines both STT and ATT features. Every BTT is also an STT and an ATT.

The main temporal features and deliverables are outlined below (Table 1). These features are focused on either system time, or business time, or both. As shown in the table, the basic temporal tables and temporal specific data manipulation language (DML) support was available at Db2 10 GA time. In Db2 10 service stream, certain enhancements were added, e.g., system-period versioning across different time zones or with daylight savings. In Db2 11, the temporal DMLs were extended to views. Besides the temporal query with Db2 10 explicit period specification, Db2 may generate implicit period specification in Db2 11 (time machine concept). Temporal special registers were introduced as the “button” to turn on and turn off the time machine, which further reduced the need of application change. Temporal auditing was designed and implemented within Db2 12 development timeframe, per customer requests, the feature was retrofitted to Db2 11 as well.

Table 1. Overall temporal support across Db2 10, 11, 12, and 12 continuous delivery

Besides temporal auditing, other three temporal features were delivered in Db2 12: temporal inclusive-inclusive model, temporal logical transaction, and temporal RI. As part of Db2 12 continuous delivery, two more temporal enhancements on temporal auditing are added and will be enabled by the function level V12R1M503 APAR (PH00506): APAR PI99084 is for replication enablement on STT and auditing columns; APAR PI95480 is to help customers who migrate to temporal auditing with existing data. This article series will focus on the features of Db2 12 and Db2 12 continuous delivery. As time goes by, sections may be added to address typical or common feedback and questions of Db2 temporal users, e.g., global process with time zone concern or local process with daylight savings concern – although system time with time zone support was available as early as in Db2 10, there were cases that the time difference problem and/or the Db2 enhancement was overlooked by the users at application design phase.

Replication Enablement on Temporal Auditing

Background and Motivation

Db2 application architect and developer would like Db2 to provide a capability to specify table-level specifications to audit the management of application data. For data security, customers want to track who did what to which piece of data, and when they did it. It would simplify and reduce the cost of developing Db2 applications requiring data versioning and tracking and allow customers to meet new compliance laws faster and cheaper because Db2 automatically manage and audit the different versions of data. The advantages include the ease of management and performance improvement compared to home-grown solutions (e.g., usage of triggers).

Using system time period and the generated expression columns (auditing columns) introduced by the temporal auditing feature, Db2 automatically tracks:

• WHEN the data is modified (available since Version 10)
• WHO modified the data in the table (available in Version 12, also retrofitted to Version 11)
• WHAT SQL operation modified the data in the table (available in Version 12, also retrofitted to Version 11)

Since the row begin, row end, transaction start id, and the auditing columns are completely controlled by the Db2 system, they are required to be defined with GENERATED ALWAYS attribute. In other words, these columns are not insertable or updatable. However, to replicate data from one environment to another, it is usually necessary to provide the values for most columns and disable recording of history rows for system-period temporal tables at the target side. To meet this business need, the first replication enablement was shipped via Db2 10 APAR PM61811. A stored procedure SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY was added as the replication control. When the stored procedure is invoked, Db2 allows a user to provide values for special temporal columns in system-period temporal tables and disable the generation of historical rows for system-period temporal tables.

Although system-period temporal table is the best choice to track complete information and data changes, the Db2 auditing columns are not limited to temporal tables. Instead, they can be defined in any regular tables. On the other hand, the stored procedure is a "dummy" one without any procedure body and parameter. Its control scope is at thread level. The control is not a toggle: once the procedure is invoked, the control is kept turned on until the end of the thread. It does not allow a user to provide values for auditing columns either. Besides temporal to temporal replication, non-temporal to temporal conversion with history recovery can be a potential future temporal and replication enhancement with great value. Compared to special registers, global variables introduced in Db2 11 have authorization control, which is important as not everybody should be allowed to override the system-generated values. All these make it necessary and important to introduce a generic control with a generic name for replication.

Description

Introduction of global variable SYSIBMADM.REPLICATION_OVERRIDE

A new built-in global variable SYSIBMADM.REPLICATION_OVERRIDE is introduced to allow the override of special temporal columns in system-period temporal tables or generated expression columns (Table 2). The variable will also disable the recording of history rows for system-period temporal tables. It can be extended in the future when additional behavior changes are needed for replication.

If the variable is set to 'Y' (default 'N'), Db2 will behave as if the SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY had been invoked:

• Allow values to be specified for special temporal columns in system-period temporal tables.
• Disable the generation of history rows for system-period temporal tables.       

Additionally, when the variable is set to 'Y', Db2 will allow values to be specified for generated expression columns.

Deprecation of stored procedure SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY

Users will not be able to invoke the SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY stored procedure when running with APPLCOMPAT = V12R1M503 or higher (Table 2). Failing applications will need to be modified to use the REPLICATION_OVERRIDE built-in global variable instead.

Table 2. Comparison between the two replication controls

Example

1. Schema definition

 2.  During inserts, Db2 allows the user to provide the values of the columns that must be defined as GENERATED ALWAYS. For any of the above columns whose values are not explicitly provided, Db2 generates the values as usual.

 3.  During updates, Db2 allows the user to provide the values of the columns that must be defined as GENERATED ALWAYS. For any of the above columns whose values are not explicitly provided, Db2 generates the values as usual. If the target of the update operation is an STT, DB2 does not generate history row into the associated history table of STT. Instead, replication logic explicitly creates an insert into history table statement on target side by reading source side log record. This automatic Db2 history creation may be enabled for RT to STT replication in the future, which is not included in the APAR (the related replication RFE is not accepted, so no commitment yet).

4. During deletes, if the target of the delete operation is an STT, Db2 does not generate history row into the associated history table of STT. Instead, replication logic explicitly creates an insert into history table statement on target side by reading source side log record. This automatic Db2 history creation may be enabled for RT to STT replication in the future, which is not included in the APAR (the related replication RFE is not accepted, so no commitment yet).

Temporal Query Enhancement on Temporal Auditing

Description

APAR PI95480 introduces a design and documentation change to enhance the migration of temporal auditing, i.e., to allow historical records containing NULL values in DATA CHANGE OPERATION column to be returned using queries with FOR SYSTEM_TIME FROM value1 TO value2 (or BETWEEN value1 AND value2) period specification. The change is effective at application compatibility level V12R1M503. In addition, a new function code (QW0376FN = 1202) is added to IFCID376 trace in this APAR.

Example

1. The definition of the system-period temporal table:

       2. The definition of the associated history table:

      3.  Show some sample records in the STT and STT_HIST tables:

     4. Migrate to use the new temporal auditing feature:

     5.  The records in STT and STT_HIST now look like this:

     6.  DELETE from STT, now the records in STT and STT_HIST appear as: 

     7.  Run temporal query:

Currently the implicit DCOP <> 'D' predicate on the history table filters out the records in STT_HIST table, hence no rows are returned. After the design change, effective at application compatibility level V12R1M503, the query will return records as follows:

What application and temporal query may be affected

A query may be affected by the enhancement if all of the following conditions are met:

1. It references a system-period temporal table (STT) with FOR SYSTEM_TIME FROM value1 TO value2(or BETWEEN value1 AND value2) period specification. The STT can be directly referenced or indirectly referenced through views.
2. The STT is defined with the ON DELETE ADD EXTRA ROW clause.
3. The STT is defined with the DATA CHANGE OPERATION column.
4. During migration to use the temporal auditing feature, the altered table(s) contain existing records.
5. The DATA CHANGE OPERATION column is populated with the default NULL value for the existing records. When the query is executed, the historical table contains records with a NULL value in DATA CHANGE OPERATION column.

How to detect the affected object and the SQL statement

Here are two sample queries to help detect the object and the SQL statement. Please note: these sample queries are provided "AS IS" for reference purposes only. They have not been tested in all environments.

The second one may return rows for query with "FOR SYSTEM_TIME AS OF value" period specification, which does not have the problem. BNAME can be name of either an STT or a view. If the object is a view, the tables on which the view depends need to be investigated up further.

Db2 EXPLAIN can be used to detect the affected queries in the DSN_PREDICAT_TABLE table (assuming DCOP is the DATA CHANGE OPERATION column): before the change, the Db2-generated predicate in history table subquery of implicit UNION ALL transformation is DCOP <> 'D'; after the change, the predicate is DCOP IS DISTINCT FROM 'D'.

After application of the PTF, binding and executing at an application compatibility level lower than V12R1M503 can be traced using the affected temporal query with function code (QW0376FN) 1202. The query will have incompatible behavior at application compatibility level V12R1M503.