Querying temporal tables

You can query a temporal table to retrieve data, based on the time criteria that you specify.

To query a temporal table, use one of the following methods:

• Specify the time criteria in the query: Issue a SELECT statement, and in the table-reference of the FROM clause, specify a period-specification.
  A period-specification consists of the following clauses:

  • FOR SYSTEM TIME or FOR BUSINESS TIME to indicate whether you want to query a system-period temporal table or an application-period temporal table
  • AS OF, FROM, or BETWEEN to indicate the time criteria for which you want data
  

  The following example shows how you can request data, based on time criteria from a system-period temporal table.

  SELECT policy_id, coverage FROM policy_info
  FOR SYSTEM_TIME AS OF '2009-01-08-00.00.00.000000000000';

  Likewise, the following example shows how you can request data, based on time criteria from an application-period temporal table.

  SELECT policy_id, coverage FROM policy_info
  FOR BUSINESS_TIME AS OF '2008-06-01';

  

  If you are requesting historical data from a system-period temporal table that is defined with system-period data versioning, Db2 rewrites the query to include data from the history table.

• Specify the time criteria by using special registers:
  The advantage of this method is that you can change the time criteria later and not have to modify the SQL and then rebind the application.
  1. Write the SELECT statement without any time criteria specified.
  2. When you bind the application, ensure that the appropriate bind options are set as follows:
     • If you are querying a system-period temporal table, ensure that SYSTIMESENSITIVE is set to YES.
     • If you are querying an application-period temporal table, ensure that BUSTIMESENSITIVE is set to YES.
  3. Before you call the application, set the appropriate special registers to the timestamp value for which you want to query data:
     • If you are querying a system-period temporal table, set CURRENT TEMPORAL SYSTEM_TIME.
     • If you are querying an application-period temporal table, set CURRENT TEMPORAL BUSINESS_TIME.
• FL 507 Make adjustments to queries if using SYSTIME_PERIOD_ADJUST='Y':

  If timestamp conflicts are detected when applications set the SYSTIME_PERIOD_ADJUST global variable is set to 'Y', Db2 avoids issuing the -20528 SQL code by adjusting the timestamp values for the row-begin column for the system-period temporal table and the row-end column for the generated history row. For more information, see SYSTIME_PERIOD_ADJUST built-in global variable.

  These timestamp value adjustments can change the rows that qualify for queries that rely on the adjusted timestamp values. Consequently, if this setting is used, you must evaluate whether the statements issued by your application can tolerate the adjusted timestamp values, to prevent queries missing any output records.

  For example, if queries in your application use FOR SYSTEM_TIME FROM value1 TO value2 or FOR SYSTEM_TIME BETWEEN value1 AND value2 clauses, you might need to expand specified period to avoid missing the adjusted timestamp values. For more information, see Updating data in system-period temporal tables in high concurrency applications.

  

Example

For example, assume that you have system-period temporal table STT with the column POLICY_ID and you want to retrieve data from one year ago. You can set the CURRENT TEMPORAL SYSTEM_TIME period as follows:

SET CURRENT TEMPORAL SYSTEM_TIME = CURRENT TIMESTAMP – 1 YEAR ;

Then you can issue the SELECT statement:

SELECT * FROM STT
WHERE POLICY_ID = 123 ;

Db2 interprets this SELECT statement as follows:

SELECT * FROM STT
FOR SYSTEM_TIME AS OF CURRENT TEMPORAL SYSTEM_TIME
WHERE POLICY_ID = 123 ;