How to set-up DB2 with WSO2 BPS

System Requirements

• DB2 Express-C 10.5. This can be downloaded over here.
• Ubuntu 13.10
• WSO2 BPS 3.5. This can be downloaded over here .

DB2 database can be installed by following the instructions given from this post. But unfortunately since we are installing a non licensed version of DB2, we won't be able to create the sample database mentioned in the blog post using the db2fs command. 

You can access the database by accessing as the db2 instance user. 

                        su - db2inst1

Step 1 - Create the databases

In order to view the available db2 database directories, you can use the following command 

                      db2 list database directory

Now we can create the necessary databases and assign necessary privileges for them to connect BPS with DB2 server. You have to create 4 such databases like bps_db, bpmn_db, reg_db and user_db.

We are going to create non restricted databases. Thus won't be having any permission issues.

                     db2 create database bps_db

                     db2 activate db bps_db 

Once the database get activated, we need to get connected with the database to perform operations on it.

                     db2 connect to bps_db

Step 2 - Creating the temporary system table spaces

You have to create temporary system table spaces for each database. This system table space stores internal temporary data required during SQL operations such as sorting, reorganizing tables, creating indexes, and joining tables. 

You have to create system tables paces with page size 4K, 8K, 16K and 32K. In addition buffer pool has to be created with the same size for each table space. Therefore in abstract, you have to create 4 buffer pools and 4 table spaces.

Creating a buffer pool with the size 4k

db2 "CREATE BUFFERPOOL STB_4_POOL PAGESIZE 4K"

Creating a system table space with the size 4k

db2 "CREATE SYSTEM TEMPORARY TABLESPACE STB_4 PAGESIZE 4K BUFFERPOOL STB_4_POOL"

More information regarding system table space and buffer pool can be found here

Step 3 - Obtaining the port number used in DB2

By default DB2 operates on port number 50000. In order to ensure the working port number of the DB2, we can execute following commands.

         db2 "get dbm cfg"|grep -i svce

This command will produce following output. 

         TCP/IP Service name                          (SVCENAME) = db2c_db2inst1

now you have to look for the port number in /etc/services. You can find a record like this

         db2c_db2inst1   50000/tcp

The above record defines the tcp port for the DB2 service which is 50000 in this case. 

Step 4 - Configuring the datasources in wso2 bps

Business Process Server 3.5.0 has to be configured with data-source properties files. These files can be found inside /repository/conf/datasources folder. Further configuration can be found here. 

I have copied the sample data-source configuration over here. 

       

Next we have to start the BPS server with -Dsetup option. This will create the tables. If not tables can be created by executing the db2 which can be found in dbscripts

Step 5 - Make sure the tables have necessary privileges

The tables should have the necessary privileges to "insert, update, delete and select". If we have created the database in restricted mode, we have to run the following commands to grant permission to the  tables.

First we have to dynamically generate command to grant privileges. Following script will dynamically generate the SQL and will write it in to the bps.sql file.

db2 "select 'grant insert, update, delete on table ' || trim(tabschema) || '.' || trim(tabname) || ' to user db2inst1;' from syscat.tables where type = 'T'" > bps.sql

Now open the bps.sql and remove the unwanted terms in the file ( in the top and bottom ).

This file will be heaving sql scripts like this 

               grant insert, update, delete on table SYSIBM.SYSDBAUTH to user db2inst1;

Next execute the file 

              db2 -tvsf bps.sql

Dropping the database in DB2

Following commands should be executed prior to dropping a database in DB2.

                      db2 disconnect all

                      db2 force applications all

                      db2 deactivate db  bps_db

                      db2 drop database BPS_DB

Note 

• WSO2 BPS 3.5 db2 scripts can be found inside the pack. ( BPS_HOME/dbscripts/bps/)