Restart sequencer after abort

deva · Post by **deva** » Fri Dec 04, 2009 11:03 am

Hi,
Need your help

I have one sequencer, it have 5 job activities and the 3rd job get failed. The data volume is huge , job get aborted after 2 m records. I want to restart the sequencer from that failure position. Which means from 3rd job activity should start and start load from 2 m record. We are using DB2 is one target and other target is oracle.
We are using data stage 8.1 Parallel.

Let say if the database is comming every 2000 records is there any way to capture the committed max key and load from there onwards.

Sreenivasulu · Post by **Sreenivasulu** » Fri Dec 04, 2009 11:10 am

Restartability of sequence does not work as desired. This view is based on my experience.

Regards
Sreeni

deva · Post by **deva** » Fri Dec 04, 2009 11:13 am

We have functionality in Datastage. to restart the process from the abort point. But I don't know exactly how to do that

Sreenivasulu · Post by **Sreenivasulu** » Fri Dec 04, 2009 11:15 am

You can do this setting using a scheduler like dollaruniverse/autosys.
If you want to do this in datastage you need to store the status in of job in a file/db and then restart from that point.

Regards
Sreeni

chulett · Post by **chulett** » Fri Dec 04, 2009 11:31 am

There is a huge difference between restarting a Sequence at the failure point and doing the same within a single job. The former is an automatic feature of checkpoints while the latter needs to be explicitly coded into your job.

chulett · Post by **chulett** » Fri Dec 04, 2009 11:32 am

Sreenivasulu wrote:Restartability of sequence does not work as desired. This view is based on my experience.

And based on my experience it works exactly as desired.

deva · Post by **deva** » Fri Dec 04, 2009 12:16 pm

Restartability of job sequencer can do by doing the check point.

but my question is how to start the loading process from the failure process, which means from the last commit.

We are not using any scheduling tool. I am running the job sequencer through datastage director.

Sreenivasulu · Post by **Sreenivasulu** » Fri Dec 04, 2009 1:02 pm

Craig i had difficulties in restarting from failover point. Will again check and update you.

Regards
Sreeni

deva · Post by **deva** » Fri Dec 04, 2009 1:33 pm

As per my knowledge , we can do in informatica. but we need to create two tables and we need to set the properties to session and workflow.
I did't know the table names.
if the session abort , it will re run the process in recovery mode. if the commit intervell is 2000 and job fail at 2050 , in the recovery mode it will delete 50 records and starts from 2001. How will do in datastage

chulett · Post by **chulett** » Fri Dec 04, 2009 1:43 pm

You'll need a mechanism to record the latest commit point and then another mechanism to check that (typically via a contraint) so that you only start 'processing' rows once you are past the previous commit point. A successful job run would set that to zero.

Of course, all this assumes a static source.

ray.wurlod · Post by **ray.wurlod** » Fri Dec 04, 2009 2:03 pm

DataStage would commit 2000 and then rollback 50 when the abort occurs.

dsuser_cai · Post by **dsuser_cai** » Fri Dec 04, 2009 4:48 pm

Hi

First if you want to restart the sequence from where it aborted, as mentioned previously you need to know where it failed, ie the point of failure. so try to get this information first. In my system, what we do is all the job activity will have a number and we have a seperate file where we store this. so every time a sequence aborts, the point of failure will be noted (to a text file - this will be a number, example 1005). So when we start the sequence next time we will read this number first (to get start point), so the sequence will start from this point of failure.

Second, if you want to process from the record where it failed, say 2 million. then you should have a mechanism in your job to capture the last key (we se the last key value) value, or if you are using dates then you need to capture the maximum or latest dates (form your target) and now if you start your job it will capture from the point it left.

chulett · Post by **chulett** » Fri Dec 04, 2009 6:21 pm

I'm curious why you built a 'check pointing' system for your job sequences when that functionality is already built in?

chulett · Post by **chulett** » Tue Dec 08, 2009 8:53 am

You don't need to 'capture the last key' but rather the last record number committed. Your source is static correct? Note that running on multiple nodes will complicate this.

PS. 'Key' just means any column(s) that uniquely identify a record.