RE: Identity value of 0

That reference appears to be for SQL UPDATE, as opposed to RLA UPDATE.

I think the RLA behavior is wrong. I submitted an RFE, but I'm not sure what more I can do.

My update trigger has the before and after buffer. If the before buffer has an ID value and the after buffer has a 0, I move the before ID into the after buffer. This occurs prior to the update actually happening, preventing the ID from being lost.




-----Original Message-----
From: Rob Berendt [mailto:rob@xxxxxxxxx]
Sent: Wednesday, February 07, 2018 12:37 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: Identity value of 0

Ok, this is how it works.
https://code.midrange.com/926dc6e2a5.html
I pasted it here because email readers have an issue with SQL comments.
It's a double dash thing.

https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/db2/rbafzupdt.htm

This still doesn't explain why you would get a zero. However you made it quite clear that you are not interested in pursuing this any further and somehow you got your trigger to assign the appropriate value.


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: Rob Berendt <rob@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 02/07/2018 09:23 AM
Subject: Re: Identity value of 0
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



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