Updating data in a system-period temporal table

Updating data in a system-period temporal table results in rows that are added to its associated history table.

About this task

In addition to updating the values of specified columns in rows of the system-period temporal table, the UPDATE statement inserts a copy of the existing row into the associated history table. The history row is generated as part of the same transaction that updates the row. If a single transactions make multiple updates to the same row, only one history row is generated and that row reflects the state of the record before any changes were made by the transaction.
Note: Timestamp value conflicts can occur when multiple transactions are updating the same row. When these conflicts occur, the setting for the systime_period_adj database configuration parameter determines whether timestamp adjustments are made or if transactions should fail. The Multiple changes to a row by different transactions example in the More examples section provides more details. Application programmers might consider using SQLCODE or SQLSTATE values to handle potential timestamp value adjustment-related return codes from SQL statements.

Procedure

To update data in a system-period temporal table, use the UPDATE statement.
For example, it was discovered that were some errors in the insurance coverage levels for a customer and the following data was updated on February 28, 2011 (2011-02-28) in the example table that had data added in the Inserting data into a system-period temporal table topic.
The following table contains the original policy_info table data.
Table 1. Original data in the system-period temporal table (policy_info)
policy_id coverage sys_start sys_end ts_id
A123 12000 2010-01-31-
22.31.33.
495925000000
9999-12-30-
00.00.00.
000000000000
2010-01-31-
22.31.33.
495925000000
B345 18000 2010-01-31-
22.31.33.
495925000000
9999-12-30-
00.00.00.
000000000000
2010-01-31-
22.31.33.
495925000000
C567 20000 2010-01-31-
22.31.33.
495925000000
9999-12-30-
00.00.00.
000000000000
2010-01-31-
22.31.33.
495925000000
  • The coverage for policy C567 should be 25000.
    UPDATE policy_info
       SET coverage = 25000
       WHERE policy_id = 'C567';
    
    The update to policy C567 affects the system-period temporal table and its history table, causing the following things to occur:
    1. The coverage value for the row with policy C567 is updated to 25000.
    2. In the system-period temporal table, the database manager updates the sys_start and ts_id values to the date of the update.
    3. The original row is moved to the history table. The database manager updates the sys_end value to the date of the update. This row can be interpreted as the valid coverage for policy C567 from 2010-01-31-22.31.33.495925000000 to 2011-02-28-09.10.12.649592000000.
    Table 2. Updated data in the system-period temporal table (policy_info)
    policy_id coverage sys_start sys_end ts_id
    A123 12000 2010-01-31-
    22.31.33.
    495925000000
    9999-12-30-
    00.00.00.
    000000000000
    2010-01-31-
    22.31.33.
    495925000000
    B345 18000 2010-01-31-
    22.31.33.
    495925000000
    9999-12-30-
    00.00.00.
    000000000000
    2010-01-31-
    22.31.33.
    495925000000
    C567 25000 2011-02-28-
    09.10.12.
    649592000000
    9999-12-30-
    00.00.00.
    000000000000
    2011-02-28-
    09.10.12.
    649592000000
    Table 3. History table (hist_policy_info) after update
    policy_id coverage sys_start sys_end ts_id
    C567 20000 2010-01-31-
    22.31.33.
    495925000000
    2011-02-28-
    09.10.12.
    649592000000
    2010-01-31-
    22.31.33.
    495925000000

More examples

This section contains more examples of updating system-period temporal tables.
Time specifications
In the following example, a time period is specified as part of the table update. The following update is run after the update in the preceding Procedure section.
UPDATE (SELECT * FROM policy_info
   FOR SYSTEM_TIME AS OF '2010-01-31-22.31.33.495925000000')
   SET coverage = coverage + 1000; 
This update returns an error because it implicitly attempts to update history rows. The SELECT explicitly queries the policy_info table and implicitly queries its associated history table (hist_policy_info). The C567 row in hist_policy_info would be returned by the SELECT, but rows in a history table that were accessed implicitly cannot be updated.
Multiple changes to a row by different transactions
In the following example, two transactions are executing SQL statements against the policy_info table at the same time. In this example, the timestamps are simplified to a placeholder instead of a sample system clock value. For example, instead of 2010-01-31-22.31.33.495925000000, the example uses T1. Higher numbered placeholders indicate later actions within the transaction. For example, T5 is later than T4.

When you insert or update multiple rows within a single SQL transaction, the values for the row-begin column are the same for all the impacted rows. That value comes from a reading of the system clock at the moment the first data change statement in the transaction is executed. For example, all times associated with transaction ABC will have a time of T1.

Transaction ABC Transaction XYZ
T1: INSERT INTO policy_info
         (policy_id, coverage)
         VALUES ('S777',7000);
 
 
T2: INSERT INTO policy_info
         (policy_id, coverage)
         VALUES ('T888',8000);
T3: COMMIT;
T4: UPDATE policy_info
      SET policy_id = 'X999'
      WHERE policy_id = 'T888';
T5: INSERT INTO policy_info
         (policy_id, coverage)
         VALUES ('Y555',9000);
T6: COMMIT;
 
After the inserts at T1 and T2, the policy_info table would look like this and the history table would be empty (hist_policy_info). The value max in the sys_end column is populated with the maximum default value for the TIMESTAMP(12) data type.
Table 4. Different transaction inserts to the policy_info table
policy_id coverage sys_start sys_end ts_id
S777 7000 T1 max T1
T888 8000 T2 max T2
After the update by transaction ABC at time T4, the policy information looks like the following tables. All the rows in the policy_info table reflect the insert and update activities from transaction ABC. The sys_start and ts_id columns for these rows are populated with time T1, which is the time of the first data change statement in transaction ABC. The policy information inserted by transaction XYZ was updated and the original row is moved to the history table.
Table 5. Different transactions after update to the policy_info table
policy_id coverage sys_start sys_end ts_id
S777 7000 T1 max T1
X999 8000 T1 max T1
Table 6. History table after different transactions update (hist_policy_info)
policy_id coverage sys_start sys_end ts_id
T888 8000 T2 T1 T2
The history table shows a sys_end time that is less than the sys_start. In this situation, the update at time T4 could not execute and transaction ABC would fail (SQLSTATE 57062, SQLCODE SQL20528N). To avoid such failures, the systime_period_adj database configuration parameter can be set to YES which allows the database manager to adjust the row-begin timestamp (SQLSTATE 01695, SQLCODE SQL5191W). The sys_start timestamp for the time T4 update in transaction ABC is set to time T2 plus a delta (T2+delta). This adjustment only applies to the time T4 update, all other changes made by transaction ABC would continue to use the time T1 timestamp (for example, the insert of the policy with policy_id Y555). After this adjustment and the completion of transaction ABC, the insurance policy tables would contain the following data:
Table 7. Different transactions after time adjustment (policy_info)
policy_id coverage sys_start sys_end ts_id
S777 7000 T1 max T1
X999 8000 T2+delta max T1
Y555 9000 T1 max T1
Table 8. History table after time adjustment (hist_policy_info)
policy_id coverage sys_start sys_end ts_id
T888 8000 T2 T2+delta T2
Multiple changes to a row in the same transaction
In the following example, a transaction makes multiple changes to a row. Using the insurance policy tables from the previous example, transaction ABC continues and updates the policy with policy_id X999 at time T6 (originally T6 was a COMMIT statement).
Transaction ABC
T6: UPDATE policy_info SET policy_id = 'R111' WHERE policy_id = 'X999';
T7: COMMIT;
This row has now experienced the following changes:
  1. Created as policy T888 by transaction XYZ at time T2.
  2. Updated to policy X999 by transaction ABC at time T4.
  3. Updated to policy R111 by transaction ABC at time T6.
When a transaction makes multiple updates to the same row, the database manager generates a history row only for the first change. This, results in the following tables:
Table 9. Same transaction after updates (policy_info)
policy_id coverage sys_start sys_end ts_id
S777 7000 T1 max T1
R111 8000 T1 max T1
Y555 9000 T1 max T1
Table 10. History table after same transaction update (hist_policy_info)
policy_id coverage sys_start sys_end ts_id
T888 8000 T2 T2+delta T2
The database manager uses the transaction-start-ID (ts_id) to uniquely identify the transaction that changes the row. When multiple rows are inserted or updated within a single SQL transaction, then the values for the transaction start-ID column are the same for all the rows and are unique from the values generated for this column by other transactions. Before generating a history row, the database manager determines that the last update to the row was for the transaction that started at time T1 (ts_id is T1), which is the same transaction start time for the transaction that makes the current change and so no history row is generated. The sys_start value for the row in the policy_info table is changed to time T1.
Updating a view
A view that references a system-period temporal table or a bitemporal table can be updated only if the view definition does not contain a FOR SYSTEM_TIME clause. The following UPDATE statement updates the policy_info table and generates history rows.
CREATE VIEW viewA AS SELECT * FROM policy_info;
UPDATE viewA SET col2 = col2 + 1000;
A view that references a system-period temporal table or a bitemporal table with a view definition containing a FOR SYSTEM_TIME clause can be made updatable by defining an INSTEAD OF trigger. The following example updates the regular_table table.
CREATE VIEW viewB AS SELECT * FROM policy_info;
   FOR SYSTEM_TIME BETWEEN 
   TIMESTAMP '2010-01-01 10:00:00' AND TIMESTAMP '2011-01-01 10:00:00';

CREATE TRIGGER update INSTEAD OF UPDATE ON viewB
   REFERENCING NEW AS n FOR EACH ROW
   UPDATE regular_table SET col1 = n.id;

UPDATE viewB set id = 500;