0 Replies Latest reply on Apr 20, 2006 5:29 AM by Arun Kumar

    Problem while running jBPM on db2 database.

    Arun Kumar Newbie

      There are some db issues which i faced while running the process flows on the db2 database.

      1. while i tried to deploy a process definition file at the start it threw me the follwoing exception ::
      Caused by: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0104N An un
      expected token "(" was found following "". Expected tokens may include: ", FRO
      M INTO". SQLSTATE=42601

      the query that bpm was trying to execute was this,
      select * from ( select rownumber() over(order by processdef0_.VERSION
      _ID desc ) as rownumber_, processdef0_.PCS_DEF_ID as PCS1_0_, processdef0_.PCS_DE
      F_NM as PCS2_0_, processdef0_.VERSION_ID as VERSION3_0_, processdef0_.ISTEM_IMPL
      CIT_ID as ISTEM4_0_, processdef0_.STARTSTATE_ID as STARTSTATE5_0_ from BPM_PCS_D
      EF processdef0_ where processdef0_.PCS_DEF_NM=? order by processdef0_.VERSION_ID
      desc ) as temp_ where rownumber_ <= ?

      This could be the reason why it failed:

      1. select rownumber() over(order by processdef0_.VERSION
      _ID desc

      the rownumber() over() as row_ makes every row distinct. the root cause for its failure as it does not work properly in db2. I have searched the forums but could not find any workaround for this issue.
      when i removed the rownumber() statement from the query and excecuted it worked fine. So there is some problem in this select rownumber() over(order by processdef0_.VERSION
      _ID desc.

      Even searched the jBoss jbpm forums,hibernate forums,db2 help site for help on the point 1. But could not find any reply.

      In one of the forums i found that while using db2 there is a additional property that we have to add in the hibernate.cfg.xml.
      < property name = "hibernate.query.substitutions" > true 1, false 0, yes 'Y', no 'N' </ property >

      I even tried using this property but still i am facing the same issue.

      Looking for help/comments regarding this issue. Kindly suggest.