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.

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

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 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog