title: Why do I get SQLCODE=-204, SQLSTATE=42704 with DB2 LUW and WebSphere App Server?
body: <p>There are numerous web pages where people show this message as a symptom (with the value after <code>SQLERRMC</code> and the driver level varying):</p>
  
  <pre class="lang-none prettyprint-override"><code>DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=M51Dev.CUSTOMER, DRIVER=3.61.65
  </code></pre>
  
  <p>including several <a href="https://stackoverflow.com/questions/1588687/db2-connection-problem-with-java">here</a> on <a href="https://stackoverflow.com/questions/7183433/ibm-db2-net-driver-sql-error-not-finding-table-name">StackOverflow</a>.</p>
  
  <p>The error codes mean that an object -- nearly always a table -- is not found, and the value of the <code>SQLERRMC</code> 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 <code>M51Dev</code>, and the table is <code>CUSTOMER</code>, and both of them exist.</p>
  
  <p>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 <code>JdbcTemplate</code>s.</p>
  
  <p>The datasource definition in WAS includes a custom property called <code>currentSchema</code>, which is set to <code>M51Dev</code>.</p>
  
  <p>This is the relevant line of SQL (or one of them, as we get the same symptom for every table):</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT rundateOverride  FROM customer WHERE customerId=1
  </code></pre>
  
  <p>And here's an extract from the stack trace. I can provide more if it helps.</p>
  
  <pre class="lang-none prettyprint-override"><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>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 <code>SET SCHEMA M51Dev</code> and not qualifying the table names, which I think most closely emulates the JDBC/JNDI environment.</p>