Abort job if 0 record from input file.

[sam334]

Hi all,
Have one question. We have a job which reads sql db and loads into db2 db everyday. Now, can I put any code in transformer which will abort the job if 0 records are there in input file.

We certainly can do a code in Job Control to abort it. Anything in transformer as a constraint...?

Job A
Job B
Job C

Runs one after one in single JC. Basically if input DB has 0 records in Job A, Job A itself will abort. So, others will not run for that day.

Thanks.

[chulett]

No possible. Zero records in the source means nothing flows through the job.

[qt_ky]

It seems like if you could force a divide by zero situation in the source stage, then the job should abort. Could you add a dummy column to the query and define it as a calcuation, such as some constant number divided by "SELECT COUNT(*) FROM SOURCE_TABLE"?

[chulett]

Still think it is better as a "pre-process". Run something to check all of the files and if any are zero, send out a alert and don't process any of them since that seems to be your requirement. Or check each before the load and only execute the job if there are records to load, otherwise alarm or whatever else you feel is appropriate.

What if B is zero and 'aborts'? Does that mean only C doesn't run or should A not have been run in that case?

[ssnegi]

Create one dummy record in a sequential file. Then use link collector to add that to the source records. Then add transformer stage and create CNT column hard coded to 1. Add this CNT column to the source columns.
This should be in another link apart from the regular link.
Then from this link add Aggregator to count the CNT column to output column CNT_ROWS. Then in another subsequent transformer2 constraint put CNT_ROWS=1. Then in another transformer3 put constraint "Abort after rows" = 1. This will abort the job if there are zero records from source.

[sam334]

Thanks everyone. Will try to implement the changes and will keep you posted.

There is an @INROWNUM system variable, could we use that. Like, @INROWNUM >0 in constraint. Lets try all of these.

Thanks again.

[chulett]

What changes? And again, an empty source will move zero records through the job so nothing will ever see your @INROWNUM constraint. I honestly don't like the shenanigans ssnegi posted when you can simply check the file before you run the job, or heck even in the 'Before Job' area as well and abort there if it tests as empty.

And you did not answer my question about B.

[sam334]

Craig,
I tried to include the @INROWNUM before seeing your post. You are correct, it is loading 0 records even though the constraints is there. I meant changes, by including the suggestion posted above.

And can't see your comment as it is blocked.

Thanks.

[chulett]

It's not "blocked" - that means you've let your Premium Membership lapse, it would seem. That would explain why you're not answering my questions. [sigh]

Let me open them up for a little while...

[sam334]

Thanks. Yes, that is the reason..I renewed the premium membership which expired last month but not yet activated for two weeks.

B and C are child tables and all of A,B,C have a process control table which updates while records load and reports run based on the update . If JobA has 0 records then I can put a code in Job Control (After JobA) which will force the Job B and Job C to abort. So, Report B and C wont run. But we want the job A to abort.

I remember couple of months ago we talked about having a script run to check the file and which will not execute the job is the file has 0 records. I will try to do it in sql server input check.

Thanks a lot.

[ray.wurlod]

Or add a simple server job (SELECT COUNT(*) FROM tablename) ahead of JobA in the sequence. This job stores the count in its user status area, and a Nested Condition activity can detect that value to determine whether even to run JobA (which, therefore, never needs to abort).

[chulett]

Just want to reiterate - IMHO there's absolutely no need for any of the jobs to abort, unless of course that happens when they are actually processing data and something goes sideways. For this situation, do whatever you need to do to check to make sure all of your prerequisites are in place. If not, send out a stern email and do not run any of the jobs. If everything is good to go, fire away!

And sorry, at some point I thought we were talking about files as the source rather than a database, hence some of the specific points in my earlier replies. Overall advice still stands, however.