Thanks Elvis. I saw the info about levels in the Info Center docs but
it seemed ambiguous at best about the scope of a level across jobs. It
sounded like another job invoking at the same level (say, a couple of
jobs running batch inserts) could confuse the return value I would
expect.
After sending the original email, I tested the process by signing onto
two sessions and doing inserts and selecting the identity value back.
After inserting a row in each session, I queried the identity value and
it was unique to that session's insert.
V6R1 would be wonderful, but I'm stuck at V5R3 for now. At least, I
hope it's just for now and not permanent.
ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx 03/30/2009 1:19:47 PM >>>
I wrote a bit about identity column attributes in our April 2006
newsletter.
Here is the excerpt from that article pertaining to your question:
"The value returned is the value that was assigned to the identity
column of
the table identified in the most recent INSERT statement issued at the
same
level for a table
containing an identity column. A new level is initiated when a trigger,
function, or stored procedure is invoked so take care when using in that
situation."
Barring the restrictions above, you're safe to assume the
IDENTITY_VAL_LOCAL
returned has been generated by your INSERT statement. Definitely not
some
other job's.
With V6R1, there is a new SELECT FROM INSERT functionality that allows
you
to return values like identity column attributes while executing the
INSERT.
This is pretty cool and you should use it if you're at 6.1.
Elvis
Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com
-----Original Message-----
Subject: SQL Identity Column
I read Daniel Cruikshank's article in the Centerfield Newsletter about
identity columns and would like to use them as suggested as a primary
key but was puzzled as to how you determine what value the system
assigned. In researching it a bit, I found the IDENTITY_VAL_LOCAL()
function.
However, I can't see that there is any guarantee that the value returned
is from the insert that was just done by my job as opposed to an insert
done by another job. How do I know that another job didn't sneak in an
insert after mine and just before the function returns a value?
midrange.com
