Finding a Input Dataset used in jobs

mohan bommalingaiah · Post by **mohan bommalingaiah** » Thu Feb 12, 2009 3:24 pm

How do i find all the job names that may be using a specific dataset easily, without having to go through each job to find out what inputs it is using in datastage.

Any kind of help will be appreciated.

DSguru2B · Post by **DSguru2B** » Thu Feb 12, 2009 4:36 pm

Use the following query at the uv account or admin command, once logged into your project.

Code: Select all

SELECT 
   DISTINCT DS_JOBS.NAME AS JOB_NAME FMT '100L', 
   DS_JOBS.CATEGORY FMT '100L', 
   DS_JOBOBJECTS.NAME AS OBJECT_NAME FMT '100L', 
   DS_JOBOBJECTS.OLETYPE FMT '100L', 
   EVAL DS_JOBOBJECTS."if index(@RECORD,'FIND ME',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 
;

Put in your dataset name where it says 'FIND ME'. Use it and Thank Kim Duke. Its his code.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Feb 13, 2009 3:31 am

I don't have 7.x, but believe you can do a "Where used" from Manager.

mohan bommalingaiah · Post by **mohan bommalingaiah** » Fri Feb 13, 2009 9:37 am

Thanks very much, and the special thanks to Kim Duke.

sendmkpk · Post by **sendmkpk** » Fri Dec 26, 2014 3:55 am

Hi,

I am using 8.1, I tried this code by kim, doesnt seem to work

or is it cause of the job parameters used in it,

plz share a thought.

Reg

ArndW · Post by **ArndW** » Fri Dec 26, 2014 6:48 am

If your dataset name is in a job parameter, then this method won't work - you would need to do a more complex search. On the other hand, if you know the parameter name you could search for that.

ray.wurlod · Post by **ray.wurlod** » Fri Dec 26, 2014 6:01 pm

DSguru2B wrote:Use the following query at the uv account or admin command, once logged into your project.

This is not a suitable answer for version 7.x. Also it would not work in the UV account.

chulett · Post by **chulett** » Fri Dec 26, 2014 6:34 pm

Hmmm... what about the code is not applicable to the 7.x version? And you are getting picky about the "uv account" comment, he did say "once logged into your project" after that, so correct in spirit.

ray.wurlod · Post by **ray.wurlod** » Sat Dec 27, 2014 2:48 pm

The table structures (and column names) have changed since version 7.x.

chulett · Post by **chulett** » Sat Dec 27, 2014 10:51 pm

This looks like the 7.x code (and table/column names) that I knew back in the day, did you actually mean it won't work with versions after 7.x? Sorry, just trying to understand your statement.