How can I return the generated keys to JDBC from a DB2 stored procedure that does an insert?

Question

I've got a simple stored procedure that does an insert to a table with an identity primary key column. I need to get the key that was generated by the insert. I would prefer to use the standard JDBC getGeneratedKeys method but I'm not sure what the stored procedure needs to do/return to build that result set. My end goal is to do this from Hibernate. I also tried using the select identity generator but it always returns 0.

Answer 1

On DB2/400 it seems to be the IDENTITY_VAL_LOCAL() function, that returns the most recently assigned value for an identity column.

http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/admin/r0004231.htm

Answer 2

I've never tried the identity val local() function; in my own stored procedures, I just do a SELECT after the insert. But you can have a stored procedure return a result set:

create procedure proc1(
IN in_val
)
language sql
dynamic result sets 1

BEGIN
-- do insert

BEGIN
DECLARE c_out CURSOR WITH RETURN TO CLIENT FOR
  select max(key) as inserted_key from table where val = in_val
FOR READ ONLY;

OPEN c_out;
END;

END;

You can probably use identity val local replacing the select with "select identity val local() from sysibm.sysdummy1". I can't seem to get the underscores to work in markdown, but hopefully this is clear.

Answer 3

The stored procedure can use identity val local to get the generated value and pass it as output parameter to Java program . Select after insert is not a good idea as other processes may have inserted new data to the table and would cause data integrity issues.

Answer 4

It is also possible to select from the insert:

SELECT pkey FROM FINAL TABLE (INSERT INTO tab(a, b, c, d) VALUES ... )

Answer 5

Fred Sobotka's answer worked for me.. thanks