iSeries incompatibility - statement too long due to many 4000 byte strings in select statement ---------------------------------------------------------------------------------------------- Key: JBPM-549 URL: http://jira.jboss.com/jira/browse/JBPM-549 Project: JBoss jBPM Issue Type: Bug Affects Versions: jBPM 3.0.2 Environment: DB2 for iSeries version 5 release 3, client running on Windows Reporter: Paul Newport Assigned To: Tom Baeyens Because there are many fields in the JBPM schema of type string, length 4000, when you use hibernate to generate the schema you end up with varchars of length 4000 when running it against DB2 for iSeries. As soon as you run queries such as that posted at the bottom you get this: SQL State: 54010 Vendor Code: -101 Message: [SQL0101] SQL statement too long or complex. Cause . . . . . : The SQL statement is longer than the limit allowed for length or complexity. The reason code is 3. The sum of the lengths of the non-LOB columns in a select list, table, view definition, or user defined table function is greater than 32766 or the definition contains a LOB and the sum of the lengths specified on the ALLOCATE clause for varying-length fields and the non-varying field lengths is greater than 32740. The maximum length is reduced if any of the columns are varying-length or allow null values. A suggested fix is to change the mapping files for properties that have string lengths of "4000" bytes to be "text" rather than "string", as the iSeries jdbc driver will only create clobs when the strign size is over 32k in length. Another solution would be to revisit string lengths to see if they all really need to be 4000 bytes long. As it stands I am patching hte hibernate mapping files manually but obviously would prefer not to. Here is the statement in question: select definition0_.PROCESSDEFINITION_ as PROCESSD4_22_, definition0_.ID_ as ID1_22_, definition0_.NAME_ as NAME3_22_, definition0_.ID_ as ID1_16_21_, definition0_.NAME_ as NAME3_16_21_, definition0_.PROCESSDEFINITION_ as PROCESSD4_16_21_, definition0_.STARTTASK_ as STARTTASK5_16_21_, definition0_.CLASS_ as CLASS2_16_21_, task1_.ID_ as ID1_14_0_, task1_.NAME_ as NAME2_14_0_, task1_.PROCESSDEFINITION_ as PROCESSD3_14_0_, task1_.DESCRIPTION_ as DESCRIPT4_14_0_, task1_.ISBLOCKING_ as ISBLOCKING5_14_0_, task1_.DUEDATE_ as DUEDATE6_14_0_, task1_.TASKMGMTDEFINITION_ as TASKMGMT7_14_0_, task1_.TASKNODE_ as TASKNODE8_14_0_, task1_.STARTSTATE_ as STARTSTATE9_14_0_, task1_.ASSIGNMENTDELEGATION_ as ASSIGNM10_14_0_, task1_.SWIMLANE_ as SWIMLANE11_14_0_, task1_.TASKCONTROLLER_ as TASKCON12_14_0_, processdef2_.ID_ as ID1_4_1_, processdef2_.NAME_ as NAME2_4_1_, processdef2_.VERSION_ as VERSION3_4_1_, processdef2_.ISTERMINATIONIMPLICIT_ as ISTERMIN4_4_1_, processdef2_.STARTSTATE_ as STARTSTATE5_4_1_, startstate3_.ID_ as ID1_5_2_, startstate3_.NAME_ as NAME3_5_2_, startstate3_.PROCESSDEFINITION_ as PROCESSD4_5_2_, startstate3_.ACTION_ as ACTION5_5_2_, startstate3_.SUPERSTATE_ as SUPERSTATE6_5_2_, processdef4_.ID_ as ID1_4_3_, processdef4_.NAME_ as NAME2_4_3_, processdef4_.VERSION_ as VERSION3_4_3_, processdef4_.ISTERMINATIONIMPLICIT_ as ISTERMIN4_4_3_, processdef4_.STARTSTATE_ as STARTSTATE5_4_3_, action5_.ID_ as ID1_8_4_, action5_.NAME_ as NAME3_8_4_, action5_.ISPROPAGATIONALLOWED_ as ISPROPAG4_8_4_, action5_.REFERENCEDACTION_ as REFERENC5_8_4_, action5_.ACTIONDELEGATION_ as ACTIONDE6_8_4_, action5_.EVENT_ as EVENT7_8_4_, action5_.PROCESSDEFINITION_ as PROCESSD8_8_4_, action5_.EXPRESSION_ as EXPRESSION9_8_4_, action5_.TIMERNAME_ as TIMERNAME10_8_4_, action5_.DUEDATE_ as DUEDATE11_8_4_, action5_.REPEAT_ as REPEAT12_8_4_, action5_.TRANSITIONNAME_ as TRANSIT13_8_4_, action5_.TIMERACTION_ as TIMERAC14_8_4_, action5_.class as class8_4_, action6_.ID_ as ID1_8_5_, action6_.NAME_ as NAME3_8_5_, action6_.ISPROPAGATIONALLOWED_ as ISPROPAG4_8_5_, action6_.REFERENCEDACTION_ as REFERENC5_8_5_, action6_.ACTIONDELEGATION_ as ACTIONDE6_8_5_, action6_.EVENT_ as EVENT7_8_5_, action6_.PROCESSDEFINITION_ as PROCESSD8_8_5_, action6_.EXPRESSION_ as EXPRESSION9_8_5_, action6_.TIMERNAME_ as TIMERNAME10_8_5_, action6_.DUEDATE_ as DUEDATE11_8_5_, action6_.REPEAT_ as REPEAT12_8_5_, action6_.TRANSITIONNAME_ as TRANSIT13_8_5_, action6_.TIMERACTION_ as TIMERAC14_8_5_, action6_.class as class8_5_, delegation7_.ID_ as ID1_10_6_, delegation7_.CLASSNAME_ as CLASSNAME2_10_6_, delegation7_.CONFIGURATION_ as CONFIGUR3_10_6_, delegation7_.CONFIGTYPE_ as CONFIGTYPE4_10_6_, delegation7_.PROCESSDEFINITION_ as PROCESSD5_10_6_, processdef8_.ID_ as ID1_4_7_, processdef8_.NAME_ as NAME2_4_7_, processdef8_.VERSION_ as VERSION3_4_7_, processdef8_.ISTERMINATIONIMPLICIT_ as ISTERMIN4_4_7_, processdef8_.STARTSTATE_ as STARTSTATE5_4_7_, event9_.ID_ as ID1_7_8_, event9_.EVENTTYPE_ as EVENTTYPE2_7_8_, event9_.TYPE_ as TYPE3_7_8_, event9_.GRAPHELEMENT_ as GRAPHELE4_7_8_, processdef10_.ID_ as ID1_4_9_, processdef10_.NAME_ as NAME2_4_9_, processdef10_.VERSION_ as VERSION3_4_9_, processdef10_.ISTERMINATIONIMPLICIT_ as ISTERMIN4_4_9_, processdef10_.STARTSTATE_ as STARTSTATE5_4_9_, action11_.ID_ as ID1_8_10_, action11_.NAME_ as NAME3_8_10_, action11_.ISPROPAGATIONALLOWED_ as ISPROPAG4_8_10_, action11_.REFERENCEDACTION_ as REFERENC5_8_10_, action11_.ACTIONDELEGATION_ as ACTIONDE6_8_10_, action11_.EVENT_ as EVENT7_8_10_, action11_.PROCESSDEFINITION_ as PROCESSD8_8_10_, action11_.EXPRESSION_ as EXPRESSION9_8_10_, action11_.TIMERNAME_ as TIMERNAME10_8_10_, action11_.DUEDATE_ as DUEDATE11_8_10_, action11_.REPEAT_ as REPEAT12_8_10_, action11_.TRANSITIONNAME_ as TRANSIT13_8_10_, action11_.TIMERACTION_ as TIMERAC14_8_10_, action11_.class as class8_10_, superstate12_.ID_ as ID1_5_11_, superstate12_.NAME_ as NAME3_5_11_, superstate12_.PROCESSDEFINITION_ as PROCESSD4_5_11_, superstate12_.ACTION_ as ACTION5_5_11_, superstate12_.SUPERSTATE_ as SUPERSTATE6_5_11_, taskmgmtde13_.ID_ as ID1_16_12_, taskmgmtde13_.NAME_ as NAME3_16_12_, taskmgmtde13_.PROCESSDEFINITION_ as PROCESSD4_16_12_, taskmgmtde13_.STARTTASK_ as STARTTASK5_16_12_, tasknode14_.ID_ as ID1_5_13_, tasknode14_.NAME_ as NAME3_5_13_, tasknode14_.PROCESSDEFINITION_ as PROCESSD4_5_13_, tasknode14_.ACTION_ as ACTION5_5_13_, tasknode14_.SUPERSTATE_ as SUPERSTATE6_5_13_, tasknode14_.SIGNAL_ as SIGNAL9_5_13_, tasknode14_.CREATETASKS_ as CREATET10_5_13_, startstate15_.ID_ as ID1_5_14_, startstate15_.NAME_ as NAME3_5_14_, startstate15_.PROCESSDEFINITION_ as PROCESSD4_5_14_, startstate15_.ACTION_ as ACTION5_5_14_, startstate15_.SUPERSTATE_ as SUPERSTATE6_5_14_, delegation16_.ID_ as ID1_10_15_, delegation16_.CLASSNAME_ as CLASSNAME2_10_15_, delegation16_.CONFIGURATION_ as CONFIGUR3_10_15_, delegation16_.CONFIGTYPE_ as CONFIGTYPE4_10_15_, delegation16_.PROCESSDEFINITION_ as PROCESSD5_10_15_, swimlane17_.ID_ as ID1_13_16_, swimlane17_.NAME_ as NAME2_13_16_, swimlane17_.ASSIGNMENTDELEGATION_ as ASSIGNME3_13_16_, swimlane17_.TASKMGMTDEFINITION_ as TASKMGMT4_13_16_, delegation18_.ID_ as ID1_10_17_, delegation18_.CLASSNAME_ as CLASSNAME2_10_17_, delegation18_.CONFIGURATION_ as CONFIGUR3_10_17_, delegation18_.CONFIGTYPE_ as CONFIGTYPE4_10_17_, delegation18_.PROCESSDEFINITION_ as PROCESSD5_10_17_, taskmgmtde19_.ID_ as ID1_16_18_, taskmgmtde19_.NAME_ as NAME3_16_18_, taskmgmtde19_.PROCESSDEFINITION_ as PROCESSD4_16_18_, taskmgmtde19_.STARTTASK_ as STARTTASK5_16_18_, taskcontro20_.ID_ as ID1_15_19_, taskcontro20_.TASKCONTROLLERDELEGATION_ as TASKCONT2_15_19_, delegation21_.ID_ as ID1_10_20_, delegation21_.CLASSNAME_ as CLASSNAME2_10_20_, delegation21_.CONFIGURATION_ as CONFIGUR3_10_20_, delegation21_.CONFIGTYPE_ as CONFIGTYPE4_10_20_, delegation21_.PROCESSDEFINITION_ as PROCESSD5_10_20_ from AS400A.PAULN.JBPM_MODULEDEFINITION definition0_ left outer join AS400A.PAULN.JBPM_TASK task1_ on definition0_.STARTTASK_=task1_.ID_ left outer join AS400A.PAULN.JBPM_PROCESSDEFINITION processdef2_ on task1_.PROCESSDEFINITION_=processdef2_.ID_ left outer join AS400A.PAULN.JBPM_NODE startstate3_ on processdef2_.STARTSTATE_=startstate3_.ID_ left outer join AS400A.PAULN.JBPM_PROCESSDEFINITION processdef4_ on startstate3_.PROCESSDEFINITION_=processdef4_.ID_ left outer join AS400A.PAULN.JBPM_ACTION action5_ on startstate3_.ACTION_=action5_.ID_ left outer join AS400A.PAULN.JBPM_ACTION action6_ on action5_.REFERENCEDACTION_=action6_.ID_ left outer join AS400A.PAULN.JBPM_DELEGATION delegation7_ on action6_.ACTIONDELEGATION_=delegation7_.ID_ left outer join AS400A.PAULN.JBPM_PROCESSDEFINITION processdef8_ on delegation7_.PROCESSDEFINITION_=processdef8_.ID_ left outer join AS400A.PAULN.JBPM_EVENT event9_ on action6_.EVENT_=event9_.ID_ left outer join AS400A.PAULN.JBPM_PROCESSDEFINITION processdef10_ on action6_.PROCESSDEFINITION_=processdef10_.ID_ left outer join AS400A.PAULN.JBPM_ACTION action11_ on action6_.TIMERACTION_=action11_.ID_ left outer join AS400A.PAULN.JBPM_NODE superstate12_ on startstate3_.SUPERSTATE_=superstate12_.ID_ left outer join AS400A.PAULN.JBPM_MODULEDEFINITION taskmgmtde13_ on task1_.TASKMGMTDEFINITION_=taskmgmtde13_.ID_ left outer join AS400A.PAULN.JBPM_NODE tasknode14_ on task1_.TASKNODE_=tasknode14_.ID_ left outer join AS400A.PAULN.JBPM_NODE startstate15_ on task1_.STARTSTATE_=startstate15_.ID_ left outer join AS400A.PAULN.JBPM_DELEGATION delegation16_ on task1_.ASSIGNMENTDELEGATION_=delegation16_.ID_ left outer join AS400A.PAULN.JBPM_SWIMLANE swimlane17_ on task1_.SWIMLANE_=swimlane17_.ID_ left outer join AS400A.PAULN.JBPM_DELEGATION delegation18_ on swimlane17_.ASSIGNMENTDELEGATION_=delegation18_.ID_ left outer join AS400A.PAULN.JBPM_MODULEDEFINITION taskmgmtde19_ on swimlane17_.TASKMGMTDEFINITION_=taskmgmtde19_.ID_ left outer join AS400A.PAULN.JBPM_TASKCONTROLLER taskcontro20_ on task1_.TASKCONTROLLER_=taskcontro20_.ID_ left outer join AS400A.PAULN.JBPM_DELEGATION delegation21_ on taskcontro20_.TASKCONTROLLERDELEGATION_=delegation21_.ID_ where definition0_.PROCESSDEFINITION_=?