3

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

  • I think you want either a Sequence or an Identity (the link is not for the Windows DB2 but the syntax is probably the same): pic.dhe.ibm.com/infocenter/db2luw/v9r7/… – ypercubeᵀᴹ Feb 18 '14 at 9:43
  • So, this might work: ALTER TABLE A1230922.CALSONW ADD COLUMN orm_id INT NOT NULL GENERATED ALWAYS AS IDENTITY START WITH 1 INCREMENT BY 1; – ypercubeᵀᴹ Feb 18 '14 at 9:45
  • @ypercube Why would you say that the DB2 LUW (Linux,Unix,Windows) link would not apply to Windows? – WarrenT Feb 19 '14 at 0:38
  • @WarrenT My bad, misread the titles. – ypercubeᵀᴹ Feb 19 '14 at 0:40
  • @WarrennT Do you have any insight on why that ALTER COLUMN fails? Is it because of the previous properties (NOT NULL and/or WITH DEFAULT 0) of the column that do not allow the GENERATED AS IDENTITY to succeed? – ypercubeᵀᴹ Feb 19 '14 at 9:30
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.