I am using hibernate 3.3 to access DB2 tables. When we upgraded to DB2 9.7.2 on Linux last week, we found we had to modify some of our hibernate SQL queries in order to be compatible with DB2 9.7.2. With DB2 9.7.1 our hibernate SQL worked fine, but with DB2 9.7.2 we receive the SQL -401 SQLSTATE 42818 error with certain queries.

Below is the hibernate SQL before we modified it for DB2 9.7.2:

Query query = session.createQuery("from ACSystem s where s.systemnm=:searchstring");
query = query.setString("searchstring", name);
List list = query.list();
Iterator results =list.iterator();

After changing the code as follows, then it worked with DB2 9.7.2:

String querystring = "from ACSystem s where s.systemnm='"
+ name
+ "'";

Iterator results = session.createQuery(querystring).list().iterator();

Has anyone run into this?

The problem is caused by the MySQL compatibility mode. It was on by default in my installation:

db2user@db2host:~$ db2set DB2_COMPATIBILITY_VECTOR
MYS

Turning it off fixed the problem:

db2user@db2host:~$ db2set DB2_COMPATIBILITY_VECTOR=0
db2user@db2host:~$ db2set DB2_COMPATIBILITY_VECTOR
0

db2user@db2host:~$ db2 stop database manager force
DB20000I The STOP DATABASE MANAGER command completed successfully.
db2user@db2host:~$ db2 start database manager
DB20000I The START DATABASE MANAGER command completed successfully.

After that my program started working again.