Could using option 1 yield an incorrect result if more than one process is
adding records to the table simultaneously?
IDENTITY_VAL_LOCAL will return the identity value of the most recent insert
on the same level. An insert in a different job is or activation group is
performed on a different level. Even a trigger or a different program or a
procedure activated in the same job/activation group is considered to be on
a different level.
BTW it also works correctly, if the record gets written with native I/O.
In either way the IDENTITY_VAL_LOCAL function should be executed immediately
after the insert/write operation for a single row.
From the SQL Reference - Release 7.2:
The value returned is the value that was assigned to the identity column of
the table identified in the most recent insert operation (specified in
either an INSERT statement or a MERGE statement).
The insert operation has to be issued at the same level; that is, the value
has to be available locally within the level at which it was assigned until
replaced by the next assigned value.
A new level is initiated when a trigger, function, or stored procedure is
invoked. A trigger condition is at the same level as the associated
triggered action.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Rich
Marion
Gesendet: Tuesday, 01.12 2015 06:15
An: 'RPG programming on the IBM i (AS/400 and iSeries)'
Betreff: RE: Identity column
I am wondering...
Could using option 1 yield an incorrect result if more than one process is
adding records to the table simultaneously? Or is the function local to the
program.
I.E.
Prog A writes a record
Prog B writes a record
Prog A used the Identity_Val_Local().
Would Prog A get the identity of the record that Prog B wrote?
Granted that the above all occurs within less than a thousandth of a second.
Thanks,
Rich
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Mike
Jones
Sent: Monday, November 30, 2015 9:08 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: Identity column
Hi Booth,
Couple ways:
1) Use the IDENTITY_VAL_LOCAL() function which returns the most recently
assigned identity column value.
Ex: VALUES IDENTITY_VAL_LOCAL() INTO :MY_ID_VAR
2) Use the data change table reference FINAL TABLE.
Ex: select NAID# into :MY_ID_VAR from final table ( put your insert
statement here )
I'm guessing option 2) is likely to perform slightly better since it gets
the job done in one request to the database, whereas option 1 takes two
requests. Note: I've never taken the time to benchmark that hypothesis
though.
Note on option 2): you can select additional columns, not just the ID
column, if you wish. If I recall correctly, option 2) will reflect changes
made to the data in BEFORE TRIGGERS, if any are present.
If the table receiving the INSERT has AFTER TRIGGERS present that modify
data in the inserted row, you should use option 1). Typically though,
column value changes in triggers are handled in BEFORE TRIGGERS.
Mike
date: Mon, 30 Nov 2015 21:35:25 -0600
from: Booth Martin <booth@xxxxxxxxxxxx>
subject: Identity column
How do I retrieve a new record that I have just created?
The file is created with a key:
NAID# decimal(5) not null // ID#
generated always as identity
(start with 1),
The scenario is this:
-User keys the new data into blank fields on the screen, but of course
not into the key field.
-User presses Enter key. Data is used to create a new record using
embedded SQL -User wants to see the record on the screen, with the key
field populated.
-I can't figure out how to do that.
--
Booth Martin
www.martinvt.com
(802)461-5349
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2016.0.7227 / Virus Database: 4477/11095 - Release Date: 11/30/15
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
midrange.com
