Listing all hash files

[SonShe]

I have a need to list all the hash files that are being created by all the jobs in one category. Then I need to some how determine which of these hash files are being used.

I would appreciate if anyone can please suggest a better way to accomplish this. I was thinking of compiling the list of hash files manually and then searching the export file for the project to determine which hash files are not being used.

Thanks in advance for the help.

[kduke]

You need to change :1 into your category but here is the query.

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 
   and DS_JOBS.CATEGORY = ':1'
group by 
   FILE_NAME, 
   JOB_NAME,
   LINK_NAME
;

[SonShe]

You need to change :1 into your category but here is the query.

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 
   and DS_JOBS.CATEGORY = ':1'
group by 
   FILE_NAME, 
   JOB_NAME,
   LINK_NAME
;

Thank you, Kim. I am not very proficient with writing queries. However, I did the following to execute the query and it does not work.

From my project directory I ran PATH=$PATH:/dstage/app/DataStage/DSEngine/bin ; export PATH. Then I ran the query as uvsh "Select .....". Did I do the right way? Please let me know how queries work.

Thanks.

[kduke]

You will either have to remove the carriage returns or run uv and run it it at the prompt. If you run it at the uv prompt then run:

Code: Select all

PTERM CASE NOINVERT

Then just paste it in.

[chucksmith]

First, create a dsx file with just the jobs from your category.

Next, try my tool List all files and tables used by jobs in a dsx file. You will find it on the DataStage Tools page of www.anotheritco.com.

It creates a nice csv file with job name, category, file type, file name, and an input/output indicator.

[bradh]

I ran Chuck's tool to list all files and tables used by jobs. It works great. Thanks Chuck. I have one question, though. What is a 'CHashedFileStage'? All of my hash files are 'Hashed Input' or 'Hashed Output'. I thougth that 'CHashedFileStage' was going to be a hashed file, but I found that the file name is a table. Any thoughts?

Thanks,

Brad

[chucksmith]

Hi Brad,

I am not sure why you are seeing CHashedFileStage. Although you say the tool is reporting a table name, when you look at the specific job in designer, what stage is it using? CHashedFileStage is a hashed file stage, but the tool has not determined if it is being used for input or output, yet.

Please let me know. I will keep looking. Thank you for your feedback.

[kduke]

'CHashedOutput','CHashedInput' determine input and output but what we think of as input DataStage thinks is output. Take the SQL and either use one value or print this field.