Query on DataStage Repository 7.5

[vinay_renu2001]

Hello,

Note: The following request is on DataStage Versions 7.5.

I need to generate a report to list out the parent categories under "Jobs" of each project on a Server.

The report should have the following columns:

Host Name
Project Name
Parent Category Name (Under Jobs)
Created By User
Created Date
Last Modified By User
Last Modified Date

This report has to be generated for Audit purposes.

I did a simple query on DS Administrator Command line by selecting one of the project:

SELECT CATEGORY FROM DS_JOBS;

The above query is listing out the parent and child categories. But, i only need parent categories along with the above listed columns.

Requirement 1:
Is there a way to write a query to get the information on all the columns listed above Or at least just with Parent Category names at each project level and export it to excel?

Requirement 2:
Is there a script or can we write one to automate listing out parent categories under "Jobs" from all the project on a Server and export the results to Excel?

I had generated a similar report on 8.x versions using Metadata Workbench.

Any help/suggestions are appreciated.

[ray.wurlod]

Code: Select all

EVAL "FIELD(CATEGORY,'\',COUNT(CATEGORY,'\'),1)"

[vinay_renu2001]

Hi Ray,

Thanks for your prompt inputs. I appreciate it!

I really liked the code you provided. I made little tweaks to the code you provided and achieved the 1st step of the intended result i.e. Listing out the Parent Categories in each Project.

Here is the tweaked version of code:

Code: Select all

SELECT DISTINCT EVAL "FIELD(CATEGORY,'\',1,1)" FROM DS_JOBS;

Now Can i list the "Created On Date", "Last Modified Date", "Created By User" and "Modified By User" columns from "DS_JOBS"?

And, Can i write a Shell to query on the DataStage command line to list out the "Parent Categories" under "Jobs" in all the Projects on a Server?

Any inputs are greatly appreciated!

[ray.wurlod]

The modification history is not stored in DS_JOBS, it is stored in DS_JOBOBJECTS. You can join these, using the criterion DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO.

Selecting for distinct category will, however, invalidate this join. So you need to think a bit more clearly about your requirement, probably to generate PARENT_CATEGORY and GROUP BY that (virtual) column.

[vinay_renu2001]

Ray,

Is new category is stored in DS_AUDIT?

[ray.wurlod]

I'm not sure. Create one and check. I'd imagine not.