Is it possible to get the RRN after an SQL insert? 
Author Message
 Is it possible to get the RRN after an SQL insert?

Hi DB2/400 gurus

I am trying to determine if it possible (via C and SQL) to reproduce
behaviour that is possible via RPG and native file access on the
iSeries. In RPG, you can use a program information data structure to
get the RRN for the most recently inserted record. (If this sounds
incorrect, it's because I don't exactly how it works. But I do know it
is possible somehow, as we have RPG code that does it.)

What I need to know is, if I am doing the insert with SQL, is there a
way to inquire what the RRN for the just inserted row was?

What I already know (so please don't suggest):
1) if I have a unique key on the file, I can re-read the record and
use the expression select RRN(table) from table where primkey = ?.
However, some files do not have a unique key.
2) I could use select max(rrn(table)) if I never reorganised the
table, but the performance would be very bad.

I'm looking for an API or SQL feature that I have missed in my quest.
Can anyone help?

Thanks

Cassie



Sat, 07 Aug 2004 13:50:19 GMT
 Is it possible to get the RRN after an SQL insert?

No easy way until iSeries adds support for IDENTITY and SEQUENCES.  You can find
some simulations of this support at:
   www.iseries.ibm.com/developer/db2/porting.html

--
Kent Milligan, DB2 & BI team
PartnerWorld for Developers, iSeries

Quote:
>>> www.iseries.ibm.com/db2

(opinions stated are not necessarily those of my employer)


Sat, 07 Aug 2004 23:37:29 GMT
 Is it possible to get the RRN after an SQL insert?
What about a trigger program or journal to retrieve the last RRN added



Quote:
>Hi DB2/400 gurus

>I am trying to determine if it possible (via C and SQL) to reproduce
>behaviour that is possible via RPG and native file access on the
>iSeries. In RPG, you can use a program information data structure to
>get the RRN for the most recently inserted record. (If this sounds
>incorrect, it's because I don't exactly how it works. But I do know it
>is possible somehow, as we have RPG code that does it.)

>What I need to know is, if I am doing the insert with SQL, is there a
>way to inquire what the RRN for the just inserted row was?

>What I already know (so please don't suggest):
>1) if I have a unique key on the file, I can re-read the record and
>use the expression select RRN(table) from table where primkey = ?.
>However, some files do not have a unique key.
>2) I could use select max(rrn(table)) if I never reorganised the
>table, but the performance would be very bad.

>I'm looking for an API or SQL feature that I have missed in my quest.
>Can anyone help?

>Thanks

>Cassie



Sun, 08 Aug 2004 22:22:42 GMT
 Is it possible to get the RRN after an SQL insert?
Sorry Elbert, I am looking for concrete answers, not vague guesses. If
you have actually accomplished this, please let me know.

There is no way to write a before trigger that guesses the next RRN.
There is no way to write an after trigger that retrieves the RRN for
the record just inserted; it's only available when inserting a new
record in RPG.

There ARE journal APIs that allow you to retrieve the RRN.
Unfortunately, they would be onerous and would require tables to be
journaled. (Journalling is optional with the RPG method.)

We are just going to tell our customers they need a unique key on
their file if they want to get the RRN back after insert, and we'll
implement a re-read via the unique key to get it back.

BTW, if Kent from IBM says there is no way to do it, I'll believe him!

Quote:

> What about a trigger program or journal to retrieve the last RRN added



Mon, 09 Aug 2004 08:08:54 GMT
 Is it possible to get the RRN after an SQL insert?
A trigger could launch a RPG program to jump to the end of the file
and READP the last record and write the RRN of that record to a Data
Area.

Then the next tiime around you could check the Data Area and know the
RRN or the Unique Key Value of the last record currently in the file.

As new records are inserted the trigger would keep the Data Area
updated in Theory.

Or you could use two Data Areas one to store first RRN were they began
inserting data and the second to store the RRN of the last record they
inserted.

But then again I'm not sure what your trying to do. Your guestion was
kinda vague.



Quote:
>Sorry Elbert, I am looking for concrete answers, not vague guesses. If
>you have actually accomplished this, please let me know.

>There is no way to write a before trigger that guesses the next RRN.
>There is no way to write an after trigger that retrieves the RRN for
>the record just inserted; it's only available when inserting a new
>record in RPG.

>There ARE journal APIs that allow you to retrieve the RRN.
>Unfortunately, they would be onerous and would require tables to be
>journaled. (Journalling is optional with the RPG method.)

>We are just going to tell our customers they need a unique key on
>their file if they want to get the RRN back after insert, and we'll
>implement a re-read via the unique key to get it back.

>BTW, if Kent from IBM says there is no way to do it, I'll believe him!


>> What about a trigger program or journal to retrieve the last RRN added



Mon, 09 Aug 2004 23:51:17 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. I am getting this message when i am tring to export or import anything using

2. SQL delete using RRN

3. Is this Possible Using DTS?Could you say the details.I am new

4. Trigger on Insert / Getting Newly Inserted Primary Key

5. Is this possible, or am I pushing it?

6. DB2's RRN function

7. Null parameters for SQL-inserts, is it possible?

8. I am getting the following connect error

9. Why am I getting a 3155 Error?

10. I am getting an error in asp

11. I am getting a DBMSSPXN SQLSTATE=0100 problem

12. Why am I getting an #ERR


 
Powered by phpBB® Forum Software