Identifying Stages that Populate DB2 Tables

[fridge]

I am interested in writing some code that would run against a project and tell me which DS jobs update DB2 tables/fields. Looking at the help screens it looks like the function DSGetprojectInfo/Stage Info and DSJ.Joblist is a starting point. Is it possible to identify stages that load DB2 tables and from there extract the relevant table names and field attributes? Such Metadata would then be held out in a flat file and loaded into a DB2 table itself.

Any help would be greatly appreciated.

[ogmios]

The easiest would be to export your jobs to XML format and write a small parse in java or perl to extract your tables.

Ogmios

[kduke]

You could also use MetaStage and the Reporting Assistant. Do a search there have been other posts on this.

[ray.wurlod]

If you do write something, make sure it finds any ODBC stage that is writing to a DB2 DSN.

It's not good enough to assert that there aren't any; you can't prevent someone from creating one. Your utility must be able to handle it.

And then there's OLEDB...

[vmcburney]

As Kim suggests I would use the Reporting Assistant for this. Dump your project data into the reporting Access database, write a query against this database against the DSSTAGES table which can be filtered on StageType to give you the right stages and can show things like TargetFileName and DataSourceName so you can find out where specific tables are used.

[chucksmith]

Use my List all files and tables used by jobs in a dsx file . You can find it on the DataStage Tools page of www.anotheritco.com.

The input to the tools is a DataStage export file. Just export all job designs only.