loading in to generated row change timestamp column

[Frank Swarbrick]

We're trying to take advantage of the new ROW CHANGE TIMESTAMP option.

Here is a simple table:

CREATE TABLE "ACCTASGN"."NUM BER_STATUS" (
"STATUS_COD E" CHAR(1) NOT NULL ,
"STATUS_DESCRIP TION" VARCHAR(40) NOT NULL ,
"LAST_UPDAT E" TIMESTAMP NOT NULL
GENERATED ALWAYS FOR EACH ROW
ON UPDATE AS ROW CHANGE TIMESTAMP
);

ALTER TABLE "ACCTASGN"."NUM BER_STATUS"
ADD CONSTRAINT "PK_NUMBER_STAT US" 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_DESCRIPT ION
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 GENERATEDOVERRI DE
REPLACE INTO acctasgn.number _status;

This gives me the following error:
SQL3526N The modifier clause "GENERATEDOVERR IDE" 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 "generatedignor e" and "generatedmissi ng".

Based on the documentation I have read

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ib

m.db2.luw.admin .dm.doc/doc/c0004592.html
I would think that "generatedoverr ide" 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

[Frank Swarbrick]

Re: loading in to generated row change timestamp column

As so often occurs, I found the answer after posting the question...

DECLARE load_curs CURSOR
DATABASE jmtest
FOR SELECT * FROM customer.accoun ts;
LOAD FROM load_curs OF CURSOR
MODIFIED BY rowchangetimest ampoverride
REPLACE INTO customer.accoun ts;

This seems to work fine.

One thing that concerns me with this, as with other modifiers such as
identityoverrid e and generatedoverri de is that if there are no such columns
the entire statement is disallowed, rather than the option just being
ignored.

What I mean is that if a table does not have a ROW CHANGE TIMESTAMP column
and I try to load it using "MODIFIED BY rowchangetimest ampoverride" it gives
me an error:

SQL3526N The modifier clause "ROWCHANGETIMES TAMPOVERRIDE" is inconsistent
with the current load command. Reason code: "3".

3 Generated or identity related file type modifiers have been
specified but the target table contains no such columns.

I want to be able to have a fairly generic process to load data in this
manner. With this type of 'error handling' in place it looks like I need to
first determine if the table has an IDENTITY column and a ROW CHANGE
TIMESTAMP column and any GENERATED columns before I can determine how to
build by LOAD statement.

Seems like rather a pain.
Plus I don't know how to do it programatically ! I'm sure there are some
system tables I can query, but I'll need some help determining what they
are. My hope is that I will be able to write a stored procedure where the
user can call it just passing the name of the table and the source database
and/or source table. The SP will have to determine if any of the modifiers
are required. (That's the part I don't know how to do.)

Frank