HI Gurus,
I need to list all the jobs in a project for which a particular table is being used.
The table name supposed to find is "R_DM_SCRB_DTL' ". In the jobs as per the convention, we need to prefix 'ora' or 'src' something as such before the table name so in the below query I used DS_JOBOBJECTS.NAME like '%_R_DM_SCRB_DTL'
I used the below query:
SELECT distinct DS_JOBS.NAME AS JOB_NAME FROM DS_JOBS,DS_JOBOBJECTS WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO AND DS_JOBOBJECTS.NAME like '%_R_DM_SCRB_DTL' AND CATEGORY LIKE '\Global\RDM\Supply Chain%';
Query works and it helps me to find if any such table is used in the job.
But in my current scenario I have a job (job_name: j_P_ITEM).P_item(table_name) is used as a source and named as ora_P_ITEM (oracle_connector stage) used. Internally in the query left outer join is performed on R_DM_SCRB_DTL'. I would also need to list such things. If 'R_DM_SCRB_DTL' is used internally in the query even need to list such jobs. Is there a way that I can write a query in command line to pull the list of such jobs? Kindly help
Thanks in advance.
Kumar
Searching for Jobs that use a particular table
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You need an additional filter to check the entire DS_JOBOBJECTS record for the (possibly partial) name of the table, maybe constraining that to records for which OLETYPE ends in "put". Use EVAL and @RECORD in the query - search DSXchange for examples.
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.