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 ??
Table usage analysis
Moderators: chulett, rschirm, roy
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.
Searching for something like OLETYPE would be a good starting point. Here's one example to check out.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 232
- Joined: Fri Aug 04, 2006 1:20 am
- Location: Bangalore
I hope Kim Duke's query will help you for finding the table names.
This query can be used for finding any string in your project. Seach the forum, you can find lot of queries like this.
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
;
-
- Premium Member
- Posts: 385
- Joined: Wed Jun 16, 2004 12:43 pm
- Location: Virginia, USA
- Contact:
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 .
You will find it and other tools on the DataStage Tools page of www.anotheritco.com .
Chuck Smith
www.anotheritco.com
www.anotheritco.com