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
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
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.