DB2 Temporal Tables – Getting Started

Getting Started with Temporal Tables

To figure out the best temporal table design aspects you need to think of the various options and considerations that will affect its performance.  The most important aspect for your temporal table is the answers that your applications or users are expecting from it.  The best way is to figure out the time aspect that the application is trying to capture.  Are your applications looking for the financial value, the insurance coverage level, enrollment status, customer value or something else?

The temporal table status can be contingent on two types of settings: business time or the system processing time.  If the processing is delayed and the system time is later than expected, does that affect your temporal table status?  Or are you using the temporal table in a real time scenario where either the business or system time will affect the meaning of the data?  There are many ways to respond to the situations and questions, but the design decision should be based on the application and user questions that need to be answered.  So it is best to test both SYSTEM_TIME and BUSINESS_TIME scenarios out and see which design provides the best answers with the best performance.

The next design point is to figure out your timestamp type.  Do your temporal table application answers require distinct timestamps throughout the system?  Your DB2 10 system now has new capabilities to provide a column that is unique within the table system wide.  This DB2 syntax is defined WITHOUT OVERLAPS and can be used for your temporal table only for your BUSINESS_TIME values.  After the temporal table is created, an index is defined for it using your unique columns and the BUSINESS_TIME WITHOUT OVERLAPS keyword.  BUSINESS_TIME is only option the WITHOUT OVERLAPS keyword works with.

When BUSINESS_TIME WITHOUT OVERLAPS is specified, the columns of the BUSINESS_TIME period must not be specified as part of the constraint.  The specification of BUSINESS_TIME WITHOUT OVERLAPS adds the following to the constraints:

  • The end column of the BUSINESS_TIME period in ascending order
  • The start column of the BUSINESS_TIME period in ascending order
  • The minimum value of a TIMESTAMP(12), the value is 0001-01-01-00:00:00.000000000000
  • The maximum value of a TIMESTAMP(12), the value is 9999-12-31-24:00:00.000000000000
  • For DATE the minimum is 0001-01-01 and the maximum value is 9999-12-31.
  • A system generated check constraint named DB2_GENERATED_CHECK_CONSTRAINT_FOR_BUSINESS_TIME is also generated this definition process to ensure that the value for end-column-name is greater than the value for start-column-name. BUSINESS_TIME WITHOUT OVERLAPS must not be specified for a PARTITIONED index.

There are a number of considerations when creating your DB2 10 temporal table. When your application needs it to be unique, the system wide the BUSINESS_TIME option provides the capabilities with some cautions.   I am sure to find some more ways to use temporal tables and will write about them in the coming weeks.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>