Db2 – creating PKs for existing tables – altering column constraints

db2

I am quite new to DB2, so I googled around how to ask questions correctly and so I hope I can provide all necessary information.

I have an existing table A1230922.CALSONW with several (195) columns but no primary key. Now I want to connect an ORM that needs a primary key, I guess for row identification.

Googling brought me the result to use the following queries:

  • add a column with NOT NULL, and DEFAULT 0 (works fine):

    ALTER TABLE tablename ADD COLUMN orm_id INTEGER NOT NULL WITH DEFAULT 0;

  • add automatic increasing key values (does not work, as it is not compatible with column):

    ALTER TABLE tablename ALTER COLUMN orm_id SET GENERATED BY DEFAULT AS IDENTITY;

I only have German error messages (and I cannot change the interface language), saying that the attributes are not compatible with the existing column.

When I try to remove the default 0, or the NOT NULL, I get the error message (translated by myself):

ALTER table A1230922.CALSONW alter column orm_id DROP NOT NULL

An error occurred during the implicit system action "3". The returned information conaint the SQLCODE "-421", the SQLSTATE "42826", and the message token "[…] full documentation.

The docs for this SQLCODE and SQLSTATE say:

Short Description: THE OPERANDS OF A UNION OR UNION ALL DO NOT HAVE THE SAME NUMBER OF COLUMNS
The operands of a UNION or UNION ALL must have the same number of columns. System action: The statement cannot be executed. Programmer response: Correct the SQL statement so that there are exactly the same number of columns in each operand.

Obviously, I am not doing anything with UNION in this context.

Next steps would be to REORG TABLE, SET orm_id = DEFAULT, and ADD CONSTRAINT pk PRIMARY KEY(orm_id).

Version information:

Client: Win 7, 32 Bit, DB2 code release"SQL09075", "08060107".

Version: "DB2 v9.7.500.702", "s111017", "IP23286", FixPak "5".

Server: Win XP (yes, development only), exactly same version

Best Answer

My expertise is on DB2 for i, not really LUW. That said, I will still offer my thoughts.

I don't think you want your ID field to have a default of zero and say GENERATED BY DEFAULT.

Since this column is giving you so much trouble and is one you added, I might be tempted to drop it, and start from scratch, if practical.

I believe the commonly recommended definition for this field would be

ALTER TABLE tablename 
  ADD COLUMN orm_id   INTEGER   NOT NULL
                                GENERATED ALWAYS AS IDENTITY
                                CONSTRAINT tablename_pk PRIMARY KEY;

But someone wiser may adjust this advice.

Related Question