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: db2inst2@tharindu-pc
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: db2inst2@tharindu-pc
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.
I can connect to a database from the terminal by following the command below.
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 admin@wso2.
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>admin@WSO2</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