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