Re: reset auto increment field.

SQL allows that effect easily. Just issue the following effective SQL "copy" request [note: CPYF will not do the same] after the new version of the production file is created:

INSERT INTO new_production_file
SELECT * FROM old_file_with_data

Without an OVERRIDING clause, the GENERATED ALWAYS identity values will be generated from the START WITH value that was specified when the new_production_file was created.

FWiW:

The MERGE statement may be able to effect same.? I did not investigate, nor have I ever used that feature.

While the two ALTER requests already utilized by the OP [first to DROP COLUMN and second to ADD COLUMN of the IDENTITY column] to effect the desired results is more work for the database, the [effective; e.g. according to the journal] original file would be reset. That may be more beneficial in some cases, rather than three steps: first to create a new TABLE, second to copy the data, and third to DROP the old file.

Regards, Chuck

On 31 Oct 2012 14:41, Anderson, Kurt wrote:

Why don't you create a new file in production? Do you need to move
data to production as well? If so, I'm also new to Identity columns
(just put in two files with them for the first time). Can you copy
the old file to the new and it knows to not copy the identity columns
and instead starts setting the identity based at your starting number
as defined in the table?

Smith, Mike on Tuesday, October 30, 2012 9:05 AM wrote:

On 30 Oct 2012 10:03, Smith, Mike wrote:

... I was hoping to get the identity values renumbered...

I have a file that I have an auto increment [<ed> IDENTITY] 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 its not absolutely necessary, but I'd like to anyway.

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

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

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