Use the New BUSINESS_TIME Special Register
The fact that different time periods appear in the same table can present some challenges when writing queries and trying to keep existing application code. Just like with system time, DB2 has a special register for business time. You set the special register to the business period of interest. In our example, it’s set to ‘2011-12-28’ as follows:
“SET CURRENT TEMPORAL BUSINESS_TIME ‘2011-12-28’;
Setting the CURRENT TEMPORAL BUSINESS TIME special register doesn’t affect regular tables. Only queries against temporal tables with a BUSINESS_TIME period enabled use the time set in the special register. There’s no effect on DDL statements.
Working with the CUST_MORTGAGE table from after our DELETE in Figure 8, we issue a SELECT SQL statement:
“SELECT acct_id, bus_start, bus_end FROM Cust_Mortgage WHERE acct_id = 4389105”;
Since only the first row in the table includes the date as specified in the special register, only the first row is returned. You can also use the FOR_BUSINESS_TIME clause in a query to specify your period of interest:
“SELECT acct_id, bus_start, bus_end FROM Cust_Mortgage FOR BUSINESS_TIME AS OF ‘2011-11-14’ WHERE acct_id = 4389105”;
Again, continuing with our example using the CUST_MORTGAGE table, the rows that fall within the business time period of interest are returned: the row with the bus_start date of ‘2011-10-23’ and bus_end date of ‘2011-12-23.’
DB2 supports several period specification options in the FOR BUSINESS_TIME clause:
• FOR BUSINESS_TIME AS OF <value>
• FOR BUSINESS_TIME FROM <value1> TO <value2>
• FOR BUSINESS_TIME BETWEEN <value1> AND <value2>.
Our example used the AS OF option. You can also use the other options; there’s a lot of flexibility.
When the CURRENT TEMPORAL BUSINESS_TIME special register is set to a non-null value, you can use data modification statements such as INSERT, UPDATE, DELETE and MERGE against application-period temporal tables.
Bitemporal tables support both system time and business time in a single table. You can manage system time with full versioning of updated and deleted rows. Queries can be along one or both dimensions and you can specify the same or different values. You can combine these options to meet your objectives.
Proper use of temporal table capabilities will save hours of development time.
System-time temporal tables let you leverage versioning and the capability to separate current and historical data. In this way, developers and DBAs can take advantage of different storage and index options for the base and history tables and placement in separate buffer pools. Temporal tables can be compressed and range-partitioned for even greater flexibility and performance.
Business time temporal tables let developers manage data according to business time and business-related application logic. This gives developers more flexibility to meet users’ needs.
Bitemporal table support lets developers and DBAs combine the best capabilities of both features for maximum flexibility.
Exploiting temporal tables requires knowing how DB2 implements inclusive-exclusive periods, the rules for updates and deletes and SQL extensions and special registers.