Operations DB

jonesv · Post by **jonesv** » Tue Jul 21, 2015 3:35 pm

Hi,

We are running DataStage 8.7 on AIX. Our Operations Database is on Oracle. We have one fairly large project (5000+ jobs) running mission critical parallel jobs 24/7. One of our departments would like to create a custom report/query against the ODB for monitoring purposes on a few of those jobs in that large project. But Operations is reluctant to grant read-only access to the ODB saying that it could affect the performance/sanctity of our production jobs. I say that the cost would be negligible. Thoughts?

Thanks

PaulVL · Post by **PaulVL** » Tue Jul 21, 2015 4:00 pm

I believe that's the whole purpose of the ODB in the first place.

A Database containing the runtime statistics so that you may query it (when needed) to determine performance over time of your jobs.

A Read Only view would be best.

I do not believe it would be detrimental to your prod ETL flow.

And again... that's why you set that thing up in the first place, to report stats on your flow.

rkashyap · Post by **rkashyap** » Tue Jul 21, 2015 4:33 pm

If Operations Console is not configured/tuned properly then its presence itself can lead to issues.

Is DSODB located on the same Oracle instance as XMETA? If not, then a query against it cannot cause performance issue. If yes, then adhoc queries in certain circumstances, can cause issues, but (tuned) reports are ok.

There are quite a few developerwork articles on leveraging DSODB data for reporting/performance monitoring and you can share these examples with Operations:Designing an alerts mechanism on DSODB,Get started with the DSODB etc.

Also note that vanilla DSODB lacks some of the indexes to support joins etc and some of the queries may lead to table scans. This can be rectified by applying "JR46761 index patch".

jonesv · Post by **jonesv** » Wed Jul 22, 2015 10:15 am

Thanks for your responses. Yes, our DSODB and XMETA databases are on the same Oracle instance. But separate schemas and tablespaces.

rkashyap · Post by **rkashyap** » Fri Jul 24, 2015 8:27 am

jonesv wrote:DSODB and XMETA databases are on the same Oracle instance. But separate schemas and tablespaces.

Canned(tuned) reports should not cause any issues. If you want to open DSODB for ad hoc queries then installing Oracle Database Resource Manager would be a good idea.

kduke · Post by **kduke** » Fri Jul 24, 2015 2:27 pm

If ODB is your bottleneck then fire your Oracle DBA.