Getting jobnmaes

bala_135 · Post by **bala_135** » Wed Oct 18, 2006 4:23 am

Hi,

I have to take all the jobnames in the sequence in the order they have been sequenced.

I am trying to do this by exporting the sequence as an XML and from the XML i am trying to get the jobnames.

In the XML they are not in the order they have been sequenced.

Why it is so or any other suggesions How can i acheive this.

Regards,
Bala.

keshav0307 · Post by **keshav0307** » Wed Oct 18, 2006 4:37 am

you need not to worked so hard. its very simple.

open the job property--->Job contorl----

read 1st few lines. it is about the jobs sequence

bala_135 · Post by **bala_135** » Wed Oct 18, 2006 5:03 am

Hi,

Thanks for the reply.I need to read it into a file or table.If i see the sequence jobcontrol there also they are not in order.

From jobcontrol how can read it to a file or table.

Regards,
Bala.

kumar_s · Post by **kumar_s** » Wed Oct 18, 2006 6:32 am

It may not be easy to pickup the order from exported file. The of execution of jobs can even changed during runtime or the logic that been defined.
But you can parse the output log file and can get the order in which the jobs been executed by that JobSequence.

kduke · Post by **kduke** » Wed Oct 18, 2006 9:39 am

The only way to get the order is through run times. The order in job properties is the order input. The same would be true if you listed the repository.

Run times are not consistent either. If you have more than one branch the order may change because the number of rows may make a job finish earlier or later in one branch than the other. I usually create a baseline from run times. This baseline is a normal day's run times. I use this as my order and remember that it is not always exactly in this order.

bala_135 · Post by **bala_135** » Sun Oct 22, 2006 11:59 pm

Hi,

Thank u for the reply.I have got it that its not going to be easy to get the jobnames from the exported file.

I am going to create a routine which is going to get the jobnames from the file and going to run the jobs.

In this case i needed a file which has the jobnames in the order i have sequenced so it solves my business logic.I can do this manually(typing in all the job names in the order i want) but i want to know if i can acheive through datastage before execting the jobs.

Thanks for the inputs already.

Regards,
Bala.

kumar_s · Post by **kumar_s** » Mon Oct 23, 2006 3:09 am

Writing the list and order of the jobs in a file and reading it back wont be required option.
As mentioned, you can get the list of jobs thats been/will be called by a JobSequence. But not the order.
If two jobs triggred parallely at the same time, it depends on the volume of data and other factor, when, which job finishes so that the consequtive job will start next.
Or if you are desperate, it can be customized to an extend based on the JobSequence design.
So explain what is the Design of the Job sequence you have, so that there mignt be a strategy where we can work on.

kduke · Post by **kduke** » Mon Oct 23, 2006 5:00 am

You can extract what I call ETL metadata from a UV Stage. Your source SQL would be the depend jobs in a sequence. Do a search for DEPEND_JOBS. I have posted this SQL before. The output would be a XML output stage.

If you had EtlStats installed then you could get the order based on run time. I included the SQL to do this in the zip file. You could easily write your own. I would create a ETL_MD_DEPEND_JOBS table. This table is called DSJobDependencies in Reporting Assistant. So if you are using batch jobs this will be updated. Sequences are not extracted in Reporting Assistant. I extract the job dependencies in DwNav into DSJobDependencies to get this relationship so you can view the seqeunce calling a job and the jobs called by a sequence. I want to view the parents and the children easily within DwNav.

I need to combine this with run time stats. When I do these types of reports then I use the max(end_ts) from ETL_JOB_HIST to limit the list to the last job run. I can post the SQL here if you want.

You just need to change the output on most of this to XML instead of html. All the reports in EtlStats output html. I would think it should be easy to write a simple job to output to XML.

kumar_s · Post by **kumar_s** » Mon Oct 23, 2006 6:53 pm

Bala,
Run the JobSequence once manually. Now you can follow the Kim's method to get the Order of the jobs that been executed.

ray.wurlod · Post by **ray.wurlod** » Tue Oct 24, 2006 9:29 am

Or just process the job sequence's log filtering the "job run requested" messages.

Or capture the "summary of sequence run" event near the end of the log.