How can I treat a sequence value like a generated key?

Question

Here is my situation and my constraints:

1. I am using Java 5, JDBC, and DB2 9.5

2. My database table contains a BIGINT value which represents the primary key. For various reasons that are too complicated to go into here, the way I insert records into the table is by executing an insert against a VIEW; an INSTEAD OF trigger retrieves the NEXT_VAL from a SEQUENCE and performs the INSERT into the target table.

3. I can change the triggers, but I cannot change the underlying table or the general approach of inserting through the view.

4. I want to retrieve the sequence value from JDBC as if it were a generated key.

Question: How can I get access to the value pulled from the SEQUENCE. Is there some message I can fire within DB2 to float this sequence value back to the JDBC driver?

Resolution: I resorted to retrieving the PREVIOUS_VAL from the sequence in a separate JDBC call.

Answer 1

Have you looked at java.sql.Statement.getGeneratedKeys()? I wouldn't hold out much hope since you're doing something so unusual but you never know.

Answer 2

You should be able to do this using the FINAL TABLE syntax:

select * from final table (insert into yourview values (...) );

This will return the data after all triggers have been fired.