Tuesday, December 4, 2012

How To find list of jobs accessing a table in Datastage?


1)Create New Server Job

Universe Stage-->Sequential file

a)Universe Stage:
Datasource name-localuv

SQL Query:
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),'PIPPO',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 

Mention the column names in Columns tab from the above sql Query

b)Mention target filename in Sequential file stage

No comments:

Post a Comment