DSXchange

Hello,

I have a Db2 table1 as target for which the key is DB Sequence generator. I also have an other table2 in the same job for which the key is the FK from the table1. Each time only one record will get inserted into the table1, once its inserted I need the seq value which has been generated and use it as key for the table2 as both have common details. Is there any way where I can store that value of table1 once inserted and use it to insert in table2?

Thank you!

1) Give the DB sequence and connection details in Surrogate Key tab in stage properties of Transformer.
2) Create a Stage Variable and pass 'NextSurrogateKey()' function as derivation.
3) Map the same Stage Variable to both Table1.PK and Table2.FK columns thru respective output links
4) Insert/Update Table1 in same job and Table2 in subsequent job. Handling data load of both Parent and Child tables in same job is not advisable.

The reason I am asking to do it in one job is the flow of the data is same all the way till target and you already said its not advisable. Is there any way where we can do it as it will be more complex to built an other job with the same flow..

regarding your input
So will the DB sequence in surrogate Key tab have the recent value in the table and later when I use nextSurrogateKey() it will give the next value which will be the same as the DB sequence value.
Also when you said map the stage variable to both table1.PK and table2.FK. I cannot use the value in table2.FK in the same job so store it in a file and retrieve in an other job. Please correct me if I am wrong

Thank you very much!

1) Yes, NextSurrogateKey() funtion will work in the way you expect.
2) Yes, you can pass the stage variable value to a file/dataset and use it next job as you wish.

Thank you Sam, will follow the same!