Page 1 of 1
Search jobs that uses a particular file
Posted: Mon Jul 04, 2005 8:06 am
by yiminghu
Hi,
I'm currently modifying a job which generates a reject files. I may need to change the format of file. Before I do that I need to know whether there is any jobs reference that file?
Is there any easy way to find out besides to go through all jobs?
Thanks,
Carol
Posted: Mon Jul 04, 2005 8:10 am
by ArndW
A quick-and-dirty method would be to export the project to a .dsx or .xml file and do a text search for occurrences of the file name in that file. Of course this only work where you specifically used the name and not a parameter. In addition, you can use the built-in metadata tools if you always loaded the column definitions for that file from the manager's repository.
Posted: Mon Jul 04, 2005 8:55 am
by kduke
The quickest way is to run this SQL. Change SEQFILENAME to whatever you are looking for in uppercase.
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),'SEQFILENAME',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
;
Posted: Wed Jul 06, 2005 5:43 am
by elavenil
This query results each & every object in the job, in which the sequential file is used. How would i get one row per a job?
Removed Object's Name & OLETYPE from the selection and group by clause but it gave me a error message as 'Internal problem with ALIAS, submit GTAR'
Can you pls enlighten me what the problem is.
Thanks & Regards
Saravanan
Posted: Wed Jul 06, 2005 9:37 am
by kduke
There are some bugs in EVAL especially in GROUP BY clauses. You have to play around with it to get it to work. If you delete a column then make sure you delete it in the group by. The error messages are not very useful and in fact misleading when you are playing around with the respository files.
Posted: Wed Jul 06, 2005 2:14 pm
by chucksmith
Try my
List all files and tables used by jobs in a dsx file tool.
You will find it on the
DataStage Tools page of
www.anotheritco.com.
Posted: Wed Jul 06, 2005 3:32 pm
by ray.wurlod
Provided you've loaded the file's definition from the Repository in all cases (all jobs), you can select the Table Definition in Manager and perform a Usage Analysis there.
Posted: Fri Jul 08, 2005 12:52 pm
by Titto
where do we run the SQL provided by Kdude, is it in Universe Stage?
Posted: Fri Jul 08, 2005 1:53 pm
by kduke
You need to telnet into your DataStage server. If you are on Windows then you will get a TCL prompt ">".
Code: Select all
LOGTO YourProject
PTERM CASE NOINVERT
(paste SQL statement above.)
If on UNIX:
Code: Select all
cd `cat /.dshome`
./bin/uv
(now you should see the TCL prompt ">")
LOGTO YourProject
PTERM CASE NOINVERT
(paste SQL statement above.)
Let us know if you have problems. By the way please call me Kim.