Updating data in a system-period temporal table

More examples

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;