ORACLE CONNECTOR AND HIGH USE OF METADATA ORACLE ALL_SYNONYM

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
sangi1981
Participant
Posts: 99
Joined: Fri Jun 13, 2008 8:10 am

ORACLE CONNECTOR AND HIGH USE OF METADATA ORACLE ALL_SYNONYM

Post by sangi1981 »

Hi all,
I have a question.
Our jobs read/write data through an Oracle Connector from DB.
Due to security reasons, the access to table happens using synonyms and views.

Our DBA noticed many access to metadata table ALL_SYNONYMS, ALL_VIEWS, ALL_TABLES, done by user datastage.


About your opinion, is there some reason?
Is there some behavior related to Oracle Connectore DS?

Thanks in advance,
Sandro
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

From what I have heard, it is quite normal and expected for DataStage to read from various system tables no matter what database is being accessed.
Choose a job you love, and you will never have to work a day in your life. - Confucius
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Gathering metadata, as you noted.
-craig

"You can never have too many knives" -- Logan Nine Fingers
sangi1981
Participant
Posts: 99
Joined: Fri Jun 13, 2008 8:10 am

Post by sangi1981 »

Our dba complain of excessive use.
So I ty to understand the behavior and if there is some issue to avoid or mitigate it.
Thank you
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

I'm not sure what "excessive use" might mean... it's not like the tables will wear out or something. Or are you being billed for your access, hence the concern?

As noted it is normal, customary and I assume essential behavior and I'm not aware of any way to mitigate it. However, if you really need to pursue this, open up a support case and see if there is anything that can be done.
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Every time you run a job, or use View Data, DataStage checks for metadata mismatch. It has to go to system tables in order to be able to do this.

Any database worth its salt should be able to handle large numbers of small select queries that don't require exclusive locks. Your DBA needs to be made aware of DataStage's behaviour and the reason for it. And of the fact that it cannot be disabled.

It's a DBA's role to provision a system that can do what its users require.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
sangi1981
Participant
Posts: 99
Joined: Fri Jun 13, 2008 8:10 am

Post by sangi1981 »

Thank you for your support.

Our DBA opened an SR to Oracle Support to verify presence of some problem on metadata management on 12C.

Cheers,
Sandro
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Ah... 12c. Probably worth mentioning.
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Oracle 12c Release 1 or Oracle 12c Release 2 ?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply