How to check a Hashed File being used by

Titto · Post by **Titto** » Fri Aug 26, 2005 1:50 pm

Can anyone tell me how to check a particular Hashed file being used by other jobs in a project.

Thanks

deepa_natarajan81 · Post by **deepa_natarajan81** » Fri Aug 26, 2005 2:04 pm

I used DwNav tool. You can download it from tools4datastage.com.
I found it very easy to navigate.

Titto · Post by **Titto** » Fri Aug 26, 2005 2:09 pm

Let me try the tool

But is there a way with out using the tool.
I think there should be some SQL to fetch the jobs using a Hashed files. I tried searching in the forum could not find it.
~

ray.wurlod · Post by **ray.wurlod** » Fri Aug 26, 2005 5:15 pm

Provided your metadata management has been disciplined you should be able to select the hashed file's table definition in Manager and perform a Usage Analysis. This will show you everywhere that the table definition has been used in the project.

kduke · Post by **kduke** » Fri Aug 26, 2005 11:23 pm

DwNav is great. MetaStage should work. This link viewtopic.php?t=94738 will work.

Titto · Post by **Titto** » Tue Aug 30, 2005 1:22 pm

hi Kim,

Following Select statement got from the link you provided

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 
;

can you please tell me what are the other columns in DS_JOBOBJECTS and DS_JOBS tables/files..
And Can i create job to read DS_JOBOBJECTS and DS_JOB table using Hash file stage to fetch the data.. is it possible?
Thanks,

kduke · Post by **kduke** » Tue Aug 30, 2005 3:18 pm

There are some tricks you have to do to SELECT either DS_JOBS or DS_JOBOBJECTS. IBM tries to protect these files. You cannot import metadata from them directly. I usually create a q-pointer which is like a synonym.

ED VOC DsJobs
1:Q
2:
3:DS_JOBS

ED VOC DsJobObjects
1:Q
2:
3:DS_JOBOBJECTS

Now you can import metadata from DsJobs and DsJobObjects. You can use these in a UV stage. So you can extract data this way. The best way to get data out of these files is to create dictionary items. This is a lot more complicated to explain.

As far as what is in files takes a lot of hacking to find out. If you look at the jobs and routines included in EtlStats then you will see a lot more fields used in these tables. I have a few jobs which I extract job names, descriptions, job numbers, categories, sequence flag and update Oracle. I want to run reports for all jobs in a category. Another report is all row counts and run times for all sequences. Most of these just get the last run of each job.

Out of DS_JOBOBJECTS I extract Stage Names, Link Names, Column Names. There is also SQL to create dictionary items. A lot of this has been posted before on this web site.

Most of this is more easily available in Reporting Assistant or MetaStage. DwNav is a product I sell which helps you find these types of things in the data produced by Reporting Assistant. This type of ETL metadata is very valuable and can help you do your job. DwNav does a great job of generating documentation which non-technical people can understand or developers which do not understand DataStage like DBAs. If you are required to create documentation or manage ETL in any way then DwNav, EtlStats and now Parameter Navigator are a huge help.

Titto · Post by **Titto** » Tue Aug 30, 2005 4:32 pm

Hi Kim,

thanks for valuable information. Could you please tell me how to create Q pointers.

Thanks

ray.wurlod · Post by **ray.wurlod** » Tue Aug 30, 2005 4:44 pm

Search for SETFILE command on this forum.

chucksmith · Post by **chucksmith** » Tue Aug 30, 2005 7:09 pm

Please consider my List all files and tables used by jobs in a dsx file tool.

You will find it on the DataStage Tools page of www.anotheritco.com.

kduke · Post by **kduke** » Tue Aug 30, 2005 7:24 pm

SETFILE is Ray's way of creating q-pointers. The ED editor is my way. I just showed you in the above post. You have to edit the VOC file. You need to learn more about Universe to do these types of things. Not hard at all.