Configuring and testing IBM DB2 UDB 7.2 (Linux): WebSphere Application Server

Configuring and testing DB2 UDB 7.2

This article describes how to do the following:

Create a DB2 instance named db2inst1 and an administration server named db2as.
Verify installation of DB2.
Create and configure a database named was40, which is used by WebSphere Application Server.
Verify connection to the was40 database.
Configure WebSphere Application Server when dropping and reinstalling the was40 administrative database.

These instructions assume that DB2 is installed in the default location (/usr/IBMdb2/V7.2) and that the required FixPak is installed.

Creating a database instance and administration server

Perform the following steps to create a DB2 instance, and the resources it requires:

Ensure that you are logged into the machine with superuser (root) privileges.
Create a home directory for the DB2 instance owner by using the mkdir command, as follows:
```
# mkdir /home/db2inst1
```
Navigate to the directory containing the DB2 setup utility, /usr/IBMdb2/V7.2/install.
Start the DB2 Setup Utility by entering the following command:
```
# ./db2setup
```
Note: The DB2 Setup Utility works with only the bash, Bourne, and Korn shells.
Highlight the Create button beside the option labeled To create a DB2 Instance, an Administration Server, or a Data Links Manager Administrator select Create and press Return.
On the Create DB2 Services dialog, highlight the Create a DB2 Instance option and press Return.
On the DB2 Instance dialog, perform the following steps, noting the values that you enter or accept for future reference:
1. Accept the default value for the User Name option, db2inst1.
2. Enter a user ID or accept the default user ID by ensuring that the Use default UID option has an asterisk (*) beside it.
3. Accept the default value for the Group Name option, db2adm.
4. Enter a group ID or accept the default group ID by ensuring that the Use default GID option has an asterisk (*) beside it.
5. Accept the default value for the Home Directory option, /home/db2inst1.
6. Type a password for the user in the Password and Verify Password options. DB2 requires a password of eight or fewer characters.
7. Highlight OK and press Return.
On the Fenced User dialog, perform the following steps, noting the values that you enter or accept for future reference:
1. Accept the default value for the User Name option, db2fenc1.
2. Enter a user ID or accept the default user ID by ensuring that the Use default UID option has an asterisk (*) beside it.
3. Accept the default value for the Group Name option, db2fadm1.
4. Enter a group ID or accept the default group ID by ensuring that the Use default GID option has an asterisk (*) beside it.
5. Enter a home directory or accept the default value for the Home Directory option, /home/db2fenc1.
6. Type a password for the user in the Password and Verify Password options. DB2 requires a password of eight or fewer characters.
7. Highlight OK and press Return.
On the DB2 Warehouse Control Database dialog, highlight the option labeled Do not set up DB2 Warehouse Control Database and press Return.
Highlight OK and press Return.
On the Create DB2 Services dialog, highlight the Create the Administration Server option and press Return.
On the Administration Server dialog, perform the following steps, noting the values that you enter or accept for future reference:
1. Accept the default value for the User Name option, db2as.
2. Enter a user ID or accept the default user ID by ensuring that the Use default UID option has an asterisk (*) beside it.
3. Accept the default value for the Group Name option, db2asgrp.
4. Enter a group ID or accept the default group ID by ensuring that the Use default GID option has an asterisk (*) beside it.
5. Enter a home directory or accept the default value for the Home Directory option, /home/db2as.
6. Type a password for the user in the Password and Verify Password options. DB2 requires a password of eight or fewer characters.
7. Highlight OK and press Return.
A Notice dialog informs you of the value being created for the DB2SYSTEM environment variable. Ensure that OK is highlighted and press Return.
On the Create DB2 Services dialog, highlight OK and press Return.
The Summary Report dialog box opens. Verify the information on the Summary Report dialog and when you have determined that it is correct, ensure that Continue is highlighted and press Return.
A Warning dialog box opens, giving you the option of canceling the configuration processes. Ensure that OK is highlighted and press Return.
A Notice dialog informs you when the processes have completed successfully. Ensure that OK is highlighted and press Return.
The Status Report dialog informs you of process successes and failures. View the Log File for information about how to correct particular failures. Ensure that OK is highlighted and press Return to exit from the Status Report dialog.
On the DB2 Setup Utility dialog, highlight Close and press Return.
On the Notice dialog, ensure that OK is highlighted and press Return.
Ensure that the user named root is a member of the administration server group named db2asgrp by editing the /etc/group file.
Create symbolic links from the home directory of the instance owner to the DB2 installation directory by executing the db2ln script, as follows:
```
# /usr/IBMdb2/V7.2/cfg/db2ln
```
Configure the instance owner db2inst1 to run the db2profile script at login by adding the following text to the /home/db2inst1/.bash_profile file of the instance owner db2inst1:
```
. /home/db2inst1/sqllib/db2profile
```

Note the space between the period (.) and the first slash (/). If you are using a different shell, edit the appropriate file accordingly.

Configure the user root to run the db2profile script at login by adding the following text to the /root/.bash_profile file:
```
. /home/db2inst1/sqllib/db2profile
```
Note the space between the period (.) and the first slash (/).
Log out then log back in for your changes to take effect.
Determine the current setting for the maximum number of message queues by using the ipcs -l command, as follows:
```
# ipcs -l
```
A portion of the output should resemble the following:
```
 ------ Messages: Limits --------
 max queues system wide = 128
 max size of message (bytes) = 8192
 default max size of queue (bytes) = 16384
```
If the value of the max queues system wide parameter is less than 128, increase it by entering the following command:
```
# echo 128 > /proc/sys/kernel/msgmni
```
It is recommended that you add this command to a startup script that is executed each time the machine is restarted, such as the /etc/rc.d/rc/local file.

Verifying installation of DB2 UDB

To demonstrate that DB2 is functioning correctly, you will create a sample database and compile and execute a Java application that accesses it. The steps below establish that the environment is set up correctly for DB2 and the IBM Java 2 SDK, and that the JDBC driver is accessible from a Java application.

Perform the following steps to create the sample database and compile and run the Java application:

Log in as the DB2 instance owner, db2inst1, by using the su command, as follows:
```
# su - db2inst1
```
When you log in as db2inst1, the command prompt changes from the # symbol to a dollar sign ($) to indicate a change in your login identity.
If this is the first time that you have logged in as the DB2 instance owner, you could be prompted to change the password. Enter a new password and press Return. DB2 requires a password of eight or fewer characters.
When prompted, type the new password again and press Return.
Ensure that the DB2 environment has been set up correctly by using the echo command to verify the value of the DB2INSTANCE environment variable, as follows:
```
$ echo $DB2INSTANCE
```
The correct value returned is db2inst1.
Ensure that the home directory of the instance owner, /home/db2inst1 has write permissions.
Create the sample database by executing the db2sampl script, as follows:
```
$ db2sampl
```
This process can take several minutes to complete.
Ensure that you are in the instance owner's home directory, /home/db2inst1.
Compile an example Java application by using the javac command, as follows:
```
$ javac -d . sqllib/samples/java/DB2Appl.java
```
The resulting class file is created in the local directory.
Start DB2 by using the db2start command, as follows:
```
$ db2start
```

Run the Java sample by using the java command, as follows:

$ java DB2Appl

Correct output resembles the following:

Retrieve some data from the database... 
Received results: 
   empno= 000010 firstname= CHRISTINE
   empno= 000020 firstname= MICHAEL 
   empno= 000030 firstname= SALLY 
   . . .
Update the database... 
Changed 1 row.

Creating a database for WebSphere Application Server

Perform the following steps to create a database named was40 and set the DB2 application heap size:

Ensure that you are logged in as the DB2 instance owner, db2inst1.
Ensure that DB2 is running.
Create a database named was40 by using the db2 create database command, as follows:
```
$ db2 create database was40
```
This process can take several minutes to complete.
Set the application heap size by using the db2 update db config command, as follows:
```
$ db2 update db config for WAS40 using applheapsz 256
```
Stop and start DB2 for your changes to take effect by using the db2stop and db2start commands, as follows:
```
$ db2stop
```
```
$ db2start
```
If an application heap size of 256 does not work for your system, increase it to 512.

Verifying the connection to the database

Perform the following steps to verify a connection to database named was40:

Ensure that you are logged in as the DB2 instance owner, db2inst1.

Connect to the database named was40 by using the db2 connect command, as follows:

$ db2 connect to was40

Correct output resembles the following:

Database Connection Information

     Database server        = DB2/LINUX 7.2.1 
     SQL authorization ID   = DB2INST1
     Local database alias   = WAS40

To disconnect from the database and log out as the DB2 instance owner, type exit at the command prompt.

Configuring WebSphere Application Server when dropping and reinstalling the `was40` administrative database

If you drop and recreate the was40 database after you have installed and successfully started the WebSphere Application Server administrative server for the first time, you must reset the values of the com.ibm.ejs.sm.adminServer.createTables flag and the install.initial.config flag. These flags are found in the WebSphere Application Server admin.config file, which is located by default in the /opt/WebSphere/AppServer/bin directory.

You must reset the values of these flags because the WebSphere Application Server product automatically changes their values from true to false when the administration server is started successfully for the first time. The product changes the values of these flags so that the creation of the database tables and installation of the Default Server and sample applications are not repeated with subsequent starts of the administration server.

Perform the following steps to drop, recreate, and set the application heap size for the was40 database and to change the values for the com.ibm.ejs.sm.adminServer.createTables and install.initial.config flags:

Log in as the DB2 instance owner. Logging in as the instance owner places you automatically in the home directory of the instance owner. When you log in as the instance owner, the command prompt changes from the # symbol to a dollar sign ($) to indicate a change in your login identity.
Ensure that DB2 is running or start it by entering the following command:
```
$ db2start
```

Drop, recreate, and set the application heap size for the was40 database by entering the following commands:

$ db2 drop database was40 
$ db2 create database was40 
$ db2 update db config for WAS40 using applheapsz 256