How to reset auto generated id in a DB2 table

[anur...@gmail.com]

Hi
Is there a way to reset the auto generated id for this table. Drop
table and recreating does not work.

[Frank Swarbrick]

>>> On 6/10/2008 at 4:30 PM, in message
<ebf88f96-d8b0-4dfc...@i18g2000prn.googlegroups.com>,

Seems like that would work, unless you are actually using a SEQUENCE and not
an IDENTITY column.

There is also this option, if it's an IDENTITY column:
ALTER TABLE my_table ALTER COLUMN my_id RESTART WITH 12345;

I would love to see this done automatically if restoring a table (with
replace) using the LOAD utility.
But that's a matter for another day...

[Mark A]

<anur...@gmail.com> wrote in message
news:ebf88f96-d8b0-4dfc...@i18g2000prn.googlegroups.com...

> Hi
> Is there a way to reset the auto generated id for this table. Drop
> table and recreating does not work.

If you have DB2 for LUW, the information you want is in the SQL Reference
Vol 2, under ALTER TABLE. Look at the ALTER COLUMN section and the "RESTART
WITH n" clause.

If you have DB2 for z/OS the SQL Reference is in a single volume (last time
I checked).

If you don't have a SQL Reference manual, you can download a searchable PDF
version for free.

[Karl Hanson]

And if you have DB2 for i5/OS (also supports ALTER .. RESTART WITH n),
the SQL Reference is here:
http://publib.boulder.ibm.com/infocenter/systems/scope/i5os/index.jsp?topic=/db2/rbafzintro.htm&tocNode=int_213704

--
Karl Hanson