Page 1 of 1
Finding a Input Dataset used in jobs
Posted: Thu Feb 12, 2009 3:24 pm
by mohan bommalingaiah
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.
Posted: Thu Feb 12, 2009 4:36 pm
by DSguru2B
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.
Posted: Fri Feb 13, 2009 3:31 am
by Sainath.Srinivasan
I don't have 7.x, but believe you can do a "Where used" from Manager.
Finding a Input Dataset used in jobs
Posted: Fri Feb 13, 2009 9:37 am
by mohan bommalingaiah
Thanks very much, and the special thanks to Kim Duke.
Posted: Fri Dec 26, 2014 3:55 am
by sendmkpk
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
Posted: Fri Dec 26, 2014 6:48 am
by ArndW
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.
Posted: Fri Dec 26, 2014 6:01 pm
by ray.wurlod
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.
Posted: Fri Dec 26, 2014 6:34 pm
by chulett
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.
Posted: Sat Dec 27, 2014 2:48 pm
by ray.wurlod
The table structures (and column names) have changed since version 7.x.
Posted: Sat Dec 27, 2014 10:51 pm
by chulett
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.