Weblogic: Call DB2 stored procedure without schema name (property currentSchema)

db2, java, jndi, weblogic

Solution

Stored procedure (and function) resolution is not controlled by the CURRENT SCHEMA special register. It is controlled by the CURRENT PATH special register.

So, you can either:

Execute the SQL statement `SET CURRENT PATH = MYSCHEMA` or

Use the `currentFunctionPath` JDBC property.

Problem

I have a Java application that runs on Weblogic. The application needs to access a stored procedure in a DB2 data base, therefore a JDBC data source is configured and accessed by its JNDI name. Data source: ``` ClassDriver: com.ibm.db2.jcc.DB2Driver Properties: user=MYUSER DatabaseName=MYDB ``` The following example works as expected. ``` Context env = null; DataSource pool = null; Hashtable ht = new Hashtable(); ht.put(Context.INITIAL_CONTEXT_FACTORY, "weblogic.jndi.WLInitialContextFactory"); ht.put(Context.PROVIDER_URL,"t3://myserver:7777"); env = new InitialContext(ht); pool = (DataSource) env.lookup("jdbc/myjndiname"); conn = pool.getConnection(); // call stored procedure with schema name String procName = "MYSCHEMA.MYSTOREDPROCEDURE"; String sql = "CALL " + procName + "(?)"; callStmt = conn.prepareCall(sql); callStmt.setString(1, "1"); callStmt.execute(); ``` But now I need to call the stored procedure without the schema name and use a JDBC driver property instead. Data source: ``` ClassDriver: com.ibm.db2.jcc.DB2Driver Properties: user=MYUSER DatabaseName=MYDB db2.jcc.override.currentSchema=MYSCHEMA com.ibm.db2.jcc.DB2BaseDataSource.currentSchema=MYSCHEMA ``` The following SQL call results in an error ``` // call stored procedure without schema name String procName = "MYSTOREDPROCEDURE"; String sql = "CALL " + procName + "(?)"; callStmt = conn.prepareCall(sql); ``` SQL error: ``` SQLCODE = -440, ERROR: NO PROCEDURE BY THE NAME MYSTOREDPROCEDURE HAVING COMPATIBLE ARGUMENTS WAS FOUND IN THE CURRENT PATH ``` I assume that the "currentSchema" properties are wrong. Edit: It looks like I was wrong: the property `currentSchema` is not the problem! The SQL statement `"select current_schema fromsysibm.sysdummy1"` returns the correct schema (`MYSCHEMA`). The question is now, why `"CALL MYSCHEMA.MYSTOREDPROCEDURE(?)"` works and `"CALL MYSTOREDPROCEDURE(?)"` results in an error... Any suggestions? Thanks!

Original source