Adopting temporal tables in DB2, Part 2: Advanced migration scenarios for system-period temporal tables

The temporal capabilities in IBM® DB2® 10 provide powerful tools for time-based data management. At the core of the temporal features in DB2 are three types of temporal tables: system-period, application-period, and bitemporal. This "Adopting temporal tables in DB2" series explains how to migrate existing tables and temporal solutions to temporal tables in DB2. Part 1 of the series describes basic scenarios for adopting system-period temporal tables in DB2, and Part 2 discusses three additional and slightly more advanced scenarios for migrating to system-period temporal tables.

Matthias Nicola (mnicola@us.ibm.com ), Senior Technical Staff Member, IBM Silicon Valley Lab

Author photo: Matthias NicolaMatthias Nicola is a Senior Technical Staff Member at IBM's Silicon Valley Lab in San Jose, CA. He focuses on DB2 performance and benchmarking, XML, temporal data management, in-database analytics, and other emerging technologies. He also works closely with customers and business partners to help them design, optimize, and implement DB2 solutions. Previously, Matthias worked on data warehouse performance at Informix Software. He received his Ph.D. in computer science from the Technical University of Aachen, Germany.


developerWorks Contributing author
        level

Jan-Eike Michels (janeike@us.ibm.com), Software Engineer, IBM Silicon Valley Lab

Jan-Eike MichelsJan-Eike Michels is a software engineer in IBM's Information Management Group (part of IBM Software Group). He represents IBM on the ANSI/INCITS/DM32.2 and ISO/JTC1/SC32/WG3 committees responsible for standardizing SQL. He also works with the DB2 development teams as well as with customers and business partners using SQL/XML or temporal solutions. He holds a master's degree in computer science from the Technical University of Ilmenau, Germany.



25 October 2012

Also available in Chinese

Introduction

DB2 supports time-based data management that allows you to insert, update, delete, and query data in the past, the present, and the future while keeping a complete history of data changes.

The article "A Matter of Time: Temporal Data Management in DB2" provides an introduction to these topics. "Best Practices for Temporal Data Management with DB2" offers additional usage guidelines. The remainder of this article assumes familiarity with these resources.

Parts 1 and 2 of this series focus on migration scenarios for system-period temporal tables. Other parts describe migration scenarios for application-period temporal tables and bitemporal tables.

When you create a system-period temporal table, you're instructing DB2 to automatically capture changes made to the table and to save "old" rows in a history table— a separate table with the same structure as your base table.

Defining a new system-period temporal table from scratch involves the following three steps (with the corresponding SQL statements shown in Listing 1):

  1. Create the base table for the current data.
  2. Create the history table (with the same structure as the base table).
  3. Enable versioning and specify which history table to use.
Listing 1. Definition of the base table
CREATE TABLE employees (
    empid        BIGINT NOT NULL PRIMARY KEY,
    name         VARCHAR(20),
    deptid       INTEGER,
    salary       DECIMAL(7,2),
    system_begin TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN, 
    system_end   TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END,
    trans_start  TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION START ID,
    PERIOD SYSTEM_TIME (system_begin, system_end) );
                
CREATE TABLE employees_hist LIKE employees;
                
ALTER TABLE employees 
    ADD VERSIONING USE HISTORY TABLE employees_hist;

Typical migration scenarios

The steps and effort to migrate an existing solution to system-period temporal tables depend on the characteristics of your existing tables and data, such as:

  • Whether you are already recording history rows, for example with triggers
  • Whether you use a single table to hold current and historical data or two separate tables
  • Whether you store one or two timestamp values for each version of a row
  • The period model (inclusive-exclusive vs. inclusive-inclusive) that you have chosen for your existing temporal solution

Based on these properties, parts 1 and 2 of this series examine five flavors of existing solutions and describe their migration to system-period temporal tables in DB2 (see Table 1).

Table 1. Overview of migration scenarios discussed in parts 1 and 2 of this series
Existing solution records history?Existing history is in a separate table?No. of timestamp columns used for versioning?Your period model?Article
Scenario 0NoN/ANoneNonePart 1
Scenario 1YesYesTwoInclusive-exclusivePart 1
Scenario 2YesNoTwoInclusive-exclusivePart 2
Scenario 3YesNoOneInclusive-exclusivePart 2
Scenario 4YesNoTwoInclusive-inclusivePart 2

For scenarios 1-4, these are the general steps for migration to DB2 temporal tables:

  1. Ensure that no table access (read or write) takes place during the migration.
  2. Disable any custom triggers or application code responsible for creating history rows on update/delete.
  3. Align the table schema: Create a history table and any additional timestamp columns, if necessary. Ensure that the current table and the history table have the same columns with the same names, order, nullability, and data types.
  4. Change the data type of existing timestamp columns to TIMESTAMP(12).
  5. Move existing history rows into a history table, if your existing solution uses a single table for current and history data.
  6. Adjust applications: INSERT, UPDATE, and DELETE statements typically require no or only minimal changes. Depending on the existing period and the number of timestamp columns minor changes to existing queries might be necessary. Additional changes are recommended for ease of use and better performance.

Scenario 2 — Single table for current and historical data

This migration scenario is similar to scenario 1 (in Part 1), except that it starts with a single table that contains both the current data and the history data. Many migration steps discussed for the scenario 1 are also relevant here. In this section, we list all required steps, but emphasize those that are new or different from scenario 1.

The existing table definition

Let's assume the existing temporal solution uses the table in Listing 2 to capture information about employees and the history of the changes.

Listing 2. Original table that contains current and history rows
CREATE TABLE employees_s2 (
    empid        BIGINT NOT NULL,
    name         VARCHAR(20),
    deptid       INTEGER,
    salary       DECIMAL(7,2),
    system_begin TIMESTAMP(6) NOT NULL DEFAULT CURRENT TIMESTAMP, 
    system_end   TIMESTAMP(6) NOT NULL DEFAULT TIMESTAMP '9999-12-30 00:00:00.000000',
  PRIMARY KEY (empid, system_begin) );

We also assume that there are UPDATE and DELETE triggers defined on the table employees_s2 that retain the before images of updated and deleted rows into the same table.

Table 2 shows sample content of the table employees_s2. Rows whose system_end value is 9999-12-30 00:00:00.000000 are current rows; other rows are history rows. For example, the record for empid 500 was logically deleted at 9:15 a.m. 30 Jun 2011, but it remains in the table as a history row.

Table 2. Sample data in employees_s2
empidnamedeptidsalarysystem_beginsystem_end
500Peter14000.002010-05-11 12:00:00.0000002011-06-30 09:15:45.123456
1000John15000.002010-05-11 12:00:00.0000009999-12-30 00:00:00.000000
1212James14000.002010-05-11 12:00:00.0000002011-05-11 09:30:00.100000
1212James24500.002011-05-11 09:30:00.1000009999-12-30 00:00:00.000000
4711Maddy14000.002010-05-11 12:00:00.0000002011-07-30 09:25:47.123456
4711Maddy15250.002011-07-30 09:25:47.1234569999-12-30 00:00:00.000000

Drop any triggers that create history rows

As in the previous scenario, you need to remove any triggers that generate history rows in your existing solution. If there are additional triggers to detect overlapping periods for the same employee ID, those triggers should also be removed. A system-period temporal table prevents such overlaps automatically.

Migrate the table definition and data

The existing table definition we assume in this scenario (Listing 2) differs from a system-period temporal table (Listing 1) in the following ways:

  1. The data type of the system_begin and system_end columns is TIMESTAMP(6), but must be TIMESTAMP(12) for a system-period temporal table.
  2. System-period temporal tables must have a transaction ID column.
  3. The generation definitions of the system_begin and system_end columns are different: DEFAULT CURRENT TIMESTAMP/TIMESTAMP '...' vs. GENERATED ALWAYS AS ROW BEGIN/END.
  4. A system-period temporal table requires a PERIOD SYSTEM_TIME specification.
  5. A system-period temporal table keeps history rows in a separate table, so a new history table is required and the existing history rows must be moved into it.
  6. In a system-period temporal table, the system_begin column is not typically not part of the primary key.
  7. Versioning must be enabled.

The most significant difference from scenario 1 is that you need to separate existing history rows from the current data and move them into a separate table. The statements in Listing 3 perform the migration and convert the table employees_s2 into a system-period temporal table.

Listing 3. Converting the table employees_s2 into an STT
-- 1.+2. Change data types to TIMESTAMP(12) and add the transID column.
ALTER TABLE employees_s2
    ALTER COLUMN system_begin SET DATA TYPE TIMESTAMP(12)
    ALTER COLUMN system_end   SET DATA TYPE TIMESTAMP(12)
    ADD   COLUMN trans_start TIMESTAMP(12) GENERATED ALWAYS
                    AS TRANSACTION START ID IMPLICITLY HIDDEN;
                
-- 3.+4. Set the auto generation of the system time columns and declare them
--       columns as a system time period. Afterwards, a REORG is required.
    ALTER TABLE employees_s2
    ALTER COLUMN system_begin DROP DEFAULT SET GENERATED ALWAYS AS ROW BEGIN
    ALTER COLUMN system_end   DROP DEFAULT SET GENERATED ALWAYS AS ROW END
    ADD PERIOD SYSTEM_TIME (system_begin, system_end);
                
REORG TABLE employees_s2;
                
-- 5. Create a new history table and move history rows into it.
CREATE TABLE employees_s2_hist LIKE employees_s2;
                
INSERT INTO employees_s2_hist
    SELECT empid, name, deptid, salary, system_begin, system_end
    FROM employees_s2
    WHERE system_end <> '9999-12-30';
                
DELETE FROM employees_s2
WHERE system_end <> '9999-12-30';
                
-- 6. Change the primary key.
ALTER TABLE employees_s2 DROP PRIMARY KEY ADD PRIMARY KEY (empid);
                
-- 7. Finally, enable versioning.
ALTER TABLE employees_s2
    ADD VERSIONING USE HISTORY TABLE employees_s2_hist;

In step 5, note that inserting or deleting a large of number of rows in a single statement might require a lot of log space. Make sure that the DB2 transaction log is large enough, or insert and delete the history rows in a series of smaller batches.

Instead of the INSERT statement, you could also use a "load from cursor" operation, as shown in Listing 4.

Listing 4. Using the LOAD utility to move history rows
DECLARE mycur CURSOR FOR
    SELECT empid, name, deptid, salary, system_begin, system_end, trans_start
    FROM employees_s2
    WHERE system_end <> '9999-12-30';
                
LOAD FROM mycur OF CURSOR INSERT INTO employees_s2_hist
    (empid, name, deptid, salary, system_begin, system_end, trans_start);

A single table that contains current and history rows might have an indicator column of type CHAR(1) to distinguish current rows ('C') from history rows ('H'). Such a column is redundant because the same information is already available in the system_end column, before and after the migration. Consider dropping the indicator column and using system_end instead. But, if applications depend on the indicator column, you can retain it without harm. You could create a BEFORE INSERT trigger on the current and the history table to set the desired indicator values automatically.

Application changes

Existing applications that read or write to your tables may or may not require minor changes, depending on how exactly they access the tables.

INSERT, UPDATE, and DELETE statements

After the migration, most if not all of your existing INSERT, UPDATE, and DELETE statements continue to work without modification.

SQL statements that may need adjustment are existing INSERT and UPDATE statements that explicitly insert or update values in the system_begin or system_end columns rather than letting DB2 fill in generated values. In a system-period temporal table, INSERT and UPDATE statements must not provide values for these columns.

Additionally, if your existing solution uses DELETE statements to periodically purge history rows from the table, you need to adjust these statements to delete history rows from the new history table instead.

Retrieving current and history rows in a single query

Since the existing solution did not have a separate history table, queries without time-based predicates in the WHERE clause have retrieved all versions of a qualifying row, the current version and any history rows.

Consider the query in Listing 5 as an example. Before the migration, this query has returned all versions of the row for employee 4711. After the migration, the same SQL statement returns only the current version of the row for employee 4711.

Listing 5. Retrieving the current and all previous versions of a row (before the migration)
SELECT empid, name, deptid, salary, system_begin, system_end
    FROM employees_s2
    WHERE empid = 4711;

To return the current and history rows for employee 4711 after the migration, add a FOR SYSTEM_TIME clause to the query, as shown in Listing 6. Alternatively, you could create a view that exposes current and history rows.

Listing 6. Retrieving the current and all previous versions of a row (after the migration)
SELECT empid, name, deptid, salary, system_begin, system_end
    FROM employees_s2 FOR SYSTEM_TIME FROM '0001-01-01' TO '9999-12-30'
    WHERE empid = 4711;

Retrieving only the current version of a row

In the existing solution, a query that wants to retrieve only the current version of row must have time-based predicate to exclude any previous versions. For example, the query in Listing 7 uses the condition system_end = '9999-12-30 00:00:00.000000' to select only the current row for employee 4711 from the existing table.

Listing 7. Retrieving the current version of a row (before the migration)
SELECT empid, name, deptid, salary, system_begin, system_end
  FROM employees_s2
  WHERE empid = 4711
    AND system_end = '9999-12-30 00:00:00.000000';

The query in Listing 7 still produces the same result after the migration. However, it can be simplified as in Listing 8 and still return the current row only.

Listing 8. Retrieving the current version of a row (after the migration)
SELECT empid, name, deptid, salary, system_begin, system_end
    FROM employees_s2
    WHERE empid = 4711;

Scenario 3 — Single timestamp column per row

The third migration scenario involves an existing temporal solution similar to scenario 2, except that only one timestamp column instead of two describes the system-time period in each row. This single timestamp column represents the start of the period. The end of the period is determined by timestamp in the -temporally speaking- "next row."

The existing table definition

An existing temporal solution that uses only a single timestamp per record might have the following table definition to capture information about employees and the history of changes.

Listing 9. Original table that contains current and history rows with a single timestamp column per row
CREATE TABLE employees_s3 (
    empid          BIGINT NOT NULL,
    name           VARCHAR(20),
    deptid         INTEGER,
    salary         DECIMAL(7,2),
    status         CHAR(1) NOT NULL,
    system_begin   TIMESTAMP(6) NOT NULL DEFAULT CURRENT TIMESTAMP, 
    PRIMARY KEY (empid, system_begin) );

Let's also assume that appropriate UPDATE and DELETE triggers on the table employees_s3 ensure that the before images of updated and deleted rows are captured as history rows in the same table.

Table 3 shows sample content of the table employees_s3. The status column holds an indication of whether the row is a current row (C), a history row (H), or a dummy row (D). Dummy rows may exist to represent the end of the period of a deleted row. For example, the first row with empid 500 was inserted on 11 May 2010 and is now a history row because it was logically deleted. The dummy row for empid 500 tells that the deletion took place at 9:15 a.m. 30 Jun 2011. Such dummy rows are artifacts of modeling the system-time period with only one timestamp column.

Table 3. Sample data in employees_s3
empidnamedeptidsalarystatussystem_begin
500Peter14000.00H2010-05-11 12:00:00.000000
500nullnullnullD 2011-06-30 09:15:45.123456
1000John15000.00C2010-05-11 12:00:00.000000
1212James14000.00H2010-05-11 12:00:00.000000
1212James24500.00C2011-05-11 09:30:00.100000
4711Maddy14000.00H2010-05-11 12:00:00.000000
4711Maddy15250.00C2011-07-30 09:25:47.123456

Drop any triggers that create history rows

As with the previous scenarios, any custom triggers that generate the history rows should be removed before starting the migration.

Migrate the table definition and data

The most prominent differences between the existing table employees_s3 in Listing 9 and a system-period temporal table such as in Listing 1 are the following:

  1. The existing table has no system_end column, which is required in a system-period temporal table. Listing 10 includes an UPDATE statement that derives appropriate system_end values from the system_begin values.
  2. The existing table has an additional status column, which you can choose to retain or remove as part of the migration.
  3. The existing table does not have separate history table, but keeps current and history rows in one table. Hence, the migration moves history rows into a new history table as in scenario 2.

The statements in Listing 10 perform the migration and convert the table employees_s3 into a system-period temporal table.

Listing 10. Converting the table employees_s3 into an STT
-- Add a system_end column to the table:
ALTER TABLE employees_s3 ADD COLUMN system_end TIMESTAMP(12);
                
-- Calculate system_end values from the existing values in the system_begin column:
UPDATE employees_s3 e1
  SET system_end = COALESCE (
    (SELECT MIN(e2.system_begin)
       FROM employees_s3 e2
       WHERE e2.empid = e1.empid
         AND e2.system_begin > e1.system_begin), '9999-12-30');
                
-- Set the required properties for the timestamp columns and declare the period:
ALTER TABLE employees_s3 
    ALTER COLUMN system_end   SET NOT NULL
    ALTER COLUMN system_begin SET DATA TYPE TIMESTAMP(12)
    ADD COLUMN trans_start TIMESTAMP(12)
      GENERATED ALWAYS AS TRANSACTION START ID IMPLICITLY HIDDEN;
                
ALTER TABLE employees_s3
    ALTER COLUMN system_begin DROP DEFAULT SET GENERATED ALWAYS AS ROW BEGIN
    ALTER COLUMN system_end                SET GENERATED ALWAYS AS ROW END
    ADD PERIOD SYSTEM_TIME (system_begin, system_end);
                
-- Must reorganize the table now:
REORG TABLE employees_s3;
                
-- Create a history table and move history rows into it:
CREATE TABLE employees_s3_hist LIKE employees_s3;
                
INSERT INTO employees_s3_hist
    SELECT *
    FROM employees_s3
    WHERE status = 'H';
                
-- Remove history and dummy rows from the employees_s3 table:
DELETE FROM employees_s3 
  WHERE status IN ('H','D');
                
-- Change the primary key:
ALTER TABLE employees_s3 DROP PRIMARY KEY ADD PRIMARY KEY (empid);
                
-- Enable versioning:
ALTER TABLE employees_s3
  ADD VERSIONING USE HISTORY TABLE employees_s3_hist;

Regarding the UPDATE, INSERT, and DELETE statements in this migration process, be aware that writing or removing large numbers of rows in a single statement might require a lot of log space. Ensure that the log is large enough, perform these writes in a series of smaller batches, or consider using "not logged initially." Instead of the INSERT statement, you could also use a "load from cursor" operation, as shown in Listing 4.

Table 4 and Table 5 show the content of the base table and history table after the migration.

Table 4. Sample data in employees_s3 after the migration
empidnamedeptidsalarystatussystem_beginsystem_end
1000John15000.00C2010-05-11 12:00:00.0000000000009999-12-30 00:00:00.000000000000
1212James24500.00C2011-05-11 09:30:00.1000000000009999-12-30 00:00:00.000000000000
4711Maddy15250.00C2011-07-30 09:25:47.1234560000009999-12-30 00:00:00.000000000000
Table 5. Sample data in employees_s3_hist after the migration
empidnamedeptidsalarystatussystem_beginsystem_end
500Peter14000.00H2010-05-11 12:00:00.0000000000002011-06-30 09:15:45.123456000000
1212James14000.00H2010-05-11 12:00:00.0000000000002011-05-11 09:30:00.100000000000
4711Maddy14000.00H2010-05-11 12:00:00.0000000000002011-07-30 09:25:47.123456000000

Application changes

Similar considerations apply as in scenario 2, so we do not repeat them here.

An existing temporal solution that relies on a single timestamp column per row requires complex logic when it needs to calculate the end timestamp of a row. With DB2's temporal features, such queries are significantly simpler because they can obtain the end timestamp from the system_end column directly.


Scenario 4 — Inclusive-inclusive period model

Scenario 4 examines an existing temporal solution that uses two timestamp columns with the inclusive-inclusive period model. In an inclusive-inclusive period, the timestamp value that indicates the end of the period is part of period and denotes the last point in time when the row was a current row.

In contrast, DB2 uses inclusive-exclusive periods where the end timestamp is not included in the period because it denotes the first point in time when the row was no longer current (expiration point).

The difference between inclusive-inclusive and inclusive-exclusive periods has far reaching consequences for managing temporal data. The article "Best Practices for Temporal Data Management with DB2" contains a section that describes the differences and explains the advantages of the inclusive-exclusive model.

The existing table definition

Let's assume the existing temporal solution uses the table in Listing 11 to capture information about employees and the history of the changes.

Listing 11. Original table that contains current and history rows
CREATE TABLE employees_s4 (
    empid        BIGINT NOT NULL,
    name         VARCHAR(20),
    deptid       INTEGER,
    salary       DECIMAL(7,2),
    system_begin TIMESTAMP(6) NOT NULL DEFAULT CURRENT TIMESTAMP, 
    system_end   TIMESTAMP(6) NOT NULL DEFAULT TIMESTAMP '3000-12-30 23:59:59.999999',
   PRIMARY KEY (empid, system_begin)  );

Let's also assume that appropriate UPDATE and DELETE triggers on the table employees_s4 create history rows. Table 6 shows sample content for this table. Rows whose system_end value is 3000-12-31 23:59:59.999999 are current rows. Other rows are history rows.

Table 6. Sample data in employees_s4
empidnamedeptidsalarysystem_beginsystem_end
500Peter14000.002010-05-11 12:00:00.0000002011-06-30 09:15:45.123456
1000John15000.002010-05-11 12:00:00.0000003000-12-31 23:59:59.999999
1212James14000.002010-05-11 12:00:00.0000002011-05-11 09:30:00.099999
1212James24500.002011-05-11 09:30:00.1000003000-12-31 23:59:59.999999
4711Maddy14000.002010-05-11 12:00:00.0000002011-07-30 09:25:47.123455
4711Maddy15250.002011-07-30 09:25:47.1234563000-12-31 23:59:59.999999

Note the differences between the timestamp values in this table vs. the table employees_s2 in scenario 2 (Table 2). For example, consider the two rows for employee James. In scenario 2, the system_end value of the first row for James is exactly the same as the system_begin value of the second row for James. The reason is that scenario 2 assumes the inclusive-exclusive period model where the expiration time of one version of a row also marks the beginning of the next version.

With the inclusive-inclusive periods in employees_s4 (Table 6), the system_begin time of a new version of a row is one unit of time after the system_end time of the previous version. In Table 6, the first row for James was current until and including '2011-05-11 09:30:00.099999', and the next version of the row became current at '2011-05-11 09:30:00.100000', which is 1 microsecond later.

The difference of 1 microsecond stems from the fact that data type for these columns is TIMESTAMP(6), which has microsecond precision. Hence, the "next" moment in time is the next microsecond. If the data type was TIMESTAMP(0) or TIMESTAMP(12), the difference between the system_end of one version of a row and the system_begin of the next version would be 1 second or 1 picosecond, respectively. This is one of the reasons why dealing with inclusive-inclusive periods can sometimes be tricky.

When you migrate existing temporal data from the inclusive-inclusive model to the inclusive-exclusive model in DB2 temporal tables, you must increase the system_end value of all history rows by one unit of time (such as 1 microsecond if your existing data type is TIMESTAMP(6)). As a result, each system_end value becomes an expiration point and identical to the system_begin value of the next version of the respective row.

Drop any triggers that create history rows

As with the previous scenarios, any custom triggers that generate history rows should be removed before the migration.

Migrate the table definition and data

The migration of the table employees_s4 with inclusive-inclusive periods to a DB2 system-period temporal table with inclusive-exclusive periods is very simple.

The migration uses the same steps as in scenario 2 (see Listing 3). However, you first need to increase the system_end values for history rows in order to convert the inclusive-inclusive periods to inclusive-exclusive periods. In our example, the system_end values for current rows also need to be changed to the value 9999-12-30 that DB2 uses to express infinity. Listing 12 shows these migration steps for the table employees_s4.

Listing 12. Converting the table employees_s4 into an STT
-- Increase the system_end values for all history rows by one microsecond:
UPDATE employees_s4
    SET system_end = system_end + 1 MICROSECOND
    WHERE system_end < '3000-01-01';
                
-- Set the system_end values for all current rows to '9999-12-30':
UPDATE employees_s4
    SET system_end = '9999-12-30'
    WHERE system_end >= '3000-01-01';
                
-- Next, perform all the migration steps from scenario 2.

Application migration

Similar considerations apply as for scenario 2, except that queries against the original inclusive-inclusive periods might need to be adjusted to the inclusive-exclusive periods.

If an application retrieves system_end values and interprets them as the end points of inclusive-inclusive periods, then such queries need to be adjusted to convert the exclusive system_end values from the system-period temporal table to inclusive values. To perform this conversion, subtract one unit of time from the exclusive system_end value. Because the data type is TIMESTAMP(12), one unit of time is 0.000000000001 seconds, which is one picosecond.

For example, if you want to know what was recorded for employee 500 in the database at midnight 1 Feb 2011, the homegrown temporal solution might have issued the query in Listing 13.

Listing 13. Sample query before the migration
SELECT empid, name, deptid, salary, system_begin, system_end
    FROM employees_s4
    WHERE system_begin <= '2011-02-01'
       AND system_end >= '2011-02-01'
       AND empid = 500 ;

Using the DB2 syntax for temporal queries, the SELECT statement in Listing 14 produces the same result as the query in Listing 13.

Listing 14. Sample query after the migration — converting system_end from exclusive to inclusive
SELECT empid, name, deptid, salary, system_begin,
       system_end - 0.000000000001 SECONDS AS system_end
    FROM employees_s4 FOR SYSTEM_TIME AS OF '2011-02-01'
    WHERE empid = 500;

In summary, consider the following guidelines for the migration of queries in this scenario:

  • Any query that returns the system_end value from history rows must subtract one time unit from the system_end value, as in Listing 14.
  • Any query that potentially returns history rows must have a FOR SYSTEM_TIME clause instead of temporal predicates in the WHERE clause, as illustrated by the change from Listing 13 to Listing 14.
  • Any query that returns all current and previous versions of a row should use a clause, such as FOR SYSTEM_TIME FROM '0001-01-01' TO '9999-12-30', as shown in Listing 6.
  • Any query that only returns current rows should be simplified by removing any temporal predicates in the WHERE clause (Listing 8).
  • If any logic in your application depends on a comparison of the system_end timestamps, any original predicate of the form system_end >= ? must be changed to system_end > ? to adjust for the fact that the migration has increased the system_end values by one time unit.

Summary

Parts 1 and 2 of this series have described five migration scenarios that illustrate how to convert existing homegrown versioning solutions to system-period temporal tables in DB2. Existing solutions can differ in several key characteristics, such as one vs. two tables to store current and history rows, one vs. two timestamp values per row, as well as inclusive-inclusive versus inclusive-exclusive periods. In each case, only a small number of statements are required to migrate data and tables. Moreover, most of the migration steps are very similar between the different scenarios.

Subsequent parts of this "Adopting temporal tables in DB2" series discuss migration scenarios for application-period temporal tables (business time) and bitemporal tables. You will find that very similar techniques apply as for system-period temporal tables.

Resources

Learn

Get products and technologies

  • Download a DB2 trial version or the free DB2 Express-C to try out the new temporal data management features yourself.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=841549
ArticleTitle=Adopting temporal tables in DB2, Part 2: Advanced migration scenarios for system-period temporal tables
publish-date=10252012