Getting universe information about a Job

[mjgmc]

Hello,

I hope I'm posting in the right place. If not, please tell me.

I'm developping a Job Control which will verify some information about a Job which name will be passed as a parameter.

I've found almost all the info I need, but I'd like to know if there's a way to find which type of job I'm looking at, i.e if it is a Sequence, Server or Parallel Job.

Shell I query the universe database or is there any "function" at the job level ?

Please, help me to find this, it's really important.

Greetings

[kduke]

I posted a routine called KgdIsJobSequence(). Look at it to create what you want. You may need to figure out a new way to calculate what is a parallel job. This routine uses what is in field 40 of the ROOT record in DS_JOBOBJECTS. I think you can look only at the DS_JOBS record which would greatly simplify this routine. Not sure what identifies a PX job in DS_JOBS. If you need more help than this then LIST DICT DS_JOBS. Post what you figure out.

[mjgmc]

Thanks for the answer.

In a matter of fact I realized that the field JOBTYPE from DS_JOBS has the following values:

0 for Job Server
2 for Job Sequence
3 for Job PX

I think this can help. Even if I'm not abble distinguish a common Job Server from a Job Control.

I'll keep looking inside these two tables for more information.

I hope this will help others.

Greetings

[kumar_s]

Its

Code: Select all

0 for Category
2 for Sequence
3 for Jobs.

[ray.wurlod]

I don't believe that's correct, but will need to check. There is another possible value, for mainframe jobs. Will post back once I've checked.

[kduke]

Code: Select all

0 Server Jobs
2 Sequence Jobs
3 PX Jobs

Mainframe is probably 1. This is field 6 of DS_JOBS. The easiest way to get this value is:

JobType = TRANS("DS_JOBS",JobName,6,"X")

[kumar_s]

Humm... Even Category is shown as Server jobs in PX only environment.

[mjgmc]

I need another information:

Each Activity and Link and its type from a Job Sequence.

I know that information is in the table DS_JOBOBJECTS, and I can get it from the command line as:

select DS_JOBOBJECTS.NAME, max(DS_JOBOBJECTS.OLETYPE) from DS_JOBS, DS_JOBOBJECTS where DS_JOBS.NAME='JsJob' and DS_JOBS.JOBNO=DS_JOBOBJECTS.OBJIDNO group by DS_JOBOBJECTS.NAME

I take only the max of the OLETYPE, because I just need to know that a link is a link, I don't need to know if it is INPUT or OUTPUT type.

How can I get this information in a routine using :

FileName = 'DS_JOBS'
open FileName To FilePtr else
...

FileName = 'DS_JOBOBJECTS'
open FileName To FileObj else
...

read ObjRec from FileObj, X then
...

What I need to know is the name of the column that I shell use in the place of the "X" which correspond to OBJIDNO.

Thanks in advance for any help.

Greetings

[ArndW]

The OBJIDNO is an I-type and is not part of the record or key. You cannot read it the way you want to, since the OBJIDNO is not unique and returns more than one record. You can use an execute to a SELECT statement and then process the returned list of record ids for that OBJIDNO.

[mjgmc]

ArndW,

As I'm not a Charter Member I don't have access to whole of your message. But I suppose you suggest me to run a quey with
DSExecute, right?

I've already tried it and whenever I run my routine (to test it), it never ends. Is it a normal behaviour?

This is the code of my routine:

Ans = @FALSE
RoutineName = "RtrGetSeqStages"
Usage = RoutineName:'(JobName)'
* -----------------------------------------------------------------
DebugSw = @FALSE


cmd = "SELECT DS_JOBOBJECTS.NAME, max(DS_JOBOBJECTS.OLETYPE) from DS_JOBS, DS_JOBOBJECTS where DS_JOBS.NAME='":JobName:"' and DS_JOBS.JOBNO=DS_JOBOBJECTS.OBJIDNO group by DS_JOBOBJECTS.NAME"

Call DSExecute("UV", cmd, Res , ReturnCode)
If ReturnCode<>0
Then
Ans = "ERROR ": ReturnCode
Goto TheEnd
End

Ans = Res<1>

* ----------------------------------------------------------------------
TheEnd:
if DebugSw then print
if DebugSw then print
if DebugSw then print Ans
if DebugSw then print



Could you please help me to figure out what's wrong?

Greetings

[ArndW]

Add a semicolon to the end of your SQL-flavor SELECT statement so that the engine knows which type of SELECT you mean. If you don't add the semicolon the engine will prompt for more code or a terminator and that is why your routine never returns.

[mjgmc]

Thanks

I've finally got what I needed. It's really bad documented this part of Datastage, right?

This is my new code and it works fine. A bit slow, but it's ok, it's not part of the regular process:

Ans = @FALSE
RoutineName = "RtrGetSeqStages"
Usage = RoutineName:'(JobName)'
* -----------------------------------------------------------------
DebugSw = @FALSE


cmd = "SELECT DS_JOBOBJECTS.NAME, max(DS_JOBOBJECTS.OLETYPE) from DS_JOBS, DS_JOBOBJECTS where DS_JOBS.NAME='":JobName:"' and DS_JOBS.JOBNO=DS_JOBOBJECTS.OBJIDNO group by DS_JOBOBJECTS.NAME;"

execute cmd capturing output returning RtnValue

Lines = DCount(output,@FM)

Stages=""
for i=3 to Lines
Space = Count(output<i>,' ')
if Trim(Field(output<i>,' ',1))<>"" Then Stages := ',' : Field(output<i>,' ',1): '|' : Field(output<i>,' ',Space+1)
next i

Ans = Right(Stages,LEN(Stages)-1)

ExitRtn:
goto TheEnd
* ----------------------------------------------------------------------
TheEnd:
if DebugSw then print
if DebugSw then print
if DebugSw then print Ans
if DebugSw then print

Thank you all for your help

Greetings