DB2 override auto generated key when inserting

Question

Hi,

I want to insert an item to a table in the database. The table has a key that is auto generated. Is it possible to override the auto generated key, (force a value). If so how?

Answer 1

I'm going to assume you are talking about identity columns and not sequences.

In DB2's CREATE TABLE syntax, have a look at the "generated-column-spec" in the syntax diagram as it relates to identity columns. There are two ways to specify how the identity value will be generated:

• GENERATED ALWAYS: this option will always generate and identity value, and you cannot specify a value for the identity column in an insert statement
• GENERATED BY DEFAULT: this option will generate an identity value if no value for the column is specified in the insert statement. If you provide a value for the column in the insert statement, db2 will not generate an identity value for it.

If the table you are trying to insert into used the ALWAYS option when the table is created, then you cannot override it. You'll need to drop and recreate the table or use the ALTER TABLE statement to redefine the column to generate the identity value by default only.

Answer 2

If you're trying to LOAD data into a table that has an identity column which is GENERATED ALWAYS then you can do this:

db2 load from tab43.ixf of ixf modified by identityoverride into tablename