Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Changing schema in DB2

 
Mikkel Kliim
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34095
337
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The create synonym statement may help you out with this.
 
Lu Battist
Ranch Hand
Posts: 104
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 65
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic