How to rollback all written records if lookup fails

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
jeisma
Participant
Posts: 18
Joined: Mon Mar 20, 2017 12:19 pm

How to rollback all written records if lookup fails

Post by jeisma »

hi,

I have a look that is set to FAIL on lookup failure. However, if there were already written records before the failure, they are not reverted.

For example if I have 10 records, and 6th record fail, the job aborts indeed, but the previously written 5 records stays in the database.


TIA!
PaulVL
Premium Member
Premium Member
Posts: 1315
Joined: Fri Dec 17, 2010 4:36 pm

Post by PaulVL »

easy way is to break it up into two jobs.

first job writes to a dataset.

if the lookup fails, then abort.

Job #2 would only run if job #1 didn't abort.

=============

Write to a temp table and do an after job upsert into your main table if the job didn't abort.

================

There are other tactics you can use as well. Like including a watermark in your table load. Then just delete the entries you inserted with that watermark. You would have to understand if it was an insert or an update...

I would simply go for the dataset approach, but your data VOLUME will dictate the best approach for you to use.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

What's your commit interval set to in the job? Sounds like it is set to 1 to see that behavior. :?
-craig

"You can never have too many knives" -- Logan Nine Fingers
jeisma
Participant
Posts: 18
Joined: Mon Mar 20, 2017 12:19 pm

Post by jeisma »

You mean Transaction - Record Count? It was at default, 2000. I changed it to value higher than the number of records being processed. Still no good. (Although, I would not do this as the number of records is unknown).
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Okay... at a high level if it only issued a commit after 2000 records or end-of-job then saying that when record 6 fails the job the previous 5 are still in the target didn't make sense to me, hence the question. Something else is going on, perhaps partitioning is affecting the behavior - one node completes and commits its work while others fail? Not sure. Running on a single node might help make the behavior less... unpredictable. :?

To have full control over it, I would follow what Paul is suggesting... use two jobs to make a validation pass through the data first and only if it is fully blessed, load the data.
-craig

"You can never have too many knives" -- Logan Nine Fingers
jeisma
Participant
Posts: 18
Joined: Mon Mar 20, 2017 12:19 pm

Post by jeisma »

i was just citing an example. but thank you. indeed i will try Paul's suggestion.
Post Reply