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