How to know whether a DS job is referenced by sequencer ?

yiminghu · Post by **yiminghu** » Fri Dec 10, 2004 9:13 am

Hi,

I have this headache problem. We're in the process of promoting our DS Jobs to production, but we found out there are lots of messy jobs in the project. Those are the test jobs created by developer during their development and tesing. Some jobs do not have very good naming standards, by looking at names, it is very hard for us to know whether this job is test job or not.

One thing we're quite sure, any valid DS job(not including sequencer) will be referenced by a sequencer or another job. I wonder whether there is any quick way to know whether the job is referenced by other jobs or not?

Thanks,

Carol

kduke · Post by **kduke** » Fri Dec 10, 2004 12:20 pm

Here is the SQL to get sequences. This is not 100% because this relies on the job dependencies in the job properties.

Code: Select all

SELECT 
   NAME,
   CATEGORY,
   EVAL "TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',31,'X')" AS DEPEND_JOBS FMT '35L',
   EVAL "if TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',40,'X') = '2' then 'Y' else 'N'" AS SEQ
FROM 
   DS_JOBS
WHERE
   SEQ = 'Y'
ORDER BY
   NAME
;

kduke · Post by **kduke** » Fri Dec 10, 2004 12:21 pm

Another method would be to search for the job name in all other jobs.

You would need to modify this SQL.

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

yiminghu · Post by **yiminghu** » Fri Dec 10, 2004 2:54 pm

Thanks a lot for your quick response.

I'm little bit confused about your solution. Shall I ran those query in Administrator/command window? What does @Record mean?

It there any manuals about those System tables such as DS_JOBS etc? I could find it in Admistrator Guide.

Thanks again.

Carol

kduke · Post by **kduke** » Fri Dec 10, 2004 3:18 pm

You can run these commands in Administrator but I prefer to telnet into the DataStage server and run them at TCL. Do a search this has been explained before. All you need to do is run the uv command in the DataStage server bin directory. You will need to be in the project directory. You could also use LOGTO if you want to research that.

yiminghu · Post by **yiminghu** » Fri Dec 10, 2004 7:51 pm

Hi Kim,

I'm not quite familiar with the command you were using. Can you tell me where I can get this kind of refernece marterial for those commands?

Regards,

Carol

kduke · Post by **kduke** » Sat Dec 11, 2004 1:04 am

The uv command is the same as sqlplus for Oracle. It is the command line way to interface with the Universe database. It is also called TCL. The Universe database is owned by IBM now. You can downlaod the manuals and even the database itself on their site. Ray posts the links for all these. You can do a search and find them.

chucksmith · Post by **chucksmith** » Sat Dec 11, 2004 10:05 am

After you determine your minimum set of jobs, I have a tool that will help you determine which routines are used by those jobs. Its input is a dsx file of those jobs. The tool is List routines called by jobs and routines in a dsx file. You may find it on the DataStage Tools page of www.aotheritco.com.