Testing for empty file condition in Server Job

[dstukai]

I have a server job which reads .csv file and load into db2 database through transformer. Now, once in a while we are getting empty file from Sales department. Can we use a function/condition by which if the file has zero data , the job will abort.

The load process look like this,

sequential--transformer--hashed--database

Appreciate your help.

[asorrell]

I usually run the job in a job sequence, and test the file length prior to execution using an "Execute Command" stage. You can then decide to abort and send email notification based on the "CommandOutput" activity variable of that stage.

[chulett]

The job itself won't care and will happily process an empty file. However, because no rows are flowing through the job you can't use a condition inside the job to abort it.

What Andy posted is cleanest and makes what is going on much more obvious / visible to the casual observer. You could do something 'Before Job' (a O/S script, or a BASIC routine) to test the size of the file and abort the job before it starts but that's much less visible... unless you put an Annotation on the canvas noting what is going on there.

[MrBlack]

At the transformer you also have the @INROWNUM available to use. You can capture the number of rows read and make a decision there.

[asorrell]

I can't think of a way that @INROWNUM would work since he's trying to capture when it would have zero rows. If you know of a way, please post details...

[ray.wurlod]

You could use a before-job subroutine to test for non-existence and zero length. Setting ErrorCode to a non-zero value will abort the job.

[MarkB]

Like Andy, I would run this from a sequence. You could do something like:

wc -l filename|awk '{print $1}' in an ExecuteCommand. If the CommandOutput > 0, then run your job, otherwise send out a notification email that there was an empty file, or however else you want to handle the situation.

[dstukai]

Thanks a lot everyone. let me try executing the commands. The problem is, we can't have the sequencer for this job. In job control we can specify a code which will tell us that the job failed for the first job in Job control and other jobs will not run. But load actually finishes even though file is empty.

But this job has a process control which triggers our reporting. If file will zero data finishes then process control triggers the wrong report. Basically, have to do something by which, the Load itself will abort if the file has zero record so our process control does not update and report will not run.

[dstukai]

You could use a before-job subroutine to test for non-existence and zero length. Setting ErrorCode to a non-zero value will abort the job. ...

Ray,
Job control has a before sub routine script which extracts the file. I can't add another before sub routine right.

[chulett]

Why can't it test the size of the file after it 'extracts' it?

[dstukai]

Craig I am pretty sure it can be done. Can you please elaborate the process it will be helpful. in Job control we have a batch script in before run Job subroutine. How can i test test along with.

[chulett]

On Windows? Hmm... UNIX would be trivial but it's harder in a batch file. Try googling for something like "DOS test file size" and code samples should turn up. As Ray noted, all you'd have to do is pass back a non-zero exit code from the batch in that case for the job to notice and abort.

[ray.wurlod]

DataStage BASIC can help here. The STATUS statement returns, among other things, the size of the file in bytes. Use in before-job subroutine.

[dstukai]

Thanks Craig and Ray. Always appreciate your help. So, far, I changed the Job control codes, so when an empty file loads, Load itself will finish but JC will abort. Will do some more research on the before job file check for size and execute it.

Thanks again.

[chulett]

As noted, lots of DOS help out there. Here's one I found with a quick search:

http://www.dostips.com/?t=Snippets.ZeroBytesCheck