Fetching Link Names for all stages, for all the jobs.

mdbatra · Post by **mdbatra** » Wed Oct 12, 2011 8:42 am

Hi,
I need to make one seq file which contains 3 columns for each job in the project:
1. Job Category(In Repository)
2. Job Name
3. Stage Names : Link Names

I am able to get upto #2 and first part of #3(till stage names). For that i have used job control using BASIC. DSGetJobInfo(with StageList + StageList2) returns comma separated Stagenames for each job.But, i am not able to figure out how to use DSGetStageInfo(with LinkList option) in loop and concatenate all the link names for a particular job, and then finally concatenate them with stage names( as described in #3 above).

I think of creating a file first with just Job Category,Job Name and Stage Names columns(File1):JobCategory JobName StageName
A XYZ 1,2,3,4..
B KLM 1,2,3,4,5..
C PQR 1,2...

Then model the above output to result(File2) in following manner:
JobCategory JobName StageName
A XYZ 1
A XYZ 2
A XYZ 3

and create output(using Job control) like:
JobCategory JobName StageName LinkNames
A XYZ 1 L1,L2,L3...
A XYZ 2 L1,L2...
A XYZ 3 L1,L2,L3,L4...

Then performing vertical pivoting on column LinknNames and then joining with File1 to get StageNames and finally concatenating them in transformer to give:

JobCategory | JobName | StageNames : LinkList

But, i trust there should be some more sophisticated approach as well for same.
Would appreciate any help on this.Thanks.

ray.wurlod · Post by **ray.wurlod** » Wed Oct 12, 2011 1:21 pm

You can use dynamic array techniques to assemble the data, then convert the dynamic array delimiters to line terminators.

mdbatra · Post by **mdbatra** » Wed Oct 12, 2011 1:30 pm

ok...Where can i find the detailed info regarding their usage ?

ray.wurlod · Post by **ray.wurlod** » Wed Oct 12, 2011 1:31 pm

Probably here, but also in the DataStage BASIC manuals.

mdbatra · Post by **mdbatra** » Wed Oct 12, 2011 1:36 pm

oh..sorry, i just asked without giving a thought to it

Thanks Ray !

ray.wurlod · Post by **ray.wurlod** » Wed Oct 12, 2011 1:43 pm

In particular Splice() - or maybe Cats() - and Reuse(), as well as Convert().

mdbatra · Post by **mdbatra** » Thu Oct 13, 2011 1:36 am

sure..will get back if i find any hiccups doing that

Thanks again !

mdbatra · Post by **mdbatra** » Mon Oct 17, 2011 12:42 am

Hi Ray,
I have used this now:

Code: Select all

number_of_stages  =  DCount(full_stage_list,",")

count = 1
                                   
LOOP

WHILE count <= number_of_stages 

stage_name  = Field(full_stage_list,",",count)

linknames   = DSGetStageInfo(job_handle,stage_name,DSJ.LINKLIST)

record      = category :"|": job_name :"|": stage_name :"|": linknames

count      += 1

REPEAT

This is giving me the result which is almost same as desirable. I went throught the suggestions you provided here but i was unable to make them work, still got to learn couple of things regarding them.

Thanks !