Updation and Insertion Problem with DB2/400

simhadri · **Joined:** Fri Apr 14, 2006 11:03 am **Posts:** 6

Hi,

My Hibernate Config file.

Code:

<hibernate-configuration>
   <session-factory>
      <property name="dialect">org.hibernate.dialect.DB2400Dialect</property>
      <property name="show_sql">true</property>
      <property name="use_outer_join">false</property>
      <property name="hibernate.connection.driver_class">com.ibm.as400.access.AS400JDBCDriver</property>
      <property name="hibernate.connection.url">jdbc:as400://172.18.0.14</property>
      <property name="hibernate.connection.username">dontest</property>
      <property name="hibernate.connection.password">dontest</property>
      <property name="hibernate.connection.pool_size">2</property>
      <property name="default_schema">LWFILES900</property>
      <property name="hibernate.cglib.use_reflection_optimizer">false</property>
      <property name="hibernate.connection.autocommit">true</property>
      <property name="hibernate.connection.isolation">1</property>
      <property name="hibernate.format_sql">true</property>
      <mapping resource="TestASMMST.hbm.xml" />
   </session-factory>
</hibernate-configuration>

Mapping File

Code:

<hibernate-mapping auto-import="true" default-lazy="false">
   <!-- Mapping of Question with table 'ASMMST' -->
   <class name="Question" table="ASMMST" dynamic-update="true">
      <id name="number" column="ASMQUN">
         <generator class="identity"/>
      </id>
      <property name="type"                column="ASMTYP"/>
      <property name="question"             column="ASMQUE"/>
      <property name="clientSelfAssessment"    column="ASMCSA"/>
      <property name="required"             column="ASMREQ"/>
      <property name="applicableSex"          column="ASMSEX"/>
      <property name="minimumAge"             column="ASMAGEMIN" type = "java.lang.Integer"/>
      <property name="maximumAge"             column="ASMAGEMAX" type = "java.lang.Integer"/>
      <property name="prerequisiteQuestion"   column="ASMPREQNM" type = "java.lang.Integer" />      
      <property name="prerequisiteAnswer"       column="ASMPREQNA"/>
      <property name="status"                column="ASMSTS" type = "java.lang.Character" />
      <property name="significantLifeEvent"   column="ASMSLE" type = "java.lang.Character" />
      <property name="strengthOrSupport"      column="ASMSTR" type = "java.lang.Character" />
      <property name="limitationToTreatment"   column="ASMLTT" type = "java.lang.Character" />
      <property name="relevantSymptom"      column="ASMRES" type = "java.lang.Character" />
      <property name="additionalOption1"      column="ASMAD1" type = "java.lang.Character" />
      <property name="additionalOption2"      column="ASMAD2" type = "java.lang.Character" />
      <property name="additionalOption3"      column="ASMAD3" type = "java.lang.Character" />
      <property name="additionalOption4"      column="ASMAD4" type = "java.lang.Character" />
   </class>
</hibernate-mapping>

Updation Code

Code:

   
                Session session = HibernateSessionFactory.currentSession();
   Question question = new Question();
   question.setMinimumAge(11);
   Transaction transaction = session.beginTransaction();
   session.update(question);
   session.flush();      
   transaction.commit();
                session.close();

Stacktrace while Updating a row which exists

Code:

Exception in thread "main" org.hibernate.exception.GenericJDBCException: could not update: [Question#0]
   at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:82)
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:70)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.persister.entity.BasicEntityPersister.update(BasicEntityPersister.java:2000)
   at org.hibernate.persister.entity.BasicEntityPersister.updateOrInsert(BasicEntityPersister.java:1909)
   at org.hibernate.persister.entity.BasicEntityPersister.update(BasicEntityPersister.java:2149)
   at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:75)
   at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:223)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:137)
   at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:274)
   at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
   at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:730)
   at TestASMMST.main(TestASMMST.java:49)
Caused by: java.sql.SQLException: [SQL7008] ASMMST in LWFILES900 not valid for operation.
   at com.ibm.as400.access.JDError.throwSQLException(JDError.java:650)
   at com.ibm.as400.access.JDError.throwSQLException(JDError.java:621)
   at com.ibm.as400.access.AS400JDBCStatement.commonExecute(AS400JDBCStatement.java:870)
   at com.ibm.as400.access.AS400JDBCPreparedStatement.executeUpdate(AS400JDBCPreparedStatement.java:1138)
   at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:22)
   at org.hibernate.persister.entity.BasicEntityPersister.update(BasicEntityPersister.java:1982)
   ... 10 more

Insertion Code

Code:

   

   Session session = HibernateSessionFactory.currentSession();
      Question question = new Question();
      question.setNumber(9199);
      question.setQuestion("Testing By Simhadri!!!!!!!!!!!!");
      question.setApplicableSex('B');
      question.setMinimumAge(11);
      question.setMaximumAge(18);
      question.setType("Y/N");
      question.setClientSelfAssessment(true);
      question.setRequired(true);
      question.setStatus('A');
      question.setSignificantLifeEvent('Y');
      question.setLimitationToTreatment('N');
      question.setStrengthOrSupport('Y');
      question.setRelevantSymptom('N');
      question.setAdditionalOption1('Y');      
      question.setAdditionalOption2('N');
      question.setAdditionalOption1('N');      
      question.setAdditionalOption2('Y');      
      System.out.println("Question NO: " + question.getNumber());
      session.save(question);

Stacktrace while inserting

Code:

Exception in thread "main" org.hibernate.exception.ConstraintViolationException: could not insert: [Question]
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:63)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1777)
   at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:2178)
   at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:34)
   at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)
   at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:240)
   at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:160)
   at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:95)
   at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:184)
   at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:33)
   at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:173)
   at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:27)
   at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:69)
   at org.hibernate.impl.SessionImpl.save(SessionImpl.java:481)
   at org.hibernate.impl.SessionImpl.save(SessionImpl.java:476)
   at TestASMMST.main(TestASMMST.java:35)
Caused by: java.sql.SQLException: [SQL0803] Duplicate key value specified.
   at com.ibm.as400.access.JDError.throwSQLException(JDError.java:650)
   at com.ibm.as400.access.JDError.throwSQLException(JDError.java:621)
   at com.ibm.as400.access.AS400JDBCStatement.commonExecute(AS400JDBCStatement.java:870)
   at com.ibm.as400.access.AS400JDBCPreparedStatement.executeUpdate(AS400JDBCPreparedStatement.java:1138)
   at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1759

Selection/Loading of a specfic row is working fine but

Regarding Updation

I am executing an UPDATE statement using Hibernate3 on a table EMPDEPT in DB2/400 on LIBRARY900 library. I am using jt400.jar as a driver api to connect to the database using Java and Hibernate.

I was getting a SQLexpection stating
java.sql.SQLException: [SQL7008] EMPDEPT in LIBRARY900 not valid for operation.

If I am executing the same update externally using sql client, it is working fine.
The Table on which I am updating is not journled.

I found somewhere on net stating this happens due to Update Safe Mode variable is set/default to 1, and update fails if more than one record is updated in the same transaction.

Does Hibernate updates more than one row at a time even it is expected to update only one row as i was updating the row using the primary key in the where clause.

As I am updating only one record, I was not able to figure out what other issues are causing this exception.

Does it has anything to do with journling and other stuff or is it the problem of Hibernate3 which I am not sure of.

Regarding Insertion

I am trying to insert a new row into a table, even the row does not exist, it is giving me an exception stating, Constraint Violation error the Duplicate Row.

I am not sure where I am going wrong.

Appreciate any help!!

Thanks & Regards,

Code:

[quote][/quote]

Code:

DWright · **Joined:** Wed Jul 07, 2004 2:00 pm **Posts:** 64

The example you provided for the update appears as if you should be doing an insert. The Question that you constructed does not have a primary key value specified, so Hibernate doesn't know what record to update. For the problem with insert, you should not be calling setNumber, since this is the primary key value. You have specified the identity generator in the mapping file, so Hibernate will create and assign the key value. By the way, are you sure that you want the autocommit setting to true in the hibernate config file? This is generally not recommended.

simhadri · **Joined:** Fri Apr 14, 2006 11:03 am **Posts:** 6

Hi,

Thanks for the response.

Instead of giving the generator as 'assigned' I had specified 'identity'.

This worked fine, but now I am not getting a Duplicate Row exception.

Now the exception was the same which I was getting on the updation.

I placed autocommit is true so to force hibernate to complete transaction by itself for testing purposes.

Once again appreciate for your response!

Do we have any DB2/400 specfic hibernate setting so as to avoid journling in the database level. I hope the DB might be blocking hibernate from executing all the insert/update/delete operations?

Any help is appreciated!!

Thanks & Regards

DWright · **Joined:** Wed Jul 07, 2004 2:00 pm **Posts:** 64

I'm not familiar with your environment, but I see that you have showSQL=true. Have you examined the SQL to see what operation LWFILES900 says is not valid? Also, I have found the showSQL less than useful, since it doesn't show the values set in the prepared statement. Instead, I use the following in my log4j.properties file:
log4j.logger.org.hibernate.SQL=debug
log4j.logger.org.hibernate.type=debug

Still not the nicest output format, but it may help.
Maybe you could post the output of this logging?

simhadri · **Joined:** Fri Apr 14, 2006 11:03 am **Posts:** 6

Hi,

Guys thanks for your support.

As the tables in DB2/400 are not journaled (i.e. not prepared to use transactions) I was getting this exception.

The main issue is; We have to journal a table before we can insert or update when using DB2/400 as a database,

The default value of autocommit property of Hibernate Session Factory is false.

I changed the autocommit property to true and removed any connection.isolation property. Ofcourse, this is not suggested and Transactions cannot be handled

That worked!!!

But???? what if i want to use explicit transactions in the middle tier instead of using them in Database layer?

Are there any properties which I need to set in Hibernate to achieve a middle tier tranasactions, irrespective of the database.

OR

Can anyone suggest any other api compatible with Hibernate that offers Transactions?

Thanks & Regards

jruffin · **Posted:** Fri Jun 23, 2006 11:21 am

I'm encountered the same problem. I followed the recommendation of setting autocommit to true and removing *isolation but the problem still persists. I just hardcoded actual values instead of passing them in.

Can anyone tell what I'm missing?

Code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
      "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>      
   <bean id="DataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" destroy-method="close">
      <property name="driverClassName">
         <value>${db.driverClassName}</value>
      </property>
      <property name="url">
         <value>${db.url}</value>
      </property>
      <property name="username">
         <value>${db.username}</value>
      </property>
      <property name="password">
         <value>${db.password}</value>
      </property>
   </bean>
   
      
   <!-- Hibernate SessionFactory (jruffinSF)
            <prop key="hibernate.cache.provider_class"> org.hibernate.cache.HashtableCacheProvider</prop>
-->
   <bean id="jruffinSF"
      class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
      <property name="dataSource">   <ref local="DataSource" /> </property>
      <property name="hibernateProperties">
         <props>
            <prop key="hibernate.dialect">${db.dialect}</prop>
            <prop key="hibernate.connection.pool_size">5</prop>
            <prop key="hibernate.default_schema">${db.defaultSchema}</prop>
            <prop key="hibernate.show_sql">true</prop>
            <prop key="hibernate.connection.autocommit">true</prop>      
         </props>
      </property>
      
      <property name="mappingJarLocations">         
            <value>WEB-INF/lib/dmdevDbFramework.jar</value>         
      </property>
      <!-- 
      <property name="mappingDirectoryLocations">
         <list>
            <value>classpath:com/ach/model</value>
         </list>
      </property>-->
   </bean>

   <bean id="hibernateTemplate"
      class="org.springframework.orm.hibernate3.HibernateTemplate">
      <property name="sessionFactory">
         <ref bean="jruffinSF" />
      </property>
   </bean>

   <bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
      <property name="sessionFactory" >      
         <ref bean="jruffinSF" />
      </property>
   </bean>   
 
   <bean id="addressDAO" class="com.ach.dao.hibernate.AddressDAOHibernate">
      <property name="sessionFactory"><ref bean="jruffinSF" /></property>   
   </bean>   
    
   <bean id="billtoDAO" class="com.ach.dao.hibernate.BilltoDAOHibernate">
      <property name="sessionFactory"><ref bean="jruffinSF" /></property>   
   </bean>

   <bean id="countryDAO" class="com.ach.dao.hibernate.CountryDAOHibernate">
      <property name="sessionFactory"><ref bean="jruffinSF" /></property>   
   </bean>
   
   <bean id="countryStateProvinceDAO" class="com.ach.dao.hibernate.CountryStateProvinceDAOHibernate">
      <property name="sessionFactory"><ref bean="jruffinSF" /></property>   
   </bean>
   
   <bean id="divisionDAO" class="com.ach.dao.hibernate.DivisionDAOHibernate">
      <property name="sessionFactory"><ref bean="jruffinSF" /></property>   
   </bean>
   
   <bean id="parentDAO" class="com.ach.dao.hibernate.ParentDAOHibernate">
      <property name="sessionFactory"><ref bean="jruffinSF" /></property>   
   </bean>
   
   <bean id="shipToDepartmentDAO" class="com.ach.dao.hibernate.ShipToDepartmentDAOHibernate">
      <property name="sessionFactory"><ref bean="jruffinSF" /></property>   
   </bean>
   
   <bean id="shipToLocationDAO" class="com.ach.dao.hibernate.ShipToLocationDAOHibernate">
      <property name="sessionFactory"><ref bean="jruffinSF" /></property>   
   </bean>
   
   <bean id="businessSegementDAO" class="com.ach.dao.hibernate.BusinessSegementDAOHibernate">
      <property name="sessionFactory"><ref bean="jruffinSF" /></property>   
   </bean>
</beans>

Stack Trace from JUNIT saveOrUpdate test

Code:

org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not update: [com.ach.model.Shiptodepartment#2]; uncategorized SQLException for SQL [update S10B506B.DMDEV.SHIPTODEPARTMENT set BUSSEGPK=?, SUBCHANNELPK=?, TERRITORYPK=?, SHIPTOLOCPK=?, SHIPTODEPTSOURCEID=?, SHIPTODEPTID=?, SHIPTODEPTNAME=?, SHIPTODEPTACTIVE=? where SHIPTODEPTPK=?]; SQL state [55019]; error code [-7008]; [SQL7008] SHIPT00001 in DMDEV not valid for operation.; nested exception is java.sql.SQLException: [SQL7008] SHIPT00001 in DMDEV not valid for operation.
java.sql.SQLException: [SQL7008] SHIPT00001 in DMDEV not valid for operation.
   at com.ibm.as400.access.JDError.throwSQLException(JDError.java:650)
   at com.ibm.as400.access.JDError.throwSQLException(JDError.java:621)
   at com.ibm.as400.access.AS400JDBCStatement.commonExecute(AS400JDBCStatement.java:870)
   at com.ibm.as400.access.AS400JDBCPreparedStatement.executeUpdate(AS400JDBCPreparedStatement.java:1138)
   at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:23)
   at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2268)
   at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:2180)
   at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2450)
   at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:91)
   at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:140)
   at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:297)
   at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
   at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:993)
   at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:340)
   at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
   at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:579)
   at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:500)
   at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:473)
   at org.springframework.transaction.interceptor.TransactionAspectSupport.doCommitTransactionAfterReturning(TransactionAspectSupport.java:275)
   at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170)
   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:176)
   at $Proxy8.saveOrUpdate(Unknown Source)
   at com.ach.dao.ShiptodepartmentTestCase.testGetS2dByS2DId(ShiptodepartmentTestCase.java:99)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
   at java.lang.reflect.Method.invoke(Unknown Source)
   at junit.framework.TestCase.runTest(TestCase.java:154)
   at junit.framework.TestCase.runBare(TestCase.java:127)
   at junit.framework.TestResult$1.protect(TestResult.java:106)
   at junit.framework.TestResult.runProtected(TestResult.java:124)
   at junit.framework.TestResult.run(TestResult.java:109)
   at junit.framework.TestCase.run(TestCase.java:118)
   at junit.framework.TestSuite.runTest(TestSuite.java:208)
   at junit.framework.TestSuite.run(TestSuite.java:203)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)

jnsunkersett · **Posted:** Wed Feb 06, 2013 9:46 am

Hi,

I am also dealing with the same issue - and it seems

the DB2/400 database must be journaled so as to be used with Hibernate.

The question is;

Will AFTER image journaling suffice or should the DB2/400 be journaled to store both BEFORE and AFTER images?

PS: http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Frzaki%2Frzakijrnbi.htm