How to get last inserted id in DB2?

Six Stars

How to get last inserted id in DB2?

Hello,
Anyone has an idea on how to get last inserted id in DB2?
I have two tables in db2, one child, one parent. I would like to insert data into one table, and get the id (which auto generated in the db2,) then insert the id into another table as a foreign key.
Thanks.
Seventeen Stars

Re: How to get last inserted id in DB2?

The best way is to get the new id first with a tDB2Row (within a flow + tParseRecordSet) and use this new id directly.
All other ways are not really reliable.
Six Stars

Re: How to get last inserted id in DB2?

Hi jlolling,
Thanks for taking time to answer my question.
Do you mean get the new id before insert any data into the first table? My table is an existing table, and id is designed to auto-generated by database. I don't think I can change it now.
If I misunderstood your meaning, could you please explain a little bit more.
Thanks!
Seventeen Stars

Re: How to get last inserted id in DB2?

In this case you cannot follow my last advise. 
Ok try this:
Within the same connection and same transaction use tDB2Input with this select:
SELECT SYSIBM.IDENTITY_VAL_LOCAL() AS id FROM my_schema.my_table

Hope it works. I have not tested it yet.
Six Stars

Re: How to get last inserted id in DB2?

Yes. I used this one. It works.
SELECT Identity_val_Local() as  id FROM sysibm.sysdummy1
Thanks for giving me the clue, jlolling.