Atomikos Forum

We have many web services (100+) accessing the same database, but each service has its own schema.  Would it be possible to have these services use the same connection pool, but refer to a different database schema? Otherwise we would have hundreds of connections. We are using Hibernate, JPA and Atomikos JTA.

Hi,

Can't you solve this by defining DBMS views and just configuring the Atomikos pools for the schema in which the views are defined?

Otherwise, schemas are supported the way they are in plain JDBC connectivity. I am not a specialist at what that means, though.

HTH
Guy

If you have sufficient privileges to access another schema's tables from your schema, this is possible.

The only way (I know) is by prefix all table names by the schema and a dot in all your queries which will certainly require some changes in your application.

Eg: when logged as USER_A, you can access USER_B's table MY_TABLE simply like this: SELECT * FROM USER_B.MY_TABLE

You will need to align with your DBAs as generally they're not keen to allow a schema to have access to another one.

Ludovic