DB Connection string with schema name

[Alan Peltz]

I'm looking for a way to specify a schema name in the database connection string or other connection properties. I am using WSAD 5.1.2 and a WAS5.1 server. If my application logs in as schema owner, it sees all the tables, views, functions, etc. just fine, but if I log in as an application user I can't.

I know that this can be fixed with private synonyms or by specifying the schema before the table reference (e.g. select * from fnsuser.fns_comm_customer). I think there should be a better/easier way. I'm pretty sure I've done this before with an oracle:thin driver. It looked something like this: jdbc racle:thin:@10.8.30.54:1526:FNSUSER

Is this possible with an oci driver? What am I missing?

[Reid M. Pinchback]

As a generic solution you could put schema prefixes on table names, but I suspect you would have already tried that anyways. Not sure if the issue I'll describe is the same for websphere, but just in case it is, the folliwng info might help. More relevant if you are using CMP, not necessarily relevant for anything else.

Different databases deal with JDBC catalogs in different ways. I'd argue the spec was a bit weak, but c'est la vie... things are as they are now.

Different servers sometimes deal with catalog information in different ways in different parts of the container; JBoss is really bad that way. I'd argue that is bug, but ditto for things being the way they are.

When using multi-catalog (schema in Oracle, database or object owner in SQLServer) products with CMP, there are two patterns of usage that can cause CMP to break, one obvious, the other less so. The obvious one is that the schema you are logging in as must have grants that allow it to see the objects in the other schema; no big surprise there. Just login as the application user via SQL*Plus, and if you can't see the objects, you know the grants are wrong.

The less obvious second situation is that if you have MULTIPLE schemas that have objects of the same type and name, then you can have situations where CMP looks in the correct schema during one state (like deployment and bean pool creation), but incorrectly in another state (like actual DML execution). If you hit that problem and schema prefixing isn't an option or doesn't work for you, the only solution I know is to alter grants to eliminate the duplicates. If all the object/name mappings are unique, the problem doesn't tend to come up.

I find people hit this problem routinely with JBoss and Oracle when they have multiple development or QA schemas in play, and slammed DBA-level grants onto everything. I've seen similar things with SQLServer, but not as often. You'll have to figure out if something comparable is happening for you with WebSphere.
[ March 09, 2006: Message edited by: Reid M. Pinchback ]

[stu derby]

I'm pretty sure I've done this before with an oracle:thin driver. It looked something like this: jdbc racle:thin:@10.8.30.54:1526:FNSUSER

Is this possible with an oci driver? What am I missing?

There's no way I know of to do this at connection time. The only feature differences I'm aware of between the OCI and thin drivers relate to networking transport options (e.g. tcp/ip vs. shared memory) and failover options; other than transport choice in the URL, the Java-level connection features are the same, I think (but I don't do OCI more than very rarely).

However, after connecting, a session may change its default schema with the "ALTER SESSION" statement. If you use this, you have to be careful of (at least) 2 things:
1) the code is always in the right schema for the SQL it's executing (I know, pretty obvious)
2) that you don't have the same letter-for-letter identical SQL executing by the same user in different schemas, with high frequency...

2) is a non-obvious performance issue. Oracle caches parsed SQL and the parsed SQL is dependent on the execution environment, which includes the user and the schema(s) against which the SQL will run. When Oracle has a cache hit, it does a "soft parse" and among other things will check that the SQL will still execute against the same schema(s); if not, it ejects the statement from the cache and does a "hard parse". A high rate of "hard parse" will wreck your performance.

From what little you've said, it sounds like you can deal with both these issues.

For other readers, the "ALTER SESSION" feature should not be used to split identically structured data into different schemas and have one DB user access it all; the performance issue above will absolutely kill you, and it gets worse not better as you add CPUs or RAC nodes.

[Alan Peltz]

Thanks for the replys. I'll give it a try.