Setting up current schema in DB2 DataSource in JDBC
You have to set the DB2 special registers:
Properties prop = new Properties();
prop.put ("CURRENT SCHEMA", "DB2TEST");
dataSource.setSpecialRegisters(prop);
Comments
-
Acewin almost 2 years
I have written a method to get a DB2 datasource instance for jdbc connection and query execution
public static DataSource getDB2DataSource() { String [] db2Details= getDB2Details(); DB2DataSource dataSource = new DB2DataSource(); //DB2SimpleDataSource dataSource = new DB2SimpleDataSource (); dataSource.setUser("TESTUSER"); dataSource.setPassword("TESTPASSWD"); dataSource.setServerName("localhost"); dataSource.setDatabaseName("TESTDB"); dataSource.setPortNumber(50000 ); // dataSource.setCurrentSchema("DB2TEST"); dataSource.setDriverType(4); return dataSource; }
and trying to execute a SQL query in the plain tyle jdbc
Connection conn = dbSource.getConnection(); String qryString = "SELECT NAME FROM EMPLOYEE where ID = 4 FOR FETCH ONLY WITH UR"; Statement stmnt = conn.createStatement(); ResultSet rSet = stmnt.executeQuery(qryString); while (rSet.next() ) { System.out.println ("ID : " + rSet.getString("NAME")); }
on executing this I am getting error
: DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: TESTUSER.EMPLOYEE
Now I had already set schema in the datasource through below statement
dataSource.setCurrentSchema("DB2TEST");
But it seems like it is not setting schema this way. I would like to understand what I am missing.
---- Update ----
It seems if I use DB2SimpleDataSource instead of DB2DataSOurce. setCurrentSchema works just fine. There seems to be a problem in driver version 8.1
BTW I already know I can set the schema by executing statement SET schema as below
stmnt.executeUpdate("SET SCHEMA DB2TEST");
setting up schema this way does not seem to be very suitable. Also setting schema over Connection object fetched from datasource is not an option as I need to pass instance of DataSource to my method.