Chapter 6: WebSphere Application Server V5 Performance Tuning

---

5.4 DB2 EJB sample application setup

In this section, we provide detailed steps regarding how to get the sample application up and running for demonstration purpose so that you could obtain a basic understanding how WebSphere Application Server V5 and DB2 UDB V8 work together.

5.4.1 DB2 Enterprise JavaBean (EJB) sample application

There is a DB2 Enterprise JavaBean (EJB) sample application called AccessEmployee shipped with DB2 UDB V8. This program utilizes server-side business components known as EJBs to implement distributed applications using a component-based development model on top of the J2EE platform provided by WebSphere Application Server. The sample application is provided in a form of Enterprise Application Archive (EAR). You can find it under sqllib\samples directory if you have installed the samples. In our test case, the name of the EAR with complete path is as below (on AIX platform):

 /home/db2inst1/sqllib/samples/java/Websphere/AccessEmployee.ear or /usr/opt/db2_08_01/samples/java/Websphere/AccessEmployee.ear 

On Windows platform, it can be found in:

 <DB2PATH>\samples\java\Websphere\AccessEmployee.ear 

Here <DB2PATH> is home directory of DB2 UDB installation.

Basically this DB2 EJB sample application provides four business services using the DB2 sample database:

• Create a new employee

  This service allows users to create a new employee record in the sample database. The methods in AccessEmployeeBean will take the necessary actions to create an employee EJB and its corresponding record in the employee table.

• Query an Employee's record

  This service provides users the ability to retrieve an employee's information from DB2. The servlet, AccessEmpServlet, delegates the user's request to AccessEmployee session bean. The getEmployeeInfo method in AccessEmployee then finds the employee entity bean, retrieves the employee data from the entity bean and sends the data back to the servlet.

• Query Employees by department and salary

  This service invokes the same AccessEmployee session bean. The getEmployeeByDept method will perform a qualified search of Employee records based on their department and a minimum salary. The findBySalaryDept method in the Employee EJB "EmployeeBean.java" uses CMR (Container-managed relationships) and an EJBQL (EJB Query Language) query to return a collection of qualifying Employee EJBs.

• Retrieve a list of Employees

  This service does not use Entity EJBs. The service displays a potentially large list of employee ids and names. Since the query is simple and the objects are not modified, the application directly accesses the DB2 database using JDBC. The method getEmpNoList in the AccessEmployee session bean performs this service.

EJB best practices have been utilized as much as possible for this sample application. The "Session Facade" design pattern is used throughout the implementation. A servlet called AccessEmpServlet will delegate data access requests to the AccessEmployee stateless session EJB. This EJB acts as a Facade for the two CMP (Container Managed Persistence) entity EJBs.

This design can be used to encapsulate the data access behind a session EJB method and reduce the network activity between the EJB client and the EJB server.

5.4.2 Application installation & resource configuration

Before discussing the detailed installation steps, you could review our testing environment by reference to Table 5-1 on page 119. Basically, besides the products installation which is covered by "Products' installation briefing" on page 119, we have the following tasks to set up the DB2 EJB Sample application:

• Database preparation

• Application preparation

• Application server preparation

• Application resource preparation

• Application installation

• Sample application testing

The following pages provide the details to carry out these tasks.

Database preparation

As the DB2 UDB database server resides on a machine separated from the machine where WebSphere Application Server resides, in order to use the Type 2 driver of DB2 Universal JDBC Driver Provider in our case, we need to catalog the remote database entry to the local machine (here we refer to kanaga machine where WebSphere Application Server resides as local machine and atlantic machine where DB2 UDB is installed as remote machine). In our scenario, we use the SAMPLE database shipped with DB2 UDB V8. The following example shows you how to catalog the remote database.

As the Example 5-8 on page 155 shows, the basic steps to catalog a remote database consists of two steps. The first step is node cataloging and it could be skipped if there is matching node entry existing in the local node directory. The second is database cataloging. Here we use the database alias "RSAMPLE" on local machine to reference the "SAMPLE" database on remote machine which could be created by DB2SETUP Wizard or manual creation by using the command db2sampl via DB2 instance owner.

Example 5-8: Remote Database Cataloging

 Kanaga:/ >su - db2inst1 Kanaga:/home/db2inst1 >db2 catalog tcpip node atlantic remote 9.1.38.92 server 50000 Kanaga:/home/db2inst1 >db2 catalog db sample as rsample at node atlantic DB20000I  The CATALOG DATABASE command completed successfully. DB21056W  Directory changes may not be effective until the directory cache is refreshed. Kanaga:/home/db2inst1 >db2 terminate DB20000I  The TERMINATE command completed successfully. Kanaga:/home/db2inst1 >db2 connect to rsample user db2inst1 Enter current password for db2inst1:    Database Connection Information  Database server        = DB2/6000 8.1.3  SQL authorization ID   = DB2INST1  Local database alias   = RSAMPLE 

Note 

It is not a requirement to catalog a local database entry for the remote database. The DB2 Universal JDBC Driver Provider, we could use the Type 2 driver without using a local database catalog entry by providing the combination of database name, server name and the port number.

In addition, as required by the EJB sample application, we set the column empno as the primary key for the employee table as below:

 db2 "alter table employee add constraint emp_pk primary key(empno)" 

This could be done remotely after establishing connection to the RSAMPLE database alias.

Application preparation

There is no DB2 EJB sample application archive on the Kanaga machine in our scenario as we only installed DB2 UDB Runtime Client in Kanaga. To install the sample application, we could copy it from Atlantic machine via ftp. In our sample, we copied it into the commonly used directory for application installation /usr/WebSphere/AppServer/installableApps.

The sample application uses environment entry to reference database schema which by default is db2admin. But for our case it is db2inst1, so we need to change it before installing the sample application. We use Application Assembly Tool (AAT) that could be invoked by the command assembly.sh to modify the environment entry, AccessEmp/DbSchema, under the Environment entries for the Session bean AccessEmployee. The value should be db2inst1 for our case, just as shown in the Figure 5-17. Remember to save the changes after setting the new value.

Figure 5-17: Using AAT to application archive properties

Application server preparation

Before installing the sample application, we need to start the WebSphere Application Server first, in our sample the server name is server1. The following example (Example 5-9) shows how to start the application server by using startServer.sh command.

Example 5-9: Starting the Application Server

 Kanaga:/usr/WebSphere/AppServer/bin >./startServer.sh server1 ADMU0116I: Tool information is being logged in file            /usr/WebSphere/AppServer/logs/server1/startServer.log ADMU3100I: Reading configuration for server: server1 ADMU3200I: Server launched. Waiting for initialization status. ADMU3000I: Server server1 open for e-business; process id is 9326 

As the example indicates, when the message like "Server server1 open for e-business; process id is 9326" appears, it means the application server is started successfully. If your application has been started before, you could use serverStatus.sh command to verify if the server status is running as shown in Example 5-10.

Example 5-10: Using serverStatus.sh to verify the Application Server is running

 Kanaga:/usr/WebSphere/AppServer/bin >./serverStatus.sh server1 ADMU0116I: Tool information is being logged in file            /usr/WebSphere/AppServer/logs/server1/serverStatus.log ADMU0500I: Retrieving server status for server1 ADMU0508I: The Application Server "server1" is STARTED 

Application resources preparation

We use the DB2 Universal JDBC Driver Provider in our case. Regarding how to create JDBC Providers, you could refer to the section "The steps to create and configure DB2 JDBC Providers" on page 139 for more details. In that section, a DB2 Universal JDBC Driver Provider is created, we also use it for our sample application.

In addition to the JDBC Providers creation, we also need to create DB2 Data Sources for the application. In order to access the DB2 database resource, an J2C Authentication Data Entry named AccEmpAlias with the following properties is created in our case:

• Alias: kanaga/AccEmpAlias

• User ID: db2inst1

• Password: password

• Description: Demo entry for AccessEmployee

You could refer to the step 1 in the section "The steps to create and configure DB2 Data Source" on page 144 for more details regarding how to create J2C Authentication Data Entry.

Then we could create the DB2 Data Source for our sample application with the following general and custom properties.

For other available properties, just use the default setting. For Custom type properties in Table 5-4 on page 158, you need to apply the changes made in general properties page first, then the custom properties could be modified.

Table 5-4: General and custom Properties Setting for sample application

Name	Value	Type
Name	EJBSample	General
JNDI Name	jdbc/Sample	General
Container managed persistence	Checked	General
Component-managed Authentication Alias	kanaga/AccEmpAlias	General
Container-managed Authentication Alias	kanaga/AccEmpAlias	General
databaseName	rsample	Custom
driverType	2	Custom
serverName	Null	Custom
portNumber	Null	Custom
enableSQLJ	true	Custom

The server may need to be restarted for all these changes to take effect. After the server is restarted, try to test connection and make sure the connection to the newly created DB2 data source could be established successfully. Regarding how to test the connection, refer to the step 5 in the section "The steps to create and configure DB2 Data Source" on page 144.

Application installation

The sample application is installed using these steps:

• In the WebSphere Application Server Administrative Console, expand Applications on the left pane

• Choose Enterprise Applications.

• Click the install button in the resulting pane that is on the right side of the page

• The "Preparing for the application installation" page is displayed, as shown in the Figure 5-18 on page 160. In our case, as the WebSphere Application Server Administrative Console is invoked on the browser of a client machine, so for the path where the enterprise application archive exists, we choose "Server path", and the corresponding value is:

   /usr/WebSphere/AppServer/installableApps/AccessEmployee.ear 

  
  Figure 5-18: Provide the EAR Path for Application Installation

• Click Next. You see a page with a subtitle You can choose to generate default bindings and mappings. Keep the default settings and click the Next button.

• Now you are in Step 1, "Install New Application" page; select "Deploy EJBs". Next click Next to Step 2.

• In Step 2, choose DB2UDB_V81 as the Deploy EJBs Option - DataBase Type, then click Next.

• Then just keep the default settings, and keep clicking Next until you reach the Summary step, as the following Figure 5-19 on page 161 shows.

  
  Figure 5-19: Application Installation Summary Page

• Click Finish to install the sample, then click Save to Master Configuration in the resulting page to finish the installation.

Now AccessEmployee application is ready for the testing.

Sample application testing

Start the application by checking the AccessEmployee application in the Enterprise Applications page, then click Start to start the application.

When the application is successfully started, go to a Web browser and enter the following URL to access the application:

 http://kanaga:9080/AccessEmp/AccessEmployee.html 

Then the DB2 EJB Sample application could be invoked as the following Figure 5-20 on page 162 shows.

Figure 5-20: DB2 EJB Sample Application home page

Then you could use the available buttons and the hot link to do different testing such as query employee information, create a new employee, etc.

In the above example, the embedded HTTP server of the application server is used for serving the request from port number 9080 on the machine Kanaga, as indicated by the URL: http://kanaga:9080/AccessEmp/AccessEmployee.html.

In order to use a separate machine as the Web server to access the sample application, we need to add URI entry for the AccessEmployee application into the Web server plug-in file plugin-cfg.xml. For demonstration purpose, we use the machine Helium as the Web server, where the IBM HTTP Server has been installed and the plug-in file has been modified to redirect the HTTP and HTTPS requests to the embedded HTTP Server of the target WebSphere application server, as shown in the step Install IBM HTTP Server and Web Server Plugins of the section "Products' installation briefing" on page 119. The plug-in file could be found under the directory:

 C:\Program Files\WebSphere\AppServer\config\cells\ 

Edit this file and add the URI entry for the AccessEmployee application as shown in the Example 5-11. You could add the entry at the end of the UriGroup block (before the line </UriGroup>).

Example 5-11: Add URI Entry into plug-in file for AccessEmployee application

 <UriGroup Name="server1_Cluster_URIs">         <Uri Name="/snoop/*"/>         ...         <Uri Name="/AccessEmp/*"/>     </UriGroup> <Route ServerCluster="server1_Cluster"         UriGroup="server1_Cluster_URIs" VirtualHostGroup="default_host"/> pane 

Restart IBM HTTP Server on the machine Helium for the modification to take effect, then the application should be reachable via the URL:

 http://helium/AccessEmp/AccessEmployee.html 

5.4.3 Using DB2 for z/OS as the Data Source

We could also use DB2 for z/OS as the data source of the DB2 EJB sample application through DB2 JDBC Providers. There are several types of DB2 JDBC Providers which support the connection to DB2 on z/OS, such as the legacy DB2 JDBC Provider, DB2/390 JDBC Provider, and DB2 Universal JDBC Driver Provider (including Type 2 and Type 4). The advantages and disadvantages of these two types is covered in previous DB2 Connect or DB2 for z/OS related sections. In this subsection, we take the Type 2 driver of DB2 Universal JDBC Driver Provider as an example to demonstrate how to make WebSphere Application Server V5 and DB2 for z/OS working together. As the intermediate layer, DB2 Connect is used to connect WebSphere Application Server with DB2 for z/OS.

To make use of the DB2 for z/OS as the data source for the DB2 EJB sample application via the Type 2 driver of DB2 Universal JDBC Driver Provider, basically the following steps should be covered:

1. DB2 for z/OS connection preparation

2. Application data preparation on DB2 for z/OS

3. Application preparation

4. Application resource preparation

5. Application installation and testing

As the way to perform most of the above steps has been covered by previous sections, so that here we would just focus on the distinctive parts, for example, data preparation for the application.

1. DB2 for z/OS connection preparation

   Please refer to the section "Using DB2 for z/OS and OS/390 as the Data Source for WAS" on page 127 for the details about how to establish the connection to DB2 for z/OS via DB2 Connect. We use the connection obtained in that step in the following steps, the related database alias is DBC8 in our example.

2. Application data preparation on DB2 for z/OS

   The DB2 EJB sample application uses two tables (EMPLOYEE and DEPAERTMENT) in the sample database which is available for DB2 UDB on distributed platforms. To prepare the data on DB2 for z/OS, you could create tables on DB2 for z/OS via the DDL obtained from the DB2 UDB on distributed platform, then export the data and import into the DB2 for z/OS. In addition, as the entity bean is used in the sample application, so it also requires a primary key on the employee table.

   Tip 

   You could use db2look utility to generate the table and index creation scripts. Use Integrated eXchangable Format (IXF) to export the data from the distributed platform then it could be imported into DB2 for z/OS easily.

   You could also use the following scripts, see Example 5-12, to create the tables and the required index on the DB2 for z/OS:

   Example 5-12: Data Preparation Scripts for DB2 EJB Sample Application

    CONNECT TO DBC8 USER DB2WAS USING <password>; ------------------------------------------------ -- DDL Statements for table "DB2WAS"."DEPARTMENT" ------------------------------------------------  CREATE TABLE "DB2WAS"."DEPARTMENT" (                   "DEPTNO" CHAR(3) NOT NULL ,                   "DEPTNAME" VARCHAR(29) NOT NULL ,                   "MGRNO" CHAR(6) ,                   "ADMRDEPT" CHAR(3) NOT NULL ,                   "LOCATION" CHAR(16))                   ; ------------------------------------------------ -- DDL Statements for table "DB2WAS"."EMPLOYEE" ------------------------------------------------  CREATE TABLE "DB2WAS"."EMPLOYEE" (                   "EMPNO" CHAR(6) NOT NULL ,                   "FIRSTNME" VARCHAR(12) NOT NULL ,                   "MIDINIT" CHAR(1) NOT NULL ,                   "LASTNAME" VARCHAR(15) NOT NULL ,                   "WORKDEPT" CHAR(3) ,                   "PHONENO" CHAR(4) ,                   "HIREDATE" DATE ,                   "JOB" CHAR(8) ,                   "EDLEVEL" SMALLINT NOT NULL ,                   "SEX" CHAR(1) ,                   "BIRTHDATE" DATE ,                   "SALARY" DECIMAL(9,2) ,                   "BONUS" DECIMAL(9,2) ,                   "COMM" DECIMAL(9,2))                   ; -- DDL Statements for primary key on Table "DB2WAS"."EMPLOYEE" ALTER TABLE "DB2WAS"."EMPLOYEE"         ADD CONSTRAINT "EMP_PK" PRIMARY KEY                 ("EMPNO"); 

3. Application preparation

   Change the database schema to your desired value. In our example, it is "DB2WAS". Refer to "Application preparation" on page 156 for details about how to change it via the AAT.

4. Application resource preparation

   The J2C Authentication Entry data is also required. In our example, the user ID and password for the testing environment are provided for the alias AccZOSAlias. Then we also need to create the JDBC provider and data source for the application. Refer to "Application resources preparation" on page 158 for more details about how to create JDBC Provider and Data Sources. In our example, the JDBC Provider previously created in the section "The steps to create and configure DB2 JDBC Providers" on page 139 is used. Similar to the Table 5-4 on page 158, the following properties are provided for the DB2 for z/OS data source creation.

   Table 5-5: Properties Setting of DB2 for z/OS data source

   Name	Value	Type
   Name	MFSample	General
   JNDI Name	jdbc/MFSample	General
   Container managed persistence	Checked	General
   Component-managed Authentication Alias	kanaga/AccZOSAlias	General
   Container-managed Authentication Alias	kanaga/AccZOSAlias	General
   databaseName	DBC8	Custom
   driverType	2	Custom
   serverName	Null	Custom
   portNumber	Null	Custom
   enableSQLJ	false	Custom

5. Application installation and testing

   If the application AccessEmployee has been existing in your WebSphere Application Server environment, you could remove it before reinstalling the application. To remove the application, first to choose the AccessEmployee application in Enterprise Applications page in the WebSphere Application Server Administrative Console, then click Stop button to stop the application if its original state is Started, after that, click Uninstall button to remove it from the WebSphere Application Server installed application list.

   Refer to "Application installation" on page 159 for more details about how to install the sample application. Specifically for the DB2 on z/OS data source, some changes need to be made based on the steps provided in "Application installation" on page 159:

   • The value of property "Deploy EJBs Option - Database Type" in step 2

     The value DB2UDBOS390_V7 in the drop-down list is chosen. Currently, specific item like DB2UDBOS390_V8 is not yet available in WAS.

   • The value of property "JNDI Name" in step 5

     As the default data source mapping for modules containing 2.0 entity beans, here jdbc/MFSample is used in our example.

   • The value of property "JNDI Name" in step 8

     This is the step "Map resource references to resources ". Same as step 5, in our example the JNDI name is jdbc/MFSample.

   After finishing the DB2 EJB sample application installation, then you could start the application for testing purpose as demonstrated in the "Sample application testing" on page 161. Please be aware that probably a restart of the WebSphere Application Server is required to make the above changes effective, if you have not done during the previous steps.

   Up to this point, you should have acquired the general steps to get DB2 UDB and WebSphere Application Server working together by installing and configuring related products as well as installing and testing the DB2 EJB sample application. Also please be aware that basically this chapter is focusing on operations that covers the general steps to prepare the runtime environment. If you want to obtain more information about the DB2 UDB V8 and WebSphere Application Server V5 integrated performance tuning, please refer to Chapter 8, "DB2 UDB V8 and WAS V5 integrated performance" on page 287 of this redbook.

---