Finding the Category for each job.

sidharthh · Post by **sidharthh** » Wed May 14, 2008 6:39 am

Hi,
I have a task in my hand where i need to pull out all the Job name from the repository sorted on the recent Job run and i need to associate the job name against the category in which they are available. I know that using DSGETJOBINFO and DSPROJECT i can get the entire job details and its last run information. But i am not sure how to find the category name in which the job is present.

I am looking for something of this sort,

Job1,Its Last Runtime,CategoryName1 in which job Job1 is present
Job2,Its Last Runtime,CategoryName2 in which job Job1 is present
Job3,Its Last Runtime,CategoryName3 in which job Job1 is present

My repository is huge (close to 5000 jobs with many category) and it is a herculean task to manually look into each category to create this report.

Could someone help me in finding a way out.

Thank You,
Regards,
Sidhartha

BugFree · Post by **BugFree** » Wed May 14, 2008 7:12 am

Code: Select all

"SELECT CATEGORY FROM DS_JOBS WHERE NAME = 'JobName'

Will get the category name with the hierarchy. Guess you will have to run it using DSExcute().

chulett · Post by **chulett** » Wed May 14, 2008 7:22 am

Welcome aboard. :D

Job name and category are elements of the DS_JOBS repository 'table'. So something simple like:

Code: Select all

SELECT NAME, CATEGORY FROM DS_JOBS;

will get you a complete list of job names and categories. This can be run from either the Administrator client or from the TCL command line. There are various ways to get this in a file, search the forums for COMO should turn them up. Then you can get that information into a lookup structure if you are building a job to produce some kind of report.

sidharthh · Post by **sidharthh** » Wed May 14, 2008 8:03 am

Hi,
Thanks for your valuable input.
I did fire the command

Code: Select all

SELECT CATEGORY FROM DS_JOBS WHERE NAME = 'One of my Job name'

. There was an ouputput window with "SQL+" alone and not any record record. Am i missing out any point ?

Cheers,
Sidhartha

ArndW · Post by **ArndW** » Wed May 14, 2008 8:05 am

add a ";" to the command.

sidharthh · Post by **sidharthh** » Wed May 14, 2008 8:07 am

Sorry, i had missed out semi-colon (;) to close the query. Its working now.

Thanks a ton,

Cheers,
Sidhartha

sidharthh · Post by **sidharthh** » Wed May 14, 2008 8:20 am

Now i have two columns (job name against the category) of my output. Now i need to fetch the last run time information for each job. I know it is there somewhere in the repository.

Is there a way i can know the DS Repository Objects, like ALL_OBJECTS/ALL_TABLES Master Meta-data object in oracle. This would help me to analyze the repository further,

Thanks in advance,

Cheers,
Sidhartha

chulett · Post by **chulett** » Wed May 14, 2008 9:10 am

That information really isn't public knowledge. You are meant to retrieve what you are looking for from the job logs, either via the various 'DSGet' functions provided for use in a job or by using the equivalent options of 'dsjob' scripted from the command line.

For example, I would think a search for 'DSGetLog' would turn up quite a number of conversations on the subject.

sidharthh · Post by **sidharthh** » Wed May 14, 2008 9:37 am

Thanks guys for all your helping hand.

Cheers,
Sid