Table usage analysis

Rangs · Post by **Rangs** » Sun Oct 08, 2006 6:42 am

I have got around 800 DataSTage jobs in my project. In those Jobs are many DB2 stages. In those DB2 stages somewhere in the Input SQL or in the table name property the table 'DI_DIMENSION' is specified.

Is there a way to list all the Jobs that contain the table. There is no table definition defined for this table.

Ofcourse one way of doing this is to export into a dsx file and search the table name to find out the jobs. But I'm wondering ..can we query to repository to get this info ??

chulett · Post by **chulett** » Sun Oct 08, 2006 6:49 am

Sure, and there are many posts here that can help you with that. The DB2 stage will have a certain OLETYPE in DS_JOBOBJECTS and you can query for that. Sorry, I don't know what it is off the top of my head so you'll need to do a little digging. Then you would constrain by your particular table name.

Searching for something like OLETYPE would be a good starting point. Here's one example to check out.

DS_SUPPORT · Post by **DS_SUPPORT** » Mon Oct 09, 2006 1:08 am

I hope Kim Duke's query will help you for finding the table names.

Code: Select all

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 Table Name',1) > 0 then 'FOUND' else ''" AS FOUND FMT '5L'
FROM 
   DS_JOBS, 
   DS_JOBOBJECTS 
WHERE 
   DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO 
   and FOUND = 'FOUND'
GROUP BY
   JOB_NAME, 
   DS_JOBS.CATEGORY, 
   OBJECT_NAME, 
   DS_JOBOBJECTS.OLETYPE, 
   FOUND
;

This query can be used for finding any string in your project. Seach the forum, you can find lot of queries like this.

kduke · Post by **kduke** » Mon Oct 09, 2006 9:00 am

The OLETYPE for plugins is usually a custom. I have a job which will pull all the custom properties and get table names and whether or not they are truncated. This job is in EtlStats in the MetaData folder.

chucksmith · Post by **chucksmith** » Tue Oct 10, 2006 3:09 pm

Try my List all files and tables used by jobs in a dsx file utility. It reads a dsx file of your job designs and routine sources, and creates a csv file of category, job name, input/output, and table name/file name/user defined sql.

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