Re: sql identity column generate always (WHY HAVE TO RESET)

I can't answer the question of why an identity column may get out of wack.
I never trusted them in the first place.

I certainly was close to falling out with ID columns before I figured out
what was going on, but they've worked very well for me since.

Admittedly I'm working at a small site at the moment and our biggest table
using auto-gen IDs has a little under half a million rows currently, but it
gets hit by a web service and an internal application server concurrently
and I've not seen it misbehave.

I don't mistrust the auto-gen ID columns.
The only misgiving I'd have about using them would be if I couldn't
guarantee that no one would CPYF data into them.

(Yes, I suppose you could argue that I can't actually guarantee no one
would ever do that... )

On Fri, 24 May 2019 at 16:22, Nathan Andelin <nandelin@xxxxxxxxx> wrote:

I can't answer the question of why an identity column may get out of wack.
I never trusted them in the first place.

Rather, we created a table that stores all our "next incremental" values
for all our files that need them, and pair that with generic procedure that
returns the next incremental value like so:

rec.siep100k = genNextSeqn('SIEP100K');

We generally place that call within a service program that we call a
"database event handler", in a procedure named on_write(), which is a
trigger event handler. I'll post a code sample. The majority of our
database tables use surrogate keys, so this setup is invoked a lot. I'm not
aware of any cases where an identity value has ever gotten out of wack. But
if there were, we have a program that can query and maintain the next
identity values for any column in any table. As I indicated earlier, those
values are just stored in a table. That table had hundreds of rows that
contain the next incremental value for hundreds of identity columns.

p on_write b export

/free

recptr = %addr(dbe.after);

if not is_valid();
return;
endif;

rec.siep100k = genNextSeqn('SIEP100K');

return;

/end-free

p on_write e
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com