Hi all,

I have a table with generated always column, I want to insert data itno the table but I can't as one of the column is generated always.

I am trying to use OVERRIDING USER VALUE but it is giving syntax error, so please guide me how to use this cmd. It i very imp foe me.

I am using db2 version 8 and OS is Window.

Thanks..............

You could use the LOAD utility with the generatedoverride modifier.

Did you specified a column list after table name in your INSERT statement?

I have two table t1 and t2.
describe table t1

Column Type Type
name schema name Length Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
CHARCOL1 SYSIBM VARCHAR 5 0 Yes
ID SYSIBM INTEGER 4 0 Yes


and t2 table is

Column Type Type
name schema name Length Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
CHARCOL2 SYSIBM VARCHAR 5 0 Yes
ID SYSIBM INTEGER 4 0 No

the id column in t2 table is generated always...
I am trying the command

insert into t2 overriding user value select * from t1;

Can I use import utility to insert.

You can solve the problem by changing the GENERATED ALWAYS to GENERATED BY DEFAULT.

Andy

Go with Andy's advice! And to add to it, Never use generated always. The generated by default will perform the same way, but it allows you to replace data that may have been deleted and retain the same number you had previously. It allows you to easily copy the data to put into a test system and retain the RI that may be based on that identity column, etc...

Dave

Thanks all

I tried that , I altered table as generated by default but the data is not inserting sequentially so that I am trying this Overriding option............

What do you mean with "not inserting sequentially"? A table has no order of its rows unless you use an ORDER BY clause. So you could sort on the generated column.

If you are referring to gaps in the generated values, you cannot avoid that anyway: a transaction could do an insert and then rollback. The generated value is used but doesn't occur in any row in the table after the rollback. (If you rely on there being no gaps, you should revisit your design - usually, it has a problem. I know that there are sometimes legal requirements that you must not have gaps. In that case, generated values are not the way to go.)

The basic question is why did you designed tables such way?

Is it not enough to add charcol2 to t1 and specify GENERATED ALWAYS(or BY DEFAULT) for id in t1?

Even if you want to separate tables by some reasons(for example, to reduce lock contensions),
why didn't specify GENERATED ALWAYS(or BY DEFAULT) for id in t1 and insert to t2 from t1?