7 Replies Latest reply on Sep 16, 2010 6:52 AM by mukh.prac

    JBPM 4.1 Blob issue on AS400 DB2

      === Environment ==============================
      - jBPM Version : JBPM 4.1
      - Database : AS400/DB2
      - JDK : 1.5.017
      - Container :1.5.017
      - Configuration : the basic jbpm configuration comes with installation.

      === Problem description =========================
      JBPM4.1 doesn't come with a db script for AS400/DB2. We created the script ourselves. It works mostly fine, however, we have troubles when we trying load the the deployed the definition from database. Here is the code.

      ProcessDefinitionQuery processDefQuery = repositoryService.createProcessDefinitionQuery();
      
      ProcessDefinitionQuery eBusinessProcessQuery = processDefQuery.processDefinitionKey("eBusinessApplicationProcessFlow");
      ProcessDefinition processDef = eBusinessProcessQuery.uniqueResult();
      
      


      From research has been done so far, it seems that JBPM is trying to assess the length method of blob after resultset is closed.

      The part is having trouble in particular is the JBPM4_LOB. Here is the table script created for it .
       create table INTBPMMGT.JBPM4_LOB (
       DBID_ bigint generated by default as identity (start with 1, increment by 1),
       DBVERSION_ integer not null,
       BLOB_VALUE_ blob,
       DEPLOYMENT_ bigint,
       NAME_ clob,
       primary key (DBID_)
       ) ;
      


      Can anyone gives a hand?

      === Stacktrace ==============================
      Hibernate: select deployment1_.OBJNAME_ as col_0_0_, deployment1_.DEPLOYMENT_ as col_1_0_ from JBPM4_DEPLOYMENT deployment0_, JBPM4_DEPLOYPROP deployment1_, JBPM4_DEPLOYPROP deployment2_, JBPM4_DEPLOYPROP deployment3_ where deployment1_.KEY_='pdid' and deployment1_.DEPLOYMENT_=deployment0_.DBID_ and deployment2_.KEY_='pdkey' and deployment2_.OBJNAME_=deployment1_.OBJNAME_ and deployment2_.DEPLOYMENT_=deployment0_.DBID_ and deployment3_.KEY_='pdversion' and deployment3_.OBJNAME_=deployment1_.OBJNAME_ and deployment3_.DEPLOYMENT_=deployment0_.DBID_ and deployment2_.STRINGVAL_='eBusinessApplicationProcessFlow'
      Hibernate: select jobimpl0_.DBID_ as DBID1_3_, jobimpl0_.DBVERSION_ as DBVERSION3_3_, jobimpl0_.DUEDATE_ as DUEDATE4_3_, jobimpl0_.STATE_ as STATE5_3_, jobimpl0_.ISEXCLUSIVE_ as ISEXCLUS6_3_, jobimpl0_.LOCKOWNER_ as LOCKOWNER7_3_, jobimpl0_.LOCKEXPTIME_ as LOCKEXPT8_3_, jobimpl0_.EXCEPTION_ as EXCEPTION9_3_, jobimpl0_.RETRIES_ as RETRIES10_3_, jobimpl0_.PROCESSINSTANCE_ as PROCESS11_3_, jobimpl0_.EXECUTION_ as EXECUTION12_3_, jobimpl0_.CFG_ as CFG13_3_, jobimpl0_.SIGNAL_ as SIGNAL14_3_, jobimpl0_.EVENT_ as EVENT15_3_, jobimpl0_.REPEAT_ as REPEAT16_3_, jobimpl0_.CLASS_ as CLASS2_3_ from JBPM4_JOB jobimpl0_ where (jobimpl0_.LOCKOWNER_ is null) and jobimpl0_.RETRIES_>0 and jobimpl0_.STATE_<>'suspended' order by jobimpl0_.DUEDATE_ asc fetch first 1 rows only
      Hibernate: select deployment0_.DBID_ as DBID1_4_0_, deployment0_.NAME_ as NAME2_4_0_, deployment0_.TIMESTAMP_ as TIMESTAMP3_4_0_, deployment0_.STATE_ as STATE4_4_0_ from JBPM4_DEPLOYMENT deployment0_ where deployment0_.DBID_=?
      Hibernate: select resources0_.DEPLOYMENT_ as DEPLOYMENT4_1_, resources0_.DBID_ as DBID1_1_, resources0_.NAME_ as NAME5_1_, resources0_.DBID_ as DBID1_2_0_, resources0_.DBVERSION_ as DBVERSION2_2_0_, resources0_.BLOB_VALUE_ as BLOB3_2_0_ from JBPM4_LOB resources0_ where resources0_.DEPLOYMENT_=?
      2009-11-10 17:18:35,250 INFO [main] [Log4jLog.java:52] [info] - exception while executing command org.jbpm.pvm.internal.query.ProcessDefinitionQueryImpl@1b7a553
      org.jbpm.api.JbpmException: couldn't extract bytes out of blob
      at org.jbpm.pvm.internal.lob.BlobStrategyBlob.get(BlobStrategyBlob.java:27)
      at org.jbpm.pvm.internal.lob.Lob.extractBytes(Lob.java:83)
      at org.jbpm.pvm.internal.repository.DeploymentImpl.getBytes(DeploymentImpl.java:162)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:585)
      at org.hibernate.proxy.pojo.javassist.JavassistLazyInitializer.invoke(JavassistLazyInitializer.java:197)
      at org.jbpm.pvm.internal.repository.DeploymentImpl_$$_javassist_27.getBytes(DeploymentImpl_$$_javassist_27.java)
      at org.jbpm.pvm.internal.repository.ProcessDeployer.deploy(ProcessDeployer.java:58)
      at org.jbpm.pvm.internal.repository.DeployerManager.deploy(DeployerManager.java:46)
      at org.jbpm.pvm.internal.repository.RepositorySessionImpl.getObject(RepositorySessionImpl.java:120)
      at org.jbpm.pvm.internal.query.ProcessDefinitionQueryImpl.execute(ProcessDefinitionQueryImpl.java:72)
      at org.jbpm.pvm.internal.query.AbstractQuery.execute(AbstractQuery.java:81)
      at org.jbpm.pvm.internal.svc.DefaultCommandService.execute(DefaultCommandService.java:42)
      at org.jbpm.pvm.internal.spring.CommandTransactionCallback.doInTransaction(CommandTransactionCallback.java:50)
      at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:128)
      at org.jbpm.pvm.internal.tx.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:77)
      at org.jbpm.pvm.internal.svc.EnvironmentInterceptor.execute(EnvironmentInterceptor.java:46)
      at org.jbpm.pvm.internal.svc.RetryInterceptor.execute(RetryInterceptor.java:55)
      at org.jbpm.pvm.internal.query.AbstractQuery.untypedList(AbstractQuery.java:62)
      at org.jbpm.pvm.internal.query.AbstractQuery.untypedUniqueResult(AbstractQuery.java:69)
      at org.jbpm.pvm.internal.query.ProcessDefinitionQueryImpl.uniqueResult(ProcessDefinitionQueryImpl.java:145)
      at au.com.qbe.lmi.common.bmp.BPMProcessDeployerTest.testLoadDeployedProcessDefinition(BPMProcessDeployerTest.java:73)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:585)
      at junit.framework.TestCase.runTest(TestCase.java:164)
      at junit.framework.TestCase.runBare(TestCase.java:130)
      at org.springframework.test.ConditionalTestCase.runBare(ConditionalTestCase.java:69)
      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:120)
      at junit.framework.TestSuite.runTest(TestSuite.java:230)
      at junit.framework.TestSuite.run(TestSuite.java:225)
      at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:130)
      at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
      at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
      at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
      at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
      at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
      Caused by: java.sql.SQLException: [PWS0001] Function did not complete successfully.
      at com.ibm.as400.access.JDError.throwSQLException(JDError.java:650)
      at com.ibm.as400.access.JDLobLocator.getLength(JDLobLocator.java:126)
      at com.ibm.as400.access.AS400JDBCBlobLocator.length(AS400JDBCBlobLocator.java:185)
      at org.hibernate.lob.SerializableBlob.length(SerializableBlob.java:54)
      at org.jbpm.pvm.internal.lob.BlobStrategyBlob.get(BlobStrategyBlob.java:25)
      ... 42 more




        • 1. Re: JBPM 4.1 Blob issue on AS400 DB2

          The default length value for the blob column is 250 for DB2.

          Do the following steps
          1. Extract jbpm.jar
          2. update jbpm.execution.hbm.xml
          Add length attribute

          3. Create the jar with the upated file

          • 2. Re: JBPM 4.1 Blob issue on AS400 DB2

            Thanks, Robinthomas.

            I did notice this issue on DB2, which will result in another sql exception, DataTruncationException. We fixed this one already. We didn't let JBPM hbm to generate tables. Instead, we use table script to create them. The length of the BLOB is 1048576 now.

            In fact, the above code works when we use it in unit test extending AbstractTransactionalSpringJbpmTestCase when the deployment is created fresh with the start process. However, it won't work if we try to start a process already deployed in the database, even unit testing. Found a link seems to related to this.

            http://www-01.ibm.com/support/docview.wss?uid=nas16b3919404d0b1c2a86256ed60055bcbf

            But upgrading jt400.jar doesn't solved the problem, already tried. Hibernate 3.3.2 documentation also says


            clob, blob

            Type mappings for the JDBC classes java.sql.Clob and java.sql.Blob. These types can be inconvenient for some applications, since the blob or clob object cannot be reused outside of a transaction. Driver support is patchy and inconsistent.


            I also debug the JBPM code. the getBytes() called outside the session.load() . Not sure it is related to the way JBPM is implemented just doesn't fit AS400 DB2. I tried to changed the behavior of transactions on JBPM, but there is little documentation talking about that. Only a little bit mentioned in the JBPM Dev Guide, talking about using SpringTransactionInterceptor. Our caller method of above code is already wrapped in the Required transaction. It should join the tx if JBPM is using required as well.

            I am posting the whole jbpm config here in case someone can help.

            <?xml version="1.0" encoding="UTF-8"?>
            
            <jbpm-configuration>
            
             <import resource="jbpm.jpdl.cfg.xml" />
             <import resource="jbpm.identity.cfg.xml" />
             <import resource="jbpm.jobexecutor.cfg.xml" />
             <import resource="jbpm.businesscalendar.cfg.xml" />
             <process-engine-context>
             <repository-service />
             <repository-cache />
             <execution-service />
             <history-service />
             <management-service />
             <identity-service />
             <task-service />
            
             <!-- Here we needed to change the transaction interceptor -->
             <command-service>
             <retry-interceptor />
             <environment-interceptor />
             <spring-transaction-interceptor />
             </command-service>
            
             <!-- Added spring as read-context-->
             <script-manager default-expression-language="juel"
             default-script-language="juel"
             read-contexts="execution, environment, process-engine, spring"
             write-context="">
             <script-language name="juel"
             factory="org.jbpm.pvm.internal.script.JuelScriptEngineFactory" />
             </script-manager>
            
             <id-generator />
             <types resource="jbpm.variable.types.xml" />
            
             <address-resolver />
            
            
             <mail-template name='task-notification'>
             <to users="${task.assignee}"/>
             <subject>${task.name}</subject>
             <text><![CDATA[Hi ${task.assignee},
             Task "${task.name}" has been assigned to you.
             ${task.description}
            
             Sent by JBoss jBPM
             ]]></text>
             </mail-template>
            
             <mail-template name='task-reminder'>
             <to users="${task.assignee}"/>
             <subject>${task.name}</subject>
             <text><![CDATA[Hey ${task.assignee},
             Do not forget about task "${task.name}".
             ${task.description}
            
             Sent by JBoss jBPM
             ]]></text>
             </mail-template>
            
             </process-engine-context>
            
             <transaction-context>
             <repository-session />
             <db-session />
            
             <message-session />
             <timer-session />
             <history-session />
             <mail-session>
             <mail-server>
             <session-properties resource="jbpm.mail.properties" />
             </mail-server>
             </mail-session>
            
             <!--
             Need to set explicitly that we don't want jbpm to create sessions
             -->
             <hibernate-session current="true" />
             </transaction-context>
            </jbpm-configuration>
            


            • 3. Re: JBPM 4.1 Blob issue on AS400 DB2

              OK, after a lot of troubleshooting debugging. Finally find a solution for it. I really hope someone from JBPM team can comment on this.

              Basically, the LobLocator won't work if I try to getBytes once the resultset is closed. This is because BlobStrategyBlob in JBPM has following method

              public byte[] get(Lob lob) {
               if (lob.cachedBytes!=null) {
               return lob.cachedBytes;
               }
              
               java.sql.Blob sqlBlob = lob.blob;
               if (sqlBlob!=null) {
               try {
               return sqlBlob.getBytes(1, (int) sqlBlob.length());
               } catch (SQLException e) {
               throw new JbpmException("couldn't extract bytes out of blob", e);
               }
               }
               return null;
               }




              The highlighted one is calling hibernate code, and then use the lob locator. If it is new deployment with the test, the cachedBytes is return, therefore, no problem.

              When we start process from existing deployment. it call this.

              log.trace("loading deployment "+deploymentId+" from db");
               DeploymentImpl deployment = (DeploymentImpl) session.load(DeploymentImpl.class, Long.parseLong(deploymentId));
               deployerManager.deploy(deployment);
               object = repositoryCache.get(deploymentId, objectName);
               if (object==null) {
               throw new JbpmException("deployment "+deploymentId+" doesn't contain object "+objectName);
               }


              The highlighted code does the loading from db. But it will not load blob data and just have lob locator instead. Then the deployerManager.deploy(deployment);
              will call the lob locator to get data. And it is not happy because the resultset is closed by then. Therefore, exception in the original post happened.


              I finally get around it by specifying the jdbc properties
              <prop key="lob threshold">1048576</prop>


              • 4. Re: JBPM 4.1 Blob issue on AS400 DB2

                The jdbc property will force the session.load to get data not lob locator. Therefore, it worked.

                I would call this is an workaround.

                I hope someone from JBPM team can help to review this and suggest a proper way.

                • 5. Re: JBPM 4.1 Blob issue on AS400 DB2
                  mukh.prac

                  Hi Jacky,

                   

                  I am facing the same issue. Using jBPM 4.3 presenlty with Spring 3.0.

                  Could you please tell where to add this prop line in the application.i.e in which xml file?

                   

                  In my spring application xml file, I have hibernateProperties element in Spring Context, where we mention such things as hibernate.url, username, password etc. Shall we add the line at this section?

                  Please help. Its very urgent.

                   

                  Thanks in advance.

                  • 6. Re: JBPM 4.1 Blob issue on AS400 DB2
                    plimanowka

                    Another workaround for DB2 v9.7 and UDB type 4 driver is to add property file named DB2JccConfiguration.properties somewhere to the application CLASSPATH with following content:

                     

                    db2.jcc.override.progressiveStreaming=2

                    db2.jcc.override.fullyMaterializeLobData=true

                     

                    First line forces DB2 UDB driver to turn off "progressive streaming" feature. Second one instructs the driver to load all LOB data during FETCH (this is ignored unless progressive streaming is turned off).

                     

                    Clues leading to this workaround:

                    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.apdv.java.doc/doc/c0052411.html

                    https://jira.jboss.org/browse/JBPAPP-2613

                     

                    More about UDB type 4 properties: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.apdv.java.doc/doc/rjvdsprp.htm.

                     

                    Anyways, those 2 lines saved my day.

                    • 7. Re: JBPM 4.1 Blob issue on AS400 DB2
                      mukh.prac

                      Hi Pawel,

                       

                      Thanks for the suggestions. For those who are struggling with the issue, we added the following properties to our datasource paramters (further to Pawel's response ):

                       

                                      <entry key="db2.jcc.override.fullyMaterializeLobData" value="true" />
                                      <entry key="db2.jcc.override.fullyMaterializeInputStreams" value="true" />
                                      <entry key="db2.jcc.override.progressiveStreaming" value="2" />
                                      <entry key="db2.jcc.override.progresssiveLocators" value="2" />
                                  
                      


                      Hope it helps.

                       

                      Thanks.