On a SQL insert, how can I get the RRN of the record inserted?
a4ginatl
inserted into a file.
Is there a simple way to retrieve the RRN for the inserted record
without having to do a Select to get it.
JTF
What are you trying to do?
Hau...@sss-software.de
If you are already on release on 6.1, you may try to combine select
and insert:
In the following example you'll get the relative record no of the
inserted rows, i.e. the SELECT-statment gets executed immediately
after the INSERT-Statement specified in the FROM-Clause.
Select rrn(InsTab)
From Final Table( Insert into MyTable
(Col1, Col2, ... ColN)
Select MyCol1, MyCol2, ... MyColN
From Other Table
Where ...) InsTab
Order By Input Sequence
Birgitta
Karl Hanson
Birgitta,
At first I thought the same thing, but then realized the RRN() in the
select list is actually for the intermediate result table (IRT) created
for the insert. The IRT is essentially the temporary result set that is
queried, so the RRN returned is for the IRT vs for the INSERT target
table (MyTable in your example).
create table qtemp.tst (c1 int)
insert into qtemp.tst values(1)
insert into qtemp.tst values(2)
select c1, rrn(t1) from qtemp.tst t1
C1 RRN ( T1 )
1 1
2 2
select rrn(InsTab) from final table
( insert into qtemp.tst values(3) ) InsTab
order by input sequence
RRN ( INSTAB )
1 <-- RRN for inserted row is 3
select c1, rrn(t1) from qtemp.tst t1
C1 RRN ( T1 )
1 1
2 2
3 3
******** End of data ********
--
Karl Hanson
Jonathan Ball
If you're doing this in an application program using embedded SQL, the
sixth element of the SQLERRD array, which is part of the SQLCA (SQL
communications area), will contain the row number of the last row
processed. So, if you're inserting one row at a time, then following a
successful insert, SQLERRD(6) should contain the RRN of the inserted
row. If you were insulting multiple rows at a time, it would contain
the RRN of the last row inserted in the multiple row insert operation.
Jonathan Ball
Sorry; the above doesn't apply to DB2 on System i; it is accurate for
Informix:
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqlt.doc/sqltmst189.htm
JTF
I looked around, but I don't think it is possible either
a4ginatl
I have a application that is writing records into a file/table all
completely random from many independent sources. Because of the
potential variety of clients, the system cannot dictate a unique
identifier as it depends on the client system to create a unique key.
Client systems could easily duplicate an ID field. So my safest way
would be to retrieve the RRN for the record added. The issue is, the
client system needs to chain back to the added record within a second
of it being added.
The application could feasibly handle 1000 records per minute being
added.
a4ginatl
wrote:
The application is being developed at V5R3.
JTF
Web or green??
walker.l2
generate a GUID which could then be passed back to the client system?
a4ginatl
This is a batch program, but is linked to Web requests. Thanks
a4ginatl
> Instead of using the RRN, why not use a trigger on the DB table to
> generate a GUID which could then be passed back to the client system?
The program does not talk directly to the client system. This program
is a batch type program that needs to retrieve the result before
returning the record to the client system. Its like a web service.
Its a while since I worked with triggers and would have to read up on
the process to see how it communicates back to the originating
program. If it does not, I guess have the same issue then with the
trigger process. I have used the triggers before responding back with
error messages.
Thanks
walker.l2
be better than generating a GUID.