10 Replies Latest reply on Nov 13, 2009 4:21 AM by Joram Barrez

    JBPM 4.0 on DB2

    Laurent Cornélis Newbie

      Quick question, is JBPM 4.0 running on DB2 ? Which version ?

      It's not easy to figure out if it is working because quick all the doc about it is for JBPM 3.2 ...

      Thanks !

        • 1. Re: JBPM 4.0 on DB2
          Sebastian Schneider Master

          Since Hibernate supports DB2 and jBPM is using Hibernate to abstract database access it should be working with DB2.

          • 2. Re: JBPM 4.0 on DB2
            Ronald van Kuijk Master

            Correct, but it could require some tweeks

            • 3. Re: JBPM 4.0 on DB2
              Yeling Shi Newbie

              I tried jbpm4 on DB2 9.5.
              There is no db script for DB2, so you can use hibernate to auto generate the schema.
              The only tweak I did is change mapping definition for class Lob in the jbpm.execution.hbm.xml mapping file,add a length attribute to the BLOB_VALUE column. like this:



              This is for one problem hibernate has with DB2,( or DB2 has with hibernate).
              you can find more information here:
              http://antagonisticpleiotropy.blogspot.com/2008/05/ibm-db2-blobs-hibernate-truncating.html

              After that, everything is fine.

              • 4. Re: JBPM 4.0 on DB2
                Yeling Shi Newbie

                Sorry the code is missing

                <column name="BLOB_VALUE_" length="128000"/></property>
                


                • 5. Re: JBPM 4.0 on DB2
                  Laurent Cornélis Newbie

                  Thank you for all the replies !

                  So I know it's possible, I'll give it a try :)

                  • 6. Re: JBPM 4.0 on DB2
                    Joram Barrez Master

                    I've used jBPM 3.x on DB2 (v 9 I believe) for a large project.

                    No problems at all (only had to change some varchar columns which were too big for the DBA's taste)

                    • 7. Re: JBPM 4.0 on DB2
                      Jonas Hauenstein Newbie

                      Even though this topic is an older one, I just got JBPM 4.1 running on Apache Derby DB2 - after some troubles with data types and mappings on hibernate. I created a corresponding db-setup sql script.



                      create table JBPM4_DEPLOYMENT (
                       DBID_ bigint generated by default as identity (start with 1, increment by 1),
                       NAME_ clob,
                       TIMESTAMP_ bigint,
                       STATE_ varchar(255),
                       primary key (DBID_)
                       );
                      
                       create table JBPM4_DEPLOYPROP (
                       DBID_ bigint generated by default as identity (start with 1, increment by 1),
                       DEPLOYMENT_ bigint,
                       OBJNAME_ varchar(255),
                       KEY_ varchar(255),
                       STRINGVAL_ varchar(255),
                       LONGVAL_ bigint,
                       primary key (DBID_)
                       );
                      
                       create table JBPM4_EXECUTION (
                       DBID_ bigint generated by default as identity (start with 1, increment by 1),
                       CLASS_ varchar(255) not null,
                       DBVERSION_ integer not null,
                       ACTIVITYNAME_ varchar(255),
                       PROCDEFID_ varchar(255),
                       HASVARS_ char(1),
                       NAME_ varchar(255),
                       KEY_ varchar(255),
                       ID_ varchar(255),
                       STATE_ varchar(255),
                       SUSPHISTSTATE_ varchar(255),
                       PRIORITY_ integer,
                       HISACTINST_ bigint,
                       PARENT_ bigint,
                       INSTANCE_ bigint,
                       SUPEREXEC_ bigint,
                       SUBPROCINST_ bigint,
                       PARENT_IDX_ integer,
                       primary key (DBID_),
                       unique (ID_)
                       );
                      
                       create table JBPM4_HIST_ACTINST (
                       DBID_ bigint generated by default as identity (start with 1, increment by 1),
                       CLASS_ varchar(255) not null,
                       DBVERSION_ integer not null,
                       HPROCI_ bigint,
                       TYPE_ varchar(255),
                       EXECUTION_ varchar(255),
                       ACTIVITY_NAME_ varchar(255),
                       START_ timestamp,
                       END_ timestamp,
                       DURATION_ bigint,
                       TRANSITION_ varchar(255),
                       NEXTIDX_ integer,
                       HTASK_ bigint,
                       primary key (DBID_)
                       );
                      
                       create table JBPM4_HIST_DETAIL (
                       DBID_ bigint generated by default as identity (start with 1, increment by 1),
                       CLASS_ varchar(255) not null,
                       DBVERSION_ integer not null,
                       USERID_ varchar(255),
                       TIME_ timestamp,
                       HPROCI_ bigint,
                       HPROCIIDX_ integer,
                       HACTI_ bigint,
                       HACTIIDX_ integer,
                       HTASK_ bigint,
                       HTASKIDX_ integer,
                       HVAR_ bigint,
                       HVARIDX_ integer,
                       MESSAGE_ clob,
                       OLD_INT_ integer,
                       NEW_INT_ integer,
                       OLD_STR_ varchar(255),
                       NEW_STR_ varchar(255),
                       OLD_TIME_ timestamp,
                       NEW_TIME_ timestamp,
                       PARENT_ bigint,
                       PARENT_IDX_ integer,
                       primary key (DBID_)
                       );
                      
                       create table JBPM4_HIST_PROCINST (
                       DBID_ bigint not null,
                       DBVERSION_ integer not null,
                       ID_ varchar(255),
                       PROCDEFID_ varchar(255),
                       KEY_ varchar(255),
                       START_ timestamp,
                       END_ timestamp,
                       DURATION_ bigint,
                       STATE_ varchar(255),
                       ENDACTIVITY_ varchar(255),
                       NEXTIDX_ integer,
                       primary key (DBID_)
                       );
                      
                       create table JBPM4_HIST_TASK (
                       DBID_ bigint not null,
                       DBVERSION_ integer not null,
                       EXECUTION_ varchar(255),
                       OUTCOME_ varchar(255),
                       ASSIGNEE_ varchar(255),
                       PRIORITY_ integer,
                       STATE_ varchar(255),
                       CREATE_ timestamp,
                       END_ timestamp,
                       DURATION_ bigint,
                       NEXTIDX_ integer,
                       SUPERTASK_ bigint,
                       primary key (DBID_)
                       );
                      
                       create table JBPM4_HIST_VAR (
                       DBID_ bigint not null,
                       DBVERSION_ integer not null,
                       PROCINSTID_ varchar(255),
                       EXECUTIONID_ varchar(255),
                       VARNAME_ varchar(255),
                       VALUE_ varchar(255),
                       HPROCI_ bigint,
                       HTASK_ bigint,
                       primary key (DBID_)
                       );
                      
                       create table JBPM4_ID_GROUP (
                       DBID_ bigint generated by default as identity (start with 1, increment by 1),
                       DBVERSION_ integer not null,
                       ID_ varchar(255),
                       NAME_ varchar(255),
                       TYPE_ varchar(255),
                       PARENT_ bigint,
                       primary key (DBID_)
                       );
                      
                       create table JBPM4_ID_MEMBERSHIP (
                       DBID_ bigint generated by default as identity (start with 1, increment by 1),
                       DBVERSION_ integer not null,
                       USER_ bigint,
                       GROUP_ bigint,
                       NAME_ varchar(255),
                       primary key (DBID_)
                       );
                      
                       create table JBPM4_ID_USER (
                       DBID_ bigint generated by default as identity (start with 1, increment by 1),
                       DBVERSION_ integer not null,
                       ID_ varchar(255),
                       PASSWORD_ varchar(255),
                       GIVENNAME_ varchar(255),
                       FAMILYNAME_ varchar(255),
                       BUSINESSEMAIL_ varchar(255),
                       primary key (DBID_)
                       );
                      
                       create table JBPM4_JOB (
                       DBID_ bigint generated by default as identity (start with 1, increment by 1),
                       CLASS_ varchar(255) not null,
                       DBVERSION_ integer not null,
                       DUEDATE_ timestamp,
                       STATE_ varchar(255),
                       ISEXCLUSIVE_ char(1),
                       LOCKOWNER_ varchar(255),
                       LOCKEXPTIME_ timestamp,
                       EXCEPTION_ long varchar,
                       RETRIES_ integer,
                       PROCESSINSTANCE_ bigint,
                       EXECUTION_ bigint,
                       CFG_ bigint,
                       SIGNAL_ varchar(255),
                       EVENT_ varchar(255),
                       REPEAT_ varchar(255),
                       primary key (DBID_)
                       );
                      
                       create table 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_)
                       );
                      
                       create table JBPM4_PARTICIPATION (
                       DBID_ bigint generated by default as identity (start with 1, increment by 1),
                       DBVERSION_ integer not null,
                       GROUPID_ varchar(255),
                       USERID_ varchar(255),
                       TYPE_ varchar(255),
                       TASK_ bigint,
                       SWIMLANE_ bigint,
                       primary key (DBID_)
                       );
                      
                       create table JBPM4_SWIMLANE (
                       DBID_ bigint generated by default as identity (start with 1, increment by 1),
                       DBVERSION_ integer not null,
                       NAME_ varchar(255),
                       ASSIGNEE_ varchar(255),
                       EXECUTION_ bigint,
                       primary key (DBID_)
                       );
                      
                       create table JBPM4_TASK (
                       DBID_ bigint generated by default as identity (start with 1, increment by 1),
                       CLASS_ char(1) not null,
                       DBVERSION_ integer not null,
                       NAME_ varchar(255),
                       DESCR_ clob,
                       STATE_ varchar(255),
                       SUSPHISTSTATE_ varchar(255),
                       ASSIGNEE_ varchar(255),
                       FORM_ varchar(255),
                       PRIORITY_ integer,
                       CREATE_ timestamp,
                       DUEDATE_ timestamp,
                       PROGRESS_ integer,
                       SIGNALLING_ char(1),
                       EXECUTION_ID_ varchar(255),
                       ACTIVITY_NAME_ varchar(255),
                       HASVARS_ char(1),
                       SUPERTASK_ bigint,
                       EXECUTION_ bigint,
                       PROCINST_ bigint,
                       SWIMLANE_ bigint,
                       TASKDEFNAME_ varchar(255),
                       primary key (DBID_)
                       );
                      
                       create table JBPM4_VARIABLE (
                       DBID_ bigint generated by default as identity (start with 1, increment by 1),
                       CLASS_ varchar(255) not null,
                       DBVERSION_ integer not null,
                       KEY_ varchar(255),
                       CONVERTER_ varchar(255),
                       HIST_ char(1),
                       EXECUTION_ bigint,
                       TASK_ bigint,
                       LOB_ bigint,
                       DATE_VALUE_ timestamp,
                       DOUBLE_VALUE_ double,
                       CLASSNAME_ varchar(255),
                       LONG_VALUE_ bigint,
                       STRING_VALUE_ long varchar,
                       TEXT_VALUE_ clob,
                       EXESYS_ bigint,
                       primary key (DBID_)
                       );
                      
                       create index IDX_DEPLPROP_DEPL on JBPM4_DEPLOYPROP (DEPLOYMENT_);
                      
                       alter table JBPM4_DEPLOYPROP
                       add constraint FK_DEPLPROP_DEPL
                       foreign key (DEPLOYMENT_)
                       references JBPM4_DEPLOYMENT;
                      
                       create index IDX_EXEC_SUPEREXEC on JBPM4_EXECUTION (SUPEREXEC_);
                      
                       create index IDX_EXEC_INSTANCE on JBPM4_EXECUTION (INSTANCE_);
                      
                       create index IDX_EXEC_SUBPI on JBPM4_EXECUTION (SUBPROCINST_);
                      
                       create index IDX_EXEC_PARENT on JBPM4_EXECUTION (PARENT_);
                      
                       alter table JBPM4_EXECUTION
                       add constraint FK_EXEC_PARENT
                       foreign key (PARENT_)
                       references JBPM4_EXECUTION;
                      
                       alter table JBPM4_EXECUTION
                       add constraint FK_EXEC_SUBPI
                       foreign key (SUBPROCINST_)
                       references JBPM4_EXECUTION;
                      
                       alter table JBPM4_EXECUTION
                       add constraint FK_EXEC_INSTANCE
                       foreign key (INSTANCE_)
                       references JBPM4_EXECUTION;
                      
                       alter table JBPM4_EXECUTION
                       add constraint FK_EXEC_SUPEREXEC
                       foreign key (SUPEREXEC_)
                       references JBPM4_EXECUTION;
                      
                       create index IDX_HACTI_HPROCI on JBPM4_HIST_ACTINST (HPROCI_);
                      
                       create index IDX_HTI_HTASK on JBPM4_HIST_ACTINST (HTASK_);
                      
                       alter table JBPM4_HIST_ACTINST
                       add constraint FK_HACTI_HPROCI
                       foreign key (HPROCI_)
                       references JBPM4_HIST_PROCINST;
                      
                       alter table JBPM4_HIST_ACTINST
                       add constraint FK_HTI_HTASK
                       foreign key (HTASK_)
                       references JBPM4_HIST_TASK;
                      
                       create index IDX_HDET_HACTI on JBPM4_HIST_DETAIL (HACTI_);
                      
                       create index IDX_HDET_HPROCI on JBPM4_HIST_DETAIL (HPROCI_);
                      
                       create index IDX_HDETAIL_HACTI on JBPM4_HIST_DETAIL (HACTI_);
                      
                       create index IDX_HDETAIL_HVAR on JBPM4_HIST_DETAIL (HVAR_);
                      
                       create index IDX_HDETAIL_HTASK on JBPM4_HIST_DETAIL (HTASK_);
                      
                       create index IDX_HDETAIL_HPROCI on JBPM4_HIST_DETAIL (HPROCI_);
                      
                       create index IDX_HDET_HVAR on JBPM4_HIST_DETAIL (HVAR_);
                      
                       create index IDX_HDET_HTASK on JBPM4_HIST_DETAIL (HTASK_);
                      
                       alter table JBPM4_HIST_DETAIL
                       add constraint FK_HDETAIL_HPROCI
                       foreign key (HPROCI_)
                       references JBPM4_HIST_PROCINST;
                      
                       alter table JBPM4_HIST_DETAIL
                       add constraint FK_HDETAIL_HACTI
                       foreign key (HACTI_)
                       references JBPM4_HIST_ACTINST;
                      
                       alter table JBPM4_HIST_DETAIL
                       add constraint FK_HDETAIL_HTASK
                       foreign key (HTASK_)
                       references JBPM4_HIST_TASK;
                      
                       alter table JBPM4_HIST_DETAIL
                       add constraint FK_HDETAIL_HVAR
                       foreign key (HVAR_)
                       references JBPM4_HIST_VAR;
                      
                       alter table JBPM4_HIST_TASK
                       add constraint FK_HSUPERT_SUB
                       foreign key (SUPERTASK_)
                       references JBPM4_HIST_TASK;
                      
                       create index IDX_HVAR_HPROCI on JBPM4_HIST_VAR (HPROCI_);
                      
                       create index IDX_HVAR_HTASK on JBPM4_HIST_VAR (HTASK_);
                      
                       alter table JBPM4_HIST_VAR
                       add constraint FK_HVAR_HPROCI
                       foreign key (HPROCI_)
                       references JBPM4_HIST_PROCINST;
                      
                       alter table JBPM4_HIST_VAR
                       add constraint FK_HVAR_HTASK
                       foreign key (HTASK_)
                       references JBPM4_HIST_TASK;
                      
                       create index IDX_GROUP_PARENT on JBPM4_ID_GROUP (PARENT_);
                      
                       alter table JBPM4_ID_GROUP
                       add constraint FK_GROUP_PARENT
                       foreign key (PARENT_)
                       references JBPM4_ID_GROUP;
                      
                       create index IDX_MEM_USER on JBPM4_ID_MEMBERSHIP (USER_);
                      
                       create index IDX_MEM_GROUP on JBPM4_ID_MEMBERSHIP (GROUP_);
                      
                       alter table JBPM4_ID_MEMBERSHIP
                       add constraint FK_MEM_GROUP
                       foreign key (GROUP_)
                       references JBPM4_ID_GROUP;
                      
                       alter table JBPM4_ID_MEMBERSHIP
                       add constraint FK_MEM_USER
                       foreign key (USER_)
                       references JBPM4_ID_USER;
                      
                       create index IDX_JOBRETRIES on JBPM4_JOB (RETRIES_);
                      
                       create index IDX_JOB_CFG on JBPM4_JOB (CFG_);
                      
                       create index IDX_JOB_PRINST on JBPM4_JOB (PROCESSINSTANCE_);
                      
                       create index IDX_JOB_EXE on JBPM4_JOB (EXECUTION_);
                      
                       create index IDX_JOBLOCKEXP on JBPM4_JOB (LOCKEXPTIME_);
                      
                       create index IDX_JOBDUEDATE on JBPM4_JOB (DUEDATE_);
                      
                       alter table JBPM4_JOB
                       add constraint FK_JOB_CFG
                       foreign key (CFG_)
                       references JBPM4_LOB;
                      
                       create index IDX_LOB_DEPLOYMENT on JBPM4_LOB (DEPLOYMENT_);
                      
                       alter table JBPM4_LOB
                       add constraint FK_LOB_DEPLOYMENT
                       foreign key (DEPLOYMENT_)
                       references JBPM4_DEPLOYMENT;
                      
                       create index IDX_PART_TASK on JBPM4_PARTICIPATION (TASK_);
                      
                       alter table JBPM4_PARTICIPATION
                       add constraint FK_PART_SWIMLANE
                       foreign key (SWIMLANE_)
                       references JBPM4_SWIMLANE;
                      
                       alter table JBPM4_PARTICIPATION
                       add constraint FK_PART_TASK
                       foreign key (TASK_)
                       references JBPM4_TASK;
                      
                       create index IDX_SWIMLANE_EXEC on JBPM4_SWIMLANE (EXECUTION_);
                      
                       alter table JBPM4_SWIMLANE
                       add constraint FK_SWIMLANE_EXEC
                       foreign key (EXECUTION_)
                       references JBPM4_EXECUTION;
                      
                       create index IDX_TASK_SUPERTASK on JBPM4_TASK (SUPERTASK_);
                      
                       alter table JBPM4_TASK
                       add constraint FK_TASK_SWIML
                       foreign key (SWIMLANE_)
                       references JBPM4_SWIMLANE;
                      
                       alter table JBPM4_TASK
                       add constraint FK_TASK_SUPERTASK
                       foreign key (SUPERTASK_)
                       references JBPM4_TASK;
                      
                       create index IDX_VAR_EXESYS on JBPM4_VARIABLE (EXESYS_);
                      
                       create index IDX_VAR_TASK on JBPM4_VARIABLE (TASK_);
                      
                       create index IDX_VAR_EXECUTION on JBPM4_VARIABLE (EXECUTION_);
                      
                       create index IDX_VAR_LOB on JBPM4_VARIABLE (LOB_);
                      
                       alter table JBPM4_VARIABLE
                       add constraint FK_VAR_LOB
                       foreign key (LOB_)
                       references JBPM4_LOB;
                      
                       alter table JBPM4_VARIABLE
                       add constraint FK_VAR_EXECUTION
                       foreign key (EXECUTION_)
                       references JBPM4_EXECUTION;
                      
                       alter table JBPM4_VARIABLE
                       add constraint FK_VAR_EXESYS
                       foreign key (EXESYS_)
                       references JBPM4_EXECUTION;
                      
                       alter table JBPM4_VARIABLE
                       add constraint FK_VAR_TASK
                       foreign key (TASK_)
                       references JBPM4_TASK;
                      


                      The hbm config would be:
                      <property name="hibernate.dialect">org.hibernate.dialect.DerbyDialect</property>
                       <property name="hibernate.connection.driver_class">org.apache.derby.jdbc.ClientDriver</property>
                       <property name="hibernate.connection.url">jdbc:derby://localhost:1527/jbpm4</property>
                       <property name="hibernate.connection.username">USER</property>
                       <property name="hibernate.connection.password">PW</property>


                      Perhaps, this helps some people with similar problems.

                      • 8. Re: JBPM 4.0 on DB2
                        Sebastian Schneider Master

                        Joram, have you been running DB2 on a mainframe system in this project or on a Windows or Linux system? Same question to you, shiyeling.

                        I'm asking because I know that there are 2 DB/2 Hibernate dialects and I am interested to know if it is possible to use the dialect for the mainframe database.

                        • 9. Re: JBPM 4.0 on DB2
                          Ronald van Kuijk Master

                          I do not know of a jBPM specific case, but I know people used Hibernate on Linux and windows systems (websphere) connecting to a DB/2 database on a mainframe. In addition, you might get a better response when you ask this in the hibernate forum.

                          • 10. Re: JBPM 4.0 on DB2
                            Joram Barrez Master

                            @Sebastian: We were using DB2 on Red Hat on a Blade cluster. For Hibernate we were using the default DB2 dialect.