DSXchange

Posted: **Mon Oct 03, 2005 12:26 am**

Dear All ,

We have one requirement , If any column used in jobs,hash file , routine etc , then find all the objects name .

For Ex:- Two Emp_Code,Emp_Name .

EMP_CODE - NUMERIC(5)

1. First Job extracting data from empmaster and create sequential file .
2. Second job create hash file with using KEY - emp_code.
3. Third Job - Use hash file and load additional data into table , here also esing emp_code .

Now As per new requirement , we need to increase field length of emp_code from NUMERIC(5) to NUMERIC(7) .

So we need to list out all the job , hash file etc wherever using emp_code column . then only we start changing .

Pl help .

Regards
Arun Verma

Posted: **Mon Oct 03, 2005 1:26 am**

You can easily get this, If you search the posts, posted by Kim Duke. Else there is a utility called "Search a dsx file for a string" in www.anotheritco.com posted by chucksmith.

Rgds
Anand

Posted: **Mon Oct 03, 2005 5:39 am**

Provided you've been diligent preserving the connections between table definitions in the Repository and in your jobs, you can do a Usage Analysis on the table definition in which the column occurs. This should reveal all the jobs in which it is used.

Posted: **Mon Oct 03, 2005 6:00 am**

The problem that the table name can be all lower case, all upper case or a mixture.

You need to change :1 into your table name in upper case. This SQL is a part of EtlStats. It is called DsSearch1.sql. It is executed by job GenHtmlFromSqlDriver. Put your tablename in parameter named Replacements. It can email you the results if setup properly.

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),':1',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
;

DSXchange

How to get DS object name

How to get DS object name