Hibernate 4
JBoss AS7
IBM DB2 v9.7
Had a problem to make hibernate work with DB2 through the JBoss datasource
org.hibernate.exception.SQLGrammarException
Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2ADMIN.TABLE_NAME, DRIVER=3.65.77
JBoss prefixes table name with the DB2 username. But hibernate searches for the table name prefixed with the schema name:
MYSCHEMA.TABLE_NAME
The solution is to add a "currentSchema" parameter to the connection url of the database:
jdbc:db2://localhost:50000/MYSCHEMA:currentSchema=MYSCHEMA;
Another slution:
in *.hbm.xml entity configuration files:
<hibernate-mapping>
<class name="packagename.classname" table="TABLE_NAME" schema="SCHEMANAME">
My working configuration:
hibernate.cfg.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<!-- Database connection settings -->
<property name="hibernate.connection.datasource">java:jboss/datasources/MYSCHEMA</property>
<property name="hibernate.current_session_context_class">thread</property>
<property name="hibernate.dialect">org.hibernate.dialect.DB2Dialect</property>
<mapping resource="Employee.hbm.xml"/>
<mapping resource="Customer.hbm.xml"/>
</session-factory>
</hibernate-configuration>
standalone.xml:
...
<subsystem xmlns="urn:jboss:domain:datasources:1.0">
<datasources>
<datasource jta="true" jndi-name="java:jboss/datasources/MYSCHEMA" pool-name="MYSCHEMA" enabled="true">
<connection-url>jdbc:db2://localhost:50000/MYSCHEMA:currentSchema=MYSCHEMA;</connection-url>
<driver>db2</driver>
<transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
<url-delimiter>|</url-delimiter>
<pool>
<min-pool-size>0</min-pool-size>
<max-pool-size>10</max-pool-size>
<prefill>false</prefill>
<use-strict-min>false</use-strict-min>
<flush-strategy>FailingConnectionOnly</flush-strategy>
</pool>
<security>
<user-name>***</user-name>
<password>***</password>
</security>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.db2.DB2ValidConnectionChecker"/>
<validate-on-match>true</validate-on-match>
<background-validation>false</background-validation>
<background-validation-millis>1</background-validation-millis>
<use-fast-fail>false</use-fast-fail>
<stale-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.db2.DB2StaleConnectionChecker"/>
</validation>
<timeout>
<blocking-timeout-millis>30000</blocking-timeout-millis>
<idle-timeout-minutes>30</idle-timeout-minutes>
<query-timeout>0</query-timeout>
<use-try-lock>0</use-try-lock>
<allocation-retry>0</allocation-retry>
<allocation-retry-wait-millis>5000</allocation-retry-wait-millis>
</timeout>
<statement>
<track-statements>false</track-statements>
<prepared-statement-cache-size>0</prepared-statement-cache-size>
</statement>
</datasource>
...
