IBM Data Studio Data Web Services, Part 3: Use a WebSphere Application Server Community Edition Web server with DB2 and Informix databases

IBM® Data Studio's Data Web Services allows you to quickly and easily expose DB2® for Linux®, UNIX®, and Windows®, DB2 for z/OS®, DB2 for iSeries®, or Informix® data source SQL scripts or stored procedures as Web services. One of the supported Web servers is the WebSphere® Application Server Community Edition V1.1.0.2, a free Web server from IBM. Using a local instance of a WebSphere Application Server Community Edition Web server and a Data Web Services supported data source, Data Web Services supports automatic JDBC driver installation and automatic database pool registration as well as automatic deployment. However, there are instances where you may not wish to use these automatic features. For instance, if your WebSphere Application Server Community Edition Web server is remote, you may first need to manually install the necessary JDBC drivers onto the Web server. Or, perhaps you want to use your own custom database pool. This tutorial takes you through the steps of working with IBM Data Studio's Data Web Services and the IBM DB2 and Informix family of databases with WebSphere Application Server Community Edition.

Michael L. Pauser (pauser@us.ibm.com), Developer, IBM

Michael is the technical lead for Data Studio's Data Web Services UI. He has extensive experience in UI development for both client-side applications as well as Web-based applications.



Patsy Yu (patsyyu@us.ibm.com), Software Engineer, IBM

Patsy is a developer for Data Studio. She has extensive experience with Web services.



13 March 2008

Also available in Vietnamese

About this tutorial

This tutorial is part of the series of IBM Data Studio Data Web Services tutorials.

Objectives

In this tutorial, you will learn to:

  • Add a WebSphere Application Server Community Edition Web server instance.
  • Launching the WebSphere Application Server Community Edition Administrative Console.
  • Manually install DB2 and Informix JDBC drivers onto a WebSphere Application Server Community Edition Web server.
  • Create a custom WebSphere Application Server Community Edition database pool for use with Data Web Services.
  • Deploy Data Web Services using a WebSphere Application Server Community Edition database pool.

Prerequisites

This tutorial is written for database programmers who are familiar with IBM Data Web Services and WebSphere Application Server Community Edition. If you are unfamiliar with any of these, you may wish to view Part 1 of this series.

This tutorial assumes that you have the following products installed:

  • IBM DB2 for Linux, UNIX, Windows V9.5. However, you can use any supported Data Web Service DB2 database. In addition, you must have the DB2 SAMPLE database installed. If you do not have this SAMPLE database installed, refer to your DB2 Information Center for instructions on installation of the SAMPLE database.
  • IBM Data Studio V1.1. During installation, ensure that the Data Web Services Development component is selected.
  • IBM WebSphere Application Server Community Edition V1.1.0.2, a free Web server from IBM. A WebSphere Application Server Community Edition install image may be bundled with your IBM Data Studio installation. If not, visit the WebSphere Application Server Community Edition download page, and follow the Previous version(s) link for additional information or to download the WebSphere Application Server Community Edition V1.1.0.2 installation image.
  • A Data Development Project with a Data Web Service. This tutorial uses the Data Development Project from Part 1 of this series.

Overview

As discussed in Part 1 series, exposing your SQL scripts and stored procedures as a Web service is an easy task to perform. As Data Web Services supports multiple Web servers and multiple databases, each has its own nuances. This tutorial focuses on using WebSphere Application Server Community Edition and show you how to:

  • Add a WebSphere Application Server Community Edition Web server instance.
  • Launching the WebSphere Application Server Community Edition Administrative Console.
  • Manually install DB2 and Informix JDBC drivers onto a WebSphere Application Server Community Edition Web server.
  • Create a custom WebSphere Application Server Community Edition database pool for use with Data Web Services.
  • Deploy a Data Web Services using a WebSphere Application Server Community Edition database pool.

Add a WebSphere Application Server Community Edition Web server instance

A Web server instance is simply a definition to an existing Web server. To add a WebSphere Application Server Community Edition Web server instance to your workspace, follows these steps:

  1. From the IBM Data Studio menubar, select Window > Show View > Other... > Server, and select the Servers node.
    Figure 1. Show View
    Show View
  2. Click OK. A Servers tab appears in your workspace.
    Figure 2. Servers tab
    Servers tab
  3. On the Servers tab, right-click in the whitespace and select New > Server. The New Server wizard appears.
  4. On the Define a New Server page, select the desired Data Web Services supported Web server. In this example, select WebSphere Application Server Community Edition v1.1.
    Figure 3. Define a new server
    Define a new server
  5. Click Next.
  6. On the Runtime page, specify the directory where your existing Web server is installed. In this example, the WebSphere Application Server Community Edition V1.1 Web server is installed in c:\wasce\.
    Figure 4. New WebSphere Community Edition Server V1.1 Runtime
    New WebSphere Community Edition Server V1.1 Runtime
  7. Click Next.
  8. The Server page lists various ports and the administrator user ID and password. For this example, accept the defaults.
    Figure 5. New WebSphere Community Edition Server V1.1 Server
    New WebSphere Community Edition Server V1.1 Server
  9. Click Next.
  10. On the Add and Remove Projects page, click Next.
  11. Finally, on the Select Tasks page, click Finish to create the Web server instance. The Servers tab now lists your Web server instance.
    Figure 6. New WebSphere Application Server Community Edition V1.1 server instance
    New WebSphere Application Server Community Edition V1.1 server instance
  12. Test that your Web server instance is valid by starting the instance. To do so, select the Web server instance, right-click, and select Start. The Web server instance is started and should display a Started status.
    Figure 7. New WebSphere Application Server Community Edition V1.1 Server instance started
    New WAS-CE v1.1 Server instance started

Launch the WebSphere Application Server Community Edition Administrative Console

Much of this tutorial uses the WebSphere Application Server Community Edition Administrative Console. To launch the Administrative Console, follow these steps:

  1. From the Servers view, right-click on the WebSphere Application Server Community Edition Web server instance and selecting Launch Community Edition Console. A login page for the WebSphere Application Server Community Edition Web server instance appears.
    Figure 8. WebSphere Application Server Community Edition Administrative Console login
    WebSphere Application Server Community Edition Administrative Console login
  2. Specify the username (the default is system) and password (the default is manager) for your Web server instance, and click Login. The main Administrative Console screen appears.
    Figure 9. WebSphere Application Server Community Edition Administrative Console main screen
    WebSphere Application Server Community Edition Administrative Console main screen

Install DB2 JDBC drivers

WebSphere Application Server Community Edition V1.1.0.2 does not include the latest version of the DB2 or Informix JDBC drivers nor all license .jar files needed for JDBC access by the WebSphere Application Server Community Edition Web server. If you are using i5/OS, you may also opt to use the IBM Toolbox for Java drivers. The following sections detail the installation of these DB2 JDBC drivers.

When accessing DB2 databases, the same db2jcc.jar file can be used to access multiple versions of DB2 on multiple platforms. For example, the same db2jcc.jar file can be used to access DB2 for Linux, UNIX, and Windows, DB2 for z/OS, DB2 for i5/OS, and Informix V11 or later. However, each DB2 platform requires an additional license file that must also be installed. For DB2 for Linux, UNIX, and Windows, the license file is db2jcc_license_cu.jar. For DB2 for z/OS and DB2 for i5/OS, the license file is db2jcc_license_cisuz.jar. While you typically only need to install a single instance of each of these files onto your WebSphere Application Server Community Edition Web server, it is recommended that you install a separate instance for each DB2 version, as shown in the sections that follow.

If you want to use the JDBC drivers shipped with Data Studio for accessing DB2 for Linux, UNIX, and Windows, DB2 for z/OS, DB2 for i5/OS, or Informix V11 or later , you can find them in the {Data Studio shared install directory}/plugins/com.ibm.datatools.db2_n.n.n.vyyyymmddHHMM/driver/ directory, where "n" is a number and "yyyymmddHHMM" is a timestamp. For Windows, the default {Data Studio shared installation directory} is C:\Program Files\IBM\SDP70Shared\. For Linux, the default {Data Studio shared install directory} is /opt/IBM/SDP70Shared/. For example, on Windows, the complete path to the file db2jcc.jar might be C:\Program Files\IBM\SDP70Shared\plugins\com.ibm.datatools.db2_1.0.100.v200709182330\driver\db2jcc.jar.

For i5/OS users, if you want to use the IBM Toolbox for Java drivers that shipped with Data Studio, you can find the jt400.jar file in the {Data Studio shared install directory}/plugins/com.ibm.datatools.db2.iseries_n.n.n.vyyyymmddHHMM/driver/ directory, where "n" is a number and "yyyymmddHHMM" is a timestamp. For Windows, the default {Data Studio shared install directory} is C:\Program Files\IBM\SDP70Shared\. For Linux, the default {Data Studio shared install directory} is /opt/IBM/SDP70Shared/. For example, on Windows, the complete path to the file jt400.jar might be C:\Program Files\IBM\SDP70Shared\plugins\com.ibm.datatools.db2.iseries_2.1.100.v200709182330\driver\jt400.jar.

For Informix V10 or earlier users, if you want to use the JDBC drivers that shipped with Data Studio, you can find the ifxjdbc.jar file in the {Data Studio shared install directory}/plugins/com.ibm.datatools.informix.driver_n.n.n.vyyyymmddHHMM/driver/ directory, where "n" is a number and "yyyymmddHHMM" is a timestamp. For Windows, the default {Data Studio shared install directory} is C:\Program Files\IBM\SDP70Shared\. For Linux, the default {Data Studio shared install directory} is /opt/IBM/SDP70Shared/. For example, on Windows, the complete path to the file ifxjdbc.jar might be C:\Program Files\IBM\SDP70Shared\plugins\com.ibm.datatools.informix.driver_1.0.0.v200709182330\ifxjdbc.jar.

Install db2jcc.jar

To install the db2jcc.jar file used by DB2 for Linux, UNIX, and Windows, DB2 for z/OS, DB2 for i5/OS, and Informix V11 and later onto the WebSphere Application Server Community Edition Web server, follow these steps:

  1. From the Console Navigation on the left-hand side of the main page, under Services select Common Libs. The Repository Viewer: Add Archive to Repository wizard is displayed.
  2. In the File field, specify the complete path or use the Browse... button to select the db2jcc.jar file that you want to install. For example, C:\Program Files\IBM\SDP70Shared\plugins\com.ibm.datatools.db2_1.0.100.v200709182330\driver\db2jcc.jar.
  3. In the Group field, specify com.ibm.db2.
  4. In the Artifact field, specify db2jcc.
  5. In the Version field, specify the version. For example, you would enter "9.5" for V9.5.
  6. In the Type field, specify jar.
    Figure 10. DB2 for Linux, UNIX, and Windows V9.5 db2jcc.jar
    DB2 for Linux, UNIX, and Windows V9.5 db2jcc.jar
  7. Click Install. The selected file is installed into the WebSphere Application Server Community Edition common libs repository.

Install db2jcc_license_cu.jar

To install the db2jcc_license_cu.jar file used by DB2 for Linux, UNIX, and Windows and Informix V11 or later onto the WebSphere Application Server Community Edition Web server, follow these steps:

  1. From the Console Navigation on the left-hand side of the main page, under Services select Common Libs. The Repository Viewer: Add Archive to Repository wizard is displayed.
  2. In the File field, specify the complete path or use the Browse... button to select the db2jcc_license_cu.jar file that you want to install. For example, C:\Program Files\IBM\SDP70Shared\plugins\com.ibm.datatools.db2_1.0.100.v200709182330\driver\db2jcc_license_cu.jar.
  3. In the Group field, specify com.ibm.db2.
  4. In the Artifact field, specify db2jcc_license_cu.
  5. In the Version field, specify the version. For example, you would enter "9.5" for V9.5.
  6. In the Type field, specify jar.
    Figure 11. DB2 for Linux, UNIX, and Windows V9.5 db2jcc_license_cu.jar
    DB2 for Linux, UNIX, and Windows V9.5 db2jcc_license_cu.jar
  7. Click Install. The selected file is installed into the WebSphere Application Server Community Edition common libs repository.

Install db2jcc_license_cisuz.jar

To register the db2jcc_license_cisuz.jar file used by DB2 for z/OS and DB2 for i5/OS onto the WebSphere Application Server Community Edition Web server, follow these steps:

  1. From the Console Navigation on the left-hand side of the main page, under Services select Common Libs. The Repository Viewer: Add Archive to Repository wizard is displayed.
  2. In the File field, specify the complete path or use the Browse... button to select the db2jcc_license_cu.jar file that you want to install. For example, C:\Program Files\IBM\SDP70Shared\plugins\com.ibm.datatools.db2_1.0.100.v200709182330\driver\db2jcc_license_cisuz.jar.
  3. In the Group field, specify com.ibm.db2.
  4. In the Artifact field, specify db2jcc_license_cisuz.
  5. In the Version field, specify the version. For example, you would enter "9.1" for z/OS V9.1 or "5" for i5/OS.
  6. In the Type field, specify jar.
    Figure 12. DB2 for z/OS V9.1 db2jcc_license_cisuz.jar
    DB2 for z/OS V9.1 db2jcc_license_cisuz.jar
  7. Click Install. The selected file is installed into the WebSphere Application Server Community Edition common libs repository.

Install jt400.jar

To install the IBM Toolbox for Java jt400.jar file used by DB2 for i5/OS onto the WebSphere Application Server Community Edition Web server, follow these steps:

  1. From the Console Navigation on the left-hand side of the main page, under Services select Common Libs. The Repository Viewer: Add Archive to Repository wizard is displayed.
  2. In the File field, specify the complete path or use the Browse... button to select the jt400.jar file that you want to install. For example, C:\Program Files\IBM\SDP70Shared\plugins\com.ibm.datatools.db2.iseries_2.1.100.v200709182330\driver\jt400.jar.
  3. In the Group field, specify com.ibm.as400.access.jt400.
  4. In the Artifact field, specify jt400.
  5. In the Version field, specify 5.
  6. In the Type field, specify jar.
    Figure 13. DB2 for i5/OS jt400.jar
    DB2 for i5/OS jt400.jar
  7. Click Install. The selected file is installed into the WebSphere Application Server Community Edition common libs repository.

Install ifxjdbc.jar

To install the ifxjdbc.jar file used by Informix V10 or earlier onto the WebSphere Application Server Community Edition Web server, follow these steps:

  1. From the Console Navigation on the left-hand side of the main page, under Services select Common Libs. The Repository Viewer: Add Archive to Repository wizard is displayed.
  2. In the File field, specify the complete path or use the Browse... button to select the ifxjdbc.jar file that you want to install. For example, C:\Program Files\IBM\SDP70Shared\plugins\com.ibm.datatools.informix.driver_1.0.0.v200709182330\ifxjdbc.jar.
  3. In the Group field, specify com.informix.jdbc.
  4. In the Artifact field, specify ifxjdbc.
  5. In the Version field, specify 10.
  6. In the Type field, specify jar.
    Figure 14. Informix V10 ifxjdbc.jar
    Informix V10 ifxjdbc.jar
  7. Click Install. The selected file is installed into the WebSphere Application Server Community Edition common libs repository.

Create a custom WebSphere Application Server Community Edition database pool

Follow these steps to create a custom WebSphere Application Server Community Edition database pool:

  1. From the Console Navigation on the left-hand side of the main page, under Services select Database Pools. A list of defined database pools is displayed.
  2. Click Using the Geronimo database pool wizard. The Database Pools: Create Database Pool wizard, Step 1: Select Name and Database screen is displayed.
  3. In the Name of Database Pool field, specify the name of your database pool. During the deployment of the Data Web Service, this name is specified as the artifact.dataSourceArtifactId parameter value. In this example, SAMPLE is specified.
    Figure 15. Name of Database Pool
    Name of Database Pool
  4. In the Database Type field, select either DB2 or Informix. In this example, DB2 was selected.
    Figure 16. Database Type
    Database Type
  5. Click Next. The Step 2: Select Driver, JAR, Parameters screen is displayed.
  6. In the JDBC Driver Class field:
    • For DB2 for Linux, UNIX, and Windows, DB2 for z/OS, DB2 for i5/OS, or Informix V11 and later, specify com.ibm.db2.jcc.DB2Driver.
    • For DB2 for i5/OS using the IBM Toolbox for Java, specify com.ibm.as400.access.AS400JDBCDriver.
    • For Informix V10 or earlier, specify com.informix.jdbc.IfxDriver.
    In this example, com.ibm.db2.jcc.DB2Driver was specified.
    Figure 17. JDBC Driver Class
    JDBC Driver Class
  7. In the Driver JAR field, select the JDBC driver .jar file(s) that you installed in the previous step. In this example, since you want to connect to a DB2 for Linux, UNIX, and Windows V9.5 database, both com.ibm.db2/db2jcc/9.5/jar and com.ibm.db2/db2jcc_license_cu/9.5/jar were specified.
    Figure 18. Driver JAR
    Driver JAR
  8. In the DB User Name field, specify the user ID used to connect to the database. In this example, db2admin was used.
    Figure 19. DB User Name
    DB User Name
  9. In the DB Password field, specify the password of the user ID used to connect to the database.
    Figure 20. DB Password
    DB Password
  10. In the Port field, specify the port used to connect to the database. In this example, 50000 was used.
    Figure 21. Port
    Port
  11. In the Database field, specify the name of the database to connect to. In this example, SAMPLE was used.
    Figure 22. Database
    Database
  12. In the Host field, specify the name or TCP/IP address of the host to connect to. In this example, localhost was used.
    Figure 23. Host
    Host
  13. Click Next. The Step 3: Final Pool Configuration screen is displayed.
  14. Verify that the JDBC Connect URL field contains the correct URL. You can compare this connection URL with that displayed in the Data Studio connection Properties view. In this example, the JDBC connect URL was left as jdbc:db2://localhost:50000/SAMPLE.
    Figure 24. JDBC Connect URL
    JDBC Connect URL
  15. The remaining Connection Pool Parameters can be left blank as the default values are used if no value is specified. However, you can choose to specify your own values. In this example, the values were left blank and the default values will be used.
    Figure 25. Connection Pool Parameters
    Connection Pool Parameters
  16. Click Test Connection to validate that WebSphere Application Server Community Edition is able to connect to the specified database using the information that you provided. If the test fails, correct the error and try again.
  17. Click Deploy. The database pool is created and is now available for use by Data Web Services.

Deploy Data Web Services using a WebSphere Application Server Community Edition database pool

Whenever deploying a Data Web Service to a WebSphere Application Server Community Edition Web server has been discussed to this point, the default Register database connection with Web server has been used, which specifies that Data Web Services should automatically register your selected database connection with the Web server. However, since you want to use the custom database pool you defined in the previous section, you must provide some additional information during Build and Deploy....

  • Unselect the Register database connection with Web server check box. This triggers additional database pool-related parameters to be displayed in the Parameters section.
    Figure 26. Register database connection with Web server
    Register database connection with Web server
  • In the Parameters section, change the parameter value for artifact.dataSourceArtifactId to be the name of your database pool. In this example, a value of SAMPLE has been specified.
    Figure 27. artifact.dataSourceArtifactId
    artifact.dataSourceArtifactId

Once deployed, the Data Web Service is configured to use the custom database pool, which you created in the previous section.


Conclusion

With IBM Data Studio's Data Web Services, you have the flexibility to have Data Web Services automatically register a database pool for your Web service or allow you to define your own database pool for use with the Web service.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, SOA and web services, WebSphere
ArticleID=294622
ArticleTitle=IBM Data Studio Data Web Services, Part 3: Use a WebSphere Application Server Community Edition Web server with DB2 and Informix databases
publish-date=03132008