topic Re: DB2 support in Alfresco Archive

DB2 support

kristin_polenz — Tue, 02 Nov 2010 10:20:48 GMT

When is the DB2 coming? It is possible to get it with the next release?

Re: DB2 support

jbarrez — Tue, 02 Nov 2010 12:05:08 GMT

It's not planned for this release.

It normally shouldnt be a hard job, so contributions are always welcome there 😉

Re: DB2 support

forgetj — Wed, 10 Nov 2010 05:59:54 GMT

I have created a script for DB2, follow this greate article.

1. create databse creation (this forum seems didn't allowed to upload files?), here only attach particular two tables.


create table ACT_RU_EXECUTION (
    ID_ varchar(64)  NOT NULL,
    REV_ integer,
    PROC_INST_ID_ varchar(64),
    BUSINESS_KEY_ varchar(255),
    PARENT_ID_ varchar(64),
    PROC_DEF_ID_ varchar(64),
    SUPER_EXEC_ varchar(64),
    ACT_ID_ varchar(255),
    IS_ACTIVE_ SMALLINT CHECK(IS_ACTIVE_ IN (1,0)),
    IS_CONCURRENT_ SMALLINT CHECK(IS_CONCURRENT_ IN (1,0)),
    IS_SCOPE_ SMALLINT CHECK(IS_SCOPE_ IN (1,0)),
    UNI_BUSINESS_KEY VARCHAR (255)  NOT NULL  GENERATED ALWAYS AS (CASE WHEN "BUSINESS_KEY_" IS NULL THEN "ID_" ELSE "BUSINESS_KEY_" END),
    UNI_PROC_DEF_ID VARCHAR (64)  NOT NULL  GENERATED ALWAYS AS (CASE WHEN "BUSINESS_KEY_" IS NULL THEN "ID_" ELSE "PROC_DEF_ID_" END),
    CONSTRAINT PK_RU_EXECUTION primary key (ID_)
);
create table ACT_HI_PROCINST (
    ID_ varchar(64) not null,
    PROC_INST_ID_ varchar(64) not null,
    BUSINESS_KEY_ varchar(255),
    PROC_DEF_ID_ varchar(64) not null,
    START_TIME_ timestamp not null,
    END_TIME_ timestamp,
    DURATION_ bigint,
    START_USER_ID_ varchar(255),
    START_ACT_ID_ varchar(255),
    END_ACT_ID_ varchar(255),
    UNI_BUSINESS_KEY VARCHAR (255)  NOT NULL  GENERATED ALWAYS AS (CASE WHEN "BUSINESS_KEY_" IS NULL THEN "ID_" ELSE "BUSINESS_KEY_" END),
    UNI_PROC_DEF_ID VARCHAR (64)  NOT NULL  GENERATED ALWAYS AS (CASE WHEN "BUSINESS_KEY_" IS NULL THEN "ID_" ELSE "PROC_DEF_ID_" END),
    CONSTRAINT PK_HI_PROC_INST primary key (ID_),
    CONSTRAINT UQ_PROC_INST_ID unique (PROC_INST_ID_)
); 

create unique index business_key_ru_idx on ACT_RU_EXECUTION
   (UNI_BUSINESS_KEY ,UNI_PROC_DEF_ID);
create unique index business_key_hi_idx on ACT_HI_PROCINST
   (UNI_BUSINESS_KEY ,UNI_PROC_DEF_ID);

2. add DB2 dependency

<dependency>
            <groupId>com.ibm</groupId>
            <artifactId>db2jcc</artifactId>
            <version>3.59.81</version>
        </dependency>

Re: DB2 support

jbarrez — Wed, 10 Nov 2010 07:15:26 GMT

Can you add your create/drop scripts to the following issue: http://jira.codehaus.org/browse/ACT-330
Are there any queries that need to be tweaked to be DB2 specific ?

Furthermore: on which DB2 version is this tested?
I see there is an 'Express' edition of DB2. Is this sufficient for testing the scripts against ?

Re: DB2 support

forgetj — Wed, 10 Nov 2010 07:58:30 GMT

Script added.
Test onB2 v9.7.200.358 Enterprise Edition.
i think this also apply to DB2-Express. http://www.ibm.com/developerworks/data/library/techarticle/0301zikopoulos/0301zikopoulos1.html

Re: DB2 support

jbarrez — Fri, 12 Nov 2010 08:20:38 GMT

I assigned the issue to me.
Thanks for the patch!

I see that you mention that the MySQL specific sql also applies to DB2. Do you mean I can just reuse those (all)?

I'll try to set up a db2 express as soon as I find the time on our qa machine http://178.77.67.242:8080/, and test the scripts.

Re: DB2 support

forgetj — Sat, 13 Nov 2010 06:48:42 GMT

yes, i just reuse the Mysql specific sql and run "mvn –Ddatabase=db2 clean install", all test passed.

Re: DB2 support

jackdd — Fri, 04 Mar 2011 07:33:31 GMT

We are playing around with DB2 support in Activiti 5.3. There is a problem with DB2 9.72 Express Version on Windows x64. The database name is not identified correctly. DB2 is special here, so additional database type mappings are needed. Every different DB2 version has its own product name. We found the problematic code in class ProcessEngineConfigurationImpl and method getDefaultDatabaseTypeMappings().

Please add the following type mappings. These mappings are used in IBM WebSphere Application Server 7.0:

databaseTypeMappings.setProperty("DB2","db2");
databaseTypeMappings.setProperty("DB2/NT","db2");
databaseTypeMappings.setProperty("DB2/NT64","db2");
databaseTypeMappings.setProperty("DB2 UDP","db2");
databaseTypeMappings.setProperty("DB2/LINUX","db2");
databaseTypeMappings.setProperty("DB2/LINUX390","db2");
databaseTypeMappings.setProperty("DB2/400 SQL","db2");
databaseTypeMappings.setProperty("DB2/6000","db2");
databaseTypeMappings.setProperty("DB2 UDB iSeries","db2");
databaseTypeMappings.setProperty("DB2/AIX64","db2");
databaseTypeMappings.setProperty("DB2/HPUX","db2");
databaseTypeMappings.setProperty("DB2/HP64","db2");
databaseTypeMappings.setProperty("DB2/SUN","db2");
databaseTypeMappings.setProperty("DB2/SUN64","db2");
databaseTypeMappings.setProperty("DB2/PTX","db2");
databaseTypeMappings.setProperty("DB2/2","db2");

If you don't want to add all these mapping, use sample code below. We use that code in our application:

if(databaseMetaData.getDatabaseProductName().startsWith("DB2"))
{
// is DB2 database
}

Re: DB2 support

jbarrez — Fri, 04 Mar 2011 15:55:58 GMT

Thanks for posting this. I will make sure it ends up in next release.