- Running db2 8.1 fp 12 on windows

I have a table with an primary key identity column, this table exists on many databases (clients running db2 personal edition). I need to alter the table def, so I want to unload the data, drop the table and recreate it. I need to maintain the key values though for referential integrity.
What I want to do is this:
- CREATE TABLE MyTable_BACKUP LIKE MyTable
- INSERT INTO MyTable_BACKUP SELECT * FROM MyTable
- DROP TABLE MyTable
- <recreate MyTABLE>
- INSERT INTO MyTable SELECT * FROM MyTable_BACKUP

OK, that all works fine.. except the identity start value is now reset back to 1 on the table, so inserting into it inserts a duplicate value.
There is an ALTER TABLE ALTER COLUMN statement for resetting the IDENTITY value, but it only takes a numeric constant, I can't seem to do this:

ALTER TABLE MyTable ALTER COLUMN MyIdentCol RESTART WITH (SELECT MAX(MyIdentCol)+1 FROM MyTable )

Is there a way to reset the next identity value dynamically?

Or any other suggestions?

Thanks for the help.

No, you must use a constant for the value. You can create an SQL statement dynamically, then execute that SQL.

Andy

I have about 1000 clients, so I need to be able to do all this within a script I will execute on each client. I'm not familar with creating dynamic SQL statements within a DB2 script.
Can that be done, and if so can you point me in the right direction to get started with it?

Actually, you would create a second script from the first and then execute that.
There are lots of examples on doing this in this forum.

Andy

I'll give that a shot, thanks for the help.