How do i find a Job by target?

[JKenklies]

Hello experts,

I often have to check backwards how the source file of my current server job is built and in which job that happens. But sometimes, the names of the jobs are kind of cryptic.

Looking for a jobs category can be done using the DS Administrator. With the statement "SELECT NAME, CATEGORY FROM DS_JOBS WHERE NAME='<JobName>';" I can find the category where the job belongs to.

The DS Manager can list a jobs sources, lookups and targets but does not provide any search function (I didn't find any).

My Question: Is there a select statement for the DS Administrator that can find all Jobs using a specific file as source, lookup or target?

Thank you for any solution...

Justus

[asitagrawal]

Well, I dont think if u can find by what name u give to the target file,
BUT,
u can find by what name u give to the stage.

For this, u must go to Manager, select the type of stage, eg. Hashed File,
and then click on 'Usage Analysis'.. this give the list of all the jbos having that satge.. now u must locate the entry which matches the satge name u have given . After locating that, right click and select Edit... it will open the job for u !!!

BUT this happens iff, u satge name similar to the file name ..

HTH !! :D

[chulett]

Yes, there are other queries that can find what you are looking for, but they require intimate knowledge of the repository structure which is not made public by the vendor. Certain long-time users have that knowledge and queries have been posted here that a search could turn up, I would think.

One more 'accessable' way is to go to Chuck Smith's website and download his utility that reads a .dsx and builds a list of that kind of information. We then load it into a database table so we can query the results.

[JKenklies]

@asitagrawal:
Thank you, i didn't know about that functionality in the Manager. But even if I get all hash files listed, I still have problems to find the one i need, because they are all prefixed with the Job Name and cannot be sorted by stage name nor searched in cause of a missing search function. But: This method is a step closer to a good solution.

@chulett:
Thanks for your answer and that link to Mr. Smiths website. I could import and compile that tools. In this jobs, you're asked to provide the "DsxPath" as parameter. On our NT Machine, this doesn't seem to work. Am I right providing my project folder as "DsxPath" ? Maybe this only works on a unix/linux system?

Thank you.

[DSguru2B]

Use the following query and thank Kim (kduke) for it.

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(@RECORD,'Your file/table name',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 
; 

Replace Your file/table name with the name of the table or the name of the file you are looking for.

[chulett]

Am I right providing my project folder as "DsxPath" ? Maybe this only works on a unix/linux system?

No, it will work on any system. No, you do not provide it a path to your Projects, but instead create a ".dsx" export from the Manager of the source of all jobs in your project. Then point it to that .dsx file.

[JKenklies]

Use the following query and thank Kim (kduke) for it.

Thank you... and thank Kim, that worked well.

@chulett: I think I know what you mean. I'll try that, thank you, too.

[sun rays]

Usage analysis of table definitions could also be helpful if you have used the table definitions to load the columns in your files / tables.

[kduke]

Wow, I get credit even without answering. Thanks.

[DSguru2B]

Impressive work leaves a mark forever. We got your back Kim.