Hibernate: ORM Mapping "Could not get next sequence value" SQLCODE: -142

[Doug MacKenzie]

Hi,

New to hibernate development but I've done some searching and haven't found the answer so I'll throw it out to the gurus.

I'm attempting to add a record to a table hosted in a DB2 database. The mapping I have is:

<hibernate-mapping >
	<class name="DBCivicAddress" table="CIVIC_ADDRESS">
		<id name="id" column="CIVIC_ADDRESS_ID" >
			<generator class="sequence">
				<param name="sequence">SQ_CIVIC_ADDRESS</param>
			</generator>
		</id>
		<property ...../>

when I attempt to save a DBCivicAddress object I get the following:

2009-02-18 15:52:27,000 DEBUG [JDBCExceptionReporter] - could not get next sequence value [values nextval for SQ_CIVIC_ADDRESS]
com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -142, SQLSTATE: 42612, SQLERRMC: null

The exception message above shows the SQL that is being attempted and it doesn't seem to be a well formed INSERT. I've manually tried a well formed INSERT using the NEXTVAL FOR SQ_CIVIC_ADDRESS to generate the id and it works fine. Conversely attempting "values nextval for SQ_CIVIC_ADDRESS] fails with the -142 error code that hibernate is reporting.

Any ideas on what may be wrong? The NEXTVAL FOR is supported by DB2 so I can only think that the -142 error being reported is referring to the "values" statement, which doesn't seem to make sense by itself and should be wrapped by an insert statement.

Thanks,
D

[Javid Jamae]

What dialect do you have set in your hibernate.cfg.xml file?

[Doug MacKenzie]

Thanks for the reply

The dialect is DB2

<property name="dialect">org.hibernate.dialect.DB2Dialect</property>

Updated
I've also tried
org.hibernate.dialect.DB2390Dialect
&
org.hibernate.dialect.DB2400Dialect

Both exhibit the same behaviour

[Javid Jamae]

The guy on this post said he had to create his own Dialect to get it to work. But that was in 2007. It seems easy though, just extend the class and override a single method.

I haven't used DB2 in a long time, so I'm sorry I don't have any better advice for you.

[Rahul Babbar]

Which version of DB2 are you using...
We had used the DB2 version 9.5 GA and there seemed to be no such problem..although we were generating the sequence in a a similar way...
Are you using the older versions...like version 8...
Anyways, the quote mentioned by Javid should fix the problem...

The guy on this post said he had to create his own Dialect

[Doug MacKenzie]

Thanks guys. Extending the DB2 dialect did the trick.