2

My DataStage version is 8.5.

I have to populate a table in DB2 with the datastage performance data, something like job_name, start_time, finish_time and execution_date.

There is a master sequence with A LOT of jobs. The sequence itself runs once a day.

After every run of this sequence i must gather performance values and load them into a table on DB2, for reporting purposes.

I'm new on datastage and i dont have any idea of how to make it work. My Data stage's environment is Windows, so i cant work on it using shell scripts.

There is some way to get this info into datastage ?

i tried to build a server routine and get data using the DSGetJobInfo, but i got stuck into parameters issues (how to pass xx jobs as a list to that).

Sorry about my english, not my native language.

Thanks in advance.

LeandroHumb
  • 843
  • 8
  • 23
  • you can export the main sequence to dsx and read it as a flat file just before running your process, it should contain the names of the jobs it calls and then use it as input for the routine – jclozano Feb 11 '15 at 16:02
  • Hey I also want to do some thing like this have you got any solution – Ahmad Qasim Apr 05 '18 at 08:23
  • Hey LeandroHumb I also want to do same thing did you got any solution about this – Ahmad Qasim Apr 05 '18 at 09:08

3 Answers3

1

Is your server also on Windows ? I am confused since you said "My Datastage " most of thetime the servers are installed on linux / unix and clients are windows.

The best command to use would be (same should work on windows and linux servers both)

dsjob -jobinfo [project name ] [Job name ]

output would be something like-

Job Status : RUN OK (1)

Job Controller : not available

Job Start Time : Tue Mar 17 09:03:37 2015

Job Wave Number : 9

User Status : not available

Job Control : 0

Interim Status : NOT RUNNING (99)

Invocation ID : not available

Last Run Time : Tue Mar 17 09:09:00 2015

Job Process ID : 0

Invocation List : [job name]

Job Restartable : 0

Ashish Devassy
  • 318
  • 1
  • 5
1

After this years i found some ways to get a job's metadata, but none of them are good as i wanted, all of them are kind of clunky to implement, and fail often. I found 3 ways to get job metadata:

  • Query directly from xmeta, on tables that match the DATASTAGEX(*) naming

  • Query from DSODB, DSODB is the database from the operations console tool, it have all log information about job runs, but operations console must be enabled to have data (turn on the appwatcher process)

For this both above you can build an ETL that reads from these databases and write wherever you want.

And the last solution:

  • Call an after-job subroutine that call a script witch writes job's results on a custom table.

If this data is needed only to report and analyse, those first two solutions are just fine. For a more especific behavior, the third one is necessary.

LeandroHumb
  • 843
  • 8
  • 23
0

What you are asking is the ETL audit process , which is one of the mainstays in ETL development . I am surprised that your ETL design does not already have one

  1. Querying XMETA - In my experience across multiple Datastage environments . I have not seen companies use XMETA DB to pull out job performance information

Why ?? Because , Datastage jobs are not recommend to access XMETA DB , considering that XMETA holds the important metadata information about DS. Maybe your Datastage administrator will also not agree to provide access for XMETA .

  1. The old and most trusted way of capturing run- meta information is to develop multliple- instance, run time column propagation transformations and also few audit tables in the database of your choice .

My idea: 1.Create table like - ETL-Run_Stats which has fields like JOB_NAME , STARTED_TS , FINISHED_TS , STATUS etc . 2. Now create your multiple instance jobs and include them in your DS master sequences .

If your DS sequence looks like this now START ------> MAIN_DSJOB -------> SUCCESS

After your Audit jobs your DS sequence should look like this

START ----> AUDIT_JOB(started) -------> MAIN_DSJOB ------> AUDIT_JOB(finished) -------> SUCCESS

  1. You can include as much functionalities you need in your AUDIT jobs to capture more runtime information

I am suggesting this only because your DS version is really old - version 8.5 .

With the newer versions of DS -- there are lot of in-built features to access this information. Maybe you can convince your Manager to upgrade DS :)

Let me know how it works

balaji karthik
  • 51
  • 1
  • 6