Hi everyone,

I'm currently working on a J2EE application at my work, that uses a DB2 AS/400 database for the datas. But we are unfortunately managing with some performances problems, dues to the lack of joining when requiring our tables.

Example :
We have a table named TravelContent, which refers to three anothers tables : Travel, ContainerContent and Article.
The problem is that when we try to SELECT datas from TravelContent, instead of making outer joins on the three others tables, Hibernate just generates 4 SELECT statements. This just destroys our performances.

What we want Hibernate to do :
SELECT * FROM TravelContent LEFT OUTER JOIN Travel... LEFT OUTER JOIN ContainerContent ... LEFT OUTER JOIN ARTICLE ... WHERE....

What Hibernate unfortunately does :
SELECT * FROM TravelContent WHERE ....
SELECT * FROM Travel WHERE ...
SELECT * FROM ContainerContent WHERE ....
SELECT * FROM Article WHERE ....

Any ideas about what to do to activate join fetching into Hibernate with the DB2400Dialect ?

Here is some of our configuration :

<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">com.webtracking.util.DB2400Dialect</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="format_sql">true</prop>
<prop key="hibernate.max_fetch_depth">5</prop>
<prop key="hibernate.connection.isolation">1</prop>
<prop key="hibernate.use_outer_join">true</prop>
<prop key="hibernate.query.substitutions">1</prop>
<prop key="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</prop>
</props>
</property>


Our mapping for TravelContent :

<hibernate-mapping package="com.webtracking.bo">
<class name="com.webtracking.bo.TravelContent" table="FLOC258" proxy="com.webtracking.bo.TravelContent" lazy="true" >

<composite-id>
<key-property name="collection" column="COL023" type="com.webtracking.hibernate.TrimmedString"/>
<key-property name="numCommande" column="NBC039"/>
<key-property name="ean" column="EAN026" type="com.webtracking.hibernate.TrimmedString"/>
<key-property name="coloris" column="COL027" type="com.webtracking.hibernate.TrimmedString"/>
<key-property name="codeVoyage" column="VOY260" type="com.webtracking.hibernate.TrimmedString"/>
<key-property name="transporteur" column="COD021" type="com.webtracking.hibernate.TrimmedString"/>
<key-property name="lieuIncotermDepart" column="CDP030" type="com.webtracking.hibernate.TrimmedString"/>
</composite-id>


<property name="piecesExpediees" column="PEX258"/>
<property name="colisExpedies" column="CEX258"/>
<property name="nbColis" column="NBC258"/>
<property name="volume" column="VOL258"/>
<property name="poids" column="PDS258"/>
<property name="dateBooking" column="BOO258"/>
<property name="dateCRD" column="CRD258"/>
<property name="codeTransitaire" column="COD022" type="com.webtracking.hibernate.TrimmedString" />


<many-to-one
name="voyage"
unique="true"
fetch="join"
outer-join="true"
insert="false"
update="false"
lazy="proxy"
not-found="ignore"
class="com.webtracking.bo.Travel">
<column name="VOY260"/>
<column name="COD021"/>
<column name="CDP030"/>
</many-to-one>

<many-to-one
name="articleCommande"
unique="true"
fetch="join"
outer-join="true"
insert="false"
update="false"
lazy="proxy"
not-found="ignore"
class="com.webtracking.bo.Article">
<column name="COL023"/>
<column name="NBC039" />
<column name="EAN026"/>
</many-to-one>


</class>
</hibernate-mapping>


Your help is really welcome :)