DS Repository

JDionne · Post by **JDionne** » Tue Feb 15, 2005 9:34 am

I need to know how to get access to the DS repository where job completion information is stored. I need to extract all of that info and put it into DB2. I dont want to gather this information at the end of every job, because we are creating a monitoring system and we need to automate gathering for many many jobs. Any Ideas?

kcbland · Post by **kcbland** » Tue Feb 15, 2005 9:45 am

Use custom designed job control to not only run jobs, but gather all process metadata during/after the run and update your audit repository realtime.

ArndW · Post by **ArndW** » Tue Feb 15, 2005 10:52 am

JDionne,

the repository, while accessible through the DS-Shell and readable from within DataStage itself, is effectively "undocumented" by Ascential. This means that if you write some snazzy reporting based on the current structure of the repository and Ascential changes the format around [as they have done with just about each and every release] you end up being SOL.

The Routine "DSGet[JobInfo" supplies most information you would want from a job, and the other DSGet... routines supply the rest. I would design my reporting around these officially published routines so that you can guarantee future compatibility.

JDionne · Post by **JDionne** » Tue Feb 15, 2005 10:53 am

ArndW wrote:JDionne,

the repository, while accessible through the DS-Shell and readable from within DataStage itself, is effectively "undocumented" by Ascential. This means that if you write some snazzy reporting based on the current structure of the repository and Ascential changes the format around [as they have done with just about each and every release] you end up being SOL.

The Routine "DSGet[JobInfo" supplies most information you would want from a job, and the other DSGet... routines supply the rest. I would design my reporting around these officially published routines so that you can guarantee future compatibility.

Is there any way to treat the repository as a source in a DS job and pump the data right into DB2?

ArndW · Post by **ArndW** » Tue Feb 15, 2005 11:21 am

JDionne,

yes, but the same maintenance problems exist. But the repository is not a small numbr of files/tables, it is somewhat more complex than that .... think of the DS repository as being one of those large wall posters you see where Oracle or other DB manufacturers list their data model.

It won't take you more than a couple of hours to wrap some code around calls to DSGetProject, DSGetJobInfo, DSGetLinkInfo and DSGetStageInfo in order to get all the logging information you require.

I also think that the ADN as well as DSXchange members have published their freeware programs that do this. Come to think of it, I saw that etlstat.zip has been updated recently, see the thread at viewtopic.php?t=91135&highlight=etlstats+zip

tetaylor · Post by **tetaylor** » Tue Feb 15, 2005 12:22 pm

Is there any way to get this information without modifying every job? I've got a lot of jobs already created and it is already making me sick to think that I'd have to make big changes to each and every one. I was thinking about a job or stored procedure that could be called in each job. Possible?

ArndW · Post by **ArndW** » Tue Feb 15, 2005 12:28 pm

Teta,

I am sitting here in car illegally accessing WiFi at a hotel, so I can't really send you anything. But no, you wouldn't have to modify any jobs, just write a DS routine that collects all the data.

I'll send some info on the morrow.

kcbland · Post by **kcbland** » Tue Feb 15, 2005 12:32 pm

tetaylor wrote:Is there any way to get this information without modifying every job? I've got a lot of jobs already created and it is already making me sick to think that I'd have to make big changes to each and every one. I was thinking about a job or stored procedure that could be called in each job. Possible?

Sure, research on this forum. We've been discussing this for YEARS. There is a significant amount of custom code, hints, questions answered, etc just waiting here for you. :D

willpeng · Post by **willpeng** » Tue Feb 15, 2005 1:56 pm

Yea, modification dsx is another way to modify all jobs at once if you know the specific item that you wish to change.

kduke · Post by **kduke** » Tue Feb 15, 2005 5:05 pm

Is there any way to treat the repository as a source in a DS job and pump the data right into DB2?

Yes. There are jobs in EtlStats which do this. I had to use UV stages instead of hash file stages.

To get row counts on every job then all you need is to do is run the job DSJobReportDbDriver and use "All" for the category name. You do not need to modify any jobs. I use it a little differently in that I get row counts at the end of every sequence. The jobs update Oracle so you need to change all of them to update DB2 instead. The create table scripts are also for Oracle but you can easily change them to DB2 or Sql Server or whatever.

ray.wurlod · Post by **ray.wurlod** » Tue Feb 15, 2005 7:49 pm

This is one of the things that MetaStage does.

MetaStage is destined to BE the repository in a future release so you may as well get started getting used to it.

I didn't tell you that.

kcbland · Post by **kcbland** » Tue Feb 15, 2005 8:22 pm

ray.wurlod wrote:MetaStage is destined to BE the repository in a future release

(Pick your deity) help us.