File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Changing schema in DB2 Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Changing schema in DB2" Watch "Changing schema in DB2" New topic

Changing schema in DB2

Mikkel Kliim

Joined: Jul 17, 2003
Posts: 1
I'm trying to keep my SQL free of qualifying schema names,
e.g. MYUSER.MYTABLE. I want this because the schemas represent the different environments, like TEST, DEMO and so on. My question is: If I have a DB2 user called TESTUSER, this will be his default schema name as well. How do I change this to the appropriate schema, e.g. TEST?
Is it done on the connection? I use a DataSource approach and CMP generated code, so I don't have access to the connection as such.
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33130

The create synonym statement may help you out with this.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Lu Battist
Ranch Hand

Joined: Feb 17, 2003
Posts: 104
I put the schema name in a properties file - which could different for each environment. Then for any sql statement in my code, I add the schema before the table name as in:
sql = "select a, b, c from " + props.getProperty("dbSchema") + ".TableXYZ where a = ?";
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

I set the library in my Connection URL( don't have my AS/400 stuff at home, but it comes after the DBName ). It suggests which library to search for the table/file first. But if it doesn't find it in the specified library, it will search the rest of the library list entries( may or may not be desirable ). It's somewhere in the AS/400 toolbox documentation for establishing a Connection.
Brian Mulholland
Ranch Hand

Joined: Mar 12, 2009
Posts: 61
We issue a SET SCHEMA SCHEMA_NAME statement to the connection as we acquire it from the pool. It is issued like any other select statement.
I agree. Here's the link:
subject: Changing schema in DB2
It's not a secret anymore!