db2move & generated columns

Lee Dilworth

unread,

Sep 30, 2002, 1:43:20 PM9/30/02

to

get SQL3550W when running against tables containing generated columns ? is
this just FOL and I have to manually export/import those tables ? or is
there a workaround/pending fix (not that I can see one on the NG)

also noticed db2move doesn't like VARGRAPHIC columns either giving SQL3309N
workaround is to use FORCEIN clause but db2move does not support this, only
stand alone IMPORT does.

Serge Rielau

unread,

Sep 30, 2002, 3:44:29 PM9/30/02

to

Hi Lee,

If it's just a warning and the column gets ignored you're fine. Import
goes through normal insert statements, so the value will be regenerated
upon insert. If db2move uses LOAD, then you'd
have to do a SET INTEGRITY with FORCE GENERATED on the table afterwards.
(in that case there is room for some improvement here. db2move should
call load with the appropiate option for generated columns)

Cheers
Serge

--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Toronto

Try the DB2 UDB V8.1 beta at
http://www-3.ibm.com/software/data/db2/

Harald Wilhelm

unread,

Oct 1, 2002, 5:55:53 AM10/1/02

to

Serge,

sorry for jumping in.

What does "SET INTEGRITY ... FORCE GENERATED" do exactly? After
reading the new SQL Reference it's not clear to me what happens
on for example IDENTITY COLUMNS. Are they renumbered? Will they
become restarted (ALTER TABLE ... RESTART WITH value)?

Lee Dilworth

unread,

Oct 1, 2002, 8:15:59 AM10/1/02

to

Hi Serge,
thanks for the update. unfortunately it isn't a warning :( all rows get
rejected, same thing happens if you use the import option of db2move. using
export/import/load on their own with the appropriate filetype modifiers
works fine.

I am not sure I understand the requirement to use SET INTEGRITY with FORCE
GENERATED since the table is never placed in a check pending state and zero
rows are accepted/inserted if run

db2move mydb import

or

db2move mydb load

so, I guess my question should have been with generated column types such as
these, db2move isn't an option at all (currently?) ?

a simple sample would be....

CREATE TABLE "DB2INST7"."TEST" (
"MY_ID" DECIMAL(9,0) NOT NULL GENERATED ALWAYS AS IDENTITY
( START WITH +1 , INCREMENT BY +1 , CACHE 20 ) , "MYNAME" VARCHAR(10) );

Harald Wilhelm

unread,

Oct 1, 2002, 9:10:21 AM10/1/02

to

Lee,

we had some problems with DB2MOVE on IDENTITY COLUMNS as well.
We had to update both machines (if you move the database
between different machines) to DB2 FP7. Actually what we do is:

db2look -d dbname -a -e -o dbname.ddl -p -l -x -td !
db2move dbname export

During reload we first put every table of the target database
into "SET INTEGRITY for tablename OFF" and load with:

db2move dbname load -lo insert ...

After that a small script issues for every table involved "SET
INTEGRITY for tablename IMMEDIATE CHECKED". Don't forget to
"ALTER tablename ... RESTART WITH value" for those tables.
Serge wrote something about "FORCE GENERATED". I'm not quiet
sure what this does exactly. In the meantime use "ALTER TABLE
.." ;-)

That works for us - even with IDENTITY COLUMNS. As I said,
we're on FP7.

Perhaps it helps.

Lee Dilworth

unread,

Oct 1, 2002, 9:16:53 AM10/1/02

to

thanks, will take a look at FP7 and see if the behaviour I see changes.

Serge Rielau

unread,

Oct 1, 2002, 9:36:22 AM10/1/02

to

It would have been in check pending if teh rows hadn't been rejected.
I recommend calling support at this point. Might be the db2move utility
simply hasn't caught up with generated columns.

Serge Rielau

unread,

Oct 1, 2002, 9:34:49 AM10/1/02

to

Uh.. yeah that darn lack of a word to distinguish these guys :-(
FORCE GENERATED refers to"columns based on expressions".
SET INTEGRITY will fill in the values for these columsn if LOAD didn't
provide them.
Reason being that LOAD in before V8 couldn't compute complex expressions.
SET INTEGRITY FORCE GENERATED is also used to regenerate values if your
expression sis altered (or a function which is called by the expression
is being changed/debugged).

If you want to regenerate IDENTITY values do the following (with enough
log or NOT LOGGED INITIALLY activated):
UPDATE T SET id_col = DEFAULT;
Specifying DEFAULT will force DB2 to generate a new value.

Cheers
Serge

Harald Wilhelm wrote:

--

Lee Dilworth

unread,

Oct 1, 2002, 9:52:40 AM10/1/02

to

cheers serge, will verify against fp7 first then log the call...

Lee Dilworth

unread,

Oct 2, 2002, 2:41:56 PM10/2/02

to

Hi Harald,
i tried this but I cannot get this to work at all using a simple sample
table:

CREATE TABLE "DB2INST7"."TEST" (
"MY_ID" DECIMAL(9,0) NOT NULL GENERATED ALWAYS AS IDENTITY
( START WITH +1 , INCREMENT BY +1 , CACHE 20 ) , "MYNAME" VARCHAR(10) );

db2move just does not seem to like GENERATED ALWAYS even at FP7.........I
have tried the SET INTEGRITY options but this makes no difference, remember
in my tests the identity column is already populated in the source db and I
want to copy these to the target, only way to do it is to use the standalon
import or load commands, that I can see anyway. I guess we need db2move to
support the load modifier "identityoverride"

this seems to tie in with the docs:

For IMPORT and LOAD:
"If a non-NULL value is specified for the identity column, the row is
rejected (SQL3550W). "

Harald Wilhelm

unread,

Oct 3, 2002, 3:46:04 AM10/3/02

to

Lee,

hmm, that's interesting. The only difference I see between your
failing and our working example is that we always use GENERATED
BY DEFAULT and NO CACHE.

It seems that there's still work being done and needed for IBM
on that one.

Some month ago I collected some requirements and nice to have
things for the DB2LOOK/DB2MOVE combo (missing COMMENTs,
automatic ALTER ... RESTART option for the IDENTITY COLUMN
handling, and so on). I was able to address these to the
developers. I'm pretty sure that we will see enhancements
around the corner ...