JBoss 7 datasource + Hibernate + DB2

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>
...