Usage of a hash-file

[jzijl]

Hello,

Is it possible to find out in which DS-jobs a hash file is used.
I am trying to find this out via the DS Manager usage analysis but can't find it.

Kind regards and thank in advance,

Jan

[ArndW]

One quick way is to export your project into a .dsx file and then search through it for the hashed file name and then go backwards until you find the job name. This is quick and easy if there are only a few occurrences of the hashed file name. I think that Kim's EtlStats might also assist in finding this. If you design your jobs and always import the hashed file metadata from the manager then you can use Usage Analysis to find occurrences.

[kduke]

The report is called DsHashFilesFind.sql in EtlStats. You can run it with job GenHtmlFromSqlDriver. You need to put the hashed file name in parameter Replacements.

Here is the SQL to run at TCL if you like:

Code: Select all

select 
   DS_JOBOBJECTS.OLETYPE AS STAGE_TYPE,
   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 EVAL DS_JOBOBJECTS."@RECORD<6>" = ':1'
group by 
   STAGE_TYPE,
   JOB_NAME,
   LINK_NAME
;

You need to replace :1 into your hashed file name. The job GenHtmlFromSqlDriver will do that for you if you install EtlStats.

[ray.wurlod]

A "hash file" is used for shaping a block of hash.

The term you need is "hashed file".

If you've been diligent with your metadata management, making sure that hashed file table definitions are properly stored in the Repository, then you can use Usage Analysis in the Manager client to answer the question "in which jobs is this hashed file used?".

[chucksmith]

Try my List all files and tables used by jobs in a dsx file tool. It produces a csv file that you can use with tools like excel to answer your question.

This, and other tools, can be found on the DataStage Tools page of www.anotheritco.com.