Perticular routine used by different # of ds jobs

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
ds_ashish
Participant
Posts: 15
Joined: Thu Nov 23, 2006 12:30 am

Perticular routine used by different # of ds jobs

Post by ds_ashish »

Hi All,

I want to find out a perticular routine say 'abc' is used by how many number of datasage job in a project.

for e.g.
Job Name Routine_Name
Job1 abc
Job2 xyz
Job3 abc
Job4 abc
Job5 xyz

Desired Output : Job1,Job3 and job4 using routine 'abc'

Thanks in Adv.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

It can be done, but not directly. Routines may be called from jobs (as before/after subroutines or as job control routines) or from Transformer stages (as transform functions) or from any active stage (as before/after subroutines).

You would need to construct a complex query (or a UNION) on DS_JOBS and DS_JOBOBJECTS to retrieve all of this information.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
baglasumit21
Participant
Posts: 132
Joined: Wed Mar 01, 2006 11:12 pm
Location: Pune

Re: Perticular routine used by different # of ds jobs

Post by baglasumit21 »

ds_ashish wrote:Hi All,

I want to find out a perticular routine say 'abc' is used by how many number of datasage job in a project.

for e.g.
Job Name Routine_Name
Job1 abc
Job2 xyz
Job3 abc
Job4 abc
Job5 xyz

Desired Output : Job1,Job3 and job4 using routine 'abc'

Thanks in Adv.
Hi Ashish,
If you want to just check how many jobs are using a particular routine then this can be done through DS manager. Just select the routine you want to check the usage for and then Right click and click 'Usage Analysis". It will give you the list of jobs along with other details about where that particular routine is used
SMB
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Usage Analysis does not pick up job control routines, nor does it pick up routines invoked by other routines unless their Dependencies tab has had the dependent routines inserted in its grid. Nor - I believe without checking - does Usage Analysis reveal routines that are invoked by Transforms. (I missed those in my earlier summary.)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Rather than the complex query, you could go old school and use your editor of choice to find all occurances. You need to go 'up' from each one to find the 'BEGINJOB' record so you'd know where you'd found it.
-craig

"You can never have too many knives" -- Logan Nine Fingers
gateleys
Premium Member
Premium Member
Posts: 992
Joined: Mon Aug 08, 2005 5:08 pm
Location: USA

Post by gateleys »

A simple(?) solution will be to have a job which reads the dsx file as a single column, parses it in a Transformer and outputs the JobNames that use the Routine. The pRoutineName is passed as a job parameter. We will use a group of stage variables (as opposed to a routine) to get the jobs.

Code: Select all

Stage Variables
-------------------
svRoutineName = UPCASE(TRIM(pRoutineName))
svInputCol    = UPCASE(TRIM(InputLink.DSXColumn))
svJobName     = If RowProcGetPreviousValue(svInputCol)  = "BEGIN DSJOB" Then FIELD(svInputCol, '"', 2) Else svJobName
svRoutineFound= If Index(svInputCol, svRoutineName, 1) > 0 Then @TRUE Else @FALSE
Now, in the Transformer output link, use the constraint -

Code: Select all

svRoutineFound
Also, in the Transformer output link, define a column called JOBNAME and set its derivation to the stage variable svJobName, that is -

Code: Select all

JOBNAME = svJobName
Let the output flow to a hashed file, with JOBNAME set as key. This will eliminate duplicate job name when the routine is used multiple times in the same job.

There you go. That should give you the names of all jobs that use the Routine name (or any string) that is passed as a job parameter.

NOTE: If the Routine Name is any common string that could occur as part of anything in the job, then you will get even those jobs that do NOT contain the Routine.

If you really want to make sure that they are not any literals in a job, but are actually routines, then you will have to parse the dsx for every type of situations where a routine can be invoked (as mentioned above - before/after jobs, before/after active stages, job control, derivations, constraints). You will have to look for occurrences of the following strings -

1. "BeforeSubr" (followed by a string pattern containing the routine name)
2. "AfterSubr" (followed by a string pattern containing the routine name)
3. "Transform" (followed by a string pattern containing the routine name)
etc ..

Similarly, you can check for job control and Constraints (that contain routines).
gateleys
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Job control routines don't have names. However, in a DSX or XML export file they do have property names.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply