Schema changes that can’t cause loss of history propagate automatically from the base table to the history table. Changes such as adding a new column to the base table result in the column being added automatically to the history table. Increasing the size of a VARCHAR column applies to the base table and history table. However, changes such as reducing the length of a VARCHAR column or dropping a column from the base table are blocked due to potential data loss. To make those changes, you must first stop versioning.
Here’s a performance tip: Normally, a history table experiences mostly insert and read activities. As such, you can eliminate the search for free space on inserts by altering the table with the APPEND ON option. When enabled, there’s no search for free space during insert processing; this can speed up inserts into the history table.
More About History Tables
History tables can save developers and DBAs lots of time, but be aware of these rules and restrictions:
• A history table can’t be dropped explicitly. It can only be dropped implicitly when the associated system-period temporal table is dropped.
• You can’t drop a table space containing a history table that doesn’t contain its associated system-period temporal table; instead, specify both tablespaces in the DROP statement.
• History table columns can’t be added, dropped or changed explicitly.
• A history table must not be defined as a parent, child or self-referencing in a referential constraint. Referential constraints involving the history table are restricted to prevent cascaded actions to the history table.
Business Time Temporal Tables
Application-period or business time temporal tables differ from system-period temporal tables in that they’re based on your applications’ logical notion of time. Some examples of the use of application periods are:
• Variable rate mortgage products
• Insurance policies
• Credit cards with expiration dates.
Business time periods are based on application-driven changes to the time dimension of business artifacts and are based on the applications’ logical view of time. Business time, by itself, doesn’t use a history table. All data is current (current information about the past, present or future state of the business).
Creating a Table With Business Time
To create a table with business time, specify the PERIOD BUSINESS_TIME with bus_start and bus_end columns of type DATE or TIMESTAMP. To prevent overlapping periods for the same object, specify the BUSINESS_TIME WITHOUT OVERLAPS clause on the PRIMARY KEY definition. In Figure 6, for a given time, only one salary figure can be valid for an employee.
An application-period temporal table can be defined so that rows with the same key don’t have any overlapping periods of BUSINESS_TIME. You achieve this by adding the BUSINESS TIME WITHOUT OVERLAPS clause to a primary key, unique constraint specification, or create a unique index statement. DB2 handles enforcement.
Inserting, Updating and Deleting Data With Business Time Temporal Tables
Inserting data into an application-period temporal table is similar to inserting data into a regular table; the only special consideration is the need for the application to include the row begin and row end values that capture when the row is valid from the perspective of the business applications. This is called the BUSINESS_TIME period. To prevent business time periods from overlapping, you can create a unique constraint or index with BUSINESS_TIME_WITHOUT_OVERLAPS specified. An insert will fail if it violates the unique constraint. Figure 7 shows the issuance of a new variable rate mortgage to a customer with ACCT_ID 4389105.