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.

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