Re: (SQL) Identity Crisis

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.

--
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.