How to find the last link of an etl job?

bharathappriyan · Post by **bharathappriyan** » Mon Apr 07, 2014 8:18 am

Hi,

DataStage: 7.5 OS: Windows

Is there a way to find the last link of an ETL job?. My requirement is to find out the start time, end time, status & rows processed by an etl job present in a project. The project has around 5000 jobs. We have an routine to give the start time, end time and status. Now i have to get the rows processed. If am able to get the last link,i can pass that to DSGetLinkInfo macro to get that.

Thanks,
Bharathappriyan

PaulVL · Post by **PaulVL** » Mon Apr 07, 2014 12:43 pm

Jobs can have multiple "end" links.

Do a search for ETLstats here on the forums.

ray.wurlod · Post by **ray.wurlod** » Mon Apr 07, 2014 4:46 pm

If you use link naming conventions, for example naming an output link tablename_OUT, then your task will become a lot easier.

bharathappriyan · Post by **bharathappriyan** » Tue Apr 08, 2014 10:57 am

Hi Ray,

80% of the jobs are using naming conventions. 20% are not.

Thanks,
Selva

ray.wurlod · Post by **ray.wurlod** » Tue Apr 08, 2014 2:05 pm

Fix the 20%.

chulett · Post by **chulett** » Tue Apr 08, 2014 3:14 pm

Technically, it seems to me that any stage with only an input link would be a target and thus considered as a 'last link'. And I believe that a call to DSGetStageInfo() would return that when using DSJ_LINKTYPES as the Info_Type.

kduke · Post by **kduke** » Thu Apr 10, 2014 9:16 pm

The job report XML has links defined as output or input. You need to figure out which is really output to you. The row counts are all attached to links and stages. The starting point is an input stage with only input rows. Same on the output stages with only output rows. If that makes sense. All stages with inputs and outputs are in the middle.

So you would need to process the input and outputs into lookups. Next process the rows. Put into tables and run SQL to find only inputs and only outputs. Compare to your job design.

A lot of work.

rameshrr3 · Post by **rameshrr3** » Mon Apr 21, 2014 2:19 pm

@chulett : If they are writing to an error/ reject target in Server jobs, it would only have an 'input' link .

ray.wurlod · Post by **ray.wurlod** » Mon Apr 21, 2014 2:23 pm

(This is probably not helpful.)
The last link in a job is the one that was added last (most recently) to the job design, and is therefore the link with the highest internal link/pin number.

kduke · Post by **kduke** » Tue Apr 22, 2014 8:08 am

It is like Bill Clinton says. Depends on how you define "last".

chulett · Post by **chulett** » Tue Apr 22, 2014 8:51 am

Exactly.