Sunday, February 14, 2016

A Simple Guide for Installing IBM DB2 and Running with WSO2 Servers

WSO2 servers are compatible with many well known database products such as Oracle, MySQL, Microsoft SQL Server, IBM DB2, PostgreSQL etc.  This is a quick start guide for you to setup IBM DB2 and use a DB2 database to run WSO2 products. In this post I am installing the IBM DB2 Express-C edition (free) on Linux (Ubuntu 15.04) and run the WSO2 Identity Server along with the DB2 database.

I have downloaded the IBM DB2 Express-C and once the zip file is extracted, I can find the db2setup executable file which can be run for GUI based installation.

Make sure that you run the installation from the root user because if you install as a non root user, you will run into several issues which would need several configuration changes.


Once you run the db2setup script, it will open the installation GUI.


Select the ‘Install a Product’ link from the left hand side panel and click the ‘Install Now’ button.


Continue the setup by clicking ‘Next’ in the following window as well.


Accept the License Agreement and click ‘Next’. 


Here I am using the Typical installation as this is just for demonstration purpose.


Here I install the DB2 Express-C and also save the response file.


I am installing the DB2 to the default file path. If you need to change it, you can specify a different file path. 


Unlike in other database products, IBM DB2 needs the Operating System level users to manage the database operations. (You can refer [1] to know about the different types of user groups needed for IBM DB2)

Here I am selecting the ‘New user’ option which would create a new user. For the DB2 Administration Server, it will suggest the uesrname like dausr1. In my operating system I already have a user named dausr1 so here it suggests dausr2 as the username. Define a password for this user and continue.



Next step is creating the database instance. 


As the DB2 Instance Owner we need another operating system user. It will suggest the username db2inst1 for this. (Here I already have a user with name db2inst1 so it has suggested db2inst2 for the username) These usernames can be changed upon your requirements. 


Next step is to create the Fenced User. The user defined functions and stored procedures will  execute under this user and group. The default suggested username would be db2fenc1. (Here I already have a user with name db2fenc1 so it has suggested db2fenc2).


Note that you can create these users separately by following [1] and choose corresponding users in the installation wizard instead of creating new users.

Then you will see a summary of the installation that is about to happen. 


Following are the details of my installation.

                                      
Product to install:                        DB2 Express-C
Installation type:                         Typical
                                     
Previously Installed Components:      
                                     
Selected Components:                  
    Base client support                   
    Java support                          
    SQL procedures                        
    Base server support                   
    DB2 data source support               
    DB2 LDAP support                      
    DB2 Instance Setup wizard             
    Integrated Flash Copy Support         
    Communication support - TCP/IP        
    DB2 Update Service                    
    Sample database source                
    First Steps                           
                                     
Languages:                            
    English                               
       All Products                      
                                     
Target directory:                          /opt/ibm/db2/V10.5
                                     
Space required:                            945 MB
                                     
New instances:                        
    Instance name:                         db2inst2
       Start instance on reboot:          Yes
       TCP/IP configuration:             
           Service name:                  db2c_db2inst2
           Port number:                   50000
       Instance user information:        
           User name:                     db2inst2
           Group name:                    db2iadm1
           Home directory:                /home/db2inst2
       Fenced user information:          
           User name:                     db2fenc2
           Group name:                    db2fadm1
           Home directory:                /home/db2fenc2
                                     
                                     
DB2 Administration server:            
    Instance user information:            
       User name:                         dasusr2
       Group name:                        dasadm1
       Home directory:                    /home/dasusr2
                                     
                                     
                                     
Contact specification:                
    New contacts:                         
       Name:                              db2inst2
       Instance:                          db2inst2
       E-mail address:                    [email protected]
       E-mail address is for a pager:     No
                                     
                                     
Response file name:                        /root/db2expc.rsp


Then it will show the progress of the installation.


Once the installation is successful, you will see the post installation steps.

These are the instructions I have got. I can see that the DB2 instance name is db2inst2 and DB2 is running on port 50000.

Required steps:

In order to start using DB2 you need to logon using a valid user ID such as the DB2 instance owner's ID "db2inst2".

You can connect to the DB2 instance "db2inst2" using the port number "50000". Record it for future reference.

Optional steps:

To validate your installation files, instance, and database functionality, run the Validation Tool, /opt/ibm/db2/V10.5/bin/db2val. For more information, see "db2val" in the DB2 Information Center.

Open First Steps by running "db2fs" using a valid user ID such as the DB2 instance owner's ID. You will need to have DISPLAY set and a supported web browser in the path of this user ID.

You should ensure that you have the correct license entitlements for DB2 products and features installed on this machine. Each DB2 product or feature comes with a license certificate file (also referred to as a license key) that is distributed on an Activation CD, which also includes instructions for applying the license file. If you purchased a base DB2 product, as well as, separately priced features, you might need to install more than one license certificate. The Activation CD for your product or feature can be downloaded from Passport Advantage if it is not part of the physical media pack you received from IBM. For more information about licensing, search the Information Center (http://publib.boulder.ibm.com/infocenter/db2luw/v10r5/index.jsp) using terms such as "license compliance", "licensing" or "db2licm".

To use your DB2 database product, you must have a valid license. For information about obtaining and applying DB2 license files, see  http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.qb.server.doc/doc/c0061199.html.

Refer to "What's New" http://publib.boulder.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.wn.doc/doc/c0052035.html in the DB2 Information Center to learn about the new functions for DB2 10.5.

Verify that you have access to the DB2 Information Center based on the choices you made during this installation. If you performed a typical or a compact installation, verify that you can access the IBM Web site using the internet. If you performed a custom installation, verify that you can access the DB2 Information Center location specified during the installation.

Review the response file created at /root/db2expc.rsp.  Additional information about response file installation is available in the DB2 documentation under "Installing DB2 using a response file".


You can also view the log file of the installation. 

Here is the log file I have got.

Checking license agreement acceptance :.......Success
Installing DB2 file sets :.......Success
Executing control tasks :.......Success
Updating global registry :.......Success
Starting DB2 Fault Monitor :.......Success
Updating the db2ls and db2greg link :.......Success
Registering DB2 licenses :.......Success
Setting default global profile registry variables :.......Success
Creating the DB2 Administration Server :.......Success
Initializing instance list :.......Success
Configuring DB2 instances :.......Success
Registering DB2 Update Service :.......Success
Configuring the DB2 Administration Server :.......Success
Updating global profile registry :.......Success
Configuring health alert notifications :.......Success

============================================================

DB2 Setup log file started at:  Mon Feb 15 08:31:27 AM 2016 IST
============================================================

Operating system information: Linux 3.19.0-44-generic.#50-Ubuntu SMP Mon Jan 4 18:37:30 UTC 2016 x86_64
                                     
Product to install:                        DB2 Express-C
Installation type:                         Typical
                                     
Previously Installed Components:      
                                     
Selected Components:                  
    Base client support                   
    Java support                          
    SQL procedures                        
    Base server support                   
    DB2 data source support               
    DB2 LDAP support                      
    DB2 Instance Setup wizard             
    Integrated Flash Copy Support         
    Communication support - TCP/IP        
    DB2 Update Service                    
    Sample database source                
    First Steps                           
                                     
Languages:                            
    English                               
       All Products                      
                                     
Target directory:                          /opt/ibm/db2/V10.5
                                     
Space required:                            945 MB
                                     
New instances:                        
    Instance name:                         db2inst2
       Start instance on reboot:          Yes
       TCP/IP configuration:             
           Service name:                  db2c_db2inst2
           Port number:                   50000
       Instance user information:        
           User name:                     db2inst2
           Group name:                    db2iadm1
           Home directory:                /home/db2inst2
       Fenced user information:          
           User name:                     db2fenc2
           Group name:                    db2fadm1
           Home directory:                /home/db2fenc2
                                     
                                     
DB2 Administration server:            
    Instance user information:            
       User name:                         dasusr2
       Group name:                        dasadm1
       Home directory:                    /home/dasusr2
                                     
                                     
                                     
Contact specification:                
    New contacts:                         
       Name:                              db2inst2
       Instance:                          db2inst2
       E-mail address:                    [email protected]
       E-mail address is for a pager:     No
                                     
                                     
Response file name:                        /root/db2expc.rsp

Checking license agreement acceptance :.......Success
Installing: BASE_CLIENT_R

Installing: DB2_PRODUCT_MESSAGES_EN

Installing: BASE_CLIENT

Installing: JAVA_RUNTIME_SUPPORT

Installing: DB2_JAVA_HELP_EN

Installing: BASE_DB2_ENGINE_R

Installing: GSK

Installing: JAVA_SUPPORT

Installing: SQL_PROCEDURES

Installing: ICU_SUP

Installing: JAVA_COMMON_FILES

Installing: BASE_DB2_ENGINE

Installing: DB2_DATA_SOURCE_SUPPORT

Installing: LDAP_EXPLOITATION

Installing: INSTANCE_SETUP_SUPPORT

Installing: ACS

Installing: COMMUNICATION_SUPPORT_TCPIP

Installing: DB2_UPDATE_SERVICE

Installing: EDB

Installing: DB2_SAMPLE_DATABASE

Installing: CLPPLUS

Installing: FIRST_STEPS

Installing: EXPC_PRODUCT_SIGNATURE

Installing DB2 file sets :.......Success
Executing control tasks :.......Success
Updating global registry :.......Success
Starting DB2 Fault Monitor :.......Success
Updating the db2ls and db2greg link :.......Success
Registering DB2 licenses :.......Success
The value "DB2_COMPATIBILITY_VECTOR=MYS" was set in the Profile Registry.

Setting default global profile registry variables :.......Success
Creating the DB2 Administration Server :.......Success
Initializing instance list :.......Success
The instance "db2inst2" has been created successfully.

The value "SVCENAME=db2c_db2inst2" was set in the DBM CFG file for the
"db2inst2" instance.

The value "DB2AUTOSTART=YES" was set in the Profile Registry for the "db2inst2"
instance.

Configuring DB2 instances :.......Success
Registering DB2 Update Service :.......Success
Configuring the DB2 Administration Server :.......Success
Updating global profile registry :.......Success
The contact "db2inst2" was successfully added.

Configuring health alert notifications :.......Success


Now I have successfully installed IBM DB2. In order to connect to the database, I can switch the user to the DB2 instance owner’s account. The username of my instance owner (specified during the installation) is db2inst2 and I switch user with the command ‘su db2inst2’ in the linux terminal.

In this user’s HOME directory, I can see a folder with the name sqllib is created. Inside that the bin folder is there which contains the db2 executable files.


Next step is to create a database. The command for creating a database is as following.

db2 create database <database name> automatic storage yes

Here, even if we do not specify the ‘automatic storage’ option, DB2 will by default create the database with this option (unless you specify the storage details) [2].

I create a database with name ‘wso2’. 


You can list all the databases from the following command.

db2 list db directory | grep "Database name"


I can connect to a database from the terminal by following the command below.

db2 connect to <database name> user <username> using <password>

Here my database name is wso2, the server admin username is dbsusr2 and the password for the user is [email protected].

Once you connect to the database, you can use the command ‘db2 list tables’ to view all the tables in the database. Here since this is a newly created database and I do not have any tables created, it will show that no table is there.


Next step is to connect this database to a WSO2 server and run it. Here I use WSO2 Identity Server. You can use any WSO2 product and follow the same steps.

The connection details to DB2 [3] database are as following which should be put in <SERVER_HOME>/repository/conf/datasources/master-datasources.xml file. (In some WSO2 products there are other database sources also i.e bps-datasources.xml and if you want to add an IBM DB2 database for such a datasource, you can add the similar configuration in the particular datasource file)

<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/wso2</url>
        <username>dasusr2</username>
        <password>[email protected]</password>
        <driverClassName>com.ibm.db2.jcc.DB2Driver</driverClassName>
        <maxActive>50</maxActive>
        <maxWait>60000</maxWait>
        <testOnBorrow>true</testOnBorrow>
        <validationQuery>SELECT 1</validationQuery>
        <validationInterval>30000</validationInterval>
     </configuration>
  </definition>
</datasource>

The database connection URL would be in the pattern jdbc:db2://<hostname>:<port>/database_name

For the username and password, you can use the server admin credentials to try this out.

The database driver class name would be com.ibm.db2.jcc.DB2Driver. Since WSO2 products are java based, you need to copy the corresponding JDBC driver to the <SERVER_HOME>/repository/components/lib directory. You can download the JDBC driver for IBM DB2 from [4] based on the DB2 version you use.

Once all above steps are done, you can start the WSO2 product with the -Dsetup option which would create the database structure.
Once the WSO2 server is started, if you list the tables in the DB2 database (with the ‘db2 list tables’ command) , you can see that the tables related to the WSO2 product are created. 


You can even query the database and verify that table creating is successful when the server started up.

When trying to create a database, if it failes with the error 'SQL1220N The database manager shared memory set cannot be allocated', you can follow [5] and get it solved. 


References

Tharindu Edirisinghe
Platform Security Team
WSO2

No comments:

Post a Comment