Page 1 of 1
Finding Routines used within a job
Posted: Thu Jan 08, 2009 12:36 pm
by tonystark622
Is there a way to list the routines used in a job?
Heck, for that matter, is there a way to programmatically get a list of routines within a project?
I know that I can manually look through a job and manually see routines used in various places (Derivations, Constraints, Stage vars, etc). I was looking for an automated way to get a list of routines within a job.
Thanks for your help,
Tony
Posted: Thu Jan 08, 2009 3:09 pm
by ray.wurlod
For the first you would need to effect a number of queries against DS_JOBOBJECTS - the ROOT record for before/after subroutines, each active stage record for active stages in server jobs, each Transformer stage record for transform functions in server jobs (and others for parallel jobs).
A query on DS_ROUTINES ought to do it for the second.
Posted: Thu Jan 08, 2009 3:40 pm
by tonystark622
Thanks, Ray.
Interestingly enough, I tried using the manual "Usage Analysis" function within DataStage Manager. I have some routines that I KNOW are used within a UserVariable activity on a Job Sequencer job. When I run the usage analysis on them, nothing shows up.
For the first you would need to effect a number of queries against DS_JOBOBJECTS...
I figured that it would be something like that. I know nothing about DS_JOBOBJECTS, so that project may have to wait for another day when I have more time.
A query on DS_ROUTINES ought to do it for the second.
Is there an easy way to get this list out into a file?
I appreciate your help.
Tony
Posted: Thu Jan 08, 2009 4:32 pm
by chulett
Is there an easy way to get this list out into a file?
One way would be using COMO, found a small discussion on its use here:
viewtopic.php?t=100948
Posted: Thu Jan 08, 2009 5:07 pm
by tonystark622
Is there an easy way to get this list out into a file?
I'm an idiot.
I used a UNIVERSE stage. Set to custom SQL.
SQL = SELECT DSRID FROM DS_Routines;
one column named DSRID varchar(72)
good as gold.
Thanks everyone.
Posted: Thu Jan 08, 2009 5:12 pm
by chulett
Or that.
![Wink :wink:](./images/smilies/icon_wink.gif)
Posted: Thu Jan 08, 2009 7:27 pm
by ray.wurlod
Didn't even need user-defined SQL!
Posted: Thu Jan 08, 2009 8:38 pm
by tonystark622
I know, Ray, but I kept getting an error because the Universe stage put double quote marks around the table name, which caused it to error with something like "Invalid table name". I couldn't quickly figure out how to make it stop doing that, so I used Custom SQL.
Posted: Fri Jan 09, 2009 1:34 am
by ray.wurlod
The real table name is DS_ROUTINES (all upper case). The UV stage quotes any table name that contains any non upper case (or numeric or underscore) character. Now you know.
Posted: Fri Jan 09, 2009 9:58 am
by tonystark622
Thanks, Ray. I didn't know that (obviously). There are so many things that I don't know about DataStage, it's embarassing.
I do appreciate your time and knowledge.
Thanks for your help,
Tony