List of Jobs across Projects

[srinagesh]

Hi !

I am looking to get list of all Jobs in all Projects on the datastage server. My approach to this is to write a UNIX script, which will connect to each of the datastage projects, execute the command to get the job list, append to a file and move on the next project.

Is there a way I can achieve the same by writing a Datastage Routine / Job. My understanding is that I can get the get the list of Jobs for the current project only (project from where I am executing this routine). Is there a way I can pass the Projectname parameter to get the Job list ?



-Nagesh

[priyadarshikunal]

You can directly query the universe to get NAME from DS_JOBS.

or do the same in routine by executing TCL on DS_JOBS to get the NAME list.

[srinagesh]

My Understanding is that querying on ds_jobs or doing DSGetProjectInfo(DSJ.JOBLIST) would only list the Jobs in the current Project to which you are logged on.

Is there a way to get the list of Jobs across all the projects.

[ArndW]

Correct, the DS_JOBS file exists in each project and only contains jobs in the local project. You could use the UNIX command "dsjob -lprojects" then loop for each project and use "dsjob -ljobs {projectname}"

[chulett]

Not quite, you can use a parameter to control what "account" a.k.a. Project your hashed file source can point to.

[ray.wurlod]

Your routine could create a temporary VOC entry that points to DS_JOBS in each other project in turn.

[DS_SUPPORT]

Your routine could create a temporary VOC entry that points to DS_JOBS in each other project in turn.

Can you please provide some examples for it?

[ray.wurlod]

Code: Select all

ProjectJobNames = ""
Ans = @NULL

* Open VOC file - error handling omitted for clarity
Open "VOC" To fVOC Else GoTo ErrorExit
TempVOCName = "JOBS_" : @USERNO

* Get list of project names
Query = "SELECT SCHEMA_NAME TO SLIST 9 FROM UV_SCHEMA WHERE SCHEMA_NAME <> 'CATALOG';
Perform Query

* Iterate through list of project names
Loop
While ReadNext ProjectName From 9

    * Create temporary VOC entry
    Command = "SET-FILE " : ProjectName : " DS_JOBS " : TempVOCName
    Perform Command

    * Get list of job names
    Query = "SELECT NAME TO SLIST 10 FROM " : TempVOCName : " WHERE NAME NOT LIKE '\\%';"
    Perform Query

    * Iterate through list of job names appending to list
    Loop
    While ReadNext JobName From 10

      ProjectJobNames<-1> = ProjectName : "." : JobName

    Repeat

    Command = "DELETE FROM VOC WHERE @ID = '" : TempVOCName : "';"
    Perform Command
Repeat

Ans = ProjectJobNames
GoTo MainExit

ErrorExit:
Call DSLogWarn("Failed to open VOC file", "Problem")

MainExit:

[DS_SUPPORT]

I am getting errors when executing the above routine. I TESTed it from DS manager. I am using Windows Server 7.5.2.

The value for @USERNO is substituted as "-2208". So TempVOCName is formed like JOBS_-2208. I am getting error like

Code: Select all

ProjectName -- : ANAND_TST_PRJ
Command -- SET-FILE ANAND_TST_PRJ DS_JOBS JOBS_-2208
DataStage/SQL: Table "JOBS_" does not exist.
DataStage/SQL: 0 records deleted.

[chulett]

Perhaps try wapping it in the absolute value function.

[DS_SUPPORT]

I have corrected the issue. The issue is , there is no "Perform Command" after the "SET-FILE".

And changed the line

TempVOCName = "JOBS_" : @USERNO

TO

TempVOCName = "JOBS_1"

[ray.wurlod]

I've added the missing Perform statement into the code.

I used @USERNO in case more than one user runs the code at the same time. This can be any system-wide-unique identifier.

[Ramani]

Hi !

I am looking to get list of all Jobs in all Projects on the datastage server. My approach to this is to write a UNIX script, which will connect to each of the datastage projects, execute the command to get the job list, append to a file and move on the next project.

Is there a way I can achieve the same by writing a Datastage Routine / Job. My understanding is that I can get the get the list of Jobs for the current project only (project from where I am executing this routine). Is there a way I can pass the Projectname parameter to get the Job list ?



-Nagesh

My suggestion would be to use the dsjob to list all the projects and store in a text file and then read line one by one from that text file and get the project name and again invoke the dsjob to get the list of jobs and append to another text file.

[ray.wurlod]

That's precisely what the query on UV_SCHEMA does in the code.