1 - IBM



IBM WebSphere Adapter for JDBC

Quick Start Tutorial V 6.1

Creating a business object for a stored procedure and executing the stored procedure using the Execute operation.

Table of Contents

1.0 Introduction 5

1.1 Learning Objectives 5

1.2 Audience 5

1.3 Software prerequisites 5

2.0 Prerequisites to Run the Scenario 6

2.1 Configuration Prerequisites 6

Creating the authentication alias 6

Creating the tables and stored procedures 12

2.2 Extracting the Sample Files 14

3.0 Creating The Adapter Project In WebSphere Integration Developer 15

4.0 Scenario: Executing A Business Object (BO) Created From A Stored Procedure 17

4.1 Configuring the adapter for outbound processing 17

4.1.1 Generating Business Objects Using the External Service Wizard 17

4.2 Deploying The Scenario Module To The Test Environment 28

4.3 Testing the Module 30

5.0 Troubleshooting 32

Adapter fails to start up 32

Failure during adapter processing 32

Table of Figures

Figure 1: IBM WebSphere Integration Developer 6

Figure 2: Start the Server 7

Figure 3: Run Administrative Console 8

Figure 4: Log into WebSphere's Administrative Console 8

Figure 5: Global Security Menu 9

Figure 6: General Properties of Authentication 9

Figure 7: J2C Authentication Data Entries 10

Figure 8: Alias, User ID and Password For Authentication To WebSphere 11

Figure 9: Save Changes on WebSphere 12

Figure 10: Stored Procedure Wizard 14

Figure 11: Import Rar File 15

Figure 12: Connector Import 16

Figure 13: File Listing of Adapter 16

Figure 14: Business Integration's External Service 17

Figure 15: New External Service 18

Figure 16: Select The Imported Adapter 19

Figure 17: JDBC Driver Jar Files Are Added 20

Figure 18: The Adapter Style Options 21

Figure 19: Discovery Configuration – Connection Properties 22

Figure 20: Object Discovery and Selection 23

Figure 21: Configuration Properties for the Stored Procedure 24

Figure 22: Configure Composite Properties 25

Figure 23: Service Generation and Deployment Configuration 26

Figure 24: Service Location Properties 27

Figure 25: Generated Artifacts and Assembly Diagram 28

Figure 26: Add Project to Server 29

Figure 27: Add Project to Server 29

Figure 28: Test Module 30

Figure 29: Select Deployment Location 31

Introduction

The JDBC Resource Adapter (RA) enables the bi-directional connectivity for integration to any database application. The exchange of data for such applications happens at the database level. Updates to database may need to be applied to another EIS and changes in an EIS may need to be applied to a database. The JDBC RA can integrate with any database, as long as there is a JDBC driver that supports the JDBC 2.0 or higher Specification, available for the database. Examples of such databases include Oracle, Microsoft SQLServer, DB2, Sybase, Informix etc. 

The document demonstrates one scenario:

Creating a business object for a stored procedure and executing the stored procedure using the Execute operation. The scenario also demonstrates the support for resultsets returned by the stored procedure.

1 Learning Objectives

After completing a tutorial, you should be able to perform the following tasks:

• Create an adapter project in WebSphere Integration Developer.

• Discover services and associated business objects from the enterprise information system (EIS) and make them part of the adapter project.

• Create a deployable module that you install on WebSphere Process Server or WebSphere Enterprise Service Bus.

• Test the module and validate the results.

2 Audience

These tutorials are for integration developers who design, assemble, test, and deploy business integration solutions.

3 Software prerequisites

To use these tutorials, you must have the following applications installed:

WebSphere Integration Developer, version 6.1

WebSphere Process Server or WebSphere Enterprise Service Bus, version 6.1

Respective JDBC Database driver

Prerequisites to Run the Scenario

1 Configuration Prerequisites

Creating the authentication alias

The authentication alias needs to be set since the adapter uses the username/password set in the authentication alias to connect to the database. Here are the steps to set the authentication alias in WPS admin console. This authentication alias will be used later when generating the artifacts for the module.

1. In WebSphere Integration Developer, switch to the Servers View by selecting Windows > Show View > Servers.

[pic]

Figure 1: IBM WebSphere Integration Developer

2. To set the authentication alias, we need to start the server. In the Servers tab in the lower-right pane of the WebSphere Integration Developer screen, right-click on the server, and then select Start

[pic]

Figure 2: Start the Server

3. When the server status is Started, right-click on the server, and then select Run administrative console.

[pic]

Figure 3: Run Administrative Console

4. Log in to the administrative console by clicking the “Log in” button.

[pic]

Figure 4: Log into WebSphere's Administrative Console

5. Click on Security ( Global Security

[pic]

Figure 5: Global Security Menu

6. On the right, click on JAAS Configuration under the Authentication heading

[pic]

Figure 6: General Properties of Authentication

7. Click on the J2C Authentication data link. It gives the list of existing aliases.

[pic]

Figure 7: J2C Authentication Data Entries

8. If the SCA_Auth_Alias entry does not exist, click New and create it; else, click on SCA_Auth_Alias and set the proper username/password that can connect to the database. Click OK.

[pic]

Figure 8: Alias, User ID and Password For Authentication To WebSphere

9. A message appears asking to Save changes to the master configuration. Click on the Save link.

[pic]

Figure 9: Save Changes on WebSphere

Note: We have created an authentication alias that we will use when we configure the adapter properties. We need to re-start the server for the changes to take effect.

Creating the tables and stored procedures

The following tables and stored procedure need to be created in the DB2 database to run the scenario.

a. Script for creating the Customer and Address tables

CREATE TABLE CUSTOMER (

"PKEY" VARCHAR(10) NOT NULL PRIMARY KEY,

"FNAME" VARCHAR(20) ,

"LNAME" VARCHAR(20) ,

"CCODE" VARCHAR(10) ) ;

CREATE TABLE ADDRESS (

"ADDRID" VARCHAR(10) NOT NULL PRIMARY KEY,

"CUSTID" VARCHAR(10) ,

"CITY" VARCHAR(20) ,

"ZIPCODE" VARCHAR(10) ) ;

Enter the following records in the two tables:

INSERT INTO CUSTOMER VALUES (‘100’, ‘fname1’, ‘lname1’, ‘IBM’);

INSERT INTO CUSTOMER VALUES (‘300’, ‘abc, ‘xyz, ‘IBM’);

INSERT INTO ADDRESS VALUES (‘100’, ‘100’, ‘cxxx, ‘xxxx);

INSERT INTO ADDRESS VALUES (‘120’, ‘100’, ‘city1’, ‘zipcode1);

b. Creating the stored procedure

The stored procedure can be created using the DB2 Development Center or WID.

In WID, create a new stored procedure using the wizard.

Here’s the stored procedure sample;

|CREATE PROCEDURE CustAddrSP ( ) |

|SPECIFIC CustAddrSP |

|DYNAMIC RESULT SETS 1 |

|------------------------------------------------------------------------ |

|-- SQL Stored Procedure |

|------------------------------------------------------------------------ |

|P1: BEGIN |

|-- Declare cursor |

|DECLARE cursor1 CURSOR WITH RETURN FOR |

|SELECT CUSTOMER.FNAME, CUSTOMER.LNAME, ADDRESS.CITY, ADDRESS.ZIPCODE |

|FROM ADDRESS JOIN CUSTOMER ON ADDRESS.CUSTID = CUSTOMER.PKEY |

|ORDER BY ADDRESS.ZIPCODE ASC; |

| |

|-- Cursor left open for client application |

|OPEN cursor1; |

|END P1 |

Follow the steps: File > New > Other > Data > Stored Procedure, to get the appropriate wizard as shown in the below figure.

[pic]

Figure 10: Stored Procedure Wizard

5 Extracting the Sample Files

Replicas of the artifacts that were created when using the external service wizard are provided as sample files for your reference. Use these files to verify that the files you create with the external service wizard are correct.

Go to the “Sample” folder and unzip the zip file into a directory of your choice (you may want to create a new directory).

Below is a table of file contents that accompany this tutorial.

|Tutorial |Folders |Descriptions |

|1 |BOCustAddrExecDB2 |Generated artifacts, such as XML Schema Definitions |

| | |(xsd) and Web Services Definition Language (WDL). |

| |BOCustAddrExecDB2App |EAR application artifacts, such as deployment |

| | |descriptors. |

| |BOCustAddrExecDB2EJB |EJB module artifacts. |

Creating The Adapter Project In WebSphere Integration Developer

Create an adapter project by importing the resource adapter archive (.RAR) file into the Connector Projects folder in WebSphere Integration Developer. The following steps describe how to do this.

1. Launch WebSphere Integration Developer by clicking Start > Programs > IBM WebSphere > Integration Developer V6.1

2. In WebSphere Integration Developer, switch to the J2EE perspective. To do this, click Window > Open perspective > Other. In the Select perspective screen, select “Show all,” then select J2EE from the list and click OK.

3. Next we import the connector rar file. Right-click in the Project Explorer view, then select Import > RAR file.

[pic]

Figure 11: Import Rar File

4 In the Connector Import screen, click the Browse button next to the “Connector file” field, then navigate to the adapter .RAR file. Uncheck the ‘Add module to an EAR project’ checkbox.

[pic]

Figure 12: Connector Import

5. Accept all other defaults, then click Finish. A new connector project named CWYBC_JDBC appears under the Connector Projects folder.

[pic]

Figure 13: File Listing of Adapter

Scenario: Executing A Business Object (BO) Created From A Stored Procedure

1 Configuring the adapter for outbound processing

Run the enterprise service discovery wizard. This process includes generating the business objects and setting adapter configuration properties via the enterprise service discovery wizard.

1 Generating Business Objects Using the External Service Wizard

Follow these instructions to launch the External Service wizard.

1. Make sure you are in the Business Integration perspective. To do this, select Window > Open Perspective > Other. In the Select Perspective screen, select Business Integration (default).

2. In WebSphere Integration Developer, launch the Enterprise Service Discovery wizard by selecting File -> New -> Business Integration -> External Service.

[pic]

Figure 14: Business Integration's External Service

3. Select the New External Service, Adapters.

[pic]

Figure 15: New External Service

4. Click the NEXT > button.

[pic]

Figure 16: Select The Imported Adapter

Based on the type of adapter imported, the adapter is selected under the appropriate type.

In this case, “IBM WebSphere Adapter for JDBC (IBM : 6.1) is the type expected. Select the adapter by its name, “CWYBC_JDBC”.

[pic]

Figure 17: JDBC Driver Jar Files Are Added

If the JDBC Driver JAR files were not found on the project’s classpath, a window prompt would appear to have the necessary files added. Each type of database server requires its own JDBC driver jar files to carry out its operations. The drivers are shipped with the database server, locate the files and add them to the project. Java-based Type 4 database connectivity is recommended and works with these jar files. If Type 2 connectivity is required, locate the native system libraries and add them to the project.

[pic]

Figure 18: The Adapter Style Options

Based on the type of processing the adapter is required to perform either Inbound or Outbound, this step is as shown in the Figure above.

1 Setting Connection Properties for the External Service Wizard

To connect to the preferred database, select the database type and version and set the following properties:

1. JDBC driver type

2. Database

3. Host name

4. Port number

5. JDBC driver classname

6. User name

7. Password

For example, refer to the following figure as the next wizard page.

[pic]

Figure 19: Discovery Configuration – Connection Properties

Select the type and version of database server and specify the connection configuration properties, a database connection will be established to retrieve the database schema.

2 Selecting the Business Objects and Services To Be Used With the Adapter

Follow these steps to select the stored procedure:

1. In the ‘Object Discovery and Selection’ screen, there are the Run Query and Edit Query buttons. The ‘Edit Query’ will enable us to narrow the list of schemas, nodes, or objects in the database which is optional. Additionally, business object application specific information can be included. After the query has been edited, clicking the ‘Run Query’ button will list the tables, stored procedures and other database objects.

[pic]

Figure 20: Object Discovery and Selection

Selecting a particular stored procedure among the discovered objects in a database schema will prompt us to set the attribute by data type and sample value to execute it. To ensure the validity of the syntax of the stored procedure using the sample values, check this option.

[pic]

Figure 21: Configuration Properties for the Stored Procedure

2. Upon selecting the stored procedure ‘CUSTADDR’, a configuration properties window as shown in the figure above will be displayed. The stored procedure is expected to return a result set; so, we set it to one and its output fields are the attributes displayed by name and data type. The result is a validation of the stored procedure as a whole.

3 Configure Composite Properties

[pic]

Figure 22: Configure Composite Properties

With the discovered and selected stored procedure as a business object, the next step is to configure the composite properties that apply to it, a maximum number of records for “RetrieveAll” operation can be specified which by default is a hundred. Secondly, a business object namespace is default to the URI as shown in the above figure. If a desire to assign the generate business objects into a separate folder other than the default of the project’s, it can be specified by name. Lastly, the option is selected by default to generate a business graph for each business object.

4 Service Generation and Deployment Configuration

Based on the J2C Authentication alias created earlier on WebSphere, specify it as a ‘J2C Authentication Data Entry’ on the next screen in order to generate the service and deploy the configuration. Alternatively, the connection properties can be used instead, simply enter the password as all other properties are recalled. Refer to the following figure.

[pic]

Figure 23: Service Generation and Deployment Configuration

Based on the data source setup, the business service deployment will depend on its configuration details. This provides a secure and reliable means of retrieving the data.

5 Generating Business Object Definitions and Related Artifacts

[pic]

Figure 24: Service Location Properties

As part of the service location properties, a new integration project module needs to be created. Specify a module name and a location by folder where the WSDL and export files will be stored or simply leave the folder name empty and it will default to the module’s folder. An interface name is required and default to “JDBCOutboundInterface”.

Click Finish to end the External Wizard process. Therefore, we conclude with the artifacts that are created as shown in the following figure.

[pic]

Figure 25: Generated Artifacts and Assembly Diagram

Right after the wizard is done, a file listing of the newly created module and an assembly diagram of its interface will be displayed in WID as shown in the above figure.

2 Deploying The Scenario Module To The Test Environment

The result of running the External Service wizard is an SCA module that contains an EIS import. Install this SCA module in the WebSphere Integration Developer integration test client. To do this, you must add the SCA module you created earlier to the server using the Servers View in WebSphere Integration Developer.

Steps for adding the SCA module to the server:

1. In WebSphere Integration Developer, switch to the Servers View by selecting Windows > Show View > Servers.

2. In the Servers tab in the lower-right pane of the WebSphere Integration Developer screen, right-click on the server, and then select Start

3. When the server status is Started, right-click on the server, and then select Add and remove projects.

[pic]

Figure 26: Add Project to Server

4. In the Add and Remove Projects screen, select the module created earlier and click Add. The project moves to the ‘Configured Projects’ list from the ‘Available Projects’ list.

[pic]

Figure 27: Add Project to Server

5. Click Finish. This deploys the project on the server. For any problems adding the project, check the Troubleshooting section.

The Console tab in the lower-right pane displays a log while the module is being added to the server.

Note: Ensure that the database driver jar exists in the runtimes lib folder of WebSphere Process Server before starting the server.

3 Testing the Module

The integration module can be tested with the WID integrated test client by simply right-click anywhere in the assembly diagram and select “Test Module”. If the assembly diagram is closed, double-click on the file “sca.module” to display it.

[pic]

Figure 28: Test Module

Based on the configuration, module, component, interface and operation properties that are set by default, the test module is ready to be invoked.

1. Execute the service by clicking Continue [pic] button.

2. In the “Select Deployment location” screen as shown below, select the server, and click Finish.

[pic]

Figure 29: Select Deployment Location

With the successful deployment of the test module and execution, the result set should return the expected records that reflect the conditions stipulated in the stored procedure.

Troubleshooting

Adapter fails to start up

If the adapter fails to start up, refer the adapter log file to find the cause of failure. Here are some of the common reasons why the adapter would fail to start up.

1. Error: Driver class does not exist.

Cause: The database driver jar does not exist in the runtimes lib folder.

2. Error: Logon error; invalid username/password.

Cause: The authentication alias does not have the proper username/password that can connect to the database.

Failure during adapter processing

If the adapter fails during processing, refer to the adapter log file to find the cause of the failure.

1. Error: Primary key does not exist

Cause: The table does not have a primary key defined. Hence, the PrimaryKey ASI on the BO is not set to true.

2. Error: A record already exists

Cause: A record with the primary key already exists in the database. Try inserting a record with a primary key that does not exist in the database.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download