DB2 temporal tables are an outstanding database automation feature that can replace significant amounts of application programming. This makes them a popular feature of DB2 10 for z/OS and DB2 10 for Linux, UNIX and Windows (LUW). There are some things you need to understand and prepare for before using temporal tables. However, once you have a better understanding of how time travel queries work, their uses and implications, you will be able to leverage them for your needs.
The Need for Temporal Data Storage
Data volume is increasing exponentially as business and regulatory demands drive the need to maintain large volumes and multiple versions of data. Flexibility in application design is important, especially in the era of the ever-expanding Internet, where adaptability and flexibility dictate whether a product, business, or strategy will succeed. Temporal designs give you the flexibility to store time-relative information, adding another dimension to your data store. Not only do you know what data is valid, but when it’s valid. This lets you stage data for future use, or go back in time to re-create a data condition when investigating an anomaly or performing analytics. You can also automatically track the history of changes to data for auditing or re-creating data at a point in time. DB2 now automates this.
DB2 Temporal Table Benefits
The great thing about triggers is that you can use them to replace large sections of application logic. You can use triggers to capture database changes and propagate them to audit tables, perform denormalization on the fly, and aggregate data to summary tables. You may be able to replace days or weeks of application programming and testing with just a few hours to set up and test the triggers. Moreover, once a process is no longer needed, you can simply DROP the triggers to remove the process without an application program change.
Temporal tables take this concept of database automation even further; they take an entire portion of application logic and place it in a relatively simple table structure with seamless automation built in. Application developers can incorporate a time dimension into their design without having to code for maintenance of the time factor; they can rely on DB2 to maintain the proper consistency of their time-dependent data. They will still have to consider time as they read their data, but DB2 automates maintenance of the time dimension.
Application-Period Temporal Tables
An application-period temporal table is a table that has been defined with the appropriate time columns along with the specification of a business period. They’re useful for managing data active during a certain time, and allow data to be staged (active in the future), current, and historical. An application-period temporal table can be useful for such things as reference and code tables where descriptions will change over time, or for more involved applications such as the active period of a specific insurance policy. The application-period is created by including a “PERIOD BUSINESS_TIME” clause that identifies the start and end time columns. These columns can be DATE or TIMESTAMP data types.
Figure 1 shows an example of a table defined as an application-period temporal table. When you define the application-period for the table, DB2 will automatically generate an implicit table check constraint that ensures the start time column value for a row is less than the end time column for that row. The application is still responsible for setting the values for the start and end time columns when new data is inserted. The application can also update start and end time columns. However, DB2 will control the data, including start and end times, when you apply time-relevant updates and deletes. Optionally, you can also define a unique constraint or index on the table that includes the application-period. This optional definition will ensure the application-periods don’t overlap across rows in the table. Figure 1 also includes this unique restriction in the definition.
System-Period Temporal Tables
A system-period temporal table is a table that has been defined with the appropriate timestamp columns, along with the specification of a system-period; it can be useful when all changes to a table need to be tracked with a retained history to fulfill auditing or compliance requirements. The system-period is created by including a “PERIOD SYSTEM_TIME” clause that identifies the start and end timestamp columns. When you define a system-period for a table, DB2 is in control of the start and end timestamps; the timestamps reflect the state of the data for the time specified. Within the base table only, data that’s currently effective is represented. Optionally, you can define a history table and associate it to the base table. Any changes to data then result in before images of the changed data being automatically replicated to the history table, with appropriate updates to the timestamps in both the base and history tables. Figure 2 shows an example of the Data Definition Language (DDL) to create two system-period temporal tables and their associated history tables.
You must consider history table, table space, and index design for system-period temporal designs. Unlike its base table counterpart, the history table is an insert-only table. You may want to consider an alternate physical design of the history table that favors a higher level of performance for inserts, depending on the frequency of base table changes. If the base table experiences highly frequent updates or deletes, some history table design considerations would be to: