We're trying to take advantage of the new ROW CHANGE TIMESTAMP option.
Here is a simple table:
CREATE TABLE "ACCTASGN"."NUMBER_STATUS" (
"STATUS_CODE" CHAR(1) NOT NULL ,
"STATUS_DESCRIPTION" VARCHAR(40) NOT NULL ,
"LAST_UPDATE" TIMESTAMP NOT NULL
GENERATED ALWAYS FOR EACH ROW
ON UPDATE AS ROW CHANGE TIMESTAMP
);
ALTER TABLE "ACCTASGN"."NUMBER_STATUS"
ADD CONSTRAINT "PK_NUMBER_STATUS" PRIMARY KEY
("STATUS_CODE");
This works fine in that LAST_UPDATE is updated each time a column in the row
in changed.
We have occasion to want to load data from, say, the production version of
this table to a table with the same definition in a test database.
Here's what I tried (coredv1 is the source database):
DECLARE load_curs CURSOR
DATABASE coredv1
FOR SELECT * FROM acctasgn.number_status;
LOAD FROM load_curs OF CURSOR
REPLACE INTO acctasgn.number_status;
Here are the results:
SQL3550W The field value in row "1" and column "3" is not NULL, but the
target column has been defined as GENERATED ALWAYS.
Obviously this is occuring because it's trying to insert the value of
LAST_UPDATE in the source table, and this is not allowed because it is
GENERATED ALWAYS.
Now, I can change the cursor to bypass this field, ie
DECLARE load_curs CURSOR
DATABASE coredv1
FOR SELECT STATUS_CODE, STATUS_DESCRIPTION
FROM acctasgn.number_status;
LOAD FROM load_curs OF CURSOR
REPLACE INTO acctasgn.number_status;
This works, but it makes LAST_UPDATE the timestamp of when I did the load,
and not the value as it is in the source table.
Technically this is 'not wrong' because that *is* in fact the date of the
last update in the destination table, but it's not really what I want.
I then tried using the load modifiers for generated columns, eg:
DECLARE load_curs CURSOR
DATABASE coredv1
FOR SELECT * FROM acctasgn.number_status;
LOAD FROM load_curs OF CURSOR
MODIFIED BY GENERATEDOVERRIDE
REPLACE INTO acctasgn.number_status;
This gives me the following error:
SQL3526N The modifier clause "GENERATEDOVERRIDE" is inconsistent with the
current load command. Reason code: "3".
Explanation:
The load file type mode (modifier) indicated, is incompatible with your
load/import/export command. This is because of one of the following
reasons:
3 Generated or identity related file type modifiers have been
specified but the target table contains no such columns.
I get the same basic thing for "generatedignore" and "generatedmissing".
Based on the documentation I have read
http://publib.boulder.ibm.com/infoce...?topic=/com.ib
m.db2.luw.admin.dm.doc/doc/c0004592.html
I would think that "generatedoverride" is what I am looking for, but perhaps
I am misreading it.
One thing that does work is if I replace "GENERATED ALWAYS FOR EACH ROW"
with "GENERATED BY DEFAULT FOR EACH ROW" in the DDL
..
The issue I have with this is, other than in this special situation, I don't
think we want to allow even the chance of having an application or a user
update this column directly. Am I just worried over nothing, and I should
go ahead and make this change (ALWAYS to BY DEFAULT)?
Thoughts?
Thanks,
Frank