IBM BPM - Using the SIBDDLGenerator command with DB2 LUW 9.7

I'm writing this down for future reference, as I know that I'll need it in the future.

IBM Business Process Manager ( and IBM Business Monitor ) need a set of database tables for the messaging engines that both products make heavy use of. Actually, a number of other products, including WebSphere Portal, can also make use of these: -

Creating a JMS resource on DB2 for WebSphere Portal V7.0.0.x running on z/OS

The product includes a rather useful little tool, sibddlgenerator, which can be used to generate the database definition language (DDL) for the database product of your choice.

As an example, I'm using DB2 Enterprise Server Edition 9.7.0.5 on Red Hat Enterprise Linux 6.3. This is the database that hosts the tables etc. for my IBM Business Process Manager Advanced 8.0.1 installation.

So here's the syntax for the command: -

$ cd /opt/IBM/WebSphere/AppServer/profiles/PCDMProfile/bin

$ ./sibDDLGenerator.sh -help

CWSIS1539I: sibDDLGenerator [-system <DBMS name>] [-version <DBMS version>] [-platform <DBMS platform>] [-schema <schema>] [-user <user>] [-create|-drop] [-database <database> (z/OS only)][-createdbstmt <true|false> (z/OS only)][-tablespaceprefix <tablespace prefix, maximum 5 characters> (z/OS only)][-storagegroup <storage group> [-catalog <high-level qualifier>] (z/OS only)] [-bufferpool <buffer pool> (z/OS only)] [-statementend <terminator>] [-noblanklines] [-nolinebreaks] [-firstline <statements>] [-lastline <statements>] [-permanent <number of permanent tables>] [-temporary <number of temporary tables>]

So I wanted to create a set of tables, for the medbpe00 schema, so I executed the following command: -

$ ./sibDDLGenerator.sh -system db2 -version 9.7 -platform linux -statementend \; -schema medbpe00 -user db2inst1

but this failed with: -

CWSIS1595E: An invalid combination of parameters has been specified, Database: db2, version: 9.7, platform: linux . Refer to the documentation for the correct parameters for your database.

Thinking laterally … hmmmm, well Linux is kinda like Unix ( albeit it's GNU - Gnu (is) Not Unix ) so I tried this instead: -

$ ./sibDDLGenerator.sh -system db2 -version 9.7 -platform unix -statementend \; -schema medbpe00 -user db2inst1

which did the trick, and returned: -

CREATE SCHEMA medbpe00;

CREATE TABLE medbpe00.SIBOWNER (
  ME_UUID VARCHAR(16),
  INC_UUID VARCHAR(16),
  VERSION INTEGER,
  MIGRATION_VERSION INTEGER
);

CREATE TABLE medbpe00.SIBOWNERO (
   EMPTY_COLUMN INTEGER
);

CREATE TABLE medbpe00.SIBCLASSMAP (
  CLASSID INTEGER NOT NULL,
  URI VARCHAR(2048) NOT NULL,
  PRIMARY KEY(CLASSID)
);

CREATE TABLE medbpe00.SIBLISTING (
  ID INTEGER NOT NULL,
  SCHEMA_NAME VARCHAR(10),
  TABLE_NAME VARCHAR(10) NOT NULL,
  TABLE_TYPE CHAR(1) NOT NULL,
  PRIMARY KEY(ID)
);

CREATE TABLE medbpe00.SIB000 (
  ID BIGINT NOT NULL,
  STREAM_ID BIGINT NOT NULL,
  TYPE CHAR(2),
  EXPIRY_TIME BIGINT,
  STRATEGY INTEGER,
  REFERENCE BIGINT,
  CLASS_ID INTEGER NOT NULL,
  PRIORITY INTEGER,
  SEQUENCE BIGINT,
  PERMANENT_ID INTEGER,
  TEMPORARY_ID INTEGER,
  LOCK_ID BIGINT,
  DATA_SIZE INTEGER NOT NULL,
  DATA VARCHAR(3360) FOR BIT DATA,
  LONG_DATA BLOB(1G),
  XID VARCHAR(254),
  DELETED SMALLINT,
  PRIMARY KEY(ID)
);

CREATE INDEX medbpe00.SIB000STREAMIX ON
medbpe00.SIB000(STREAM_ID,SEQUENCE) ALLOW REVERSE SCANS;

ALTER TABLE medbpe00.SIB000 VOLATILE CARDINALITY;

CREATE TABLE medbpe00.SIB001 (
  ID BIGINT NOT NULL,
  STREAM_ID BIGINT NOT NULL,
  TYPE CHAR(2),
  EXPIRY_TIME BIGINT,
  STRATEGY INTEGER,
  REFERENCE BIGINT,
  CLASS_ID INTEGER NOT NULL,
  PRIORITY INTEGER,
  SEQUENCE BIGINT,
  PERMANENT_ID INTEGER,
  TEMPORARY_ID INTEGER,
  LOCK_ID BIGINT,
  DATA_SIZE INTEGER NOT NULL,
  DATA VARCHAR(3360) FOR BIT DATA,
  LONG_DATA BLOB(1G),
  XID VARCHAR(254),
  DELETED SMALLINT,
  PRIMARY KEY(ID)
);

CREATE INDEX medbpe00.SIB001STREAMIX ON
medbpe00.SIB001(STREAM_ID,SEQUENCE) ALLOW REVERSE SCANS;

ALTER TABLE medbpe00.SIB001 VOLATILE CARDINALITY;

CREATE TABLE medbpe00.SIB002 (
  ID BIGINT NOT NULL,
  STREAM_ID BIGINT NOT NULL,
  TYPE CHAR(2),
  EXPIRY_TIME BIGINT,
  STRATEGY INTEGER,
  REFERENCE BIGINT,
  CLASS_ID INTEGER NOT NULL,
  PRIORITY INTEGER,
  SEQUENCE BIGINT,
  PERMANENT_ID INTEGER,
  TEMPORARY_ID INTEGER,
  LOCK_ID BIGINT,
  DATA_SIZE INTEGER NOT NULL,
  DATA VARCHAR(3360) FOR BIT DATA,
  LONG_DATA BLOB(1G),
  XID VARCHAR(254),
  DELETED SMALLINT,
  PRIMARY KEY(ID)
);

CREATE INDEX medbpe00.SIB002STREAMIX ON
medbpe00.SIB002(STREAM_ID,SEQUENCE) ALLOW REVERSE SCANS;

ALTER TABLE medbpe00.SIB002 VOLATILE CARDINALITY;

CREATE TABLE medbpe00.SIBXACTS (
  XID VARCHAR(254) NOT NULL,
  STATE CHAR(1) NOT NULL,
  PRIMARY KEY(XID)
);

CREATE TABLE medbpe00.SIBKEYS (
  ID VARCHAR(50) NOT NULL,
  LAST_KEY BIGINT NOT NULL,
  PRIMARY KEY(ID)
);

GRANT SELECT,INSERT,UPDATE ON medbpe00.SIBOWNER TO db2inst1;

GRANT SELECT,INSERT,UPDATE ON medbpe00.SIBOWNERO TO db2inst1;

GRANT SELECT,INSERT ON medbpe00.SIBCLASSMAP TO db2inst1;

GRANT SELECT,INSERT ON medbpe00.SIBLISTING TO db2inst1;

GRANT SELECT,INSERT,DELETE,UPDATE ON medbpe00.SIB000 TO db2inst1;

GRANT SELECT,INSERT,DELETE,UPDATE ON medbpe00.SIB001 TO db2inst1;

GRANT SELECT,INSERT,DELETE,UPDATE ON medbpe00.SIB002 TO db2inst1;

GRANT SELECT,INSERT,UPDATE,DELETE ON medbpe00.SIBXACTS TO db2inst1;

GRANT SELECT,INSERT,UPDATE ON medbpe00.SIBKEYS TO db2inst1;

Now, for BPM, we need two sets of tables, one for schema MEDBPE00 and one for schema MEDBPS00, so I ran the commands twice, as follows: -

$ ./sibDDLGenerator.sh -system db2 -version 9.7 -platform unix -statementend \; -schema medbpe00 -user db2inst1 -database medb > ~/createMEDBPEtables.sql

$ ./sibDDLGenerator.sh -system db2 -version 9.7 -platform unix -statementend \; -schema medbps00 -user db2inst1 -database medb > ~/createMEDBPStables.sql

generating a .SQL file in each case.

Thanks to Bob for his inspiration with this post: -

BPM Version 8 Standard Edition Databases

*UPDATE* 10 January 2013

I made a slight typo above when I specified -schema medbpe00 and -schema medbps00. I did, of course, mean to write medpe00 and medps00 as per the following: -

$ ./sibDDLGenerator.sh -system db2 -version 9.7 -platform unix -statementend \; -schema medpe00 -user db2inst1 -database medb > ~/createMEDBPEtables.sql

$ ./sibDDLGenerator.sh -system db2 -version 9.7 -platform unix -statementend \; -schema medps00 -user db2inst1 -database medb > ~/createMEDBPStables.sql

With the wrong schema names, the Messaging cluster will not start, which is somewhat annoying.