DB2 z/OS support

[pero]

This kind of query is not supported by DB2 version 9.1 for z/OS database:

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 "PROC_DEF_ID_" is null then "ID_" else "PROC_DEF_ID_" end),
    primary key (ID_)
);‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The problem is that "case" expression is not supported under "generated always as" for this DB2 version.
I see that ACT_RU_EXECUTION table for other databases(postgres) doesn't contain UNI_BUSINESS_KEY and UNI_PROC_DEF_ID columns at all.
What if we don't define those columns on our db2? Will activiti still work?

Is there some other solution to this problem possibly?

Thanks.

[jbarrez]

Yes - Activiti will still work.

The only 'problem' which you will have, is that there will be no check on the uniqueness of the process instance business key - which means you will have to force that uniqueness in the logic of your app.

[pero]

I found solution to the problem.

On db2 for z/os you can define folowing unique index

create unique where not null index ACT_UNIQ_RU_BUS_KEY on ACT_RU_EXECUTION(PROC_DEF_ID_, BUSINESS_KEY_);
After that you can remove UNI_BUSINESS_KEY and UNI_PROC_DEF_ID columns from the ACT_RU_EXECUTION table definition.

Same goes for ACT_HI_PROCINST table too.

[pero]

I've ran into some more issues with activiti on DB2 for Z/OS.

Insert queries in MyBatis mappign files are not properly formatted.

For example:
This is not working:

insert into ACT_ID_USER (ID_, REV_, FIRST_, LAST_, EMAIL_, PWD_)
    values (
      #{id ,jdbcType=VARCHAR},
      1,
      #{firstName ,jdbcType=VARCHAR},
      #{lastName ,jdbcType=VARCHAR},
      #{email ,jdbcType=VARCHAR},
      #{password ,jdbcType=VARCHAR}
    )

Returns illegal syntax error.
But this is ok:


insert into ACT_ID_USER (ID_, REV_, FIRST_, LAST_, EMAIL_, PWD_)
values ( #{id ,jdbcType=VARCHAR}, 1, #{firstName ,jdbcType=VARCHAR}, #{lastName ,jdbcType=VARCHAR}, #{email ,jdbcType=VARCHAR}, #{password ,jdbcType=VARCHAR} )

I have opened a jira issue with this problems
http://jira.codehaus.org/browse/ACT-817

[sebastian_s]

I just commented the JIRA:

I found this JIRA issue and the following forum post today when searching the forums for DB2-related topics. IMHO it woulnd't hurt to change the formatting of the query mentioned above nor would it break anything. But it would give people the opportunity to use Activiti with DB2 on z/OS even it is not supported officially.

[jbarrez]

So am I right: it would involved change *all* queries to the single line format?

[bell]

Are there or has there been any changes to officailly support DB2 on z/OS?

[trademak]

Hi,

No we don't support DB2 on z/OS. The main reason is that we don't have a decent QA environment to test against DB2 on z/OS.

Best regards,