Search jobs that uses a particular file

[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

[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.

[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
;

[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

[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.

[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.

[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.

[Titto]

where do we run the SQL provided by Kdude, is it in Universe Stage?

[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.