• Make the history table APPEND ONLY
• Make the table space MEMBER CLUSTER
• Use a larger index page size than 4K to reduce the frequency of index page splits.
Frequent REORGs to the history table or index are important when there’s high insert activity resulting from updates to the base table, regardless of the physical design. If there are also frequent reads from the history table, then the REORGs become even more important; you will want to respect the clustering that’s meaningful for the readers.
An application-period and system-period design can be incorporated into a single table, making what’s known as a bi-temporal table. This design gives you all the flexibility and power to combine both the application-period and system-period controls in a single table. The features of each application-period and system-period can be assumed in a bi-temporal design.
Referential Integrity and Temporal Tables
DB2 doesn’t currently support time-based referential integrity. You can define a primary key on an application-period temporal table, but can’t establish relationships with other tables. Since the whole point of application-period temporal tables is to add a time component to what was otherwise a primary key, this adds some complexity to a database design that uses application-period temporal tables. One solution is to establish separate key tables that support the database-enforced referential integrity, while their application-period temporal children support storage of the detailed elements (see Figure 3).
Setting up database-enforced referential integrity for system-period temporal tables is more traditional and straightforward. Since the system-period columns aren’t part of the primary key, and there’s still only one row per primary key, then traditional parent-child relationships can be enforced for the base table.
Changing Data Relative to Time
With an application-period temporal table, the application and database control changes to the span in time in which a row is active. When making changes to data, you can specify start and end times for your data values since the application ultimately controls these values. For example, you can change the end date of the data for ACTNO 10 in our ACT table to make the value expire at the end of 2012. Section A of Figure 4 shows that update statement and the result. You can execute normal insert and update statements, but if the unique or primary key of the table includes the business time, then your insert can add a row for an existing non-time-based key value and a delete could remove multiple rows representing different time ranges for a value. Our ACT table has a non-time key column of ACTNO and then the time duration as part of its unique key. Inserts, updates, and deletes must consider the time duration.
In addition to application-provided start and end times, the database can also control the active time range for a key value dependent on the updates and deletes performed. DB2 has provided an extension to the SQL syntax to allow a time specification for temporal modifications to data in the form of the FOR PORTION OF clause. This clause lets you specify the time period in which a row for the non-time key columns is active. So, for our data (notice I didn’t say row) represented by the ACTNO 10, you can start a new representation of the data for a future time period. Imagine that, beginning in June 2012, this ACTNO will represent something called “MANAGE/ADVISE2.” The update, as section B of Figure 4 shows, will result in the addition of a row for ACTNO 10 as well as an update so the data for the two new time periods is properly represented.
With system-period temporal tables, the start and end timestamps are out of the control of the application and completely controlled by the data server. Inserts add data to the base table, updates modify data in the base table, and deletes remove data from the base table. Only updates will result in changing start timestamps in the base table, and the end timestamps will always be the maximum value.