Time Travelling with DB2 10 for z/OS

Most IT systems need to keep historical as well as current information. Previously, these kinds of requirements would have involved the DBA and application developers spending valuable time creating and testing the code and associated database design to support the historical perspective, while minimizing any performance impact.

DB2 10 provides this functionality as part of the database engine. The temporal data feature gives:

• Improved DBA and developer productivity
• Reduced errors in application code
• Reduced time-to-market for new applications
• Improved performance by driving function into the database engine
• Improved application consistency
• Supports compliance / audit requirements

All the DBA needs to do is indicate which tables/columns require temporal support when they are created, and DB2 will automatically maintain the history whenever an update is made to the data. Elegant SQL support allows the developer to query the database with an “as of” date, which will return the information that was current at the specified time.

Temporal Data Concepts

Temporal Table
This is a table that supports “data versioning” to allow point-in-time queries to be executed. Multiple versions of each row are kept by DB2 as they change over time. Additional metadata is kept, recording the period in time when a given version of the row was valid. This differs to traditional non-temporal tables where only the current version of a row is available unless a developer or DBA has taken steps to support historical perspective.

Period
This is the time during which a given version of a row is/was valid. A period is defined by special start timestamp and end timestamp columns in the temporal table. Note that in current DB2 implementation start timestamp is inclusive (>=), but end timestamp is not inclusive (<)

Business Temporal Table
A temporal table that uses business-defined periods to record row history. This is associated with a business event such as a change of address and it is useful for tracking business events over time. History is maintained in the base table. Application has control over start and end timestamp for a given period.

System Temporal Table
A temporal table that uses DB2 system-defined periods to record row history. Associated with a DB2 system event (INSERT/UPDATE/DELETE) against a table – all changes captured. DB2 automatically sets start and end timestamp for a period. History is maintained in a separate table. This table is useful for audit and compliance requirements.

BI Temporal Table
A temporal table that supports both business and system time periods. Two sets of start/end timestamps are defined.

DB2 v10 for z/OS Upgrade Experiences
Don’t forget to join the IDUG: DB2 10 for z/OS Upgrade Experiences group on LinkedIn – https://www.linkedin.com/groups?gid=3797589  Whether you have already upgraded to DB2 10, are in the process of upgrading, trying to decide whether to upgrade to DB2 9 or DB2 10, or anywhere along the migration path, this group is where you can discuss your experiences and learn from experts and peers.

For more information on visit our migration page.

• Share