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).
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