controlling jobs

RikDeclercq · Post by **RikDeclercq** » Mon Jul 23, 2012 3:07 am

We have job with the following logic. Insert the contents of a file to a table but first truncate the table. If the file cannot be read (wrong format or is not available) the job should fail and the truncate should not happen. Is this possible in a job ?

ArndW · Post by **ArndW** » Mon Jul 23, 2012 3:22 am

Since the stages in your job all get started in parallel the design will not work as you described in one job. While you could write a complicated job to emulate this functionality (have a transform stage split the source stream, one output link would only take the first row (from the first processing node) then do a dummy write to the database which would execute the truncate table. That stage would have a reject link which would be used as a reference to a lookup stage in the main data stream. This is a dummy lookup, but would serve to synchronize the job so that no database actions are performed until after the truncate. This is a rather roundabout way of doing the truncate and it would be better and more reliable to split your logic into 2 jobs - the first doing all the source processing, perhaps into a dataset and then the second doing the database actions if the first is successful.

ArndW · Post by **ArndW** » Mon Jul 23, 2012 3:36 am

Here is what that job would look like

RikDeclercq · Post by **RikDeclercq** » Mon Jul 23, 2012 3:40 am

Thanks very much for the quick reply

. I will study your proposal.

ArndW · Post by **ArndW** » Mon Jul 23, 2012 3:45 am

Note that if all goes well the dummy lookup will get no rows - it is just there to perform timing synchronization so that the second database stage doesn't get any data until the first (truncate) stage completes execution. This isn't a particularly clean method and I would recommend solving it with two jobs.

ray.wurlod · Post by **ray.wurlod** » Mon Jul 23, 2012 4:30 am

The Data Set stage does not support input and output links in the same job.

A better approach would be to use a sequence to prevent the job from executing if the file is not present.

ArndW · Post by **ArndW** » Mon Jul 23, 2012 4:51 am

Ray, I'm confused as to why you mention a dataset not taking an input and output in the same job - my proposal using a dataset specifically stated two jobs, one to read the source and produce a dataset, then a second to read that dataset into a database.

RikDeclercq · Post by **RikDeclercq** » Mon Jul 23, 2012 5:06 am

ray.wurlod wrote:The Data Set stage does not support input and output links in the same job.

A better approach would be to use a sequence to prevent the job from executing if the file is not present. ...

Of course a sequence is best to handle this and we will do so. But we want to prevent that if somebody accidentely lances a job we will loose data. We found a solution by doing the following in the job properties :
Before-job subroutine "DSWaitForFile" with InputValue "/filePath/file.txt timeout=0"

ray.wurlod · Post by **ray.wurlod** » Mon Jul 23, 2012 4:09 pm

ArndW wrote:Ray, I'm confused as to why you mention a dataset not taking an input and output in the same job - my proposal using a dataset specifically stated two jobs, one to read the source and produce a dataset, then a second to read that dataset into a database.

The image had a Data Set with an input and an output. I wanted to pre-empt confusion.

ArndW · Post by **ArndW** » Tue Jul 24, 2012 12:34 am

Which image had a dataset with an input and output? My sample just has a copy stage to convert the reject dataset from the database write to a reference link used by the dummy lookup.