Getting Job Names from Sequences

[imbos123]

Hi,

Is there a way to find out job names used in a sequence.
My aim is to populate two tables. First table will contain All the sequence names and other one will contain job names and they will be tied up to sequence table. This wil help me to know which sequence contains which jobs.

I wish to create one time load jobs.
Kim, I used to ETLstats methods to load jobs and sequences seperately but was not able to find out a way to related them.

[johnthomas]

One method is to export all jobs into xml and create a job which reads this file to get the sequence name and job names. we may need to try this out

[kduke]

You need to create UV stage and use the dictionary items in this thread viewtopic.php?t=105954&highlight=JobAct

[kduke]

If you cannot get it done then I will add it to EtlStats soon.

[DSguru2B]

Kim rules.

[kduke]

You will need to create one dictionary item.

Code: Select all

INSERT INTO DICT DS_JOBOBJECTS 
(
   FIELD, 
   CODE, 
   EXP, 
   NAME, 
   FORMAT, 
   SM
) VALUES (
   'JobActJobName', 
   'D', 
   '12', 
   'JobActJobName', 
   '30L', 
   'S'
)
; 

The DSX is at http://www.Duke-Consulting.com/Download ... 061215.dsx

You need to create this table:

Code: Select all

Create Table ETL_SEQUENCE_JOB (
   SEQUENCE_JOB_NAME varchar(60),
   LINK_NAME         varchar(60),
   DEPEND_JOB_NAME   varchar(60)
   Primary Key (
      SEQUENCE_JOB_NAME, 
      LINK_NAME 
   ) 
)

[ray.wurlod]

There is an event late in the job sequence's log called "summary of sequence run". This gives a list of the activities that were actually executed (omits those that were bypassed by the control logic or checkpoint logic).

[imbos123]

Hi Kim,

I used the logic mentioned by you to get the Job names in a sequence. I was wondering whether i can find out in what sequence they are called like
1) Extract
2) Transform
3) Insert
4) Update
This would help me in showing the status of the sequence jobs in the same sequence they are called.

Also, in the link below
http://dsxchange.com/viewtopic.php?p=20 ... 2c4#208804

Ray has suggested to use commandline script to get job names. But this won't help in our case because we are maintaning a single database for job statistics across all the projects.
I am trying to load all the tables containing category names, job names, sequences, job history and row history in the same way you did in EtlStats. But to load the static tables like category names, job names, sequences I have to export these jobs to individual projects and then run them and load the tables.
Do let me know whether i can achieve this from one single project. Thats the reaosn I had the question of accessing DS_JOBS/DS_JOBOBJECTS from one project to another

[kduke]

I have no idea what you mean by:

what sequence they are called like
1) Extract
2) Transform
3) Insert
4) Update
This would help me in showing the status of the sequence jobs in the same sequence they are called.

The sequence name is in the same row as each job that it runs. You would need to add project name to the key to post these. Make sure you do not clear the table. You still need to run this job in each project, unless you figure out what q-pointers are. This is an advanced Universe concept.

[ray.wurlod]

Hi Kim,
Ray has suggested to use commandline script to get job names. But this won't help in our case because we are maintaning a single database for job statistics across all the projects.

I suggested no such thing. I mentioned that the summary of the sequence run is in the job log.

You can get it out of there without a command line script - perhaps in a routine using the DataStage API without hacking the Repository. Perhaps even in a simple job.

[imbos123]

Hi,
I did not mean to say your script was incorrect. I was just trying to explain what I am trying to achieve. I am using similar script to get all the PROJECTS on that server. I am sorry if my writing suggested something else.

By sequence I meant
Steps they are executed that is first job is Extract then Transform then insert and then update. I would like to know if there is a way i can identify these dependencies.

[kduke]

I would say you need this job posted and what Ray was talking about. You need to load up EtlStats. Once the job run times are in a table then sort them by run date then you will have the order the jobs ran in.