CheckPoints in Sequencer
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 30
- Joined: Tue Jun 20, 2006 10:22 pm
CheckPoints in Sequencer
Hi,
I have 4 jobs in a sequencer.While it was running the third job has been aborted.when it was aborted it has been loaded the records 3000 records into target.I resolved that job and again i restarted the sequencer.I want to load the record number 3001 onwards not from starting.I enabled the checkpoint option in Sequencer also.But it has been loading from the starting and rejecting the records since those records already loaded into target.I want to load from 3001 onwards.
How to resolve this please give me the solution.
Regards,
Reddy.
I have 4 jobs in a sequencer.While it was running the third job has been aborted.when it was aborted it has been loaded the records 3000 records into target.I resolved that job and again i restarted the sequencer.I want to load the record number 3001 onwards not from starting.I enabled the checkpoint option in Sequencer also.But it has been loading from the starting and rejecting the records since those records already loaded into target.I want to load from 3001 onwards.
How to resolve this please give me the solution.
Regards,
Reddy.
Re: CheckPoints in Sequencer
So, your 'Rows per transaction' was set as a factor of 3000. The DataStage checkpoint does NOT rollback rows that have been committed. But as Kim pointed out, it skips 'jobs' that have finished successfully (or with warnings) and reruns those that failed. In order that you start loading from the 3001 row (assuming that the abort resolution did not change any of your values), put a constraint in your transformer (prior to the target stage): @INROWNUM > 3000. But of course, this is just a quick fix.prmuttireddy wrote: when it was aborted it has been loaded the records 3000 records into target.
gateleys
Or to simplify your life, set the transaction size to 0 which means all or nothing. In other words, if the job fails, the inserted rows will be rolled back. This way every time you restart your job, it will start loading afresh.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 30
- Joined: Tue Jun 20, 2006 10:22 pm
-
- Participant
- Posts: 10
- Joined: Wed May 17, 2006 9:03 pm
You can do it yes. But there are limitations. If your database is partitioned then it becomes even more complicated.
Do a select max(id) from table and store the result in a hashed file keyed on a dummy key.
Reference that key in your transformer and specify the constraint
In.Key > Max.key
where In.key is your input key and Max.key is the max key value stored in the hashed file. This way if 3000 records got loaded and your input has keys from 1-6000. The next time the job runs after failure, the Max.key will be 3000 and hence the transformer will only allow 3001+ values to pass through.
Once again, this will only work for a non-partitioned database.
But if your data size is rather small, just specify the transaction size to 0 and get done with it.
Do a select max(id) from table and store the result in a hashed file keyed on a dummy key.
Reference that key in your transformer and specify the constraint
In.Key > Max.key
where In.key is your input key and Max.key is the max key value stored in the hashed file. This way if 3000 records got loaded and your input has keys from 1-6000. The next time the job runs after failure, the Max.key will be 3000 and hence the transformer will only allow 3001+ values to pass through.
Once again, this will only work for a non-partitioned database.
But if your data size is rather small, just specify the transaction size to 0 and get done with it.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Thanks Chuck.
You can also consider something like a 'check-point' system, one that records the most recent commit point as the job runs in a flat file, for instance. At completion of the job the file is deleted.
Before starting the job the file is checked and - if it exists - that number is passed to the load job to be used in a constraint as Chuck mentioned. When the file cannot be found, pass in a zero for that parameter value. Pretty straight-forward to set something like that up via a Sequence job.
I don't believe this method will be 'complicated' by a partitioned database as there is no 'max' calculation involved.
You can also consider something like a 'check-point' system, one that records the most recent commit point as the job runs in a flat file, for instance. At completion of the job the file is deleted.
Before starting the job the file is checked and - if it exists - that number is passed to the load job to be used in a constraint as Chuck mentioned. When the file cannot be found, pass in a zero for that parameter value. Pretty straight-forward to set something like that up via a Sequence job.
I don't believe this method will be 'complicated' by a partitioned database as there is no 'max' calculation involved.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 83
- Joined: Sat Oct 28, 2006 6:25 am
Although was surprised by the concept not applicable to partitioned db, I tried it on my db (db2 paritioned) and it worked fine!
Can you please detail as in what kind of complexity you are talking about here ?
Can you please detail as in what kind of complexity you are talking about here ?
DSguru2B wrote:You can do it yes. But there are limitations. If your database is partitioned then it becomes even more complicated.
Do a select max(id) from table and store the result in a hashed file keyed on a dummy key.
Reference that key in your transformer and specify the constraint
In.Key > Max.key
where In.key is your input key and Max.key is the max key value stored in the hashed file. This way if 3000 records got loaded and your input has keys from 1-6000. The next time the job runs after failure, the Max.key will be 3000 and hence the transformer will only allow 3001+ values to pass through.
Once again, this will only work for a non-partitioned database.
But if your data size is rather small, just specify the transaction size to 0 and get done with it.
-
- Premium Member
- Posts: 81
- Joined: Mon Nov 21, 2005 4:17 am
- Location: Sydney, Australia
- Contact:
To Load from 3001
Hi,
If you are using a Bulk Load Stage you can specify Restart Count in the properties as 3001 and Load Mode as INSERT.
This will start loading from 3001st row.
Regards,
Suresh N
If you are using a Bulk Load Stage you can specify Restart Count in the properties as 3001 and Load Mode as INSERT.
This will start loading from 3001st row.
Regards,
Suresh N
SURESH NARASIMHA
-
- Participant
- Posts: 30
- Joined: Tue Jun 20, 2006 10:22 pm
If you tested it out and it works with partitioned databases then thats really good, as it cleared my misunderstanding too. I was thinking in terms of, data being passed in different channels to the database table. It was a blurry thing in my mind, thats why i advised and warned the OP about it in the begining before proposing my method.
Thanks for clearing that out for me.
Regards,
Thanks for clearing that out for me.
Regards,
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.