DB2 390 Dialect and Sequences

PeteZybrick · **Joined:** Tue Oct 30, 2007 4:08 pm **Posts:** 1

This happened on 3.2.0 and 3.2.5. I couldn't get Sequence to work with DB2 390 (Z/OS). Here is the exception:
com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -142, SQLSTATE: 42612, SQLERRMC: null

According to the DB2 doc, this means "The SQL Statement is not supported". Reviewed the error messages with the DBA, it looks like the SQL statement that Hibernate is trying to execute:

values nextval for <schema.sequence>

is supported for DB2 UDB on Windows and Linux, but on Z/OS it must be:

select next value for <schema.sequence> from sysibm.sysdummy1;

I think this is a bug. I got around it by extending DB2390Dialect and overriding the getSequenceNextValString() method:

public class DB2390DialectSequenceFix extends DB2390Dialect {

public String getSequenceNextValString(String sequenceName) {
return "select next value for " + sequenceName + " from sysibm.sysdummy1;";
}

}

TomAtoms · **Joined:** Tue Sep 04, 2007 2:21 pm **Posts:** 14

Pete,

Check your configuration, I am using a DB2/s390 and it works fine for me selecting from sysibm.sysdummy1.

Tom

teclis · **Joined:** Sun Jun 25, 2006 6:48 am **Posts:** 2

I confirm what PeteZybrick writes. Are there any differences in the DB2-Versions on z/OS? It's DB2 9 here.