There are numerous web pages where people show this message as a symptom (with the value after SQLERRMC and the driver level varying):
DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=M51Dev.CUSTOMER, DRIVER=3.61.65
including several here on StackOverflow.
The error codes mean that an object -- nearly always a table -- is not found, and the value of the SQLERRMC parameter contains the name of the object in question. In every other occurrence of this I can find, the solution was just that they needed to qualify the table name with the schema name. However, as you can see from the line above, that isn't the case here. The schema is M51Dev, and the table is CUSTOMER, and both of them exist.
The environment is WebSphere Application Server (7.0.0.31 in this particular case, but I somehow doubt that's relevant) running on Windows Server 2003 R2, with DB2 9.7.300.3885 on an old XP machine. The datasource is defined in WAS and retrieved via JNDI. the application is written in Java, and our SQL is executed using JDBC via Spring's JdbcTemplates.
The datasource definition in WAS includes a custom property called currentSchema, which is set to M51Dev.
This is the relevant line of SQL (or one of them, as we get the same symptom for every table):
SELECT rundateOverride FROM customer WHERE customerId=1
And here's an extract from the stack trace. I can provide more if it 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.q.a(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 as some background: this is a banking application that supports multiple database platforms. We've got years of experience with it using various versions of Oracle, MS SQL Server and DB2 for System i; but we're relatively new to using DB2 LUW. Still, some of my colleagues have the above configuration working, at least when they have WAS and DB2 on the same machine.
And I can run an SQL client on the machine that has WAS, and connect to the database in question with the same parameters and query it successfully, including using SET SCHEMA M51Dev and not qualifying the table names, which I think most closely emulates the JDBC/JNDI environment.
The answer is case sensitivity.
This answer was in fact provided by mustaccio in a comment, but they don't seem to want to add it as an answer. If you see this, @mustaccio, please add your own answer, so I can accept it.
Anyway, it turns out that WAS appears to effectively put quotes round the values you specify in the "Custom Properties" section. I had entered M51Dev for the currentSchema property, and it was being sent to DB2 as "M51Dev". But DB2 had stored the schema name as M51DEV It sees those as two different values, of course..
Specifiying M51DEV for the property value fixed the problem.