System-period temporal tables and the switch from daylight saving time to standard time

You might get SQL errors if you update system-period temporal tables during the hour before the switch to standard time.

If your system uses daylight saving time during a portion of the year, and your row-begin column, row-end column, and transaction-start-ID column in a system-period temporal table are defined as TIMESTAMP WITHOUT TIME ZONE, might get errors with SQLCODE -20528 when you update the temporal table between 1:00 a.m. and 1:59 a.m. before or after the time change. The following example demonstrates how the error can occur.

Suppose that you create system-period temporal table POLICY_INFO:

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

Next, you create history table HIST_POLICY_INFO, and alter table POLICY_INFO to associate history table HIST_POLICY_INFO with POLICY_INFO:

CREATE TABLE HIST_POLICY_INFO                    
 (POLICY_ID CHAR(10) NOT NULL,                   
 COVERAGE INT NOT NULL,                          
 SYS_START TIMESTAMP(12) WITHOUT TIME ZONE NOT NULL,
 SYS_END TIMESTAMP(12) WITHOUT TIME ZONE NOT NULL,  
 TRANS_START_ID TIMESTAMP(12) WITHOUT TIME ZONE);   

ALTER TABLE POLICY_INFO                            
 ADD VERSIONING USE HISTORY TABLE HIST_POLICY_INFO;

At 1:30 a.m. on the day on which the switch to standard time occurs, you issue this SQL statement, which inserts a row into POLICY_INFO.

INSERT INTO POLICY_INFO (POLICY_ID, COVERAGE)
 VALUES('B123', 12500);

The POLICY_ID, COVERAGE, SYS_START and SYSEND columns of POLICY_INFO contain these values:


POLICY_ID  COVERAGE  SYS_START                         SYS_END     
---------------------------------------------------------------------------------------
B123          15000  2020-11-01-01.30.00.000000000000  9999-12-30-00.00.00.000000000000

Your system administrator switches the system to standard time at 2:00 a.m., which changes the time to 1:00 a.m.
At 1:25 a.m., after the switch to standard time occurs, you issue this SQL statement, which updates the row that you inserted in POLICY_INFO in the previous step.
```
UPDATE POLICY_INFO SET COVERAGE=12500
 WHERE POLICY_ID='B123';
```
If this update operation succeeded, a record like this would be written in the HIST_POLICY_INFO table:
```
POLICY_ID  COVERAGE  SYS_START                         SYS_END     
---------------------------------------------------------------------------------------
B123          12500  2020-11-01-01.30.00.000000000000  2020-11-01-01.25.00.000000000000
```
The row-begin column would have a greater value than the row-end column. Db2 therefore does not allow the update operation, and issues an error with SQLCODE -20528.

To avoid SQLCODE -20528 errors because of the switch to standard time, you can take one of these actions:

Do not do any updates to system-period temporal tables between 1:00 a.m. and 1:59 a.m. before or after the switch from daylight saving time to standard time.
Define the row-begin, row-end, and transaction-start-ID columns in your system-period temporal tables and history tables as TIMESTAMP(12) WITH TIME ZONE. When the columns are defined in that way, their data is stored in UTC, with a time zone of +00:00, so the time change cannot result in a row-begin column with a time that is later than the row-end column time.