How to manually set job status based on the result of query

yiminghu · Post by **yiminghu** » Mon Dec 01, 2003 1:42 pm

I have a problem regarging to capture logic errors within ETL. To keep the siutation simple, I am giving a sample here.

There are several jobs related a fact tables. We can separate the jobs into 2 groups, one group handles dimension mapping ( one job handles one dimension), it maps the source key within fact table to surrogate keys in datamart. Second group of job does all the calculation or transformation. Obviously, second group of job should be executed after first group. What we want to accomplished is that at the end of first group of job, we check whether all the source_key in fact table has its corresponding surrogate key, if any surrogate key is missing, we want set the job status as 'failure', and second groups of job would not start at all.

I don't know whether it is doable in datastage, or I have to let the process continue until the later process catches the error.

ariear · Post by **ariear** » Mon Dec 01, 2003 3:25 pm

If your first job terminates in a database stage like Oracle than continue with a query/stored procedure that returns a single value like 0/>0 and in a transformer/stage variable call the utilityAbortToLog (The transformer should terminate into a sequential stage writing to /dev/null.
It can be designed also in a batch or sequencer as a separate job

kcbland · Post by **kcbland** » Mon Dec 01, 2003 7:51 pm

I really don't understand why you designed your jobs as such, but, to answer your question....

Your solution is in job control. You have two choices, either create a BATCH job control that runs the first series of jobs, then checks the results via whatever means you want, then decides if to run the second series of jobs, or use a Sequencer with two nested Sequencer jobs for the first series and another for the second series. Put your check in the middle between the two nested jobs.