Menu
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.
363
"SQLCODE=-204, SQLSTATE=42704" is a DB2 error code indicating 'object does not exist'; in this case, it is indicating that either the USER_TABLES object is not present, or that the <UserID> user does not have access to read this. USER_TABLES is an Oracle compatibility feature.
When Db2 executes an SQL statement, it returns information about the statement execution. This information includes the SQL return code (SQLCODE) and the SQLSTATE, which indicate whether statement execution was successful.
The DB2 error states that DB2 failed to insert the new record into the due to a primary key constraint violation. This means that there is already a record in the TI_APGROUP_0 table that has the same CATENTRY_ID value as the record you are attempting to insert.
SQLCODE and SQLSTATE. SQLCODE and SQLSTATE are variables in which the DBMS returns ANSI/ISO Entry-92-compliant status codes indicating the results of the last SQL statement that was executed. SQLCODE Variable. SQLCODE is an integer variable in which the DBMS returns the status of the last SQL statement executed.
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.
130
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
