Posted by: Sasirekha R
DB2, IBM, row versioning, temporal, time, zOS
DB2 version 10 provides row versioning and time travelling
DB2 version 10 for z/OS, recently released, provides features for row versioning and time travel querying. With audit and compliance requirements demanding that the multiple versions of the data pertaining to a long period is stored and the changes made track able, we can almost say that it was high time DB2 came up with these features.
What we used to achieve using triggers, stored procedures and complex querying is now achievable using simple SQLs. I believe that it is not overstated when one of the insurance customers – who was involved in the Beta version – has said that over 80% of the applications requiring temporal features can exploit this and can save time and also make applications easier for business users to understand.
DB2 is providing support for both system time period as well as business time (or application time) periods. System time simply is based on the time when the transaction changes are made in the system, where application time is more logically tied to the application. When the application requires temporal support based on both, then IBM lets the usage of bitemporal tables.
The following points are worth noting as the system managed temporal features can be exploited without impacting the existing applications:
1. The historic data (or versions) is maintained in a separate history table. This also means less impact on performance of applications when they operate in normal mode (accessing only current data)
2. The temporal columns (begin time, end time, transaction time) etc. can be automatically generated and marked as hidden. So the existing applications can work as is – absolutely no changes required even in case of SELECT * or INSERT.
3. The temporal table and the history table can be associated and disassociated easily (just by using the ALTER TABLE command).
4. The historical data is accessed ONLY if the SELECT statement includes the period specifications.
The three key types of period specifications supported are:
- AS OF certain time
- FROM time1 TO time2
- BETWEEN time1 AND time2
DB2 handles these versioning and time travel querying, automatically and transparently – especially so in case of system-managed temporal tables. Business Time based is a bit more sophisticated, as the users have to handle the setting of timestamps and past, present and future effective dates and their associated business data are all maintained in a single table. But then it gives better control to the users and enables the applications to effectively manage and track transactions.
Applications that handle Insurance policies, credit cards, loans etc. are the ones that can directly start using these features. In addition to compliance, business analytics is an area which can make best use of these temporal features. More enhancements and performance improvements on the temporal features can be expected in the next releases.