Need to search a Sequential file for example x.csv in jobs

Kalyana Chakravarthy · Wed Oct 29, 2008 6:03 am

Need to search a Sequential file for example x.csv where all it is used in the jobs and can I get using Datastage.Is it possible using this Datastage.

Kalyan

chulett · Post by **chulett** » Wed Oct 29, 2008 6:34 am

Did you import the metadata for that file and use that metadata in your jobs? If so, then Usage Analysis should work. Otherwise, export the project to a dsx file and search the export for that string.

ray.wurlod · Post by **ray.wurlod** » Wed Oct 29, 2008 9:17 am

Code: Select all

SELECT NAME FMT '32L', CATEGORY FMT '40L' FROM DS_JOBS WHERE JOBNO IN (SELECT OBJIDNO FROM DS_JOBOBJECTS WHERE EVAL "@RECORD" LIKE '%x.csv%');

chulett · Post by **chulett** » Wed Oct 29, 2008 9:18 am

Or that.

Lotus26 · Post by **Lotus26** » Tue Nov 11, 2008 3:17 pm

Hi Ray

I was trying to run this query

SELECT NAME FMT '32L', CATEGORY FMT '40L' FROM DS_JOBS WHERE JOBNO IN (SELECT OBJIDNO FROM DS_JOBOBJECTS WHERE EVAL "@RECORD" LIKE '%x.csv%');

But it is failing
DataStage/SQL: syntax error. Unexpected symbol. Token was "@RECORD".

Any suggestions?Thanks in advance.

ray.wurlod · Post by **ray.wurlod** » Tue Nov 11, 2008 4:24 pm

None. It works fine for me.

chulett · Post by **chulett** » Tue Nov 11, 2008 6:14 pm

Make sure you are in a Project - either issue a LOGTO YourProject or launch the shell from inside the project directory so it is automatically logged into the right project.

Lotus26 · Post by **Lotus26** » Wed Nov 12, 2008 9:31 am

Yes..It worked for me .But a small syntax change like I have used:

/dsadm/Ascential/DataStage/DSEngine/bin/uvsh "SELECT NAME FMT '32L', CATEGORY FMT '40L' FROM DS_JOBS WHERE JOBNO IN (SELECT OBJIDNO FROM DS_JOBOBJECTS WHERE EVAL '@RECORD' LIKE '%x.dat%');"

Thanks for all your suggestions.

chulett · Post by **chulett** » Wed Nov 12, 2008 11:04 am

Because you needed to look for a dat extension rather than csv? Ok. Mine worked fine with both "@RECORD" and '@RECORD' so that doesn't seem to be signficant.

Lotus26 · Post by **Lotus26** » Wed Nov 12, 2008 12:59 pm

I am having one more question . Can we find out like in what job a particular dataset is used in the same way or will there be a different query or way for that?

I would really appreciate any suggestions.Thanks in advance.

Lotus26 · Post by **Lotus26** » Wed Nov 12, 2008 1:07 pm

Sorry ..I did tried with the same query and was able to find out the job names .

Thanks for all your help and suggestions.