There are many web pages where people show this message as a symptom (with a value after SQLERRMC and the driver level change):
DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=M51Dev.CUSTOMER, DRIVER=3.61.65
including a few here on https://stackoverflow.com/a/166185/169/ .
Error codes mean that the object - almost always a table - was not found, and the value of the SQLERRMC parameter contains the name of the object in question. In any other case, I can find this, the solution was that they needed to qualify the table name with the schema name. However, as can be seen from the line above, this is not so. The schema is M51Dev , and the table is CUSTOMER , and both exist.
The environment is WebSphere Application Server (7.0.0.31 in this particular case, but for some reason I doubt it), running on Windows Server 2003 R2, with DB2 9.7.300.3885 on the old XP machine. The data source is defined in WAS and retrieved through JNDI. the application is written in Java, and our SQL is executed using JDBC through Spring JdbcTemplate s.
The data source definition in WAS includes a custom currentSchema property that is set to M51Dev .
This is the corresponding SQL string (or one of them, since we get the same symptom for each table):
SELECT rundateOverride FROM customer WHERE customerId=1
And here is an excerpt from the stack trace. I can provide more if that helps.
bad SQL grammar [SELECT rundateOverride FROM customer WHERE customerId=1]; nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=M51Dev.CUSTOMER, DRIVER=3.61.65 at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:406) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:455) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:463) at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:471) at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:476) at com.misys.meridian.runtime.userPromptable.SchedulerService.refreshMarketCentreSystemDates(SchedulerService.java:1539) at com.misys.meridian.runtime.userPromptable.SchedulerService.performService(SchedulerService.java:270) at com.misys.meridian.runtime.userPromptable.SchedulerService.prompt(SchedulerService.java:175) at com.misys.meridian.runtime.userPromptable.GenericDelegate.process(GenericDelegate.java:95) at org.apache.camel.util.AsyncProcessorConverterHelper$ProcessorToAsyncProcessorBridge.process(AsyncProcessorConverterHelper.java:61) at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:73) at org.apache.camel.processor.DelegateAsyncProcessor.processNext(DelegateAsyncProcessor.java:99) at org.apache.camel.processor.DelegateAsyncProcessor.process(DelegateAsyncProcessor.java:90) at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:73) at org.apache.camel.processor.DelegateAsyncProcessor.processNext(DelegateAsyncProcessor.java:99) at org.apache.camel.processor.DelegateAsyncProcessor.process(DelegateAsyncProcessor.java:90) at org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:71) at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:73) at org.apache.camel.processor.DelegateAsyncProcessor.processNext(DelegateAsyncProcessor.java:99) at org.apache.camel.processor.DelegateAsyncProcessor.process(DelegateAsyncProcessor.java:90) at org.apache.camel.processor.interceptor.TraceInterceptor.process(TraceInterceptor.java:91) at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:73) at org.apache.camel.processor.RedeliveryErrorHandler.processErrorHandler(RedeliveryErrorHandler.java:333) at org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:223) at org.apache.camel.processor.RouteContextProcessor.processNext(RouteContextProcessor.java:45) at org.apache.camel.processor.DelegateAsyncProcessor.process(DelegateAsyncProcessor.java:90) at org.apache.camel.processor.interceptor.DefaultChannel.process(DefaultChannel.java:304) at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:73) at org.apache.camel.processor.Pipeline.process(Pipeline.java:117) at org.apache.camel.processor.Pipeline.process(Pipeline.java:80) at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:73) at org.apache.camel.processor.Pipeline.process(Pipeline.java:117) at org.apache.camel.processor.Pipeline.access$100(Pipeline.java:43) at org.apache.camel.processor.Pipeline$1.done(Pipeline.java:135) at org.apache.camel.processor.ThreadsProcessor$ProcessCall.run(ThreadsProcessor.java:56) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:450) at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:314) at java.util.concurrent.FutureTask.run(FutureTask.java:149) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:906) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:929) at java.lang.Thread.run(Thread.java:761) Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=M51Dev.CUSTOMER, DRIVER=3.61.65 at com.ibm.db2.jcc.am.ed.a(ed.java:676) at com.ibm.db2.jcc.am.ed.a(ed.java:60) at com.ibm.db2.jcc.am.ed.a(ed.java:127) at com.ibm.db2.jcc.am.gn.c(gn.java:2554) at com.ibm.db2.jcc.am.gn.d(gn.java:2542) at com.ibm.db2.jcc.am.gn.a(gn.java:2034) at com.ibm.db2.jcc.t4.cb.g(cb.java:140) at com.ibm.db2.jcc.t4.cb.a(cb.java:40) at com.ibm.db2.jcc.t4.qa(q.java:32) at com.ibm.db2.jcc.t4.rb.i(rb.java:135) at com.ibm.db2.jcc.am.gn.gb(gn.java:2005) at com.ibm.db2.jcc.am.gn.a(gn.java:3023) at com.ibm.db2.jcc.am.gn.a(gn.java:667) at com.ibm.db2.jcc.am.gn.executeQuery(gn.java:651) at com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.executeQuery(WSJdbcStatement.java:999) at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:440) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:395) ... 40 more
Just like some background: it is a banking application that supports several database platforms. We have many years of experience working with it using various versions of Oracle, MS SQL Server and DB2 for System i; but we are relatively new to DB2 LUW. However, some of my colleagues have the above configuration, at least when they have WAS and DB2 on the same machine.
And I can run the SQL client on a machine with WAS and connect to the appropriate database with the same parameters and query it successfully, including using SET SCHEMA M51Dev and not qualify the table names that I think the JDBC environment most closely emulates / Jndi.