Datastage Routine Function

sivainsg · Post by **sivainsg** » Wed Oct 09, 2013 12:50 am

Hi All

we have a requirement from our client as below.

a.Fetch all the jobs from all the projects in the datastage server using datastage server routine and determine its start time and end time.
b.We tried using DSGetProjectInfo. But this command only gives the job names of the project on which the routine is actually created.
c.but we want this server routine to return the jobs of all projects irrespective of where this routine is present.

Kindly help me in doing this.

Thanks
Siva

chulett · Post by **chulett** » Wed Oct 09, 2013 12:56 am

Does it need to be a 'server routine'? Doing this at the command line via a shell script would be another possibility. Get a list of projects, iterate through the list and then get a list of jobs from each project. then iterate through that list to get their most recent start/end times. All via options within the dsjob utility.

However, you should be able to do something similar from a routine via the API.

sivainsg · Post by **sivainsg** » Wed Oct 09, 2013 1:16 am

Hi Chulett

Thanks for your response. Actually we are enhancing one of the Server routines which we have. And this is one of the functions that routine performs to provide the output.

Hence the client mandate is to use the routine to avoid more re work creating all other features which are currently present in the routine which they feel is time consuming.for developement and testing.

ArndW · Post by **ArndW** » Wed Oct 09, 2013 2:40 am

With DSGetProjectInfo() you get a list of jobs.
With DSAttachJob() you get a handle to the job, used by the following routines.
With DSGetJobInfo() you get a lot of details for the job.
With DSGetLinkInfo() you get further information
With DSGetStageInfo() you get further information.
With DSDetachJob() you release the job handle.

sivainsg · Post by **sivainsg** » Wed Oct 09, 2013 2:48 am

Hi Arndw

Thanks for your response...

With DSGetProjectInfo() we get only the jobs in the current project. But we need the details of all jobs of all the projects.

Ex:
DSGetProjectInfo() for Project A gives all jobs and its associated details present in project A.

But if there are 2 other projects with A, say A,B,C... We need all the jobs in A,B,C been retrieved in by the routine.

ArndW · Post by **ArndW** » Wed Oct 09, 2013 3:26 am

Hmm... I can't seem to find an API call to list the projects. There is the "dsjob -lprojects" command line option to list the projects.

Here's a quick and dirty subroutine to list all the project in BASIC:

Code: Select all

Ans = ''
OPEN '','UV.ACCOUNT' TO UvAccount_ptr 
THEN
   SELECT UvAccount_ptr TO 2
   Finished = 0
   READNEXT Key FROM 2 ELSE Finished = 1
   LOOP UNTIL Finished
      READ UvRecord FROM UvAccount_ptr, Key ELSE UvRecord = ''
      IF UvRecord<7>[1,9] = 'DataStage' THEN Ans<-1> = Key
      READNEXT Key FROM 2 ELSE Finished = 1
   REPEAT
   CLOSE UvAccount_ptr
END
ELSE CALL DSLogWarn("Unable to open UV.ACCOUNT","")

ray.wurlod · Post by **ray.wurlod** » Wed Oct 09, 2013 4:30 am

This is far more easily done using the command line tool dsjob - as you note the scope of the routine is its own project.

The alternative is to go through the process of globally cataloging a routine so that it can be called from any project. This is not the preferred approach of the vendor (their preference is to limit scope to the project, for a whole lot of very good reasons), but it is possible. Fairly good "UniVerse" knowledge is required, since the main routine will have to create a PARAGRAPH that invokes the global routine in each project.

chulett · Post by **chulett** » Wed Oct 09, 2013 7:11 am

You can use DSGetProjectList() to get a list of Projects on the server.

ArndW · Post by **ArndW** » Wed Oct 09, 2013 7:27 am

Craig,

I knew that I'd used a function before to get that information and didn't have to resort to reading UV.ACCOUNT (I'll leave the source code up anyway); but I checked the local and online DataStage help files and couldn't find the function and thought that my memory was playing tricks on me. It seems that the function isn't documented (anymore). Thanks!

sivainsg · Post by **sivainsg** » Wed Oct 09, 2013 6:29 pm

ArndW wrote:Hmm... I can't seem to find an API call to list the projects. There is the "dsjob -lprojects" command line option to list the projects.

Here's a quick and dirty subroutine to list all the project i ...

'

Hi Arndw

I am unable to see your complete post. Could you please help me..

ArndW · Post by **ArndW** » Thu Oct 10, 2013 2:19 am

sivainsg,

The site is funded through premium memberships and having a couple of user's posts restricted to those premium members. While many of posts are not restricted this one, containing BASIC code, is limited in this way.

Joining up is not expensive and getting just one answer to a problem that blocks you can pay for a whole year's membership many times over.

In addition, my code is not necessary to solve your problem, Craig posted a response and an official API routine call which makes what I wrote unnecessary.