3.1 JDBC provider scenario

< Day Day Up >

We used the JDBC provider that comes with WebSphere Application Server V5.0.2 for Linux on iSeries to access the data stored in DB2 for OS/400. JDBC provider works in conjunction with JDBC driver and Data Source. This section discusses the following topics:

Scenario overview
How to get the JDBC driver
How to configure the JDBC provider and Data Source
How to install our sample application

3.1.1 Scenario overview

We used the MyBankCMP application to demonstrate the process of installing and deploying a Web application in WebSphere Application Server V5.0.2 for Linux on iSeries, which uses a JDBC provider to access DB2 for OS/400. The MyBankCMP application has an entity bean that represents an Account object and a session bean that represents a simple transfer funds transaction, as shown in Figure 3-1. WebSphere handles the persisting of the account information to a database. In fact, we need to configure a JDBC provider and a Data Source so that the MyBankCMP application can use persistent data. The application features Container-Managed Persistence (CMP) and Local Interfaces which are part of the J2EE 1.3 specification.

click to expand
Figure 3-1: Overview of MyBankCMP sample application

Steps to set up and run the MyBankCMP sample application

This is a summary of the steps to set up, install, deploy, and run the MyBankCMP application:

Create an iSeries user profile WSDEMO with password WSDEMO1. This user profile is used to access the database via a Data Source.
Create the collection bankdata.
Set up the JDBC driver.
Create the JDBC provider and Data Source.
Install the MyBankCMP application.
Update the Web server plugin.
Test the MyBankCMP application.

3.1.2 Environment setup

This section provides a step-by-step guide to set up the environment used for this scenario.

User profile creation for database

We need a user profile for Data Source configuration. This user profile needs to exist on the server where the target database resides. In our scenario, it is an iSeries server. Execute the following command on the iSeries command entry screen:

    CRTUSRPRF USRPRF(WSDEMO) PASSWORD(WSDEMO1)

Be sure to give enough privilege to create libraries.

Create the collection Bankdata

The Bankdata collection is the database used in our example. The following steps describe the procedure to create the library, the table, and the journal for that table.

Open the iSeries Navigator and connect to the system that you will use for the example. Log in with the user profile that you created in the previous step, WSDEMO.
In the iSeries Navigator, expand iSeries system → Database → DB name.
Right-click Libraries and select New Library as shown in Figure 3-2.

Figure 3-2: iSeries Navigator- Creating a new Library
In the pop-up window, type bankdata as the name of the new library, then click OK. The BANKDATA library is created and added to the list of displayed libraries.
Right-click the relational database and select Run SQL Scripts. Then type the create table and alter table statements shown in Figure 3-3.

Figure 3-3: iSeries Navigator- Running SQL statements
Right-click BANKDATA in the iSeries Navigator and select New → Journal (see Figure 3-4). A database table has to be journaled in order to be used by EJB.

Figure 3-4: iSeries Navigator- Creating a journal
Fill in the name of the journal and the library to hold a receiver, if the library does not appear in the choice list. Key in the values as shown in Figure 3-5, then click OK.

Figure 3-5: iSeries Navigator- Journal parameters
You are returned to the iSeries Navigator window as illustrated in Figure 3-4. Click BANKDATA again in the iSeries Navigator. In the right-hand pane right-click the journal name JOURNAL and select Starts and ends table journaling option. In the new pop-up window, expand BANKDATA and select the ACCOUNT table as shown in Figure 3-6. Click the Add button, then click OK.

Figure 3-6: iSeries Navigator- Start journaling ACCOUNT table

With this procedure, we create the collection and enable the use of the EJBs over the ACCOUNT table using journaling. Now we are ready to configure the datasource of our program.

3.1.3 Setting up the JDBC driver

The JDBC driver provides the functionality for the Java application to access data stored in DB2. There are multiple types of JDBC drivers, such as Type 1, 2, 3, and 4. In our scenario, in which we are accessing DB2 for OS/400, the best choice in terms of reliability and simplicity is the Type 4 driver that comes with IBM Toolbox for Java for iSeries.

The other JDBC driver commonly used is from the IBM Developer Kit for Java (native driver). Your choice of which JDBC driver to use depends on the following considerations:

Local or remote database access: You can configure WebSphere Application Server to access local or remote databases. For local database access, the native driver that comes with IBM Developer Kit for Java will perform best since it does not have any communications overhead.
Java Transaction API (JTA): If you need two-phase commit capability, you need to use a JTA-enabled driver. Enterprise JavaBeans that access multiple database tables within a transaction boundary require JTA support. Both drivers mentioned previously provide support for JTA.

In our scenario, we used a toolbox JDBC driver that comes with IBM Toolbox for Java for iSeries. The following steps show you how to get the driver and set it up.

IBM Toolbox for Java for iSeries JDBC driver is provided as file jt400.jar. This file is located in the IFS of the iSeries. Copy this file by FTP to the Linux file system, as shown in Figure 3-7.

Figure 3-7: FTP- Copy jt400.jar to Linux file system
Now you need to let WebSphere JDBC Manager know the location of the jt400.jar file. Sign on to the administration console of WebSphere Application Server V5.0.2 for Linux on iSeries via any Web browser. Expand Environment → Manage WebSphere Variable and click New, as shown in Figure 3-8.

Figure 3-8: WebSphere console- Creating a WebSphere variable
On the Configuration tab (Figure 3-9) define a specific name of the variable and the location of the file in the Linux file system. We used the following values for our scenario:
- Name: OS400_TOOLBOX_JDBC_DRIVER_PATH
- Value: /opt/WebSphere/AppServer/classes
- Description: jt400.jar file directory in Linux File System
Figure 3-9: WebSphere console- Creating variables/configuration

When you are done, click OK.
The message shown in Figure 3-10 is displayed. Click the Save link to save the changes.

Figure 3-10: WebSphere console- Save message
Finally, a save acknowledgement window appears. Click Save to store the changes in the master configuration, as illustrated in Figure 3-11.

Figure 3-11: WebSphere Console - Saving on master configuration

3.1.4 Create the JDBC provider and Data Source

Now you are ready to configure the JDBC provider and the Data Source. DataSources are assigned to a JDBC provider in WebSphere Application Server 5.0. A Data Source object represents the logical name of a JDBC-enabled database. This shields the enterprise bean developer from the underlying physical location of the database.

JDBC providers, DataSources, and other resources are organized under the Resources heading. Expand Resources and click JDBC Providers, as illustrated in Figure 3-12, to configure the Data Source. Existing JDBC providers will be displayed in the right-hand frame. Verify that the Scope is set to Node, then click New to create a JDBC provider, as shown in Figure 3-12.

Figure 3-12: WebSphere console- Resources/JDBC providers
Select the JDBC provider. In our scenario, we chose the DB2 UDB for iSeries Toolbox (XA), as illustrated in Figure 3-13.

Figure 3-13: WebSphere console- Selecting DB2 JBDC driver
After you click the OK button, the configuration properties for the JDBC provider are displayed as shown in Figure 3-14. They all should be correct since you already defined the classpath in the environment settings configuration (see 3.1.3, "Setting up the JDBC driver" on page 90 for details). Therefore, no changes need to be made here. Click OK to continue.

Figure 3-14: WebSphere console- Configuration properties for JDBC provider
Once the JDBC provider is created with your choice of JDBC driver, you are ready to create a Data Source. Click DB2 UDB for iSeries (Toolbox XA) as shown in Figure 3-15.

Figure 3-15: WebSphere console- JDBC provider created
The configuration properties for the JDBC provider will be displayed. Click Data Sources to create a new Data Source as illustrated in Figure 3-16.

Figure 3-16: WebSphere console- JDBC additional properties/data sources
The list of Data Sources that are already configured for the JDBC provider now appears. If this is the first time you are configuring a Data Source for the JDBC provider, the list will be empty. Click New to create a new Data Source.
An empty General Properties for the Data Source screen will be displayed. Enter the following items to configure the datasource as illustrated in Figure 3-17:
- Name: This is the name of the Data Source. In our scenario, it is MyBankDS.
- JNDI Name: The format of the name is jdbc/anyname. In our scenario, it is jdbc/MyBank.
- Container-managed persistence: This option specifies if this Data Source will be used for container-managed persistence of EJBs. This will cause a corresponding CMP connection factory to be created. So, whether to select or not depends on your application which uses this Data Source. We chose to use it.
- Description: This is the description of the Data Source. In our scenario, it is MyBank Datasource.
Figure 3-17: WebSphere console- General properties for the datasource

Click OK to continue; this will bring you to Figure 3-18.

Figure 3-18: WebSphere console- Update additional properties
The Data Source is now created. However, there are some additional configuration properties which still need to be set. Click the MyBankDS link as shown in Figure 3-18.
The Data Source configuration properties will be listed. Scroll down until you see J2C Authentication Data Entries in the Related Items section. Click this entry, as shown in Figure 3-19.

Figure 3-19: J2C Authentication Data Entries option
On the next screen, shown in Figure 3-20, click New to create a new J2C Authentication Data Entry.

Figure 3-20: J2C Authentication data entry

Enter an Alias of MyBankAlias, a USER ID of wsdemo, and a password of wsdemo1, as illustrated in Figure 3-21.

click to expand
Figure 3-21: Defining user and password for used by J2C security

Note

The user profile and password defined here must exist on your iSeries server. All connections obtained from this Data Source use the user ID and password specified here. If not, your connection will be rejected when you try to make a connection.

Take a shortcut back to the Data Source: click MyBankDS in the right upper link list, as shown in Figure 3-22.

Figure 3-22: Shortcut to MyBankDS
Scroll down and click Custom Properties (Figure 3-23).

Figure 3-23: Select Custom properties
Configure the JDBC toolbox driver options. First, select the serverName option as shown in Figure 3-24.

Figure 3-24: serverName option in the custom properties menu
Enter the name of your server (AS05 for example) in the Value field, as shown in Figure 3-25.

Figure 3-25: Configuring the serverName
Another item to configure is the database name. Click databaseName as shown in Figure 3-26.

Figure 3-26: Database option in Custom properties menu
The database name is the same name that you can see using the iSeries command WRKRDBDIRE in your environment. Enter the information as shown in Figure 3-27.

Figure 3-27: Configuring the database name
The structure of the DB2 UDB for iSeries follows the structure of libraries as collections. For this reason you must define the libraries option in your JDBC configuration as illustrated in Figure 3-28.

Figure 3-28: Libraries option in Custom properties menu
Key in the name of the collection in the Value field that you will use for this JDBC connection, as illustrated in Figure 3-29. In our scenario, the name of the collection, (Library in OS/400 terms), is BANKDATA.

Figure 3-29: Configuring the Library name
Click the MyBankDS link at the top of the screen shown in Figure 3-29. Assign the new J2C Authentication Data Entry to the Component-managed Authentication Alias and also to the Container-managed Authentication Alias in the General Properties section, as shown in Figure 3-30.

Figure 3-30: Assign MyBankAlias to Datasource
- From the Component Managed Authentication Alias drop-down, select the newly created MyBankAlias and click Apply.
- From the Container-managed Authentication Alias drop-down, select the newly created MyBankAlias and click OK.
Now it is done! Your Data Source is now completely defined. The configuration changes to your server have to be saved. Click Save to apply your changes to the master configuration.

Before this new JDBC Provider and Data Source can be used by an application, the server will need to be restarted.

3.1.5 Installing the MyBankCMP sample application

During installation you can specify binding information for the application running on your WebSphere Server. The application you will install is a small banking application. The application features Container-Managed Persistence (CMP) and Local Interfaces, both of which are part of the J2EE 1.3 specification. After you have finished installation, you will test the application through a Web interface which uses the local interfaces to create accounts and transfer funds.

Use the Administrative console to install applications. The following steps describe how to do this.

Be sure that the WebSphere server is up and running. In addition, if you have configured an instance and you want to install the application there, check that your instance is up and running
Open the administrative console for your instance.
Expand the application option and click the Install New Application link as shown in Figure 3-31.

Figure 3-31: WebSphere console- Installing a new application
In the "Preparing for the application installation" panel select the Local path radio button and click Browse. Navigate through the directory structure, select the .EAR file that includes the application you want to install, and click Open. This procedure results in a screen like the one shown in Figure 3-32.

Figure 3-32: Importing .EAR file to WebSphere Application Server environment
The "Preparing for the application installation" window appears again, but now with the file in the local path text area (Figure 3-33). Click Next to continue the configuration.

Figure 3-33: Defining the path where the EAR files resides
On the next page, there are a number of settings which can be changed to customize your application specifically for running on WebSphere. The only settings you need to change are for the Connection Factory Bindings.

As illustrated in Figure 3-34, select the Default connection factory bindings radio button (you may have to scroll down) and enter eis/jdbc/MyBank_CMP for the JNDI name. Ensure Per Connection Factory is set for the resource authorization. This setting will use the user name and password information associated with the Data Source when authenticating to the database. Click Next to continue the configuration.

Figure 3-34: Installing application- Connection factory bindings
The installation wizard starts, and automatically expands "Step 1: Provide options to perform the installation."

In this step you can set the Deploy EJBs enable box and give a name to the application. Figure 3-35 illustrates this procedure applied to our example. Click Next to go on to Step 2.

Figure 3-35: Installation wizard- Step 1
The Step 2 panel (Figure 3-36) has options to use when deploying enterprise beans. We accepted the default here. Choose the database that you will use in your application; in our example we used DB2UDBISERIES.

Figure 3-36: Installation wizard- Step 2
When you click next, Step 3 is expanded (Figure 3-37). In this step, you can specify the JNDI names for the Account and Transfer enterprise beans. In our example Ensure Account and Transfer have been set to ejb/MyBank/Account and ejb/MyBank/Transfer respectively. Click OK to continue with the installation.

Figure 3-37: Installation wizard- Step 3
In the Step 4 screen (Figure 3-38) you can specify a Data Source specifically for your EJB jar file. Select EJB Module MyBankCMPEJB and enter eis/jdbc/MyBank_CMP for the JNDI Name. Select Per connection factory from the Resource Authorization drop-down list. Click Next for the next step.

Figure 3-38: Installation wizard- Step 4
In Step 5, you can define a Data Source and a resource authorization for each entity EJB. We only had one Entity EJB, but we assigned it a Data Source to demonstrate the user interface.

To do the same, expand the Apply Multiple Mappings link as shown in Figure 3-39.

Figure 3-39: Installation wizard- Step 5

To map the EJB to a specific datasource, perform the following steps:
1. From the "Specify existing Resource JNDI name" drop-down list, select nodename:eis/jdbc/MyBank_CMP, where nodename is the name of your Linux server. In our case we selected linux:eis/jdbc/MyBank_CMP. This is the connection factory that corresponds to the Data Source that we created in 3.1.4, "Create the JDBC provider and Data Source" on page 94.
2. Select the checkbox for the EJB Module you want to assign the JNDI name to from the list. In our example the EJB name is Account.
3. Click the associated Apply for the JNDI name (Figure 3-40). Verify that eis/jdbc/MyBank_CMP appears under "JNDI Name" for MyBankCMPEJB.
  
  Figure 3-40: Installation wizard- Step 5
4. On the same panel select Per Connection Factory from the "Resource authorization" drop-down list and click the checkbox for the EJB Module again.
5. Click the associated Apply. Verify that the "Resource Authorization" for the Account EJB is Per Connection Factory.
6. Click Next for the next step.
In Step 6 (Figure 3-41) you can specify EJB references which exist in your application. These references will need to match the JNDI names set for the referenced enterprise beans. In our application we had two EJBs; the first two modules are referenced by ejb/MyBank/Account JNDI name; for transfer, the JNDI name is ejb/MyBank/Transfer.

Figure 3-41: Installation wizard- Step 6
In the Step 7 panel, you can specify the virtual host which the Web module will be assigned to. Check MyBankWeb and select the "Virtual Host" your application will use. Our application works with the default_host, as illustrated in Figure 3-42. Click Next.

Figure 3-42: Installation wizard- Step 7
In the Step 8 panel (Figure 3-43) you can specify which server you would like your application to be installed to. Since we were working with a WebSphere Application Server instance with a single application server, we did not have an option of a different server, but if you have multiple nodes, more values will appear in the "Clusters and Servers" text area. To follow our example, accept the default value and click Next for Step 9.

Figure 3-43: Installation wizard- Step 8
In the Step 9 panel, security can be defined and set for specific methods within your EJB module. In our example we have not set any security roles for the EJB module. Click Next to continue to Step 10.
Step 10 (Figure 3-44) presents a summary of the options that the wizard will use during the installation. Click Finish to submit these options.

Figure 3-44: installation Wizard - Step 10
The installation will begin. The progress will be displayed in the Workspace window of the console. When the installation has completed, you will see the messages shown in Figure 3-45.

Figure 3-45: Executing the application installation wizard
When the application has finished installing, save the configuration changes by clicking the Save to Master Configuration link in the installation window. When the Save window appears, expand View items with changes as shown in Figure 3-46. Note that some items will be updated or added to the master configuration. Click Save to accept the changes.

Figure 3-46: Finishing installation- Saving in master configuration

3.1.6 Updating the Web server plugin

This is the last step before actually testing your scenario: updating the Web server plugin.

To submit the changes to the Web server plugin configurator, click Environment → Update Web Server Plugin on the WebSphere admin console, as illustrated in Figure 3-47. Click OK to update the configuration file.

Figure 3-47: Updating Web server plugin
When it is done, the message in Figure 3-48 appears at the top of your screen.

Figure 3-48: Acknowledge updating Web server plugin
You can see the configuration file by clicking the link View or download the current web server plugin configuration file. Check that the new entry (as emphasized by a circle in Figure 3-49) is in the file. That entry is the URL subdirectory that you will use when you test the application.

Figure 3-49: plugin-cfg.xml file after upgrading Web server plugin
Start the MyBankCMP application via the administrative console. Expand Applications → Enterprise Applications as illustrated in Figure 3-50. Select the checkbox before the name of the application, MyBankCMP, and click Start.

Figure 3-50: Starting the MyBankCMP application

Now your bank application is ready to test, so let's check if it really works.

3.1.7 Testing the MyBankCMP sample application

As we discussed in 3.1, "JDBC provider scenario" on page 84, our scenario application has two functions. The first is to create accounts and the second is to move funds from one account to another.

Perform the following steps to test the application online:

Open your Web browser and access the MyBankCMP application with the following URL:

http://server_name:HTTPport/MyBankCMPWeb/

In our example, as shown in Figure 3-51, the server name is colombia and the port is 9080.

Figure 3-51: MyBankCMP start page
Click the Create an Account link. The screen shown in Figure 3-52 appears.

Figure 3-52: Create a new account

Enter the desired information for the account. In our example we created an account number 10 with 1000 in savings.

As soon as you click Create, a confirmation is returned, as shown in Figure 3-53. Use the same process to create another account with number 20, type savings, and starting balance of 2000.

Figure 3-53: Bank account created
Click the Transfer Funds link. You will now transfer 500 dollars from account 20 to account 10, as shown in Figure 3-54

Figure 3-54: Transfer funds
Click Transfer. The results of the transaction appear in the same frame, as illustrated on Figure 3-55.

Figure 3-55: Transfer successful

< Day Day Up >