Stored procedure return the primary key after insert ?

Question

Hello guys, I want a stored procedure to return the primary key of the new record after it gets executed. I think it will be returned by OUT parameter in the procedure. But how to select the newly inserted row ID ? I don't want to use select MAX(row_id) as it is a multi user environment. Any procedure sample will be appreciated.

My platform is ISeries DB2 V5 R4. Thanks.

Edit

The row id Column is not an identity column. It uses a sequence for the key which gets generated via a trigger before insert on table.

Edit

Here is what I am trying to do

Begin Stored procedure
   Insert into Employees;
   (row id gets automatically generated by trigger) 
Return row id ;

I want to avoid a select in returning row id.

Answer 1

just set the out parameter to the column that contains the PK.

CREATE PROCEDURE DB2TBL.DO_STUFF (IN Param1 INT, IN Param2 CHAR(32),OUT Param3 INT) 
/* Param1 is primary key */
LANGUAGE SQL
P1: BEGIN
DECLARE OUTPARAM INT;
/* Do the stored procedure */
SET OUTPARAM = Param1; 

Answer 2

--UPDATED---

Hi Popo,

First off could you give more detail on what you mean when you say the rowid is assigned by a trigger?

If you had a real identity column you would use the IDENTITY_VAL_LOCAL() function like this right after the INSERT: SELECT IDENTITY_VAL_LOCAL() INTO myrowid FROM SYSIBM.SYSDUMMY1; I'm not 100% on that syntax because I generally use embedded SQL and it works differently there so you might have to play with it. IBM documentation is at http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/index.jsp?topic=/db2/rbafzscaidentity.htm.

However since you are doing something more complicated, I think this alternate method might work. You'll need to re-format your INSERT to be wrapped in a SELECT.

SELECT myrowid
INTO myrowid
FROM FINAL TABLE (
   INSERT INTO myfile (myrowid, other_stuff) VALUES (default, 'blah')
)

You'll need to adjust for the proper field names and so on but I think this will do the trick. There's not much documentation but if you want to see it go to http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/index.jsp?topic=/db2/rbafzbackup.htm and scroll all the way down to the bottom of the page.

Cheers