Function sequence error in db2

homeijer · **Joined:** Wed Aug 27, 2003 9:12 am **Posts:** 4

Hi,

We are using hibernate on websphere 3.5 against a db2 (v7.2) database.
When we retrieve an complex object (that has just been saved) with relations, we get an sql exception:
CLI0125e function sequence error.

On an ibm site I found the following comment:
http://www-912.ibm.com/s_dir/slkbase.NS ... enDocument

I don't know if this applies to our situation, but could this be a problem in hibernate?

Any other ideas?

TIA,
Michael Homeijer

homeijer · **Joined:** Wed Aug 27, 2003 9:12 am **Posts:** 4

The problem seems to be a many-to-one relationship within a component.

The problem does not occur on jboss to db2 on NT and does occur on websphere to db2 on aix.

TIA,
Michael

homeijer · **Joined:** Wed Aug 27, 2003 9:12 am **Posts:** 4

To problem was was solved by changing the mapping as described below.
Could it be a problem that there were two many-to-one relations named "'land", one in the "adres" component and one in the "persoon" class?

Ideas welcome

TIA,
Michael

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
<class name="xx.Persoon" table="txxpersoon">
<id column="PERSOON_ID" name="id" type="java.lang.Integer">
<generator class="sequence">
<param name="sequence">HAA.SEQ_PERSOON</param>
</generator>
</id>
<property column="ACHTERNAAM" name="achternaam"/>
.....
<component name="adres" class="com.abnamro.generic.haai.model.Adres">
<property column="POSTCODE" name="postcode" />
<property column="HUISNUMMER" name="huisnummer" />

</component>
 <many-to-one name="adresland" class="com.abnamro.generic.haai.model.Land" column="F_ADRESLAND"/>
<many-to-one name="land" class="com.abnamro.generic.haai.model.Land" column="F_LAND"/>
</class>
</hibernate-mapping>

bbonner · **Joined:** Fri Sep 12, 2003 12:40 pm **Posts:** 65

I don't know why IBM's code does this, but here's what I found with tealwren's help:

I supposed if you have a many-to-one relationship in your collection (and AutoCommit = true), you'll see a function sequence error. I set autocommit to false (based upon your post) and lo-and-behold the function-sequence error is gone.

The error occurs when it does the following.

Hibernate goes to process a list. It goes through the list until it hits a many-to-one. At that point, it generates a new statement searching for the ID. (if there are a lot, you'll run into the n+1 problem, but fortunately for us, it will cache them and just do the matching in memory).

It processes this second statement successfully, and it adds it to the results:

Code:

results.add( getResultColumnOrRow(row, rs, session) ); // Line 178 of the Loader.class of 2.03

When it goes to run rs.next():

Code:

for ( count=0; count<maxRows && rs.next(); count++ ) { // line 151 of the Loader.class of 2.03

it blows the Function Sequence Error.

This really doesn't make sense to me, because this is query only. I think it's a bug in IBM's code. Hibernate does an excellent job of cleaning up the statements and result sets.

From the javadoc for JDBC Connection.setAutoCommit

Quote:

...The commit occurs when the statement completes or the next execute occurs, whichever comes first. In the case of statements returning a ResultSet object, the statement completes when the last row of the ResultSet object has been retrieved or the ResultSet object has been closed. In advanced cases, a single statement may return multiple results as well as output parameter values. In these cases, the commit occurs when all results and output parameter values have been retrieved.

I think it's caused by a bug in IBM's code. I have a CLI trace that shows the outer statement being committed when it should have been the inner statement that was committed. I'll try to send them a PMR.

My environment was:
Win2K (or AIX) it happened on both:
( DBMS NAME="DB2/NT", Version="07.02.0003", Fixpack="0x23040105" )
Hibernate 2.03
Using COM.ibm.db2.jdbc.DB2ConnectionPoolDataSource