MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » October 2012

Re: reset auto increment field.



fixed

On 30 Oct 2012 09:04, Smith, Mike wrote:
I have a file that I have an auto increment field in. Currently
the file is in development and I would like to clean up the
auto incremented field before it goes to production.

I know it is not absolutely necessary, but I'd like to anyway.

I have tried the following
"alter table mytable alter column tiid reset with 1"

http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/sqlp/rbafysqlpidentity.htm
Presumably the proper syntax "RESTART WITH 1" was used, else the success implied below would seem improbable.?

I get a message that the alter completed, but I don't see any
difference in the tiid column.

That ALTER COLUMN request does not change any existing data, only the column attributes. The effect of that change to the column attributes is seen on the next INSERT of data, such that the next non-overridden identity value will be the numeric value of one; i.e. the specified "restart with" value.

Or perhaps the attempt to visualize the difference was by review of DSPFFD [¿or catalog?] whereby the CREATE using AS IDENTITY with attribute START WITH numeric-constant was seen, and that originally specified numeric-constant remained unchanged.? If so, then consider:
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzatabl.htm
"RESTART does not change the original START WITH value."

What am I doing wrong with this statement?
Is there another way to do this?
I'm on V6R1.


If the IDENTITY can restart from an apparent initial value, then likely the TABLE is empty, which means re-creating the file is a probable alternative.?






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact