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

[Jay Vaughn <jeffersonvaughn@xxxxxxxxx>]

Mark, that is an interesting approach and may reveal some good info.

Thanks!

Jay

On Fri, May 24, 2019 at 2:53 PM Mark Waterbury <
mark.s.waterbury@xxxxxxxxxxxxx> wrote:

> Unless I misunderstood the "requirements" you could do something like
> this, using STRSQL or Run SQL Scripts or equivalent:
>
> Suppose you have this table:
> CREATE TABLE ORDERS
>    (ORDERNO INTEGER NOT NULL
>    GENERATED ALWAYS AS IDENTITY
>    (START WITH 500
>    INCREMENT BY 1
>    CYCLE),
>    SHIPPED_TO VARCHAR (36) ,
>    ORDER_DATE DATE)
> Then do an INSERT like this:
>
> INSERT INTO ORDERS (SHIPPED_TO, ORDER_DATE)
>     VALUES('My Sample Company', '2019-05-24' )
>
>
> Next, run this query to see what was just inserted (to determine the "next
> number" value just used):
>
>     SELECT * FROM ORDERS WHERE ORDER_DATE = '2019-05-24'
>
>
> Now, suppose you see this result:
>
>  ORDERNO   SHIPPED_TO                            ORDER_DATE
>      536   My Sample Company                      05/24/19
>
>
>
> Now, you know that the "next number" should be > 536 ...
>
>
> Run this query to determine if there are any "pending conflicts" ...
>
>     SELECT * FROM ORDERS WHERE ORDERNO > 536
>
>
> Examine the results; that should indicate whether there is any potential
> trouble lurking ...
> (Ideally, no rows should be returned.)
>
>
> Preferably, do this during "off-peak hours" when no one else is updating
> that table actively.
>
>
> It might also be nice to also "clean up" after, by deleting the "dummy"
> row inserted:
>
>
>     DELETE FROM ORDERS WHERE ORDERNO = 536
>
>
> No need for embedded SQL, unless you want to develop a tool to automate
> this process.
>
> Does that help?
>
>
> Mark S. Waterbury
>
>
> --
> 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