Running WSO2 API Manager with IBM DB2 database

WSO2 is recently identified as a leader in the API Management solutions in the market by the Forrester Wave. Refer here to get the Forrester Wave report. WSO2 API Manager is a 100% open source product that provides capabilities to fully manage your APIs, including authentication, authorization, throttling among many others.

This post is about running the product with IBM DB2. To successfully run WSO2 products, we need to have databases configured and populated with the required schema. By default, WSO2 ships a file based database, namely H2. To try out the product you don’t have to configure and setup any databases. It is just a matter of downloading the product and running. But in production environments, WSO2 recommends you choose a proper RDBMS to host the required schemas and make WSO2 products connect with it.

WSO2 can work with any database that has a JDBC connector. Popular choices are Oracle, Microsoft SQL Server and MySQL. At times, we in WSO2 also get queries from the community and potential customers alike on the possibility to run the product with IBM DB2. As you have probably guessed, WSO2 products are capable of working with the IBM DB2.

Let’s look at how you can quickly setup WSO2 API Manager with IBM DB2.

Download the latest API Manager (2.6.0) here. Once downloaded, unzip it and navigate to [API-HOME]/dbscripts directory. You should find db2.sql in it for CARBON DB and another db2.sql inside the apimgt directory for APIM DB. If you look at mb-store and the metric directory you won’t find corresponding db2.sql, but you find scripts for other database types. WSO2 may come up with DB2 specific scripts for these MB STORE and METRICS databases, but it is not necessarily needed. Since these databases are never meant to be shared among API Manager components, we can leave it with the H2 database. Major data storage operations happen in the CARBON DB and the APIM DB. MB STORE is mainly used by the traffic manager component, which should be local to it. METRIC DB is used by the running JVM to store metrics about the JVM. Thus it should be local to the JVM too. So leaving it with H2 is fine.

Let’s dive into IBM DB2. IBM DB2 is not a freely available database. Fortunately, there is an express edition which you can download from the IBM web site. The same is also available as a docker image which you can find it here. We are going to use the docker image to run it locally.

You need to have docker installed. Refer https://docs.docker.com/install/ on how to get docker installed.

Let’s create a docker volume to start with. This is needed to persist the databases that we will create within the docker instance later on.

docker volume create db2

Let’s run the docker image with the volume mounted to the home directory of the docker instance (/home/db2inst1). Run the below command. Note you have to provide a password for the DB2INST1 default user. Make a note of it as we’ll need this password to configure the datasources and access the DBs from a database client.

docker run -it -p 50000:50000 -e DB2INST1_PASSWORD=your_password -e LICENSE=accept -v db2:/home/db2inst1/ ibmcom/db2express-c:latest bash

Once you run the command you’ll be in the root prompt. Execute following commands.

[root@967609dd6676 /]# su - db2inst1
Last login: Wed May 20 21:57:28 UTC 2015
[db2inst1@967609dd6676 ~]$ db2start
SQL1063N  DB2START processing was successful.
[db2inst1@967609dd6676 ~]$ db2sampl

First command switches the current user prompt to that of db2inst1. Next command starts the database. The last command will create a SAMPLE database.

Let’s create the CARBON DB and APIM DB for the WSO2 API Manager. Issue the db2 command to start with and follow the commands as shown below.

[db2inst1@967609dd6676 ~]$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 10.5.5
You can issue database manager commands and SQL statements from the command 
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside 
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 => create database carbondb
db2 => connect to carbondb
db2 => create schema DB2INST1
db2 => set schema DB2INST1
db2 => disconnect carbondb
db2 => create database apimdb
db2 => connect to apimdb
db2 => create schema DB2INST1
db2 => set schema DB2INST1

The DB2 commands basically create databases named carbondb and apimdb and sets schema as DB2INST1. Note these databases are also now mounted to the docker volume that we created earlier. Thus they are persisted.

Now let’s use a database client to connect to these databases. I used DB Visualizer trial version for this. Use the connection wizard to create a connection to carbondb with following properties.

Driver : DB2
Database Server : localhost
Database Port : 50000
Database : carbondb
Database Userid : DB2INST1
Database password : <password_you_noted_earlier>

To create a connection to APIM DB, all the properties remain same except Database change as apimdb.

Once the connection is established, navigate to the DB2INST1 schema of carbondb connection and create a new query editor and copy and paste db2.sql from dbscripts directory from the API_HOME. This script contains some TRIGGER statements which uses semicolon (;) within BEGIN ATOMIC block which makes the statement to end. That will be problematic to run as it is in the DB Visualizer. If you see closely, all the statements are ending with forward slash (/), hence it needs to be the end of the statement in the query. It can be configured in the DB Visualizer by navigating to Tools -> Tool Properties -> General Tab -> SQL Commander -> Statement Delimiters and set the SQL Statement Delimiter 1 as forward slash (/) and SQL Statement Delimiter 2 as something like # (Nether of this should be a semicolon). After applying the changes, you should be able to run the script and it will create the necessary tables. Follow the same instructions to populate the APIM DB, but choosing the db2.sql from the APIM_HOME/dbscripts/apimgt.

Now the database is ready. Let’s configure the API Manager.

Configure the datasources in the API_HOME/repository/conf/datasources/master-datasources.xml.

<datasource>
    <name>WSO2_CARBON_DB</name>
    <description>The datasource used for registry and user manager</description>
    <jndiConfig>
        <name>jdbc/WSO2CarbonDB</name>
    </jndiConfig>
    <definition type="RDBMS">
        <configuration>
            <url>jdbc:db2://localhost:50000/carbondb</url>
            <username>DB2INST1</username>
            <password>your_password</password>
         <driverClassName>com.ibm.db2.jcc.DB2Driver</driverClassName>
            <maxActive>50</maxActive>
            <maxWait>60000</maxWait>
            <testOnBorrow>true</testOnBorrow>
            <validationQuery>SELECT 1 FROM sysibm.sysdummy1</validationQuery>
            <validationInterval>30000</validationInterval>
            <defaultAutoCommit>true</defaultAutoCommit>
        </configuration>
    </definition>
</datasource>
<datasource>
    <name>WSO2AM_DB</name>
    <description>The datasource used for API Manager database</description>
    <jndiConfig>
        <name>jdbc/WSO2AM_DB</name>
    </jndiConfig>
    <definition type="RDBMS">
        <configuration>
            <url>jdbc:db2://localhost:50000/apimdb</url>
            <username>DB2INST1</username>
            <password>your_password</password>
            <defaultAutoCommit>true</defaultAutoCommit>
         <driverClassName>com.ibm.db2.jcc.DB2Driver</driverClassName>
            <maxActive>50</maxActive>
            <maxWait>60000</maxWait>
            <testOnBorrow>true</testOnBorrow>
            <validationQuery>SELECT 1 FROM sysibm.sysdummy1</validationQuery>
            <validationInterval>30000</validationInterval>
        </configuration>
    </definition>
</datasource>

You also need to copy the db2jcc.jar, db2jcc_license_cu.jar into the API_HOME/repository/components/lib directory. These jar files can be found in the /home/db2inst1/sqllib/java folder of the docker instance.

That’s it. Go to API_HOME/bin and start the API Manager with wso2server script. It should start successfully and now you can play around the product all with IBM DB2. Enjoy.