topic Re: How to get last inserted id in DB2? in Talend Studio

How to get last inserted id in DB2?

Anonymous — Wed, 03 Jun 2015 18:50:54 GMT

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.

Re: How to get last inserted id in DB2?

Anonymous — Wed, 03 Jun 2015 21:35:14 GMT

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.

Re: How to get last inserted id in DB2?

Anonymous — Wed, 03 Jun 2015 21:56:14 GMT

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!

Re: How to get last inserted id in DB2?

Anonymous — Wed, 03 Jun 2015 22:16:39 GMT

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.

Re: How to get last inserted id in DB2?

Anonymous — Thu, 04 Jun 2015 18:13:07 GMT

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