I encounted a problem when trying to use
Hibernate2 to manage a big database.
I'm using IBM DB2.
My database have about 1000 records in it.
There are blob type columns in these records.
The actual size of each record is about 12KB,
so the total size of all records is about 12MB.
When I tried to delete all records using Hibernate,
I got a DB2Exception.

My program is like this:

session = sessions.openSession();
tx = session.beginTransaction();
session.delete("from test.testRecord as record");
tx.commit();

The log is like this(include the SQLs displayed by hibernate):
(The chinese characters in the log means
"There are not enough memory in heap to execute command")

Hibernate: select testreco0_.ID as ID, testreco0_.status as status, testreco0_.messageSize as messageS3_, mqdbreco0_.message as message, from TESTRECORD testreco0_

18:16:48,700 WARN JDBCExceptionReporter:38 - SQL Error: -973, SQLState: 57011
18:16:48,700 ERROR JDBCExceptionReporter:46 - [IBM][CLI Driver][DB2/NT] SQL0973N "APP_CTL_HEAP" 堆中没有足够的存储器可用来处理语句。 SQLSTATE=57011

18:16:48,750 ERROR JDBCExceptionReporter:73 - Could not execute query
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0973N "APP_CTL_HEAP" 堆中没有足够的存储器可用来处理语句。 SQLSTATE=57011

at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:260)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:197)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(SQLExceptionGenerator.java:448)
at COM.ibm.db2.jdbc.app.DB2ResultSet.next(DB2ResultSet.java:487)
at net.sf.hibernate.loader.Loader.doFind(Loader.java:151)
at net.sf.hibernate.loader.Loader.find(Loader.java:620)
at net.sf.hibernate.hql.QueryTranslator.find(QueryTranslator.java:928)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1343)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1322)
at net.sf.hibernate.impl.SessionImpl.delete(SessionImpl.java:1457)
at net.sf.hibernate.impl.SessionImpl.delete(SessionImpl.java:1443)
at test.test(TEST.java:55)


I noticed that the Hibernate executed an unnecessary select command.
Also, when I reduce the size of the records to let the commands be executed successfully,
The SQL executed is like this:

Hibernate: select testreco0_.ID as ID, testreco0_.status as status, testreco0_.messageSize as messageS3_, mqdbreco0_.message as message, from TESTRECORD testreco0_
Hibernate: delete from TESTRECORD where ID=?

Does this mean that the hibernate is actually deleteing the records one by one instead of use one sql command? (That's why hibernate should use that select command first to get all the IDs.)
So is there a way to avoid the unnecessary select command? Otherwise, I have to give up using hibernate and use JDBC instead.

This has been discussed OVER and OVER again!


As is documented in various places, Hibernate/ORM is NOT the best way to do mass updates/deletes. SQL/JDBC is perfectly good for these kinds of usecases!


A mass update or delete is a fundamentally non-object-oriented operation.


P.S. The DB2 exception is a very common one that results from DB2 having a stupidly too small default value for one of its config parameters. Ask your DBA how to fix it - it is very simple, I just forget the name of the parameter.