DSXchange

Posted: **Thu Dec 08, 2005 2:56 am**

Hi All,

I would like to know whether there is any command or any other way to know the list of hash files and sequential files (at a strech) and also in which job they are created in a particular project?

Thanks,
Shyju.

Posted: **Thu Dec 08, 2005 4:30 am**

There is no direct to do this and ensure a correct list. Hashed files created in the project directory (and not in a remote path) are detectable, but hard to distinguish from the normal files used by DataStage. The builtin metadata and report generator can give some clue as to which files are used, but it relies on using the repositorie's table definitions in all the jobs. I've written some programs to read and parse an exported .dsx file to get file objects used, but that was a bit complicated as well - plus most file objects are not hard-coded but use parameters for their paths and thus even that method is not going to be 100% accurate.

You can use the UNIX "file" command and it's associated magic file to locate all hashed files on a system but you will need to process that list to remove all standard DataStage files. And that will located hashed files only, not sequential files.

Posted: **Thu Dec 08, 2005 5:07 am**

Hi Shyju,

You can use the search facility, to find out the Query. Kim Duke has posted lot of queries to do this. Else Chuck Smith has a tool, List All Files And Tables in DSX in his Website

Following is Kim's Query to list all the Hash files in the Project

Code: Select all


select 
   EVAL DS_JOBOBJECTS."@RECORD<6>" AS FILE_NAME FMT '35L', 
   DS_JOBS.NAME AS JOB_NAME FMT '35L', 
   DS_JOBOBJECTS.NAME AS LINK_NAME FMT '35L' 
from 
   DS_JOBOBJECTS, 
   DS_JOBS 
where 
   DS_JOBOBJECTS.OLETYPE in ('CHashedOutput','CHashedInput') 
   and DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO 
group by 
   FILE_NAME, 
   JOB_NAME, 
   LINK_NAME 
;

He has posted a similar query to list the Sequential files.

Code: Select all


select 
   EVAL DS_JOBOBJECTS."@RECORD<6>" AS FILE_NAME FMT '45L', 
   DS_JOBS.NAME AS JOB_NAME FMT '35L', 
   DS_JOBOBJECTS.NAME AS LINK_NAME FMT '35L' 
from 
   DS_JOBOBJECTS, 
   DS_JOBS 
where 
   DS_JOBOBJECTS.OLETYPE in ('CSeqOutput','CSeqInput') 
   and DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO 
group by 
   FILE_NAME, 
   JOB_NAME, 
   LINK_NAME 
;

You can combine these 2 queries like

Code: Select all

DS_JOBOBJECTS.OLETYPE in ('CHashedOutput','CHashedInput','CSeqOutput','CSeqInput')

Posted: **Thu Dec 08, 2005 5:41 am**

Thanks Andal

The querys are very useful

Thanks for your response

RK

Posted: **Fri Dec 09, 2005 11:23 am**

Where do we run these SQL's if it is in Administrator, should it be in single line ?

Posted: **Sun Dec 11, 2005 10:47 pm**

Thanks andal... The information was very useful. I got the list of hash and sequential files by running the sqls in administrator in a single line.

Posted: **Sun Dec 11, 2005 11:20 pm**

All the Credit goes to Kim Duke. You can run these sqls in Administrator or You can use this sql in Universe and redirect the output to Excel sheet.

DSXchange

Command to know list of hash files in a particular project

Command to know list of hash files in a particular project