Page 1 of 1
Identify Jobs that load/reads from a particular database
Posted: Fri Jan 25, 2013 3:03 pm
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.
Posted: Fri Jan 25, 2013 3:55 pm
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.
Posted: Mon Jan 28, 2013 9:47 am
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.
Posted: Mon Jan 28, 2013 2:09 pm
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.
Posted: Mon Jan 28, 2013 3:38 pm
by chulett
Thank goodness you said something because I was gonna fix it for you!
Posted: Tue Jan 29, 2013 4:14 pm
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;