RE: SQL Identity Column

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?