Finding Routines used within a job

tonystark622 · Post by **tonystark622** » Thu Jan 08, 2009 12:36 pm

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

ray.wurlod · Post by **ray.wurlod** » Thu Jan 08, 2009 3:09 pm

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.

tonystark622 · Post by **tonystark622** » Thu Jan 08, 2009 3:40 pm

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

chulett · Post by **chulett** » Thu Jan 08, 2009 4:32 pm

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

tonystark622 · Post by **tonystark622** » Thu Jan 08, 2009 5:07 pm

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.

chulett · Post by **chulett** » Thu Jan 08, 2009 5:12 pm

Or that.

ray.wurlod · Post by **ray.wurlod** » Thu Jan 08, 2009 7:27 pm

Didn't even need user-defined SQL!

tonystark622 · Post by **tonystark622** » Thu Jan 08, 2009 8:38 pm

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.

ray.wurlod · Post by **ray.wurlod** » Fri Jan 09, 2009 1:34 am

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.

tonystark622 · Post by **tonystark622** » Fri Jan 09, 2009 9:58 am

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