Trigger Job on the basis of records Processed

[anupam]

Hi All,

I have an Application in which i am doing some transformation and creating 2 dat files. 1 for current month and 1 for previous month. By design it may happen that the previous month file is having 0 records.

Once, the files are created, the data has to be loaded to the database by next Jobs using sqlldr.

Now the issue is, I want to trigger loading Job for the previous month only if the record count in the prvious month file is greater then 0.

The top level Sequencer triggers following Jobs in the order given below :
1. transformation Job (dat files are created for current month and previous month)
2. Loading Job ( for Current Month )
3. Loading Job ( for Previous Month )

I want 3rd job to be triggered only when the record count in Previous month is more then 0. Currently 3rd job is triggered always irrespective of the Record Count.

[nick.bond]

I take it the records are in a sequential file. If so do the following:

build a Routine that opens the file and tries to read the first record. if it can read the record return one value (NewRecords) else return (NoRecords). You can use this returned value in a simple job sequencer to determine whether to run your 3rd job or not.

Some simple code would be:

Equate vRoutine TO 'myRoutineName'
OpenSeq parth/myfilename TO vFile Else #vFile is just a variable
Call DSLogWarn("File not available", vRoutine)
End
ReadSeq vLine From vFile
vStatus=Status() #finds out what happened when file read

If vStatus=0 the vRecordPresent = 1 Else vRecordsPresent=2

Call DSSetUserStatus(vRecordsPresent)

=======================

You can now use UserStatus as the criteria for the Trigger in your job control.

So call your routine from the sequence job before trying to run job 3 using the trigger to check that USERSTATUS=1 as the condition before the job is run.

[chulett]

That's one approach. There's always more than one way to skin a cat here.

You could also build a generic routine to get a Link Row Count from a job and call that in your Sequencer, then branch on a 0 or >0 row count being returned from it.

You can also build the User Status setting into the original job. I built a simply routine that calls DSSetUserStatus based on the input parameter and the passes it out as the answer. This allows me to pass something through a job and out via User Status all at once. It could be as simple as sending @OUTROWNUM to it for each row, but that won't catch your zero row condition. You could, however, build a Stage Variable and use the routine in conjunction with a "0" to both set it's Initial Value and User Status to zero. Then on the first output row set it to one and pass that out to User Status as well. After that simply set it's value to itself as (in your case) you don't need to know the actual row count, simply the fact that there was at least one record processed. With that in place, simply use JobStageName.$UserStatus in a Custom Trigger. Or you can branch around the third job by testing the $UserStatus value from the first job in a Nested Exception stage after the second job.