Hi all,
I am new to DataStage, and the following is my current scenario:
1) My input is a sequential file;
2) A transformer stage extracts info from the file's header, that are to be stored in an SQL Server table with an identity column called SheetID;
3) The file body contains records that are to be stored in another table, having SheetID as a foreign key.
My goal is to insert the header's data into the first table, retrieve the generated identity value and use it to store the rest of the file.
Any suggestion will be welcome
THank you in advance.
Stefano
Inserting data in SQL table & retrieving its id column v
Moderators: chulett, rschirm, roy
</a>Hi Arnd,and thank you for your quick reply.
Yes, I thought about something similar, my stage variable is ready to accept the value you mean and it would be glad to do it, but that poor variable itself cannot figure out how to retrieve the SQL-generated identity value.
In addition, identity column values aren't to be included in the INSERT statement, but when I don't include all column values in the SQL Server stage, DataStage raises an error.
Stefano
Yes, I thought about something similar, my stage variable is ready to accept the value you mean and it would be glad to do it, but that poor variable itself cannot figure out how to retrieve the SQL-generated identity value.
In addition, identity column values aren't to be included in the INSERT statement, but when I don't include all column values in the SQL Server stage, DataStage raises an error.
Stefano
ElBandido (Americans would add "Frito" to the name...)
Since you are generating the key in Oracle you have not choice but to read it into DS from Oracle after the fact. Because of timing and buffering issues you will have to be careful, i.e. it is no good reading the value before it shows up in Oracle.
I would make the Header link from your transform write the information as the 1st link out of the transform and make sure you immediately write the data and don't buffer it.
If your data volume is small then you could put a lookup link from that same Oracle stage back into the transform to get that column value back and use that value in each row. Since this actually does perform a lookup for each row this isn't the most efficient of methods.
As this is easiest solution I'll leave it at that, if this will now work because of data volumes post that and either myself or someone else will assist.
Since you are generating the key in Oracle you have not choice but to read it into DS from Oracle after the fact. Because of timing and buffering issues you will have to be careful, i.e. it is no good reading the value before it shows up in Oracle.
I would make the Header link from your transform write the information as the 1st link out of the transform and make sure you immediately write the data and don't buffer it.
If your data volume is small then you could put a lookup link from that same Oracle stage back into the transform to get that column value back and use that value in each row. Since this actually does perform a lookup for each row this isn't the most efficient of methods.
As this is easiest solution I'll leave it at that, if this will now work because of data volumes post that and either myself or someone else will assist.