Technical Blog Post
Abstract
DB2: Preserving existing timestamp data when moving tables which contain a ROW CHANGE TIMESTAMP column
Body
Tables which contain a generated column defined with the ON UPDATE AS ROW CHANGE TIMESTAMP clause are intended to track the time of the last update of each row in the ROW CHANGE TIMESTAMP column. However if one wishes to export and repopulate the data in a table containing a ROW CHANGE TIMESTAMP column (for example extracting and moving the data to another database) there can be specific considerations if the desire is to preserve the original timestamp values in the new table.
Recommended method: In order to populate the table data with the original row change timestamps in a new table:
(1) use the LOAD command instead of IMPORT
(2) use the rowchangetimestampoverride modifier on the LOAD command to ensure that the timestamps in the input file are preserved after the table is loaded.
For example,
CREATE TABLE IMAIONE.T1 (C1 INTEGER, C2 CHAR(10), C3 GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP NOT NULL);
INSERT INTO IMAIONE.T1 (C1,C2) VALUES (1,'aaa');
INSERT INTO IMAIONE.T1 (C1,C2) VALUES (2,'bbb');
INSERT INTO IMAIONE.T1 (C1,C2) VALUES (3,'ccc');
INSERT INTO IMAIONE.T1 (C1,C2) VALUES (4,'ddd');
INSERT INTO IMAIONE.T1 (C1,C2) VALUES (5,'eee');
These update statements will populate the C3 column with appropriate timestamps for the times the updates are done.
UPDATE IMAIONE.T1 SET C2='fff' WHERE C1=1;
UPDATE IMAIONE.T1 SET C2='ggg' WHERE C1=2;
UPDATE IMAIONE.T1 SET C2='hhh' WHERE C1=3;
UPDATE IMAIONE.T1 SET C2='iii' WHERE C1=4;
UPDATE IMAIONE.T1 SET C2='ccc' WHERE C1=5;
db2 export to t1.del of del select "*" from imaione.t1
db2 export to t1.ixf of ixf select "*" from imaione.t1
t1.del:
1,"fff ","2016-08-22-15.48.18.605048"
2,"ggg ","2016-08-22-15.48.18.607083"
3,"hhh ","2016-08-22-15.48.18.609124"
4,"iii ","2016-08-22-15.48.18.611137"
5,"ccc ","2016-08-22-15.48.18.613183"
To populate this same data in a new table with the same definition but keeping the original timestamps, i.e. as can be demonstrated as follows:
CREATE TABLE IMAIONE.T2 (C1 INTEGER, C2 CHAR(10), C3 GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP NOT NULL);
db2 load from t1.ixf of ixf modified by rowchangetimestampoverride replace into imaione.t2
Agent Type Node SQL Code Result
______________________________________________________________________________
LOAD 000 +00000000 Success.
______________________________________________________________________________
PRE_PARTITION 000 +00000000 Success.
______________________________________________________________________________
RESULTS: 1 of 1 LOADs completed successfully.
______________________________________________________________________________
Summary of LOAD Agents:
Number of rows read = 5
Number of rows skipped = 0
Number of rows loaded = 5
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 5
db2 select "*" from imaione.t2
C1 C2 C3
----------- ---------- --------------------------
1 fff 2016-08-22-15.48.18.605048
2 ggg 2016-08-22-15.48.18.607083
3 hhh 2016-08-22-15.48.18.609124
4 iii 2016-08-22-15.48.18.611137
5 ccc 2016-08-22-15.48.18.613183
Other methods can be used to populate data for this kind of scenario, but may not preserve all of the desired characteristics of the data and the table. Using LOAD provides more flexibility than IMPORT because the latter populates data via regular SQL UDI statements and as such is restricted by the generated column definition of the row change timestamp column.
Other examples:
1) Using IMPORT with replace on another table defined with GENERATED ALWAYS row change timestamp column:
CREATE TABLE IMAIONE.T2 (C1 INTEGER, C2 CHAR(10), C3 GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP NOT NULL);
db2 import from t1.ixf of ixf replace into imaione.t2
IMPORT will reject the rows with SQL3550W The field value in row "" and column "3" is not NULL, but the target column has been defined as GENERATED ALWAYS.
2) Using IMPORT of an IXF file containing the table definition and data to a new table
import from t1.ixf of ixf create into imaione.t2
This will preserve the existing timestamps successfully, but the generated property will be lost: in the definition of the resulting table
CREATE TABLE "IMAIONE "."T2" ( ... "C3" TIMESTAMP NOT NULL )
3) Define the target table as GENERATED BY DEFAULT instead of GENERATED ALWAYS
CREATE TABLE IMAIONE.T2 (C1 INTEGER, C2 CHAR(10), C3 GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP NOT NULL);
import from t1.del of del replace into imaione.t2
select "*" from imaione.t2
C1 C2 C3
----------- ---------- --------------------------
1 fff 2016-08-22-15.48.18.605048
2 ggg 2016-08-22-15.48.18.607083
3 hhh 2016-08-22-15.48.18.609124
4 iii 2016-08-22-15.48.18.611137
5 ccc 2016-08-22-15.48.18.613183
This method will allow the existing row change timestamp values to be populated in the new table successfully, but the generated always property will be lost, and for a ROW CHANGE TIMESTAMP column, it cannot be changed back to GENERATED ALWAYS from GENERATED BY DEFAULT. This has the disadvantage that user applications must then be depended on not to specify a value for the column when executing UDI statements against the table, if the same semantics as a generated always column need to be maintained.
alter table imaione.t2 alter column c3 set generated by default
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0190N ALTER TABLE "IMAIONE.T2" specified attributes for column "C3" that
are not compatible with the existing column. SQLSTATE=42837
UID
ibm11140682