aspose file tools*
The moose likes JDBC and the fly likes Changing schema in DB2 Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Changing schema in DB2" Watch "Changing schema in DB2" New topic
Author

Changing schema in DB2

Mikkel Kliim
Greenhorn

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.
Cheers,
Mikkel
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30764
    
156

The create synonym statement may help you out with this.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, 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.
Jamie
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: http://aspose.com/file-tools
 
subject: Changing schema in DB2