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

[Craig Richards <craig@xxxxxxxxxxxxxxxx>]

> 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