find datastage jobs in project using field name

singhald · Post by **singhald** » Thu Oct 07, 2010 5:11 am

Hi All,

I have to do some amendment in a field which is being used by datastage jobs in a project. I have 1000s of datastage jobs in my project. I need to findout the impected datastage jobs which are using that fields.

Can any one please let me know if we have any utility/ program by which we can do this.

Thanks in Advance.
Deepak

chulett · Post by **chulett** » Thu Oct 07, 2010 5:23 am

The Usage Analysis tool in the Manager, perhaps, its icon looks like a 'beaker'. That or by searching a dsx export of the project.

singhald · Post by **singhald** » Thu Oct 07, 2010 5:36 am

Thanks Craig,

I think, The first option of Usage Analysis doesn't give the informaiton about the field/metadata.

I am trying the second option, but it is taking lot of time and it manual work which currently i am doing.

Please let me know if you have come accross this type of problem before and how did you workout.

Thanks

Deepak

chulett · Post by **chulett** » Thu Oct 07, 2010 5:43 am

If you have stored your table meta data in the repository and loaded it into the job then the first option does exactly what you need. Otherwise, you've got no other option but a search of the dsx. Well, except for things like Chuck Smith as written, he has a utility to parse a dsx and I've modified it in the past to load the results to a database table so they become query-able.

Check out his website at: www.anotheritco.com

eostic · Post by **eostic** » Thu Oct 07, 2010 8:37 am

Metadata Workbench can provide you with this information also, by doing a query that looks at "Stage Columns"....which are effectively, Columns-on-a-link.....

Ernie

vinothkumar · Post by **vinothkumar** » Thu Oct 07, 2010 9:02 am

I think you can tweak the below query running in Universe tables for your need.

SELECT DS_JOBS.NAME AS JOB_NAME, DS_JOBS.CATEGORY , DS_JOBOBJECTS.NAME AS OBJECT_NAME , DS_JOBOBJECTS.OLETYPE , EVAL DS_JOBOBJECTS."if index(upcase(@RECORD),'YOUR_FIELD_NAME',1) > 0 then 'FOUND' else ''" AS RECORD , EVAL DS_JOBOBJECTS."@RECORD<8>" AS OBJ_TYPE FMT '50L' FROM DS_JOBS, DS_JOBOBJECTS WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO AND RECORD='FOUND';

Same SQL will be used the find the list of jobs using a particular table.

chulett · Post by **chulett** » Thu Oct 07, 2010 9:11 am

eostic wrote:Metadata Workbench can provide you with this information also, by doing a query that looks at "Stage Columns"....which are effectively, Columns-on-a-link.....

And that is available in the 7.x release?

chulett · Post by **chulett** » Thu Oct 07, 2010 9:12 am

vinothkumar wrote:I think you can tweak the below query running in Universe tables for your need.

That looks very... Duke like.

vinothkumar · Post by **vinothkumar** » Thu Oct 07, 2010 9:44 am

Yes Criag. That SQL was taken from this forum only.

We can create a simple server job with universe stage to execute this SQL.

eostic · Post by **eostic** » Thu Oct 07, 2010 1:56 pm

And to Craig's point, Workbench isn't available in 7.x, but you could import 7.x Jobs into 8.x with Workbench and immediately analyze them (meaning, they don't have to be compiled and running in v8, just imported).

Ernie