Start of change

Updating data in system-period temporal tables in high concurrency applications

FL 507 If an error situation occurs when too many concurrent statements attempt to update the same data rows in a system-period temporal table, you can specify that Db2 makes adjustments to the resulting timestamp values in the history table to avoid the error.

About this task

A system-period temporal table includes a system period (specified by a SYSTEM_TIME clause) and is defined with system-period data versioning.

An error situation can occur when too many concurrent statements attempt to update the same data rows in a system-period temporal table. In this situation, a timing window can occur where a row to be inserted into the associated history table would have a beginning timestamp value greater than the ending timestamp value, and Db2 currently returns the -20528 SQL code error when this situation occurs. However, the SYSTIME_PERIOD_ADJUST global variable can be used to avoid these errors.

Procedure

  1. If applications encounter -20528 SQL code errors when too many concurrent statements attempt to update the same data rows in user-defined system-period temporal tables, set the SYSTIME_PERIOD_ADJUST global variable value to 'Y'.

    When the SYSTIME_PERIOD_ADJUST is set to 'Y' and Db2 detects timestamp conflicts, Db2 adjusts the timestamp values for the row-begin column for the system-period temporal table and the row-end column for the generated history row. Db2 also issues the +20528 SQL code when making the adjustments.

    The following example shows how multiple concurrent transactions can potentially generate a history row that has the row-end timestamp less than the row-begin timestamp. In this example, the POLICY_INFO table is the system-period temporal table. The associated history table HIST_POLICY_INFO is specified in a USE HISTORY TABLE clause in the ALTER TABLE ADD VERSIONING statement.

    Assume that POLICY_INFO was defined by the following statements:

    CREATE TABLE POLICY_INFO 
(POLICY_ID CHAR(10) NOT NULL, 
COVERAGE INT NOT NULL, 
SYS_START TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN, 
SYS_END TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END, 
ts_id TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID, 
PERIOD SYSTEM_TIME(SYS_START,SYS_END));

    
CREATE TABLE HIST_POLICY_INFO 
(POLICY_ID CHAR(10) NOT NULL, 
COVERAGE INT NOT NULL, 
SYS_START TIMESTAMP(12) NOT NULL, 
SYS_END TIMESTAMP(12) NOT NULL, 
ts_id TIMESTAMP(12) NOT NULL);

    
ALTER TABLE POLICY_INFO 
ADD VERSIONING USE HISTORY TABLE HIST_POLICY_INFO;

    In the following examples, the timestamp values are simplified as Tn instead of a system-clock value, where n is a numeral and higher n values indicate later actions and times. That is, T2 represents a later timestamp value than T1. The value max in the SYS_END column represents the maximum default value for the TIMESTAMP WITHOUT TIME ZONE data type, which is '9999-12-30-00.00.00.000000000000'.

    During the insert process of transaction A (TRA), Db2 assigns T1 to ts_id. This value is used for both INSERT and UPDATE statements in TRA. T2 and T3 are used as the unique timestamp values for transactions C (TRC) and B (TRB) respectively when Db2 processes the INSERT statements in TRC and TRB.

    TS_ID Transaction A (TRA) Transaction B (TRB) Transaction C (TRC)
    T1 
    INSERT INTO POLICY_INFO
(POLICY_ID, COVERAGE) 
VALUES('S777', 7000);
    		   
    T2     
    INSERT INTO POLICY_INFO
(POLICY_ID, COVERAGE)
VALUES('X999', 9000);
    T3   
    INSERT INTO POLICY_INFO 
(POLICY_ID, COVERAGE) 
VALUES('T888', 8000);
    		 
    T4   
    COMMIT;
    		  
    T5 
    UPDATE POLICY_INFO SET  
COVERAGE = 9000 WHERE 
POLICY_ID = 'T888';
    		   
    T6 
    COMMIT;
    		    
    T7     
    UPDATE POLICY_INFO 
SET COVERAGE = 10000 WHERE 
POLICY_ID = 'T888';
    T8     
    COMMIT;

    After the INSERT at T3, the POLICY_INFO table contains the following rows:

    POLICY_ID COVERAGE SYS_START SYS_END TS_ID
    S777 7000 T1 max T1
    X999 9000 T2 max T2
    T888 8000 T3 max T3

    At T5, transaction TRA is being executed to update the same row that is inserted by transaction TRB. TRA still contains T1 as the system transaction time.

    If the SYSTIME_PERIOD_ADJUST global variable is set to 'N', Db2 detects that the system period is in conflict (T3 > T1) and returns SQLCODE -20528. Then at T7, transaction TRC also fails with the same SQLCODE -20528 because the system period is in conflict too (T3 > T2).

    Therefore, if the SYSTIME_PERIOD_ADJUST global variable is set to 'N', transactions TRA and TRC must be rerun.

    Using the previous example, if transactions TRA and TRC set the SYSTIME_PERIOD_ADJUST to 'Y', Db2 avoids the failure by adjusting the timestamp values. In the following examples, the timestamp values are simplified as Tn instead of a real system-clock value. The value max represents the maximum default value for the TIMESTAMP WITHOUT TIME ZONE data type.

    When the UPDATE statement of transaction TRA is being executed at T5, Db2 detects that the system period is in conflict (T3 > T1). Since SYSTIME_PERIOD_ADJUST is specified, Db2 adjusts the SYS_START value of the row to be updated to T3 + 1 microsecond. Transaction TRA commits successfully. After T5, the POLICY_INFO table contains the following rows:

    POLICY_ID COVERAGE SYS_START SYS_END TS_ID
    S777 7000 T1 max T1
    X999 9000 T2 max T2
    T888 9000 T3 +1 max T1

    After T5, the HIST_POLICY_INFO table contains the following row:

    POLICY_ID COVERAGE SYS_START SYS_END TS_ID
    T888 8000 T3 T3 +1 T3

    Db2 makes another adjustment for the UPDATE statement of transaction TRC at T7. After the UPDATE statement at T7 the POLICY_INFO table contains the following rows:

    POLICY_ID COVERAGE SYS_START SYS_END TS_ID
    S777 7000 T1 max T1
    X999 9000 T2 max T2
    T888 10000 T3 +1 max T2

    After the UPDATE statement at T7, the HIST_POLICY_INFO table contains the following rows:

    POLICY_ID COVERAGE SYS_START SYS_END TS_ID
    T888 8000 T3 T3 +1 T3
    T888 9000 T3 +1 (T3 +1) +1 T1
  2. When applications set the SYSTIME_PERIOD_ADJUST global variable is set to 'Y', you might need to update queries to avoid missing data rows where Db2 adjusted timestamp values to avoid conflicts.
    In general, queries that use FOR SYSTEM_TIME FROM value1 TO value2 or FOR SYSTEM_TIME BETWEEN value1 AND value2 clause with an expanded period have higher possibility of including the data rows that were adjusted, as illustrated by the following examples.
    Using AS OF to query the temporal table
    SELECT * FROM POLICY_INFO FOR SYSTEM_TIME AS OF T3;

    The result for the preceding example returns the following three rows, with SYS_START <= T3 and T3 < SYS_END.

    POLICY_ID COVERAGE SYS_START SYS_END TS_ID
    S777 7000 T1 max T1
    X999 9000 T2 max T2
    T888 8000 T3 T3 + 1 T3
    Using FROM to query the temporal table
    SELECT * FROM POLICY_INFO FOR SYSTEM_TIME FROM T1 TO max;

    The result returns the following rows, with SYS_START < max and T1 < SYS_END.

    POLICY_ID COVERAGE SYS_START SYS_END TS_ID
    S777 7000 T1 max T1
    X999 9000 T2 max T2
    T888 10000 T3 + 2 max T2
    T888 8000 T3 T3 + 1 T3
    T888 9000 T3 + 1 T3 + 2 T1
    Using BETWEEN to query the temporal table
    SELECT * FROM POLICY_INFO FOR SYSTEM_TIME BETWEEN T1 AND T3 + 2;

    The result returns the following rows, with SYS_START < max and T1 < SYS_END.

    POLICY_ID COVERAGE SYS_START SYS_END TS_ID
    S777 7000 T1 max T1
    X999 9000 T2 max T2
    T888 10000 T3 + 2 max T2
    T888 8000 T3 T3 + 1 T3
    T888 9000 T3 + 1 T3 + 2 T1
End of change