Hi all,

I am stuck with this one:

Caused by: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/SUN] SQL0101N The statement is too long or too complex. SQLSTATE=54001

at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:270)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:207)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(SQLExceptionGenerator.java:458)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2(DB2PreparedStatement.java:2110)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeQuery(DB2PreparedStatement.java:1596)
at com.mchange.v2.c3p0.impl.C3P0PreparedStatement.executeQuery(C3P0PreparedStatement.java:188)
at com.mchange.v2.c3p0.impl.C3P0PooledConnection$2.executeQuery(C3P0PooledConnection.java:473)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:83)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:631)
at net.sf.hibernate.loader.Loader.doResultSet(Loader.java:166)
at net.sf.hibernate.loader.Loader.doFind(Loader.java:111)
at net.sf.hibernate.loader.Loader.loadEntity(Loader.java:663)
at net.sf.hibernate.loader.Loader.loadEntity(Loader.java:678)
at net.sf.hibernate.loader.EntityLoader.load(EntityLoader.java:57)
at net.sf.hibernate.loader.EntityLoader.load(EntityLoader.java:49)
at net.sf.hibernate.persister.NormalizedEntityPersister.load(NormalizedEntityPersister.java:415)
... 14 more
Exception in thread "main"

What can I do ??
How can I tell Hibernate to build shorter SQL queries?

TIA,
--steve p.

Not nearly enough info here. And I'll leave it at that.

Steve,

The statement is basically a SELECT in a large object graph using outer join = true. If you want, I can try to paste that VERY large statement, but I will have to trim out confidential info.

Another question please:

Will the dimension of the SELECT statement depend on wether I use or don't use lazy?

thx,

--steve p.

If the statement is to long when using outer joins then remove the outer join (false) so the graph will be populated using multiple smaller SQL statements.

HIbernate 2.1 provides the hibernate.max_fetch_depth property which is very useful

It works by setting the above value, but it is much, much slower :(

thx,

--steve p.

Of couse I do not know specific of your project but generally idea of fetching "as much as you can" is very bad idea. Probably you can focus on data you really need and fetch only them (making everything else lazy/proxy)

There's also a DB2 setting that you can change to allow larger statements... I don't remember what it was but I found it via google.

Good luck,
Jenica