Driver DB2 for AS400

carlomagno8012 · **Joined:** Fri Sep 17, 2004 4:29 pm **Posts:** 10

Someone knows if there are any problem with db2 driver for AS400?

i have worked with this driver: COM.ibm.db2.jdbc.net.DB2Driver, and this work fine. but i need work over AS400 and i know that i need a special driver, therefore i like to know any trouble about that.

dirkschmidt · **Posted:** Thu Sep 23, 2004 5:40 am

Hi,

we have been using hibernate on iSeries for a couple of month without experiencing any serious problems. Hibernate works both with XA and non XA drivers (com.ibm.as400.access.AS400JDBCDriver); Hibernate uses a special dialect (hibernate.dialect=net.sf.hibernate.dialect.DB400Dialect) to correctly render SQL statements on the iSeries.

The only important thing is that IBM offers 2 drivers for the iSeries : 1) Toolbox driver to access iSeries from any OS (e.g. Windows PC) + 2) a native driver if your AppServer runs on the iSeries. Initially we had some problems configuring the drivers correctly (jars, libraries, connections, etc.) but none of these were related to Hibernate.

suffolksoft · **Joined:** Tue Jan 04, 2005 11:06 am **Posts:** 15

Hibernate 2.1.6
With DB2 on iServer V5R1
hibernate properties file at end of post

I AM having a problem with AS400 and hope dirk can shed some light.
In spite of many alternative attempts, I can not insert records into a
table. Below is error message:

[java] Hibernate: insert into TRMIDOH ( ... ) values (...)
[java] 12:39:57,671 WARN JDBCExceptionReporter:38 - SQL Error: -7008, SQLState: 55019
[java] 12:39:57,671 ERROR JDBCExceptionReporter:46 - [SQL7008] TRMIDOH in TESTTRUST not valid for operation.

From other forums, I have learned this is related to 1) journaling
and/or 2) auto commit. See below Is this to believed?

My dba insists jourmalling is on for table in question and
Hibernate in Action tells me auto commit is set to FALSE for
all connections by default.

Does anyone (dirk?) have any ideas? Am I in need of any
special properties on url? Is there an explicit way to set
auto commit false? (numerous googles have given no answer)

A couple other notes: The insert in question WORKS from
a sql interpreter when connected as same user.
AND
I can read records.

TIA
Rob

hibernate.properties

hibernate.dialect=net.sf.hibernate.dialect.DB2400Dialect
hibernate.connection.driver_class=com.ibm.as400.access.AS400JDBCDriver
hibernate.connection.isolation=4
hibernate.connection.url=jdbc:as400://192.168.1.3/testtrust
hibernate.connection.username=<...>
hibernate.connection.password=<...>

The AS400 Expert says:

Quote:

Subject: Re: Error 7008 Please help.
Reply | Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse

Your Insert/Update request is running under commitment control which
requires journalling/logging of the target table on the AS/400. So
you need to start journalling the table on the AS/400 or disable
commitment control for this request.

--
Kent Milligan, DB2/400 Solutions Team
AS/400 Partners In Development
k...@us.ibm.com

emmanuel · **Posted:** Wed Jan 12, 2005 7:20 am

You can either give to Hibernate your own configured user connection.
Or ou can set autocommit to true by writing you own connection provider.
This is something (autocommit=true) we do not recommand.

The only valid sample I've seen so far is non-journalized Informix DB instances.

suffolksoft · **Joined:** Tue Jan 04, 2005 11:06 am **Posts:** 15

In the interest of closure, I should add that the OTHER
dba came in today and "discovered" that the table
in question was NOT journaled. Once he turned journaling
on, all was sweet.

Thanks
Rob

dirkschmidt · **Posted:** Fri Jan 14, 2005 4:56 am

Hi suffolksoft ,

if you run into config - problems with hibernate/iSeries/jdbc dont hesitate to ask. It took us some time but most problems we encountered were caused by the jdbc /iSeries combination rather than hibernate / iSeries.

suffolksoft · **Joined:** Tue Jan 04, 2005 11:06 am **Posts:** 15

That is a generous offer. I have made my email public
in my profile. Could you send me yours so we don't have
to busy this forum with any potentially extended conversations.

As of now, all is well with db2/iseries/jdbc/hibernate.

Rob

etienne · **Posted:** Fri Jan 14, 2005 12:16 pm

Hi,

I am having a big problem concerning the link between Hibernate and a DB2-AS400 database. I cannot reach a set of "children" below a "parent". The children collection is Empty, I should have a non-empty collection.

I have a Account table having many "trades" (one-to-many relationship). The primary key of the trade is a "Char 18". I should find 4 children of trade below the Account Parent. The problem is not in the xml mapping, I have build a identical datamodel on a MySql (Intel-PC) database and I can read the Trades Collection on MySql but not on DB2-AS400! If I try to get a single instance of Trade (or a single instance of Account) it works well.

If I put Hibernate on the Debugger, I see that the Collection of Trade is created and filled with the correct number of Trade Object, The Trade containing the correct data, The Account Object is even reacheble from the Trade Obejct, but somewhere at the end of the Collection Building process, the Collection is re-initialised (or lost) and an empty Collection is added as the Trades collection under the Account Object.

I am using Hibernate 3.0Beta, I have try the same with Hibernate 2.1.7c and I get the same error.

This bug is starting to be anoying. Does anyone have an Idea to correct this bug?

Any Idea?

Etienne
Montreal

Code:

<property name="connection.driver_class">com.ibm.as400.access.AS400JDBCDriver</property>
       <property name="connection.url">jdbc:as400:123.123.123.123/QWERTY</property>

       <property name="connection.username">xxx</property>
      <property name="connection.password">xxx</property>
      <property name="dialect">org.hibernate.dialect.DB2400Dialect</property>
      
      <property name="show_sql">true</property>
       <property name="generate_statistics">true</property>

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC 
   "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping 
   package="com"
   default-access="field">
   
   <class name="Account" table="atfa0100">
      <id name="aimsAccountProductNo" column="acgicd">
         <generator class="assigned"></generator>
      </id>
      <property name="aimsAccountProduct" column="acj8cd"/>
      <property name="aimsProductCode" column="acabcd"/>
      
       <set name="trades"
             cascade="all">
            <key column="trpgcd"/>
            <one-to-many class="Trade"/>
        </set>
      
   </class>
   
   <class name="Trade" 
            table="atft2100">

      <id name="uniqueId" column="trafnb">
         <generator class="assigned"></generator>
      </id>
      
      
      <property name="transactionDate" column="tregdc"/>
      <property name="effectiveDate" column="trehdc"/>
      <property name="aimsSecurityNo" column="trpmcd"/>
      <property name="quantity" column="trahqy"/>
      <property name="amount" column="traiam"/>
      <property name="currencyOfAmount" column="trpncd"/>
      <property name="aimsBSCode" column="trg9cd"/>
      <property name="cancelCode" column="trc1st"/>

        <many-to-one name="account" 
         column="trpgcd" 
         class="Account"
         not-null="true"/>
   </class>
   
   
</hibernate-mapping>