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.
Listing all hash files
Moderators: chulett, rschirm, roy
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
;
Mamu Kim
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.kduke wrote: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 ;
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.
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:
Then just paste it in.
Code: Select all
PTERM CASE NOINVERT
Mamu Kim
-
- Premium Member
- Posts: 385
- Joined: Wed Jun 16, 2004 12:43 pm
- Location: Virginia, USA
- Contact:
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.![Wink :wink:](./images/smilies/icon_wink.gif)
It creates a nice csv file with job name, category, file type, file name, and an input/output indicator.
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.
![Wink :wink:](./images/smilies/icon_wink.gif)
It creates a nice csv file with job name, category, file type, file name, and an input/output indicator.
Chuck Smith
www.anotheritco.com
www.anotheritco.com
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
Thanks,
Brad
-
- Premium Member
- Posts: 385
- Joined: Wed Jun 16, 2004 12:43 pm
- Location: Virginia, USA
- Contact:
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.
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.
Chuck Smith
www.anotheritco.com
www.anotheritco.com