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.
Perticular routine used by different # of ds jobs
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 132
- Joined: Wed Mar 01, 2006 11:12 pm
- Location: Pune
Re: Perticular routine used by different # of ds jobs
Hi Ashish,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.
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
Now, in the Transformer output link, use the constraint -
Also, in the Transformer output link, define a column called JOBNAME and set its derivation to the stage variable svJobName, that is -
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).
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
Code: Select all
svRoutineFound
Code: Select all
JOBNAME = svJobName
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: