Db2 – Data Migration: Unload/load of GENERATED ALWAYS TIMESTAMP

db2db2-luw

While migrating data from one Db2 database to another we need to retain the ROW CHANGE TIMESTAMP of the migrating data in the target table CFFOLIO.FOLIO

We have a ROW CHANGE TIMESTAMP attribute in the target tables. The constraint is like in the table shown below:

CREATE TABLE "CFFOLIO"."FOLIO" (
"FOLIO_ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE -9223372036854775808 MAXVALUE 9223372036854775807 NO CYCLE CACHE 20 NO ORDER ),
"FOLIO_STATE_ID" BIGINT NOT NULL,
"TIMESTAMP" TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP,
"ASSIGNEE" VARCHAR(50 OCTETS),
"LOCKED_BY" VARCHAR(50 OCTETS),
"LOCKED_TIMESTAMP" TIMESTAMP,
"FOLIO_PRIORITY_TYPE_ID" BIGINT NOT NULL,
"CONTEXT_ID" BIGINT NOT NULL,
"NAME" VARCHAR(100 OCTETS),
"DESCRIPTION" VARCHAR(2000 OCTETS),
"FOLIO_KEY" VARCHAR(50 OCTETS),
"SCORE" DECIMAL(6 , 2) NOT NULL,
"FRAUD_ASSESSMENT_VALUE_ID" BIGINT,
"DUE_TIMESTAMP" TIMESTAMP,
"INBASKET_ID" BIGINT NOT NULL,
"TZ_OFFSET" BIGINT,
"IS_CONFIDENTIAL" INTEGER NOT NULL DEFAULT 0
)

The data being migrated already has the TIMESTAMP values and these need to be retained in the target table.

In order to retain the TIMESTAMP of existing data,
we had to remove the constraint first:

ALTER TABLE CFFOLIO.FOLIO ALTER COLUMN TIMESTAMP DROP GENERATED;

load the data — we used a Java program to load the values from CSV files — then put the constraint back, and we have a challenge here.

We are able to do step 1 and 2 but cannot find the correct syntax to put the constraint back. We tried the following:

SET INTEGRITY FOR CFFOLIO.FOLIO OFF; 
ALTER TABLE CFFOLIO.FOLIO ALTER COLUMN TIMESTAMP 
  SET GENERATED ALWAYS AS (FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP); 
SET INTEGRITY FOR CFFOLIO.FOLIO IMMEDIATE CHECKED FORCE GENERATED;

ALTER TABLE above gives a syntax error.

What is the correct syntax for altering the table? Is there any other way to do this?

Best Answer

I'm not sure you can alter a column with that clause. The knowledge center suggests that this syntax should do it,

ALTER TABLE CFFOLIO.FOLIO ALTER COLUMN "TIMESTAMP"
SET GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP;

but it does not appear to work.

One option would be to use LOAD. E.g. https://www.ibm.com/developerworks/community/blogs/IMSupport/entry/DB2_Preserving_existing_timestamp_data_when_moving_tables_which_contain_a_ROW_CHANGE_TIMESTAMP_column?lang=en

Related Question