Temporal Table Support

You can create, template, and alter temporal tables.
carcudb2
You can create, template, and alter temporal tables.
If you are implementing temporal table support on a DB2 subsystem running DB2 10 or above, review the following information:
  • Support is provided for temporal tables that exist in explicitly created tablespaces. For temporal tables that exist in implicitly created tablespaces, SQL errors can occur.
    The following table types are provided:
    • Temporal
    • History (for system-period or bi-temporal tables)
      History tables are not displayed in the list of objects to template, alter, or drop.To create the history table, use
      either
      of the following options:
      • Template the base table, delete the PERIOD clause and AS column clauses: AS ROW BEGIN, AS ROW END, and AS TRANSACTION STARTID, and then ALTER TABLE ADD VERSION.
      • Flip the table type to REGULAR, and then ALTER TABLE ADD VERSION.
    To add or drop versioning, use ISQL.
  • When altering a temporal table, you can:
    • Add or change a default attribute of a column defined AS ROW BEGIN, AS ROW END, or AS TRANSACTION START ID.
    • Add a PERIOD definition.
  • When creating, altering, or templating a table, you can add the BUSINESS_TIME WITHOUT OVERLAPS clause to the definition of a unique constraint. Use the B option on the Unique Constraints Management panel. The table must be a temporal table with a BUSINESS_TIME period defined. The constraint cannot explicitly specify a column of the BUSINESS_TIME period.
  • When creating, altering, or templating an index, you can specifically include or exclude the BUSINESS_TIME WITHOUT OVERLAPS clause in the index key. To enable this functionality, use the BTWOO field on the Index Create, Alter, and Template panels.
    The index and table must meet the following conditions:
    • The index must be defined as unique, nonpartitioned, and type 2.
    • The index key cannot explicitly specify a column of the BUSINESS_TIME period.
    • The table must be defined with a BUSINESS_TIME period.
Example: How to Create System Time and Business Time Temporal Tables
This example provides high-level information for creating, templating, and altering temporal tables. For a system time temporal table, the BEGIN, END, and transaction ID columns must be TIMESTAMP(12). The default indicators are B, E, and I, respectively. For a business time temporal table, you can pick all timestamp formats or date, size of 6.
Follow these steps:
  1. To create temporal tables:
    • Specify the Table Type as T (for temporal)
    • Define a business time period table with begin (B) and end (E) columns, a column type (date or all timestamp formats), and a size of 6.
    • Define system period time tables with begin (B) and end (E) columns, and a transaction indicator (I). Use a column type of timestamp and a size of 12.
    Save and generate DDL.
  2. Create a history table by templating the previously created system period time table, and making the following changes:
    • Change the Table Type to Regular. The B, E, and I attributes go away.
    • Change the table name.
    • (Optional) Blank out the tablespace name to create a new one.
    Save and generate DDL.
  3. To tie together the tables by adding versioning, use ISQL (ALTER TABLE ADD VERSION).
  4. To alter these tables, change the business table column type from date to timestamp, and analyze the changes. The drop and recreate are generated created for the table. You can also add new columns.