I am using Hibernate 3.0.5 with DB2 in standalone mode.
I was able to insert data into the database but HQL and Criteris queries are all failing. Even a simple query like "from MedicalHistoryClaim claim"
is failing. The stack trace leads me to think that somhow the JDBC layer thinks that an executeUpdate is being used versus an executeQuery.
I have attache the mapping documents, test code and stack trace.
Also i am using the new
Can someone please help here as our decision to use hibernate depends on this proof of concept working and i would really like to be using it. Thanks a lot.

Here is the hibernate.cfg.xml:
<session-factory>

<!-- database connectivity properties -->
<property name="hibernate.query.substitutions">true 1, false 0, yes 'Y', no 'N'</property>
<property name="connection.driver_class">com.ibm.db2.jcc.DB2Driver</property>
<property name="connection.url">jdbc:db2://localhost:50002/MTMDHIST</property>
<property name="connection.username">db2admin</property>
<property name="connection.password">password</property>
<property name="dialect">org.hibernate.dialect.DB2Dialect</property>
<property name="show_sql">true</property>
<property name="use_sql_comments">true</property>
<property name="default_schema">DB2ADMIN</property>

<property name="transaction.factory_class">
org.hibernate.transaction.JDBCTransactionFactory
</property>

<!-- c3p0 - Connection pooling properties -->
<property name="c3p0.max_size">2</property>
<property name="c3p0.min_size">2</property>
<property name="c3p0.timeout">5000</property>
<property name="c3p0.max_statements">100</property>
<property name="c3p0.idle_test_period">3000</property>
<property name="c3p0.acquire_increment">2</property>
<property name="c3p0.validate">false</property>

<!-- auto schema export -->
<!-- property name="hbm2ddl.auto">create-drop</property>
<property name="hbm2ddl.auto">create</property>
<property name="hbm2ddl.auto">update</property -->

<!-- mapping files -->
<mapping resource="build/conf/hibernate/MedicalHistoryClaim.hbm.xml"/>
<mapping resource="build/conf/hibernate/MedicalHistoryLineItem.hbm.xml"/>

<!-- cache settings -->

</session-factory>


Here are the Mapping documents:
<class name="MedicalHistoryClaim" table="CLAIM">
<id name="id" column="ID"
type="long" unsaved-value="0">
<generator class="identity"/>
</id>

<property
name="statusCode"
type="string"
update="true"
insert="true"
column="STATUSCODE"
length="10"
not-null="true"
/>

<property
name="icn"
type="long"
update="true"
insert="true"
column="ICN"
/>


Here is the sample code:
public class Main
{
private SessionFactory factory;

/**
* Demonstrates query - Get claims by its Status Code or if null is input then get all the claims in the DB
*/
public void viewClaims(String statusCode) throws Exception
{
Session s = factory.openSession();
Transaction tx=null;
try
{
tx = s.beginTransaction();
List list = null;
list = s.createQuery("from MedicalHistoryClaim claim").list();

if (list.size()==0) throw new IllegalArgumentException("No claim found with the given status code: " + String.valueOf(statusCode) );

Iterator iter = list.listIterator();
while( iter.hasNext() )
{
MedicalHistoryClaim claim = (MedicalHistoryClaim)iter.next();
System.out.println( String.valueOf(claim));
Iterator lineItems = claim.getLineItems().iterator();
while( lineItems.hasNext() )
{
MedicalHistoryLineItem item = (MedicalHistoryLineItem)lineItems.next();
System.out.println( String.valueOf(item));
}
}

tx.commit();
}
catch (Exception e)
{
if (tx!=null) tx.rollback();
throw e;
}
finally
{
s.close();
}
}

public static void main(String[] args) throws Exception
{
final Main test = new Main();
test.factory = new Configuration().configure().buildSessionFactory();
test.viewClaims( "PAID" );
test.factory.close();
}
}



Below is the Full log of test program execution including exception that occurs:
C:\bea81\jdk142_04\bin\javaw.exe -classpath C:\hib-schema\build\classes;C:\bea81\jdk142_04\jre\lib\charsets.jar;C:\bea81\jdk142_04\jre\lib\jce.jar;C:\bea81\jdk142_04\jre\lib\jsse.jar;C:\bea81\jdk142_04\jre\lib\plugin.jar;C:\bea81\jdk142_04\jre\lib\rt.jar;C:\bea81\jdk142_04\jre\lib\sunrsasign.jar;C:\bea81\jdk142_04\jre\lib\ext\dnsns.jar;C:\bea81\jdk142_04\jre\lib\ext\ldapsec.jar;C:\bea81\jdk142_04\jre\lib\ext\localedata.jar;C:\bea81\jdk142_04\jre\lib\ext\sunjce_provider.jar;C:\hibernate-3.0\lib\commons-collections-2.1.1.jar;C:\hibernate-3.0\lib\ant-launcher-1.6.3.jar;C:\hibernate-3.0\lib\asm.jar;C:\hibernate-3.0\lib\cleanimports.jar;C:\hibernate-3.0\lib\concurrent-1.3.2.jar;C:\hibernate-3.0\lib\cglib-2.1.jar;C:\hibernate-3.0\lib\ant-1.6.3.jar;C:\hibernate-3.0\lib\ant-antlr-1.6.3.jar;C:\hibernate-3.0\lib\ant-junit-1.6.3.jar;C:\hibernate-3.0\lib\ant-swing-1.6.3.jar;C:\hibernate-3.0\lib\asm-attrs.jar;C:\hibernate-3.0\lib\connector.jar;C:\hibernate-3.0\lib\antlr-2.7.5H3.jar;C:\hibernate-3.0\lib\commons-logging-1.0.4.jar;C:\hibernate-3.0\lib\c3p0-0.8.5.2.jar;C:\hibernate-3.0\lib\dom4j-1.6.jar;C:\hibernate-3.0\lib\ehcache-1.1.jar;C:\hibernate-3.0\lib\jaas.jar;C:\hibernate-3.0\lib\jacc-1_0-fr.jar;C:\hibernate-3.0\lib\jaxen-1.1-beta-4.jar;C:\hibernate-3.0\lib\jboss-cache.jar;C:\hibernate-3.0\lib\jboss-common.jar;C:\hibernate-3.0\lib\jboss-jmx.jar;C:\hibernate-3.0\lib\jboss-system.jar;C:\hibernate-3.0\lib\jdbc2_0-stdext.jar;C:\hibernate-3.0\lib\jgroups-2.2.7.jar;C:\hibernate-3.0\lib\jta.jar;C:\hibernate-3.0\lib\junit-3.8.1.jar;C:\hibernate-3.0\lib\log4j-1.2.9.jar;C:\hibernate-3.0\lib\oscache-2.1.jar;C:\hibernate-3.0\lib\proxool-0.8.3.jar;C:\hibernate-3.0\lib\swarmcache-1.0rc2.jar;C:\hibernate-3.0\lib\versioncheck.jar;C:\hibernate-3.0\lib\xerces-2.6.2.jar;C:\hibernate-3.0\lib\xml-apis.jar;C:\hibernate-3.0\hibernate3.jar;C:\hib-schema;C:\hib-schema\lib\db2jcc.jar;C:\hib-schema\lib\db2jcc_license_cu.jar Main
09:53:17,239 INFO Environment:464 - Hibernate 3.0.5
09:53:17,239 INFO Environment:477 - hibernate.properties not found
09:53:17,249 INFO Environment:510 - using CGLIB reflection optimizer
09:53:17,249 INFO Environment:540 - using JDK 1.4 java.sql.Timestamp handling
09:53:17,329 INFO Configuration:1110 - configuring from resource: /hibernate.cfg.xml
09:53:17,329 INFO Configuration:1081 - Configuration resource: /hibernate.cfg.xml
09:53:17,690 INFO Configuration:444 - Mapping resource: build/conf/hibernate/MedicalHistoryClaim.hbm.xml
09:53:17,830 INFO HbmBinder:260 - Mapping class: MedicalHistoryClaim -> CLAIM
09:53:17,860 INFO Configuration:444 - Mapping resource: build/conf/hibernate/MedicalHistoryLineItem.hbm.xml
09:53:17,920 INFO HbmBinder:260 - Mapping class: MedicalHistoryLineItem -> LINEITEM
09:53:17,990 INFO Configuration:1222 - Configured SessionFactory: null
09:53:17,990 INFO Configuration:875 - processing extends queue
09:53:17,990 INFO Configuration:879 - processing collection mappings
09:53:17,990 INFO HbmBinder:2041 - Mapping collection: MedicalHistoryClaim.lineItems -> LINEITEM
09:53:17,990 INFO Configuration:888 - processing association property references
09:53:17,990 INFO Configuration:917 - processing foreign key constraints
09:53:18,010 INFO C3P0ConnectionProvider:50 - C3P0 using driver: com.ibm.db2.jcc.DB2Driver at URL: jdbc:db2://localhost:50002/MTMDHIST
09:53:18,010 INFO C3P0ConnectionProvider:51 - Connection properties: {user=db2admin, password=****}
09:53:18,010 INFO C3P0ConnectionProvider:54 - autocommit mode: false
Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@785d65 [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@b8f8eb [ acquireIncrement -> 2, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, idleConnectionTestPeriod -> 3000, initialPoolSize -> 2, maxIdleTime -> 5000, maxPoolSize -> 2, maxStatements -> 100, maxStatementsPerConnection -> 0, minPoolSize -> 2, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@18b81e3 [ description -> null, driverClass -> null, factoryClassLocation -> null, jdbcUrl -> jdbc:db2://localhost:50002/MTMDHIST, properties -> {user=******, password=******} ] , preferredTestQuery -> null, propertyCycle -> 300, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, usesTraditionalReflectiveProxies -> false ] , factoryClassLocation -> null, numHelperThreads -> 3, poolOwnerIdentityToken -> 785d65 ]
09:53:20,023 INFO SettingsFactory:77 - RDBMS: DB2/NT, version: SQL08022
09:53:20,023 INFO SettingsFactory:78 - JDBC driver: IBM DB2 JDBC Universal Driver Architecture, version: 2.6.80
09:53:20,323 INFO Dialect:92 - Using dialect: org.hibernate.dialect.DB2Dialect
09:53:20,333 INFO TransactionFactoryFactory:34 - Transaction strategy: org.hibernate.transaction.JDBCTransactionFactory
09:53:20,343 INFO TransactionManagerLookupFactory:33 - No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
09:53:20,343 INFO SettingsFactory:125 - Automatic flush during beforeCompletion(): disabled
09:53:20,343 INFO SettingsFactory:129 - Automatic session close at end of transaction: disabled
09:53:20,353 INFO SettingsFactory:144 - Scrollable result sets: enabled
09:53:20,353 INFO SettingsFactory:152 - JDBC3 getGeneratedKeys(): disabled
09:53:20,353 INFO SettingsFactory:160 - Connection release mode: null
09:53:20,353 INFO SettingsFactory:178 - Default schema: DB2ADMIN
09:53:20,353 INFO SettingsFactory:187 - Default batch fetch size: 1
09:53:20,353 INFO SettingsFactory:191 - Generate SQL with comments: enabled
09:53:20,364 INFO SettingsFactory:195 - Order SQL updates by primary key: disabled
09:53:20,364 INFO SettingsFactory:334 - Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
09:53:20,364 INFO ASTQueryTranslatorFactory:21 - Using ASTQueryTranslatorFactory
09:53:20,364 INFO SettingsFactory:203 - Query language substitutions: {no='N', true=1, yes='Y', false=0}
09:53:20,374 INFO SettingsFactory:209 - Second-level cache: enabled
09:53:20,374 INFO SettingsFactory:213 - Query cache: disabled
09:53:20,374 INFO SettingsFactory:321 - Cache provider: org.hibernate.cache.EhCacheProvider
09:53:20,384 INFO SettingsFactory:228 - Optimize cache for minimal puts: disabled
09:53:20,384 INFO SettingsFactory:237 - Structured second-level cache entries: disabled
09:53:20,394 INFO SettingsFactory:257 - Echoing all SQL to stdout
09:53:20,394 INFO SettingsFactory:261 - Statistics: disabled
09:53:20,394 INFO SettingsFactory:265 - Deleted entity synthetic identifier rollback: disabled
09:53:20,404 INFO SettingsFactory:279 - Default entity-mode: pojo
09:53:20,574 INFO SessionFactoryImpl:152 - building session factory
09:53:20,584 WARN Configurator:126 - No configuration found. Configuring ehcache from ehcache-failsafe.xml found in the classpath: jar:file:/C:/hibernate-3.0/lib/ehcache-1.1.jar!/ehcache-failsafe.xml
09:53:20,994 INFO SessionFactoryObjectFactory:82 - Not binding factory to JNDI, no JNDI name configured
09:53:20,994 INFO SessionFactoryImpl:379 - Checking 0 named queries
org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:82)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:70)
Hibernate: /* from MedicalHistoryClaim claim */ select medicalhis0_.ID as ID, medicalhis0_.FROMDATE as FROMDATE0_, medicalhis0_.TODATE as TODATE0_, medicalhis0_.STATUSCODE as STATUSCODE0_, medicalhis0_.ICN as ICN0_, medicalhis0_.ICNTOCREDIT as ICNTOCRE6_0_ from DB2ADMIN.CLAIM medicalhis0_
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
09:53:21,395 WARN JDBCExceptionReporter:71 - SQL Error: -99999, SQLState: null
at org.hibernate.loader.Loader.doList(Loader.java:1596)
at org.hibernate.loader.Loader.list(Loader.java:1577)
09:53:21,395 ERROR JDBCExceptionReporter:72 - executeQuery method cannot be used for update.
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:395)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at Main.viewClaims(Main.java:97)
at Main.main(Main.java:187)
Caused by: com.ibm.db2.jcc.b.SqlException: executeQuery method cannot be used for update.
at com.ibm.db2.jcc.b.id.a(id.java:2439)
at com.ibm.db2.jcc.b.jd.a(jd.java:1956)
at com.ibm.db2.jcc.b.jd.V(jd.java:505)
at com.ibm.db2.jcc.b.jd.executeQuery(jd.java:488)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:75)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)
at org.hibernate.loader.Loader.doQuery(Loader.java:391)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
... 7 more
Exception in thread "main" Process terminated with exit code 1


Name and version of the database you are using: DB2- UDB-8.0

The generated SQL (show_sql=true):
Hibernate: /* from MedicalHistoryClaim claim */ select medicalhis0_.ID as ID, medicalhis0_.FROMDATE as FROMDATE0_, medicalhis0_.TODATE as TODATE0_, medicalhis0_.STATUSCODE as STATUSCODE0_, medicalhis0_.ICN as ICN0_, medicalhis0_.ICNTOCREDIT as ICNTOCRE6_0_ from DB2ADMIN.CLAIM medicalhis0_

I found the problem. It was to do with the
following line in hibernate.cfg.xml

<property name="use_sql_comments">true</property>

For DB2 8.x (and maybe for the previous versions as well but I have only tested with db2 8.1), use_sql_comments should not be specified in the
hibernate configuration file. Or it needs to be set to "false".