DB2 Add Identity Column to Existing Table

Hi Jim,

That's the proper way to add an identity column in DB2.

I've never set the Identity value set this way though:

 UPDATE tablename SET id = DEFAULT

And didn't know it existed.  For such a small table, there are a lot of SQL solutions to set the initial value.

And showing off for a minute....  ;)

  UPDATE (SELECT id, row_number() over () FROM tablename) as (id, rn) SET id=rn

That will initialize the ID column to incremental from 1 to count(rows) and should be nearly instantaneous.

You'll also need to set the *NEXT* identity value

  SELECT max(id) from tablename

Assuming the highest ID is 700, set it to 701:

  ALTER TABLE tablename ALTER COLUMN id RESTART WITH 701

Note that DB2 has no inherent constraints on an IDENTITY that prevent it from setting a value that's already in the table.  If you RESTART at 1, all of the existing ID value will eventually be duplicated.  If you RESTART with 501, the existing values over 500 will eventually be duplicated.  You'll only resume storing unique keys when the IDENTITY value grows to be larger than the largest key.

But you can only get into a position to store duplicates by user error.  DB2, like all DBMS, is really good at incrementing and storing the next IDENTITY value.  It's actually a table property (only 1 IDENTITY per table) and is assigned to a specific column.

And if you're not sure that everything is happy just create a unique index on the column.  If you commonly join on the column you probably want it indexed anyway....

Kent

Thank you!  Now I have another question.

I dropped the column and then added it back as an integer with default 0 and REORGed the table.  When I selected from it, I see the new column with 0 for the value.  So as a test I tried to update the first row and set the new column to 1.

UPDATE table
SET NEW_ID = 1 
WHERE SERIAL_ID  = 1

Select allOpen in new window

That has been running for 18 minutes now.  When I look at the LIST APPLICATION SHOW DETAIL it shows the APPL_STATUS as COMP which is compiling, but why is it doing that?  I have to reboot the server to get rid of this process.  I have added and dropped the column a few times trying to get this to work, did I break something?  Why is the status for this comp?

Jim

Some DDL changes can cause DB2 to have to rebind (recompile) objects.  Many objects are stored and executed as compiled code or pseudo code.  A SELECT statement that is run repeatedly always does exactly the same thing.  It processes the table list (FROM clause), performs all of the joins, filters the results (WHERE clause) and selects the columns that the query wants.  Every table is defined in the DB2 system tables (just as every other major DBMS defines user tables in system tables).  When a query selects columns A, B, and C, the process of parsing the SQL reads the system tables, determines that these columns are at positions 1, 17, and 6, and uses that to pull the desired data from the chosen row(s).  DB2 compiles those statements to bypass repeatedly interrogating the system tables and speed things up.  The compiled code has it built into it that the data in positions 1, 17, and 6 will be used.

That's a long explanation as to "why".  The "what/how" is to force the rebinding(s) by running db2rbind.

  db2rbind {db_name}

So when I looked at the LIST APPLICATION SHOW DETAIL today, it still shows a process with the APPL_STATUS as COMP which is compiling, and it has been running since Friday.  

The table I tried to add the identity column to is locked from everything except SELECT * FROM TABLE WITH UR;

The only way to get rid of this process is to restart the server.  Any clue what is going on?  Is seems stupid that I can't add an identity column to an existing table.

Jim

Hi Jim,

It seems odd that you need to restart the server.  Stop/Start of the DB2 instance should be sufficient.  

I've never run into this and am somewhat surprised that you can't kill the process.  I can understand DB2 "protecting" the process due to it's nature, but I'd expect a graceful exit at the next job step so that nothing is left in a corrupt state.

Did you get a chance to run db2rbind after setting up the identity column?

I tried but I get the message..

 Database cannot be connected.  SQLCODE: -552

Have you tried recycling the database manager?

Yes but it just hangs.  The only way I can get that process to end is to reboot the server.  This is really frustrating.

I've never heard of that.  Maybe one of the other guys here has.

It suggests to me that something is wrong with the server, configuration, or installed software.

I am starting to wonder if that is the case.

I have a feeling it is due to DB2 on windows.  The way I understand it, is that when you issue an abort on a process it waits for an interrupt and if it is compiling an object at the OS level that gets "stuck" it never gets an interrupt, so it never gets the chance to kill the process.

There are different levels of killing Windows processes just as there are different levels on Unix.  The normal termination is a signal to the process asking it to go away gracefully.  Brute force killing of a process has risks since the normal cleanup that the process would do on termination is skipped.  In simple terms, imagine a process holding S or X locks in DB2 when the process just went away.  DB2 would have no way to know if the locks are even valid.

In this case, if the process won't respond to a termination request, a brute force termination probably leaves you in just as bad a condition.

Hi Jim,

Have you made any progress on this?

Kent

No.  I have a test box I am going to try some different things on.  It seems that if I try to make the column an identity column it starts that compile process and locks the table.  I have to recycle the DB2 instance to stop it.  I am going to do some testing to see if I can figure out a sequence that avoids that.

Thanks!

Here is exactly what I ran on my test box.

ALTER TABLE TestTable ADD COLUMN ID INTEGER NOT NULL DEFAULT 0;
UPDATE (SELECT ID, row_number() over () FROM TestTable) as (ID, rn) SET PURPOSE_ID = rn
ALTER TABLE TestTable ALTER COLUMN ID DROP DEFAULT;
ALTER TABLE TestTable ALTER COLUMN ID SET GENERATED ALWAYS AS IDENTITY;
SELECT max(ID) from TestTable;  -- 671 for this test

-- Set next ID to be MAX(PURPOSE_ID) + 1
ALTER TABLE  TestTable ALTER COLUMN ID RESTART WITH 672;

This all works fine, but when I look at LIST APPLICATIONS SHOW DETAIL I see this.

DB2SERVICE  toad.exe 1124  00047 1          0                2224            Compiling       Not Collected  C:\DB2\NODE0000\SQL00001\MEMBER0000\

And when I try to insert a new row into the table it just hangs and I can't cancel the operation.  If I reboot the server and try to insert a new row, the same thing happens because the process tries to compile the table first.

I am about to give up on this process and just create a new table with the ID I need.

ARRGGHH

If you can compile the objects from an external process (db2rbind) you might have better luck.

Kent

Can't get that to run either.  Might be my lack of knowledge but I tried "db2rbind database name  -l c:\temp\log.txt" and the output is "   Database cannot be connected.  SQLCODE: -552" which is not true since I was connected when I ran it.

I am about to quit my job and raise goats or something.

The insert gives me a new error now.

Execute requires the command to have a transaction object when the connection assigned to the command is in a pending local transaction.

My guess is the table can't create the new ID because it is still compiling?

Jim