Thank you all for the responses.
Unfortunately it seems like what happened to me is that when the CPYF ran (which carried over the originating identity values - must be using OVERRIDING SYSTEM VALUE under the covers), the destination table did not recognize the inserted identity values in regard to establishing what the next identity should be.
To test this with SQL, I see the same result.
insert into kja9lib/edthsthdrp (editid, client)
overriding system value Values(8061, 'TST')
That inserted 1 record.
The previous record was ID = 8060.
I then added a record (via DBU) and got the duplicate record error.
Currently we only have 1 file in production with an identity column, and thankfully this files only exists in one location. I'm actually working on a project to redesign some of our files and I was adding identity columns to them (these are files that would go into 50+ libraries). The problem is that I can't envision (currently) how we'd work ALTER TABLE into our file conversion process. I've been to numerous sessions on using SQL to create tables/indexes at conferences, and unless it went over my head, I don't ever recall being told "Don't recreate your files, only alter them." What I'm gathering from the use of Identity is that going forward we can't ever recreate the file without manual intervention. I'm not making any decisions right now, I need time to think this over, but I'm leaning toward "it's easy enough to set the next available number on the fly programmatically."
In our DDS, when we'd modify a source we'd simply mod-mark it and add a maintenance comment detailing the change (like we do for any other changed program). That's what I was doing in SQL, if I added a field, I'd add it to the CREATE TABLE (and to the LABEL ON COLUMN) statements. That I'm aware of, this has had no adverse effect on our other SQL tables which do not use identity columns. How do people generally go about tracking changes to a SQL table in their source? I can't really fathom having a separate source for each change (given the 10 character member name limitation).
Mark, the ALTER TABLE with restart tested well. The Sequence object looks interesting, but I don't think it would suit our purposes.
-Kurt
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Mark S Waterbury
Sent: Friday, April 05, 2013 1:17 PM
To: Midrange Systems Technical Discussion
Subject: Re: (SQL) Identity Crisis
Hi, Kurt:
Now that "the damage is done" (so to speak), you can reset it by doing something like this:
First, run a query to find the higest identity value, e.g.:
SELECT max(yourIdentityColumn) FROM yourTable
Then, reset the "next number" like this:
ALTER TABLE yourTable ALTER COLUMN yourIdentityColumn RESTART WITH
max-value+1
where "max-value+1" is replaced with the result of the above query.
HTH,
Mark S. Waterbury
On 4/5/2013 1:33 PM, Anderson, Kurt wrote:
Well, crisis somewhat averted, but I'm having an issue with an identity field in a SQL created file.
Here's an excerpt from the table definition:
EditID Dec (10, 0) NOT NULL GENERATED ALWAYS AS IDENTITY
(START WITH 1 INCREMENT BY 1 CYCLE),
Primary Key(EditID)
The file was created last October and has been fine all along. Today we added a new field to the file and recreated the table. We copied the old records in, and they retained their EditID values, so that was great. However, the moment a record was added, it attempted to start at 1 again and got a duplicate record error. Crap! To get around the issue, for the time being, I modified the EditID definition to "START WITH 8060." We're off and running again.
For every change to the file will the "START WITH" have to be modified? Is there another way to get around this issue?
I have a feeling the answer might be to use ALTER TABLE instead of recreating the file. It's not that I'm against that, it's just that I'm already introducing a lot of change (new stuff) here, and I don't want that bubble to burst. In addition, we don't have change management software (sigh), so I ended up creating a system to implement files across all of our clients' libraries. To have to redesign it to know if the RUNSQLSTM it runs is executing a CREATE or an ALTER is a big deal.
I appreciate any input.
We're on IBM i 7.1.
Thanks,
Kurt Anderson
Sr. Programmer/Analyst
CustomCall Data Systems, a division of Enghouse Systems Ltd.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
