Hello,
I have to insert number of rows in to DB table, At the same time I want to know , If Insert was a success then I want to write in one file else I want to write in another File. Is there any way, I can check about the status of insert, may be link status etc.?
input---xformer--> insert
|
|
IF insert Fail write to one file else write to another file.
How to know whether INSERT in Oracle Was a success
Moderators: chulett, rschirm, roy
Re: How to know whether INSERT in Oracle Was a success
May I suggest to read the DataStage books or follow some training 
The following options to detect errors are from the DataStage books you get while installing DataStage (all require a transformer and a stage to write errors to in this transformer):
1) The easiest is to set the "Reject Row" in the constraint of the error link to "Yes" and leave the constraint empty. This way it the reject link will act as catch-all for rows which have not been written successfully (either because they do not satisfy the link constraints or because of write errors) Since it?s a catch-all such a link should be defined as the last link in processing order;
2) Set the "Reject Row" in the constraint of the error link to "Yes" and set the constraint to "inputlinkname.REJECTED". Rows of inputlinkname that could not be written either because of not matching the constraint or actual write errors. The reject link should occur after the output link;
3) Set the "Reject Row" in the constraint of the error link to "Yes" and set the constraint to "inputlinkname.REJECTEDCODE <> 0". Rows of inputlinkname that could not be written because of write errors will be written to the error-link;
4) Set the "Reject Row" in the constraint of the error link to "Yes" and set the constraint to a combination of constraints. E.g inputlinkname.REJECTEDCODE = 0 AND inputlinkname.REJECTED (rows with no write errors but that failed the constraint);
5) Set the "Reject Row" in the constraint of the error link to "No", but use a constraint to match "rejected rows".
If an input row does not match any of the constraints of the outputs including the Reject output the row will be ignored.
The thing for you to use would be option 3) above.
Ogmios
The following options to detect errors are from the DataStage books you get while installing DataStage (all require a transformer and a stage to write errors to in this transformer):
1) The easiest is to set the "Reject Row" in the constraint of the error link to "Yes" and leave the constraint empty. This way it the reject link will act as catch-all for rows which have not been written successfully (either because they do not satisfy the link constraints or because of write errors) Since it?s a catch-all such a link should be defined as the last link in processing order;
2) Set the "Reject Row" in the constraint of the error link to "Yes" and set the constraint to "inputlinkname.REJECTED". Rows of inputlinkname that could not be written either because of not matching the constraint or actual write errors. The reject link should occur after the output link;
3) Set the "Reject Row" in the constraint of the error link to "Yes" and set the constraint to "inputlinkname.REJECTEDCODE <> 0". Rows of inputlinkname that could not be written because of write errors will be written to the error-link;
4) Set the "Reject Row" in the constraint of the error link to "Yes" and set the constraint to a combination of constraints. E.g inputlinkname.REJECTEDCODE = 0 AND inputlinkname.REJECTED (rows with no write errors but that failed the constraint);
5) Set the "Reject Row" in the constraint of the error link to "No", but use a constraint to match "rejected rows".
If an input row does not match any of the constraints of the outputs including the Reject output the row will be ignored.
The thing for you to use would be option 3) above.
Ogmios
In theory there's no difference between theory and practice. In practice there is.