JDBC Pools connection to different database components with one user

, ,
1

Hi,

I have created TN ,IS and MWS database components using DBConfigurator.But DBAs have provided me only one user to connect to all 3 Schemas.

Please tell me while setting up JDBC pools ,what all properties I need to setup to limit the schema usage.

I have already tried this one :
jdbc:wm:oracle://elgwmdev:1527;serviceName=webm82d;TableFilter=‘’.‘WMB2B_IS’
User : WEBM
Pwd: *******
WEBM is the user DBAs has provided with all permissions provided.
but it’s not able to view tables under WMB2B_IS schema.

2

Just to clarify , these are the 3 schema’s - WMB2B_IS ,WMB2B_TN and WM_MWS.And WEBM is the user to access all these 3 schemas.

3

Did you try this with out giving other params and see if the connection works for the WEBM account?

jdbc:wm: oracle://elgwmdev:1527;serviceName=webm82d

4

Yes RMG , connection works without params.But that connection just goes upto default schema of user WEBM .And this user has no objects at all.

Hence I’m receiving all errors like table or view doesn’t exist for WMERROR , WMSESSION and scehduler page as well.

5

Yes RMG , connection works without params.But that connection just goes upto default schema of user WEBM .And this user has no objects at all.

Hence I’m receiving all errors like table or view doesn’t exist for WMERROR , WMSESSION and scehduler page as well.

6

When you ran the dbconfigurator did you select Component as ALL for that WEBM user?..so this take care of all tables created under default schema.

HTH
RMG

7

That’s how we followed steps :

With DB Configurator , I created DB components -

User DB Component
WMB2B_IS IS Product
WMB2B_TN TN Product
WM_MWS MWS Product

Then DBA created a one user called “WEBM” and grant select,insert,delete,update roles to this user on Schemas - WB2B_IS ,WMB2B_TN,WM_MWS.
(Note : DBA created WEBM just as user ,it has no objects underlying it but it has access to other schemas)

So in JDBC Pool connections ,we are using a schema user (WEBM) instead of schema owner (WMB2B_IS,WMB2B_TN,WM_MWS).

Now while passing connection parameters ,
jdbc:wm:oracle://elgwmdev:1527;serviceName=webm82d
User : WEBM
pwd : *******

I need a param to pass the schema owner name (WMB2B_IS , WMB2B_TN or WM_MWS).And as per guide for DataDirect Connect JDBC 4.2 driver ,I don’t see any param provided to pass the schema name.

It seems like it’s a driver limitation and we have to use schema owner as User while estabilishing connection.

Any thoughts ?

8

What is your IS version?

But why do you have to create differrent schemas for each PRODUCT?

9

8.2.2

10

But why do you have to create different schemas for each PRODUCT instead of selecting as ALL option for WEBM user account?

So that when you create pool alias def just point/reference the Functional alias for TN/ISCore/Internal/Process Engine/ProcessAudit the same associated pool alias.

Hope you got my point and it should work and IS can access to all the main tables.

HTH,
RMG

11

We want to keep TN schema separate from IS schema to prevent any database related issues .
And moreover , this is not the reason for my issue.

DBAs can’t share schema owner password with application ,that’s why they created a user (WEBM) to access those schemas and asked us to use this user for JDBC Pool connection.

12

sounds a valid reason…

Please check with SAG support for URL format that works for limiting schema in your case as no proper documentation value given.

jdbc:wm:oracle://:<1521|port>;serviceName=;[=…]

13

Yes , I raised a request today .Will share once I receive any update.
So far I did google, it seems it’s JDBC direct driver limitation that it doesn’t have any = thing to define Schema name.

For JDBC adapter connections though ,we have this option property called TableFilter=.WMB2B_IS.I used it and it works perfect.

14

OK sounds perfect and keep posted:

15

Raised a case with Software AG and here is the solution :

jdbc:wm:oracle://:;serviceName=;InitializationString=(ALTER SESSION SET CURRENT_SCHEMA=)

Thanks everybody for the help .

16

Thanks for sharing back and the info:

17

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.