DataStage objects information into a relational table

[samarvind]

Hi,

We have A requirement to store all the datastage components information into an Audit table having following metadata

PROJECTNAME (Name of the project)
OBJECTNAME ( like Jobname, Sequence, tabledefinition,shared container..etc)
OBJECTTYPE (Job or sequence or shared container or others)
FOLDER (Designer Repository path in which it is stored)
VERSIONNO (Version number of an object ex. 50.0.1)
LASTMODIFIEDDATE
MODIFIEDBY (Who modified it)

Please can anyone help me out extracting this information stored in the datastage repository files?

Thanks
Sam

[ray.wurlod]

This information should already be stored in relational form in the XMETA database. But it's painful to get out of there.

You can get some of the information you require using DataStage API calls,and the remainder from various tables whose names begin with DS_ in the project itself.

Search DSXchange for a list of those tables.

Structure of those tables is not documented so you will have some detective work to do. And note that not every object type has a version.

[samarvind]

Hi Ray,

I don't have any idea about XMETA database. How do we find out that XMETA database is available in first instance? How to access it? What Stage in DataStage to be used to access the data in XMETA database?

Could you point me to the XMETA DATABASE dictionary or any documentation that you may have seen it?

I think it will be very useful to know about datastage repository as this is my third client who is asking for information about various DataStage repository objects for operational reporting like count of jobs, last modified date,who modified it, how long did it run, how many failures..and much more

Not only this any documentation about understanding the DS repository would be grateful if somebody could point me to right direction.

Thanks
Arvind

[asorrell]

As Ray said earlier, there isn't any documentation. The XMETA database is an internal database and as such, IBM doesn't provide any documentation. Your administrator will have the connection information required to access it, but be aware if you modify it in any way you've probably completely messed up your project if not your entire site.

The DS_files in the project also contain much of that information, but some of them are internal Hash files. That means you'll need to understand the uniVerse shell language to access them via uvsh command line, or know how to write Server jobs to pull data from Hash files.

If you are on version 8.7 or above the Operational Console is available and much of that information is stored in the ODS database (which is documented), but unfortunately only for jobs that have been run recently.

You'll need to inform whomever requested this that it isn't a simple task because the information isn't readily available in usable format.

Also - because it is an internal database, it usually changes with every major release and IBM doesn't guarantee any backwards compatibility.

[kduke]

EtlStats has jobs that extract most of this from DS_JOBS and DS_JOBOBJECTS. You DS_AUDIT for the rest. Ray has posted ways to report on those from Universe shell.