Setting up current schema in DB2 DataSource in JDBC

Question

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.

Try issuing setSchema() on a connection once you obtain it, not on the datasource. — mustaccio
I need to do it on dataSource. I am trying to test my code and I need to pass datasource object. Also there is not setSchema for connection. — Acewin
Hmm, this is good. I resolved the setSchema part. It seems the JRE being used in the project was WebSphere JRE which did not had setSchema for connection. — Acewin
Its quiet strange that dataSource is not taking schema when set through setCUrrentSchema — Acewin

Stavr00 Stavr00 · Accepted Answer · 2016-03-14T20:25:27

You have to set the DB2 special registers:

Properties prop = new Properties();
prop.put ("CURRENT SCHEMA", "DB2TEST");
dataSource.setSpecialRegisters(prop);

Source: https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_rjv00016.html

Setting up current schema in DB2 DataSource in JDBC

1 Answers