How do I generate DataStage job reports?

This is a large topic and you are well advised to search through the forum archives for threads on the options discussed below. DataStage Hawk combines the DataStage and MetaStage repositories and products into one so most of these options may be redundant in that release.

Process metadata is the details of job execution such as run times and dates and row counts.
Job metadata is the details of the job design such as stage names and link names.

MetaStage
MetaStage is the preferred DataStage reporting tool and certainly the one with the widest range of functions and reports.

Strengths: it has the most reporting options. It can compare difference between DataStage jobs. It has data lineage, impact analysis and customisable job reports. It automatically collects DataStage process metadata. It can combine DataStage metadata with the metadata of other products.

Weakness: it requires its own repository and insists on installing this repository via the Administrator tool. You cannot send a schema creation script to your DBA. It can only be installed on Windows machines.

DataStage Reporting Assistant
This used to be the reporting tool of choice in old DataStage releases but has been superceded by MetaStage. In recent releases it cannot report on parallel jobs. It can still be used to extract details on server jobs and deliver them to an ODBC database. Once in the database you can build useful SQL statements to report on job metadata.

Strengths: it is easy to run. It gives easy access to job metadata with fully customisable SQL selects of job names and job properties.

Weakness: it cannot report on parallel jobs. It does not include process metadata. It cannot refresh data automatically or via a schedule. No job bitmaps.

dsjob
The dsjob command has a range of reporting functions and can be run on the server command line or on the client command line. DataStage 7.x installs dsjob on your windows client machines. Typical dsjob commands include -lprojects to list projects, -ljobs to list jobs and -jobreport to generate process metadata.

Dsjob is used by several of our job reporting options and you can use it for your own custom job reporting.

XML export reporting
Recent DataStage releases support export files in an XML format. DataStage comes with a couple XML formatting files with an XLS file extension, if you apply that styling to an XML export file and view it in a browser it turns your export file into a job report.

Strengths: can be run against any XML export file. Lets you report on projects without requiring access to the project environment. With smart xls programming it can apply qa checks on job properties. It's the easiest way to generate reports on remote DataStage projects sent via email.

Weakness: no bitmaps of the jobs available. Need to be profficient in xls to get full value. It cannot report on process metadata. No job bitmaps but it is easy to integrate with the Designer job bitmap generator.

HTML job reports
In version 7 of DataStage the Designer has a very useful Generate Report function. This is available from the File menu and is also available from the command line. See the Designer PDF for details. On Ascential devnet there are a couple uploads that let you run Generate Report for all jobs in a project. The report is in HTML format and comes with a bitmap of the job.

Strength: it's very sexy, it blows people away if they haven't seen good job reporting before. The bitmap generator can be integrated with other reporting options such as XML Export reporting.

Weakness: no easy way to run it for all jobs in a project. You need to get the devnet download. No main index pages available, you need Kim Duke's job reporter for that. Need to know HTML and XML programming to customise it. No process metadata.

Kim Duke HTML reporting
Uses the bitmap generator function of the Designer to create job bitmaps. Uses a set of custom server jobs and job control code to create HTML pages that provide an index of jobs and job reports.

Strength: better formatting and navigation then the standard Ascential job reports.

Weakness: installation and minor configuration of the server jobs is required. Does not work for sites that chose not to authorise server jobs. No process metadata, see etlstats.

Kim Duke ETLSTATS
Uses a series of server jobs to retrieve DataStage process metadata from the internal DataStage repository and a set of generated dsjob job reports. The driving Sequence job needs to be run after each batch is completed to retrieve process metadata for that batch.

Strength: easier to install and use then MetaStage. Comes with a simple database schema for capture DataStage process metadata.

Weakness: can be difficult to install and configure if you are not familiar with batch jobs or server jobs. Currently configured for Oracle, you need to convert it to other database types.

Wish List: needs a readme with install and run instructions. Could be converted to use the Dynamic Database Stage instead of the Oracle OCI stage.

Custom Repository SQL
Search the forum for tips on querying the DataStage repository directly. There are metadata and job tables which can be accessed via Universe database SQL statements.

Weakness: the internal DataStage repository consists of Universe tables. You cannot point a DataStage Universe plugin at these tables and the column list and definitions are not published in the data dictionary. So it is not easy to extract from the internal repository. The Kim Duke reporting packages are a good example of how to do it through BASIC code.

Recommendation
Apply naming standards for jobs, stages and links that will assist your reporting choice. For example process metadata with link row counts is easier to report on if you have a prefix in the name that identifies the content of the link.