DB2: Convert existing column to identity

Question

I am attempting to convert an existing column to identity.

So far I am able to do so very easily with the following:

alter table list alter column id set generated always as identity (start with 0);

Unfortunately, I need it to start at the maximum number of the data that is already in the table.

I have tried the following but it complains of the subquery.

alter table list alter column id set generated always as identity (start with (select max(id) from list);

The subquery returns fine when ran alone.

Answer 1

I have found an interesting solution on a blog: How to add IDENTITY column to existing table,

1. Add not null integer column You need to provide default value for it otherwise db2 will refuse it to make not null alter table public.clicks add column id integer not null default 0

2. Drop default value from column I am not exactly sure why it is needed because some manuals on internet omits this step but i was not able to make it work without this on DB2 9.7.3 LUW alter table public.clicks alter column id drop default

3. Now set column to always generated alter table public.clicks alter column id set generated always as identity

4. Reorg table to make it writeable reorg table public.clicks

5. Now replace zeros with generated itentity values update public.clicks set id = default

6. And optionally make id column primary key for table alter table public.clicks add constraint pkey primary key(id)

Another solution using a stored procedure

Restart IDENTITY Column when data is migrated to DB2

If a table is created with an IDENTITY column and you need to make sure that your identity column starts from the last max value that you load the data. IBM Data Movement Tool does this automatically but if you are not using the tool and want a way to sync up start value of the identity column with the data in your database, you can use this stored procedure to sync the data. link

--#SET TERMINATOR @
CREATE PROCEDURE RESETIDENTITYSTARTVALUE
(
    IN schemaname VARCHAR(128), 
    IN tablename VARCHAR(128)
)
BEGIN
   DECLARE sqlcode INTEGER;
   DECLARE maxid BIGINT;
   DECLARE idcolname VARCHAR(128);
   DECLARE stmttxt VARCHAR(1000);
   DECLARE s STATEMENT;
   DECLARE cur CURSOR FOR s;

   SELECT colname INTO idcolname
   FROM SYSCAT.COLUMNS
   WHERE tabname = tablename
   AND tabschema = schemaname
   AND identity = 'Y';

   IF SQLCODE = 100 THEN
      SIGNAL SQLSTATE '78000'
      SET MESSAGE_TEXT = 'can''t find identity column';
   END IF;

   SET stmttxt = 'SELECT MAX("' || idcolname || '") FROM "' ||
          schemaname || '"."' || tablename || '"';
   PREPARE s FROM stmttxt;
   SET maxid = 0;
   OPEN cur;
      FETCH cur INTO maxid;
   CLOSE cur;
   SET stmttxt = 'ALTER TABLE "' || schemaname || '"."' || tablename ||
           '" ALTER COLUMN "' || idcolname ||
           '" RESTART WITH ' || CHAR(maxid + 1);
   EXECUTE IMMEDIATE stmttxt;
END
@

db2 connect to sample
db2 -tf sp.sql
db2 terminate

DB2 manual: if you want to modify an identity column

Modifying an identity column definition

If you are recreating a table followed by an import or load operation, and if you have an IDENTITY column in the table then it will be reset to start generating the IDENTITY value from 1 following the recreation of the contents of the table. When inserting new rows into this recreated table, you do not want the IDENTITY column to begin from 1 again.

You do not want duplicate values in the IDENTITY column. To prevent this from occuring, you should:

1. Recreate the table.
2. Load data into the table using the MODIFIED BY IDENTITYOVERRIDE clause. The data is loaded into the table but no identity values are generated for the rows.
3. Run a query to get the last counter value for the IDENTITY column:SELECT MAX(<IDENTITY column>). This will return with the equivalent value of what would have been the IDENTITY column value of the table.
4. Use the RESTART clause of the ALTER TABLE statement: ALTER TABLE <table name> ALTER COLUMN <IDENTITY column> RESTART WITH <last counter value + 1>
5. Insert a new row into the table. The IDENTITY column value will be generated based on the value specified in the RESTART WITH clause.
6. Set the integrity of the table using the SET INTEGRITY statement once all of the data is entered into the table.