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.
Identify Jobs that load/reads from a particular database
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
"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.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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;