[resolved] Rollback entire transaction

One Star

[resolved] Rollback entire transaction

Hi,
I have delimited file, mapped via tMap to 4 different Oracle output tables. If there is an error record in any of the records, I need the entire transaction to be rolled back. And I also want all the records that were rejected to be in the Oracle File.
Here's what I have so far:
tOracleConnection-->onSubjobOK-->tFileInputDelimited-->tMap-->OracleOutput1-->??????
If I have 5 records, and 2 were rejected, while 3 were good, how do I rollback the entire thing?
Records can be rejected for variety of reasons: formatting issues, or b/c they already exist in the database...
I put tRollback after tOracleOutput, but not sure how to specify a condition.
Thanks,
Boris
One Star

Re: [resolved] Rollback entire transaction

Hi,
Here's a suggestion of what you could do:
1. Wire the Rejects link from each of the tOracleOutput to the OracleFile.
2. Have a PostJob flow as the following:
tPostJob ---onComponentOk--> tJava ----- if (number of lines in OracleFile > 0) ----> tOracleRollBack
----- if (number of lines in OracleFile <= 0) -----> tCommit
Note: You can get the number of lines in OracleFile through the following: ((Integer)globalMap.get("tFileOutputDelimited_1_NB_LINE")).intValue()
Hope this helps!
Regards,
Serpico
One Star

Re: [resolved] Rollback entire transaction

I tried without tJava by doing the following:
tOracleOutput-->Rejects-->tFileOutputDelimited-->runIf-->tOracleRollback.
in runIf I specified a condition: ((Integer)globalMap.get("tFileOutputDelimited_1_NB_LINE")) > 0
When I run it, it checks runif, marks it as True, but does not rollback the other records, that were inserted.
One Star

Re: [resolved] Rollback entire transaction

You might want to increase the "commit every" field in the tOracleOutput.
One Star

Re: [resolved] Rollback entire transaction

Makre sure the "Auto Commit" in your tOracleConnection is unchecked.
One Star

Re: [resolved] Rollback entire transaction

i have it set to 10000.
and I am only processing three test records as of now.
One Star

Re: [resolved] Rollback entire transaction

auto commit is unchecked.
What else could it be?
One Star

Re: [resolved] Rollback entire transaction

Make sure then you "use an existing connection" in your tOracleOutput.
One Star

Re: [resolved] Rollback entire transaction

Serpico,
That was it!!! use existing connection box had to be checked.
Thanks for figuring this out.
I will go and try to put all of this in PostJob and Prejob.
Thanks again, I really appreciate this.
One Star

Re: [resolved] Rollback entire transaction

Do I need to put any code into tJava, or just into RunIf connectors?
One Star

Re: [resolved] Rollback entire transaction

No need to put any code in tJava. I usually put this component as a pre-step to use the RunIf connectors.
One Star

Re: [resolved] Rollback entire transaction

So for two outputs, I specify:
((Integer)globalMap.get("tFileOutputDelimited_1_NB_LINE")) <= 0 &&
((Integer)globalMap.get("tFileOutputDelimited_2_NB_LINE")) <= 0
in the RunIf connector and get an error.
Is this not the correct way?
One Star

Re: [resolved] Rollback entire transaction

i guess this is correct: ((Integer)globalMap.get("tFileOutputDelimited_1_NB_LINE")+(Integer)globalMap.get("tFileOutputDelimited_2_NB_LINE")) <= 0
One Star

Re: [resolved] Rollback entire transaction

((Integer)globalMap.get("tFileOutputDelimited_1_NB_LINE")) <= 0 &&
((Integer)globalMap.get("tFileOutputDelimited_2_NB_LINE")) <= 0

The above condition will call the Commit if it is true.
On the opposite, the following condition will call the RollBack if it is true:
((Integer)globalMap.get("tFileOutputDelimited_1_NB_LINE")) > 0 ||
((Integer)globalMap.get("tFileOutputDelimited_2_NB_LINE")) > 0