Java 为什么使用 DB2 LUW 和 WebSphere App Server 时会出现 SQLCODE=-204、SQLSTATE=42704？

Why do I get SQLCODE=-204, SQLSTATE=42704 with DB2 LUW and WebSphere App Server?

提问by Martin McCallion

There are numerous web pages where people show this message as a symptom (with the value after SQLERRMCand the driver level varying):

有许多网页，人们将此消息显示为症状（后面的值SQLERRMC和驱动程序级别各不相同）：

DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=M51Dev.CUSTOMER, DRIVER=3.61.65

including several hereon StackOverflow.

包括一些在这里上StackOverflow的。

The error codes mean that an object -- nearly always a table -- is not found, and the value of the SQLERRMCparameter 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.

错误代码意味着没有找到对象——几乎总是一个表——并且SQLERRMC参数的值包含有问题的对象的名称。在我能找到的所有其他事件中，解决方案只是他们需要用模式名称限定表名。但是，正如您从上面的行中看到的，这里的情况并非如此。架构是M51Dev，表是CUSTOMER，并且它们都存在。

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.

环境是运行在 Windows Server 2003 R2 上的 WebSphere Application Server（在这个特殊情况下为 7.0.0.31，但我怀疑这是否相关），在旧的 XP 机器上运行 DB2 9.7.300.3885。数据源在 WAS 中定义并通过 JNDI 检索。该应用程序是用 Java 编写的，我们的 SQL 是通过 Spring 的JdbcTemplates使用 JDBC 执行的。

The datasource definition in WAS includes a custom property called currentSchema, which is set to M51Dev.

WAS 中的数据源定义包括一个名为 的自定义属性currentSchema，该属性设置为M51Dev。

This is the relevant line of SQL (or one of them, as we get the same symptom for every table):

这是 SQL 的相关行（或其中之一，因为我们对每个表都有相同的症状）：

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.access0(Pipeline.java:43)
    at org.apache.camel.processor.Pipeline.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.JdbcTemplateQueryStatementCallback.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.

就像一些背景：这是一个支持多个数据库平台的银行应用程序。我们在使用各种版本的 Oracle、MS SQL Server 和 DB2 for System i 方面拥有多年的经验；但我们对使用 DB2 LUW 还比较陌生。尽管如此，我的一些同事仍然可以使用上述配置，至少当他们在同一台机器上安装 WAS 和 DB2 时。

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 M51Devand not qualifying the table names, which I think most closely emulates the JDBC/JNDI environment.

我可以在有 WAS 的机器上运行 SQL 客户端，并使用相同的参数连接到有问题的数据库并成功查询，包括使用SET SCHEMA M51Dev和不限定表名，我认为这最接近于 JDBC/JNDI 环境.

采纳答案by Martin McCallion

The answer is case sensitivity.

答案是区分大小写。

This answer was in fact provided by mustaccioin 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.

这个答案实际上是由mustaccio在评论中提供的，但他们似乎不想将其添加为答案。如果您看到这一点，@mustaccio，请添加您自己的答案，以便我接受。

Anyway, it turns out that WAS appears to effectively put quotes round the values you specify in the "Custom Properties" section. I had entered M51Devfor the currentSchemaproperty, and it was being sent to DB2 as "M51Dev". But DB2 had stored the schema name as M51DEVIt sees those as two different values, of course..

无论如何，事实证明 WAS 似乎有效地将引号括在您在“自定义属性”部分中指定的值周围。我已经输入M51Dev了该currentSchema属性，它作为"M51Dev". 但是 DB2 已经存储了模式名称，因为M51DEV它当然将它们视为两个不同的值。

Specifiying M51DEVfor the property value fixed the problem.

指定M51DEV属性值解决了这个问题。