Abort a Job if No rows are selected from source

[nkln@you]

I have a job whch has a Source OCI Stage, Transformer, Target OCI stage.

I need to abort the job if no rows are selected in Source OCI stage. How can we do this, I tried using the following in Transformer

If @INROWNUm=0 Then UtilityAbortToLog('The Job aborted as no Date is selected from DW_PROCESS_CTL to be populated as START_DATE into COMM_EXTRACT_PROCESS_CTL -- Manual Interpretation is Required ') Else Read_comm_extract_procees_ctl.start_date

I thought if no rows are selected, @INROWNUM becomes 0, but when I ran the job the job didnt get aborted, even though there were no rows selected.

I have two questions

1. How to abort the job
2. Is it good to abort a job like this in production, I have to do this because if no rows are selected, the sequencer will take wroong incremental dates?

[DSguru2B]

You can have an after job subroutine (custom) that check for the link count. If its zero, you can log the error as a fatal error which will cause the job to abort.
Its not a good practice to abort jobs in production.

[chulett]

There is no such thing as an @INROWNUM of 0. You don't "need" to abort, from what I can tell you just need to conditionally execute a series of following jobs when the records selected is not zero.

So, have the Sequence (no 'r') job check the job after it runs to see if it processed any rows. Only continue down your path if it did. If nothing was processed, do something more graceful - perhaps an alert email. Or just declare the battle won and exit the Sequence.

[nkln@you]

Craig,

I didnt understand this

So, have the Sequence (no 'r') job check the job after it runs to see if it processed any rows. Only continue down your path if it did. If nothing was processed, do something more graceful - perhaps an alert email. Or just declare the battle won and exit the Sequence.

How can we check whether the job has processed any rows in Sequencer.

How can we send alert mails based on conditions.

[chulett]

Build a generic routine to leverage DSGetLinkInfo. Use it in a Routine Activity stage to return a row count from the previous job. Branch based on the value returned.

You conditionally send emails the same way - by branching your workflow in the Sequence. That's what the various Triggers control - have your "Routine returned 0" trigger linked to an Email Notification stage, for example. The "Routine > 0" trigger runs the remaining jobs.

[nkln@you]

I used

LinkRowCount =DSGetLinkInfo(StartDate_Handle,ActiveStageName,LinkName,DSJ.LINKROWCOUNT)

When 1 row is passing through the link, i am getting the value of this function as -1. Is there any way that -1 can be output of this function

[ArndW]

-1 means you have specified one or more invalid input parameters. Is your first parameter, the job handle, being set correctly with a DSAttachJob() call or using the DSJ.ME variable? Are the stage and link names correct? Once all 3 are set appropriately you will get a non-negative number back - either 0 or the number of rows processed.

[chulett]

Check this post for a list of error codes. Specifically you'll note that -1 means "Invalid job handle". I would assume that your attach call failed.

[nkln@you]

I resolved that -1 issue, I have another issue while calling this routine from sequencer.

I have written a function

GetLinkRowCount(JobName,ActiveStageName,LinkName) as follows:

$INCLUDE DSINCLUDE JOBCONTROL.H

V_JobName = JobName
V_ActiveStageName = ActiveStageName
V_LinkName = LinkName
Handle = DSAttachJob (V_JobName, DSJ.ERRFATAL )
LinkRowCount = DSGetLinkInfo(Handle,V_ActiveStageName,V_LinkName,DSJ.LINKROWCOUNT)
ErrorCode = DSDetachJob(Handle)
Ans = LinkRowCount

JobName is UtilityCommExtractProcessCtl
ActiveStageName is Input_comm_extract_process_ctl
LinkName is Read_comm_extract_procees_ctl

The number of rows passed through Read_comm_extract_procees_ctl
is 1.

Then I tested this routine with the TEST button available in the routine, the output was 1.

But when I called the Routine from a Sequecner usng Routine Activity LinkRowCountCommxtractProcessCtl with the same parameter values, I am getting value of LinkRowCountCommxtractProcessCtl.$ReturnValue as 0.In all cases this value is being shown as 0. No idea why this is behaving like this.

Any inputs on this?

[gateleys]

I have a job whch has a Source OCI Stage, Transformer, Target OCI stage.

I need to abort the job if no rows are selected in Source OCI stage. How can we do this, I tried using the following in Transformer

An easier solution would be to -
1. Create another job prior to this job with an OCI input, Transformer and hashed file.
2. Modify your initial sql to just return a Count.
3. Pass Count value to output link (that is the hashed file). Create a dummy_ID field in hashed file and set it as key (with a value of 1).
4. In your job sequence, let this job execute, and follow it with a Routine activity that reads this hashed file. Set the trigger to abort the job if return value is 0.

Or, at step 3, set the userstatus area with the count value, and later in the sequence, trigger the link according to the userstatus value.

Slightly lengthy process, but Simple.

gateleys