Re: Identity value of 0

[Gord Hutchinson <gordm1@xxxxxxxxxxxxxxx>]

I just copy/pasted your script and I did not get a value of 2.  After doing
the update I got:

ID MYTEXT
1 a

We're still on 7.1


Gord


On Wed, Feb 7, 2018 at 9:22 AM, Rob Berendt <rob@xxxxxxxxx> wrote:

> Here's some freaky stuff.  I never would have suspected I would have
> gotten that 2.
>
> create table qtemp.testtable (
>                 "ID" INTEGER GENERATED ALWAYS AS IDENTITY (
>                 START WITH 1 INCREMENT BY 1
>                 NO MINVALUE NO MAXVALUE
>                 NO CYCLE NO ORDER
>                 CACHE 20 ),
>                 mytext char(20));
> INSERT INTO QTEMP.TESTTABLE (MYTEXT) VALUES('D');
>
> SELECT * FROM QTEMP.TESTTABLE;  -- "ID"=1
>
> UPDATE QTEMP.TESTTABLE OVERRIDING USER VALUE
> SET ID =0, MYTEXT='a'
> WHERE ID=1;
>
> SELECT * FROM QTEMP.TESTTABLE;  -- "ID=2
>
> UPDATE QTEMP.TESTTABLE OVERRIDING SYSTEM VALUE
> SET ID =0, MYTEXT='q'
> WHERE ID=2;
>
> SELECT * FROM QTEMP.TESTTABLE;  -- "ID=0
>
>
> Rob Berendt
> --
> IBM Certified System Administrator - IBM i 6.1
> Group Dekko
> Dept 1600
> Mail to:  2505 Dekko Drive
>           Garrett, IN 46738
> Ship to:  Dock 108
>           6928N 400E
>           Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> From:   Justin Taylor <JUSTIN@xxxxxxxxxxxxx>
> To:     "MIDRANGE-L (midrange-l@xxxxxxxxxxxx)" <midrange-l@xxxxxxxxxxxx>
> Date:   02/06/2018 12:10 PM
> Subject:        Identity value of 0
> Sent by:        "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
>
>
>
> I have a DDL table with an identity column defined.  Some new rows are
> created with a 0 value.  I didn't think that was possible.  Any ideas?
>
> For reference, here's the column definition:
>                 "ID" INTEGER GENERATED ALWAYS AS IDENTITY (
>                 START WITH 1 INCREMENT BY 1
>                 NO MINVALUE NO MAXVALUE
>                 NO CYCLE NO ORDER
>                 CACHE 20 )
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> 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: http://amzn.to/2dEadiD
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> 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: http://amzn.to/2dEadiD