Oracle Connecter - Do Not Truncate on Failure

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
yugee
Participant
Posts: 34
Joined: Fri Feb 04, 2011 5:54 pm

Oracle Connecter - Do Not Truncate on Failure

Post by yugee »

Hello,

My requirement is to truncate and load an Oracle table every day with a new file. But the catch here is, do not truncate if there is any issue with the file. My design has a external file, XML Input, transformer and Oracle Connector. I have mentioned to truncate the table. But when the job fails due to any parsing issues, the truncate statement is getting executed at the starting of the job and deleting the data. I can't have any intermediate files to first parse and validate then load. Is there any way I can stop truncating the table when the job fails?
Thanks, Yugee
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

If you're saying that you cannot, for some reason, split the existing design into two jobs (which seems like the most logical thing to do...), then I suppose you could change the Oracle Connector target table to a temporary work table that still gets truncated no matter what. Then you could perhaps craft some sort of After-SQL statements, with annotations, to check for success (records in the temp table) and if it passes that test then truncate and load the real table. Personally I would just keep it simple and go with two jobs so that the logic is more obvious to the next developer who comes along in the future.
Choose a job you love, and you will never have to work a day in your life. - Confucius
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

You can't stop the truncate, I agree with Eric on this one.
-craig

"You can never have too many knives" -- Logan Nine Fingers
yugee
Participant
Posts: 34
Joined: Fri Feb 04, 2011 5:54 pm

Post by yugee »

I agree with your design, I copied the job and replaced OC with a CopyStage. I did all my field validations in this job. The next job will just take the file and load it to the actual table. There is still a chance of truncating the table if any issue with loading data to table, but I have covered 99% of the validations in previous job. If any such things occur while testing, I will add them as well.
Thanks a lot for your suggestions.
Post Reply