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.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog