Conditional execution of next job in sequence

[Havoc]

There is a requirement where there are two jobs. One job loads a dataset. And another job loads from the dataset to a table. The first job should load all records into the dataset but if there are any 'error' records , the next job which loads from the dataset to the table should not run.

The only way i can think of is to write an indicator value to a sequential file and then use Execute Command Activity Stage to read from the file and trigger the next job depending upon the indicator value in the Sequential File.

Is there a better way to go about implementing this?

Thanks in advance

[ray.wurlod]

Be more specific about the design of the first job. How does it identify and capture these "error" records? Surely it is enough to detect the non-zero size of the file into which they are captured?

[ArndW]

Why not make sure that any rejected records trigger a warning. Start both jobs in a sequence but make the second one dependant upon the first one not generating warnings.

[Havoc]

Why not make sure that any rejected records trigger a warning. Start both jobs in a sequence but make the second one dependant upon the first one not generating warnings. ...

Its not exactly 'rejected records'. The next job should only start when there are no 'exception' records. There is an exception table to which records with column X,Y,etc are null are sent. When a single row passes to the exception table the next job should not start. The capturing of these 'exception' records is done in a transformer.

[ArndW]

Either in a user activity of the calling sequence or in the Job Control section of the second job add some lines to get the number of rows that went down that exception link from the last run of job 1, if that is greater than 0 then either don't start the second job or (if you decide to put it in the job control section) abort the job without running the PX portion.

You can get the number of rows through a link using DSAttachJob(), DSGetLinkInfo() and DSDetachJob()

[ag_ram]

Can an after Job Abort routine be used (based on the record count through the "exception" link) that will abort execution till there are no exception records . That way execution of next job can be controlled

[ArndW]

But the original poster didn't want to abort the first job, just not execute the second one - otherwise it would have been simple to put a limit of 1 row on the exception link and trigger an immediate failure.

[Havoc]

But the original poster didn't want to abort the first job, just not execute the second one - otherwise it would have been simple to put a limit of 1 row on the exception link and trigger an immediate ...

Yeah that's right.. The problem is each job has a separate sequence of its own. So the main sequence is actually calling each job sequence. So job1 is in jobsequence1 and job2 is in jobsequence2 and the master sequence is

jobsequence1->jobsequence2

My bad.. I didnt say this earlier. So now how can we conditionally execute jobsequence2 ?

[ArndW]

I suppose my earlier post got cut off at whatever the 'premium poster' limit of words is, so you didn't see all that I wrote. You can find out how many rows went down a particular link using the DSGetLinkInfo() routine, which you can set up and call from within a user activity stage in a sequence. With that number of rows you can branch your sequence so that the job doesn't get executed if that row count is greater than zero.

[Havoc]

I suppose my earlier post got cut off at whatever the 'premium poster' limit of words is, so you didn't see all that I wrote. You can find out how many rows went down a particular link using the DSGetLinkInfo() routine, which you can set up and call from within a user activity stage in a sequence. With that number of rows you can branch your sequence so that the job doesn't get executed if that row count is greater than zero.

Hi Arnd, Thanks for the reply. How do I use the DSGetLinkInfo() function in the situation stated?

Job1 in JobSequence1 and Job2 in JobSequence2. Master sequence being :

JobSequence1 -> JobSequence2

The syntax of DSGetLinkInfo() function is

DSGetLinkInfo (JobHandle, StageName, LinkName, InfoType)

How do i get the JobHandle of Job1 ?

[ArndW]

There are 3 calls you need to do, each is a function.
1. JobHandle variable from DSAttachJob({YourJobName})
2. Use that handle in the call to DSGetLinkInfo()
3. Close the handle with DSDetachJob()

[Havoc]

There are 3 calls you need to do, each is a function.
1. JobHandle variable from DSAttachJob({YourJobName})
2. Use that handle in the call to DSGetLinkInfo()
3. Close the handle with DSDetachJob() ...

When you say 'call', do you mean that there should be three separate user activity variables? I didnt quite get this ...
Can you please explain this with the following example:

job1
job2 (needs to run based on job1 execution)

[ArndW]

Yes, 3 separate user variables in that activity (I'm fairly certain that user variables can be used later in the same stage, analogous to what happens in Transform stage). I would write this as a simple routine

Code: Select all

CheckRowFunction(DummyArg)

   JobName = 'MyJob'
   LinkName = 'MyLink'
   StageName = 'MyStage'
   JobHandle = DSAttachJob(JobName,DSJ.ERRFATAL)
   IF JobHandle
   THEN
      Ans = DSGetLinkInfo(JobHandle,StageName,LinkName,DSJ.LINKROWCOUNT)
      Dummy = DSDetachJob(JobHandle)
   END
   ELSE Ans = 'Error Opening Job'

This is just a quickie routine, you can search the forum for more elaborate ones.

[Havoc]

Yes, 3 separate user variables in that activity (I'm fairly certain that user variables can be used later in the same stage, analogous to what happens in Transform stage). I would write this as a simp ...

From what i understand:

there should be three user activity variables and we later use that user variable activity in a trigger.. the problem is :

when i create a new user variable activity and i put in the following expression for the first variable :

DSAttachJob({Job1}) <- there's a warning message saying 'Expected Expression'. Am I missing out on something ?

I tried all permutations and combinations DSAttachJob({"Job1"}),DSAttachJob("Job1") <-the warning for this is variable DSAttachJob not found..

[ArndW]

I posted a full routine, but you would need to join as a premium member to use it. Lookup up DSAttachJob() in the docs, it has 2 parameters.