Identify Jobs that load/reads from a particular database

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Post Reply
Nagaraj
Premium Member
Premium Member
Posts: 383
Joined: Thu Nov 08, 2007 12:32 am
Location: Bangalore

Identify Jobs that load/reads from a particular database

Post by Nagaraj »

How do i identify the Job Names which are reading/writing from a particular database?
There is Mainatanance going on the one particular database and there are 100's of jobs scheduled on the server, its hard to find the jobs read/loading this particular database other than manual intervention.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

If you have Metadata Workbench you can create a lineage report that will show you exactly this information.

If you do not have Metadata Workbench you probably build a query against DS_JOBOBJECTS and DS_JOBS that finds input links (for writes) or output links (for reads) that refer to the particular database. You can find examples on DSXchange.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Nagaraj
Premium Member
Premium Member
Posts: 383
Joined: Thu Nov 08, 2007 12:32 am
Location: Bangalore

Post by Nagaraj »

Is there any way to find in the designer environment? I tried usually DB object is not stored in the designer, its at table level.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Iff you have been diligent with your metadata (loading imported table definitions into jobs and not editing them there) you can perform a Where Used analysis on the table definition in Designer.

"Iff" is mathematicians' shorthand for "if and only if". It is not a typo.
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 »

Thank goodness you said something because I was gonna fix it for you! :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
Nagaraj
Premium Member
Premium Member
Posts: 383
Joined: Thu Nov 08, 2007 12:32 am
Location: Bangalore

Post by Nagaraj »

Even this works at table level, i need to find whichever jobs on the server either reading or writing to the DB, This has the table name

Code: Select all

SELECT DS_JOBS.NAME AS JOB_NAME, DS_JOBS.CATEGORY, DS_JOBOBJECTS.NAME AS OBJECT_NAME, DS_JOBOBJECTS.OLETYPE, EVAL DS_JOBOBJECTS."if index(upcase(@RECORD),'TABLE_NAME',1) > 0 then 'FOUND' else ''" AS FOUND FMT '5L' FROM DS_JOBS, DS_JOBOBJECTS WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO and FOUND = 'FOUND' GROUP BY JOB_NAME, DS_JOBS.CATEGORY, OBJECT_NAME, DS_JOBOBJECTS.OLETYPE, FOUND; 
Post Reply