Set Transaction

[aditya]

DS Gurus,

I have a requirement where I have to log errors to a file, do a count reconciliation on the file and check whether the count on the error file is greater than say x%

If the count is greater than x% then I will have to rollback all the inserts I have made to the target tables. Else I will have to commit them.

I had thought of the following job structure:

Script which
invokes the ===> DS Job
Set Transaction ===> DS Job ----> Reconciliation---> Script that
in Oracle commits the
transaction

Multiple DS Jobs run in parallel and are all associated with the same transaction.

My question was whether such a scenario is feasible or is there a better turnaround.

Thanks
Aditya.

[DSguru2B]

Do a count on the file, something like "wc -l" and perform your percentage manipulation. Pass that value as a job parameter to your job.
Specify a reject link that will capture all the rejects. The constraint will, along with the .REJECTED, IF @OUTROWNUM > ABORTCOUNT then UtilityAbortToLog("Threshold Reached") else @TRUE.
'ABORTCOUNT' will be the parameter.

[chulett]

Don't have time for a 'proper' answer, but it's way too late to commit in a script - it's done by then. Once the stage is 'closed' your changes are either commited or rolled back, all you can do after that is 'undo' them all. Which goes way beyond deleting new records, typically.

I do believe that you'll need to do your calculation 'on the fly' and abort the moment you exceed the threshold...