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

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

DECLARE load_curs CURSOR
DATABASE jmtest
FOR SELECT * FROM customer.accounts;
LOAD FROM load_curs OF CURSOR
MODIFIED BY rowchangetimestampoverride
REPLACE INTO customer.accounts;

This seems to work fine.

One thing that concerns me with this, as with other modifiers such as
identityoverride and generatedoverride 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 rowchangetimestampoverride" it gives
me an error:

SQL3526N The modifier clause "ROWCHANGETIMESTAMPOVERRIDE" 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

Frank Swarbrick wrote:
> As so often occurs, I found the answer after posting the question...
>
> DECLARE load_curs CURSOR
> DATABASE jmtest
> FOR SELECT * FROM customer.accounts;
> LOAD FROM load_curs OF CURSOR
> MODIFIED BY rowchangetimestampoverride
> REPLACE INTO customer.accounts;
>
> This seems to work fine.
>
> One thing that concerns me with this, as with other modifiers such as
> identityoverride and generatedoverride 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 rowchangetimestampoverride" it gives
> me an error:
>
> SQL3526N The modifier clause "ROWCHANGETIMESTAMPOVERRIDE" 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.)
SYSCAT.COLUMNS should have the information. If not then one of teh
related COL views.

I agree that this error seems to be a bit anal...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab