Hash File Usage Analysis

[yendra]

Hi everybody,

I'm using the datastage 7.5 and use a lot of hash file for references. And now we in the phase that will require to modify the hash file. And the issue that we need to know the impact to the job that use that hash file.

Is there any way to now what job that tied up to the hash file? Because we can now the job reference to another job via USAGE ANALYSIS.


Regards

[DS_SUPPORT]

Welcome to DSXchange...

Execute the following code in Administrator Command prompt, and you will get the desired results.

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>" = 'YOUR HASHED FILE NAME'
group by 
   STAGE_TYPE,
   JOB_NAME,
   LINK_NAME
;

[yendra]

Welcome to DSXchange...

Execute the following code in Administrator Command prompt, and you will get the desired results.

Is there any command that i need to execute before execute the select statement? Like DS.TOOLS?

I just run it command but return error

Thanks

[DS_SUPPORT]

Wrap all the lines together, (remove all enter characters) and make it as a single line and execute it.

Alternatively, you can can use this code in universe stage and get the desired output. Search the forum or read help, on how to use the universe stage

[yendra]

Wrap all the lines together, (remove all enter characters) and make it as a single line and execute it.

Alternatively, you can can use this code in universe stage and get the desired output. Search the forum or read help, on how to use the universe stage

I wrap all the lines together and run it

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>" = 'test' group by STAGE_TYPE, JOB_NAME, LINK_NAME;

But it return error : DataStage / SQL: syntax error. Unexpected literal string. Token was "DS_OBJECTS.OLETYPE

I just had zero experience on universe data and your enlightment will be very helpfull. Still taking to search the reference.

Regards

[ray.wurlod]

It's not DS_OBJECTS, it's DS_JOBOBJECTS.

[yendra]

It's not DS_OBJECTS, it's DS_JOBOBJECTS. ...

Thanks a lot for your code. Now i had the result and it show all the reference the HASH FILE

select DS_JOBOBJECTS.OLETYPE,DS_JOBS.NAME, DS_JOBOBJECTS.NAME 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>" = 'TR_06_SUB_MSTR'