Toolbox for Java/JTOpen / Bugs / #300 Nonsense SQL0406 w/ jtopen 6.4

#300 Nonsense SQL0406 w/ jtopen 6.4

Milestone: JDBC

Status: closed

Owner: Paul D

Labels: None

Priority: 5

Updated: 2009-05-20

Created: 2009-03-19

Creator: Lajos Incze

Private: No

I'm using jtopen w/ ibatis to connect a cli application to an AS400 server. After upgrading from 6.3 to 6.4 a mystical error arised. From a certain point in the sequence of database transfers a certain kind of transfer fails with a nonsense error. Some log lines will serve here, the failng statement is inserting into the ZPTRPF table, I put some successful, and a typical failing startement:

Successful insertion:

---
2009-03-19 15:01:39,821 [main] DEBUG - {conn-335066} Preparing Statement: insert into ZPTRPF (DATIME,CHDRCOY,CHDRNUM,TRANNO,FEEDBACK_IND,VALIDFLAG,PTRNEFF,BATCTRCDE,USER_PROFILE,JOB_NAME) values (?,?,?,?,?,?,?,?,'WEBAGNT','EFUNDSYNC')
2009-03-19 15:01:39,822 [main] DEBUG - {pstm-336251} Executing Statement: insert into ZPTRPF (DATIME,CHDRCOY,CHDRNUM,TRANNO,FEEDBACK_IND,VALIDFLAG,PTRNEFF,BATCTRCDE,USER_PROFILE,JOB_NAME) values (?,?,?,?,?,?,?,?,'WEBAGNT','EFUNDSYNC')
2009-03-19 15:01:39,822 [main] DEBUG - {pstm-336251} Parameters: [2009-03-19 15:01:36.627, 2, 00008971, 146, Y, , 20011226, B521]
2009-03-19 15:01:39,822 [main] DEBUG - {pstm-336251} Types: [java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String]
---

Failng statement and the returned error:

---
2009-03-19 15:01:39,830 [main] DEBUG - {conn-335066} Preparing Statement: insert into ZPTRPF (DATIME,CHDRCOY,CHDRNUM,TRANNO,FEEDBACK_IND,VALIDFLAG,PTRNEFF,BATCTRCDE,USER_PROFILE,JOB_NAME) values (?,?,?,?,?,?,?,?,'WEBAGNT','EFUNDSYNC')
2009-03-19 15:01:39,831 [main] DEBUG - {pstm-336256} Executing Statement: insert into ZPTRPF (DATIME,CHDRCOY,CHDRNUM,TRANNO,FEEDBACK_IND,VALIDFLAG,PTRNEFF,BATCTRCDE,USER_PROFILE,JOB_NAME) values (?,?,?,?,?,?,?,?,'WEBAGNT','EFUNDSYNC')
2009-03-19 15:01:39,831 [main] DEBUG - {pstm-336256} Parameters: [2009-03-19 15:01:36.627, 2, 00008971, 147, Y, , 20011126, B503]
2009-03-19 15:01:39,831 [main] DEBUG - {pstm-336256} Types: [java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String]
2009-03-19 15:01:39,858 [main] ERROR - SQL error while transfering data (of CHDRNUM 00008971)
java.sql.SQLDataException: [SQL0406] Conversion error on assignment to column TRANNO. Cause . . . . . : During an attempt to assign a value to column TRANNO with an INSERT, UPDATE, ALTER TABLE, or REFRESH TABLE statement, conversion error type 6 occurred. If precompiling, the error occurred when converting a numeric constant to the same attributes as column TRANNO. A list of the error types follows: -- Error type 1 is overflow. -- Error type 2 is floating point overflow. -- Error type 3 is floating point underflow. -- Error type 4 is a floating point conversion error. -- Error type 5 is not an exact result. -- Error type 6 is numeric data that is not valid. -- Error type 7 is DBCS data that is not valid. Recovery . . . : Change the statement so that the result value fits in column TRANNO and is valid, or create the table or view again, specifying a new type or length for column TRANNO so that the result value can be assigned. Technical description . . . . . . . . : During precompile, an attempt is made to convert numeric constants in INSERT and UPDATE statements to the attributes of the target column. This would improve performance when the program is called.
at com.ibm.as400.access.JDError.createSQLExceptionSubClass(JDError.java:770)
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:665)
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:635)
at com.ibm.as400.access.AS400JDBCStatement.commonExecute(AS400JDBCStatement.java:917)
at com.ibm.as400.access.AS400JDBCPreparedStatement.execute(AS400JDBCPreparedStatement.java:828)
at sun.reflect.GeneratedMethodAccessor12.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStatementLogProxy.java:62)
at $Proxy1.execute(Unknown Source)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:80)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteUpdate(MappedStatement.java:216)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:94)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExecutorDelegate.java:393)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionImpl.java:82)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.insert(SqlMapClientImpl.java:58)
at hu.f400.efund.agent.TxParamUpdate.executeDefault(TxParamUpdate.java:41)
at hu.f400.efund.agent.LifeEfundTransactTransfer.execute(LifeEfundTransactTransfer.java:64)
at hu.f400.efund.agent.LifeEfundTransfer.execute(LifeEfundTransfer.java:20)
at hu.f400.efund.agent.LifeEfundTransfer.main(LifeEfundTransfer.java:34)
---

This error has arised w/ both 32 (1.6.0_12) and 64 bit (1.6.0_10) Sun jvm, failing much erlier with the 64 bit version.

Thanks.

incze

Discussion

Jeff Lee - 2009-03-19

assigned_to: nobody --> pauldevman
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Nobody/Anonymous - 2009-03-20

This may be the same error we've seen with Java 6 Hotspot. Try running your tests with -client on the commandline (Sun JVMs default to having Hotspot "-server" on). If that fixes it, then try the following on the commandline:

-XX:CompileCommand=exclude,com/ibm/as400/access/AS400JDBCPreparedStatement,commonExecuteBefore

That will exclude a particular method from Hotspot optimization. For us, that fixed the problem. See this post for more info:

http://kcbaltz.blogspot.com/2009/03/we-recently-found-and-resolved-issue-at.html

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Lajos Incze - 2009-03-23

Indeed, the Hotspot compiler option excluding the jt400 prepared statements did cure my problem (Using the "-client" flag did not. Is it sure that in client mode these optimizations do not take place?)

Anyway, many thanks.

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

K.C. Baltz - 2009-03-23

(I previously posted as nobody)

I had thought that -client was the way to disable HotSpot. However, your experience reminds me that you still get HotSpot, but with different settings. I had thought that one of those settings prevented optimizations, but I guess not.

Glad to hear it solved the problem. BTW, our first workaround to this problem involved upgrading iBATIS from 2.2 to 2.3 because the newer releases support PreparedStatement caching. You might try that as well, if nothing else than for the performance boost. Note that this fix wasn't successful for apps using Spring to talk to iBATIS as something about the way the two interact causes iBATIS not to cache prepared statements.

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Lajos Incze - 2009-03-23

My experiences were bound to the next dependencies (maven conf excerpt)

<dependency>
<groupId>org.apache.ibatis</groupId>
<artifactId>ibatis-sqlmap</artifactId>
<version>2.3.4.726</version>
</dependency>
<dependency>
<groupId>net.sf.jt400</groupId>
<artifactId>jt400</artifactId>
<version>6.4-jdbc40</version>
</dependency>

w/ jvm 1.6_012 i386 32-bit 1.6_011 i386 64-bit both on a "Red Hat Enterprise Linux Server release 5.3 (Tikanga)" linux.

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Paul D - 2009-03-23

Thanks so much for the info. We have finally reproduced this on a linux system with java _12 also.
We will now work with Sun to see what traces to send them.

-Paul

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Paul D - 2009-03-23

Forget to mention that under java 1.6.0_1 the error was not present.
Then it appeared after upgrading to _12...
So it definitely sounds like a sun bug.

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Lajos Incze - 2009-03-23

"We have finally reproduced this on a linux system with java _12 also. We will now work with Sun to see what traces to send them."

Great news, I was thinking hard how to isolate a reproduceable case (preserved what I incidentally had, but that's not reproducable elsewhere) but nothing came to my mind.

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Nobody/Anonymous - 2009-04-01

hi,

thanks to all developers. we have the same issue using Centos+Sun JDK 1.6.0_12+JBoss 4.2.3+Hibernate+JSF with iSeries backend DB. i spent a lot of time in debugging but did not find the error.

this bugdescrption (-client option) solved my problem.

thanks to all!!

alram

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Paul D - 2009-05-20

status: open --> closed
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Paul D - 2009-05-20

I am going to close this since the workaround for this is in jtopen 6.5.

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Paul D - 2009-05-20

Closing

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Nonsense SQL0406 w/ jtopen 6.4

Group

Searches

Help

#300 Nonsense SQL0406 w/ jtopen 6.4

Discussion