Checkpoints (or savepoints) in a job

[sathyanveshi]

Hi,

Is it possible to use checkpoints (savepoints) in a datastage job? What I need is to commit the data after every 1000 rows or after every 10 seconds. And if the job fails, then re-running of the job should start reading the next chunk of data from the previous checkpoint/savepoint and proceed from there? Is such a functionality available in DataStage?

Cheers,
Mohan

[ray.wurlod]

Not directly.

You have control over the number of rows per transaction but to implement the rest you would have to design it in. You would need to keep a count of how many rows had been loaded, and build in restart logic (perhaps driven from a job parameter) to start from that point.

It can be done, it has been done, but you need to take care to design exactly what you require.

You also need to be careful that you can exactly reproduce the source data; this is most easily achieved using a staging area immediately after the data have been extracted. A re-run starts from the staging area.

[vmcburney]

I'm giving a server job answer to a parallel job question but hopefully some of it is useful.

One problem you are going to have is the potential difference between input rows and output rows from a transformer caused by constraints. For example if your job reads in 10090 and only 9050 are passed to the database then your restart becomes 9000 but needs to be applied to the output count of the transformer, not the output count of the input stage.

A routine would attach the job and get a link count of the final output link into the database stage. This number gets rounded down to 9000 and passed into the job as a job parameter. In the transformer you need to maintain a stage variable that keeps a count of rows that pass all constraints. This is done by moving all constraint code into stage variables that are flags, the counter increments if all the flags are false, the flags also are used in the constraints derivation code. An additional row recover constraint only lets rows through if the output counter is higher then the recover amount of 9000. Normally this row recover amount is set to 0 so all rows go through.

[dsxdev]

In PX the records are not read in order there is crossing over of data between partitions. How is this handled when we restart the job again.

Suppose 9050 records were read from input and output how do we identify which 9050 records were read and output.

This really puzzles and the Row Commit and Time commit interval options have been puzzling me, how they work (more right do they really work).