job status

leochoa · Post by **leochoa** » Mon Mar 16, 2009 12:12 pm

How to change the status code inside data stage code. ?
Jobs are submitted from Mainfarme.
I am getting status=2 in two different jobs. in one is fatal and in the other one is OK
Thanks

DSguru2B · Post by **DSguru2B** » Mon Mar 16, 2009 12:27 pm

Use the -jobstatus in your dsjob run command.

ray.wurlod · Post by **ray.wurlod** » Mon Mar 16, 2009 3:21 pm

The only way you can influence the status code returned by a DataStage job is to write it:

so that it generates no warnings or errors (returns 1)

so that it generates warnings but no fatal errors (returns 2)

so that it generates at least one fatal error (returns 3)

sbass1 · Post by **sbass1** » Thu Apr 02, 2009 5:07 pm

I've run into this issue as well...

My sequence jobs are something like:

Code: Select all

SEQ_Main
   SEQ_Lev1
      SEQ_Lev1.1
         SEQ_Lev1.1.1
            Job1 --> Job2 --> Job3 --> JobN

I'd like to keep the triggers for the downstream sequences as "OK". However, Job2 generates a warning (database reject due to key constraints). This isn't fatal (esp. in DEV), and I'd like the flow to continue.

I believe I can use the custom trigger:

Code: Select all

ActivityName.$JobStatus Matches "1":@VM:"2"

to allow the flow to continue (and I believe this could apply to the OP's question if he's using sequence jobs).

However, I'd prefer to leave the triggers as "OK", but "force" a Warning to OK on a case-by-case basis per job.

Thus, it it possible to code something like:

Code: Select all

If JobName.$JobStatus = 2 The JobName.$JobStatus = 1

ideally in the job itself somehow, otherwise in a sequence, then have that match the "OK" trigger for the downstream sequence?

P.S.: An OOTB trigger "OK or Warning" would be useful.

sbass1 · Post by **sbass1** » Thu Apr 02, 2009 11:45 pm

OK, this is what I've come up with. Say you have these sequence jobs:

Code: Select all

Seq_Parent
   Seq_Child
      Job

where Job generates a warning.

Options:

1. Set Seq_Child compilation options - untick "Log warnings after activities that finish with status other than OK".

Pros:
Seq_Child will finish with OK, so downstream activities in Seq_Parent will execute with the OK trigger.
Job's log still shows the warnings

Cons:
While there is a message in Seq_Parent's log that Seq_Child finished with warnings, it's not obvious (no Event type = Warning)

2. Set trigger in Seq_Parent to custom Seq_Child.$JobStatus Matches "1":@VM:"2"

3. In Seq_Parent, create a Failed trigger, say to a Termination stage, and set an Otherwise trigger for the OK & Warning statuses.

I'm not sure which would be best case...still mulling it over. Leaning toward #2.

Additional input most welcome!

ray.wurlod · Post by **ray.wurlod** » Thu Apr 02, 2009 11:49 pm

Don't use literal constants ("1" : @VM : "2") - these values may change in future. Prefer DS Constants (DSJS.RUNOK : @VM : DSJS.RUNWARN).

There's no "best" - you do what's right in each case. Some exceptions at your site, Scott, require email to be sent. So your decision would be biased towards solutions that can accommodate that requirement.

shahidbakshi · Post by **shahidbakshi** » Fri Apr 03, 2009 12:49 am

Hi , Can we use the below code.
AAAiles_Seq.$JobStatus=DSJS.RUNFAILED Or AAAFiles_Seq.$JobStatus = DSJS.RUNWARN

intstead of using system variable @VM. Please share your ideas.
If not then what is the significance of using system variable(@VM A value mark (a delimiter used in UniVerse files), Char(253))

Thanks in advance

ray.wurlod · Post by **ray.wurlod** » Fri Apr 03, 2009 12:57 am

Yes you can; in a trigger expression it's clearer to use OR conjunction.

shahidbakshi · Post by **shahidbakshi** » Fri Apr 03, 2009 1:17 am

ray.wurlod wrote:
Yes you can
; in a trigger expression it's clearer to use OR conjunction. ...

Thanks Ray .

sbass1 · Post by **sbass1** » Fri Apr 03, 2009 3:00 am

ray.wurlod wrote:Yes you can; in a trigger expression it's clearer to use OR conjunction.

Matches was just easier to input - choose the right Activity variable once, then paste in the above. If BASIC had the IN operator I would use it.

shahidbakshi, @VM is the separator for the Matches function meaning "or", so Matches will return true if $JobStatus is 1 or 2. See the doc for more details.

chulett · Post by **chulett** » Fri Apr 03, 2009 7:48 am

sbass1 wrote:
ray.wurlod wrote:Yes you can; in a trigger expression it's clearer to use OR conjunction.
Matches was just easier to input

Hardly the best criteria for making the decision regarding which one to use.

sbass1 · Post by **sbass1** » Fri Apr 03, 2009 2:53 pm

chulett wrote:
sbass1 wrote:
ray.wurlod wrote:Yes you can; in a trigger expression it's clearer to use OR conjunction.
Matches was just easier to input
Hardly the best criteria for making the decision regarding which one to use.

OK...honestly...on this one, I'm not understanding why this isn't a good approach?

I could code:

MyActivity.$JobStatus=DSJS.RUNOK or MyActivity.$JobStatus=DSJS.RUNWARN

MyActivity$JobStatus Matches DSJS.RUNOK : @VM : DSJS.RUNWARN

MyActivity$JobStatus IN (DSJS.RUNOK, DSJS.RUNWARN) (if this operator existed).

Other than someone supporting this code later and not understanding the syntax of the Matches operator, why is using Matches "worse"? One thing I can think of is if Matches is appreciably slower than coding the direct equality comparisons.

FWIW I think Matches is syntactically more elegant: one of my pet peeves in SQL is seeing something like:

WHERE VAR=1 or VAR=2 or VAR=3 or VAR=4 or VAR=5 ...

instead of

WHERE VAR IN (1,2,3,4,5)

In this example, I think the second form is clearer.

chulett · Post by **chulett** » Sat Apr 04, 2009 8:32 am

OK. There was a couple of reasons why, after much pondering, I made that comment. And since you asked understand this is all me, my personal opinion so take it cum grano salis as Ray might say.

First off I agree with your comment about matches being 'syntactically more elegant' and would vastly prefer there be an 'in' rather than 'matches' or having to string a series of 'OR's together, especially as the number of elements to check expands. One mitigating circumstance here, however, is the fact there are only two values to check.

The first issue I have with it is it is a very... 'techie'... solution. You touched on the point in your post, your 'other than someone supporting this code later' comment and why Ray (I assume) noted the 'or conjunction' was clearer. IMHO it's very important to consider future generations, Those Who Come After Us. Sure, it works, but 95+ percent of the people I've worked with over the years wouldn't have a clue what it means. They spend their time working in the GUI and never venture 'under the covers' into the dark underbelly of the product or architecture. They'll use routines and perhaps write very simple ones but they'd much rather let someone like me do the dirty work for anything 'complex'. And the matches function isn't really all that high on their Must Use list, even a relatively benign example of MATCHES "1N0N" could be just gobbledygook and it only gets worse when they get more complex. Throw any of the system variable marks like @VM or @FM into the mix and it's everyone man the lifeboats. I don't recall the last time I saw a 'matches' in a derivation that I didn't put there, accompanied by an annotation explaining what it was doing.

The second issue was what I quoted, something that touched one of my hot buttons - the simple statement that you chose that path because it was 'just easier to input'. I say this in a generic sense - it always irks me when I discuss options with someone and we boil things down to a small number of choices and I know they'll be taking the easiest path. Not the best, not the most robust, but the easiest. Human nature, I guess, to prefer the shortest path to a goal but there are times when the needs of the many outweigh the needs of the few. Or the one.

So there you have it, why I made the comment, a brief peek into the dark recesses of my curmudgeon psyche. Hope that helps people understand where I was coming from with it.

ray.wurlod · Post by **ray.wurlod** » Sat Apr 04, 2009 1:54 pm

If you really want to get Craig (H) going, as about all those posters who want to create one generic job that can do everything. No, wait, who want US to create that job for them.

(I get somewhat worked up about that also. I believe - know - that DataStage was designed to be driven by the metadata, not the other way around.)

Bring on the RMM stage, I say!