- 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
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
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.
- WSO2 BPS 3.5 db2 scripts can be found inside the pack. ( BPS_HOME/dbscripts/bps/)