1



IBM WebSphere Adapter for JDBC

Quick Start Tutorial V 6.1

Configuring WebSphere adapter for JDBC for inbound processing by using Datasource with a prepared statement cache

Table of Contents

1.0 Introduction 4

1.1 Learning Objectives 4

1.2 Audience 4

1.3 Software prerequisites 4

2.0 Preparing to run through the tutorial 4

2.1 Configuration prerequisites 4

2.1.1 Creating an authentication alias……………………………………………....

2.1.2 Creating the data source………………………………………………………

2.1.3 Creating the tables…………………………………………………………….

2.2 Extracting the sample files 30

3.0 Tutorial 1: Receiving events from the Oracle database (inbound processing) 31

3.1 Configuring the adapter for inbound processing 31

3.1.1 Setting connection properties for the external service wizard 31

3.1.2 Selecting the business objects and services to be used with the adapter 41

3.1.3 Generating business object definitions and related artifacts 44

3.1.4 Setting up the components to be part of the Inbound Environment 44

3.2 Deploying the module to the test environment 58

3.3 Testing the assembled adapter application 61

3.4 Clearing the sample content 67

4.0 Troubleshooting 68

4.1 Test connection failure…………………………………………….68

4.2 Adapter fails to start……………………………………………….68

Introduction

The JDBC Resource Adapter 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 Resource Adapter 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.

To gain practical knowledge in setting up and deploying the adapter, complete one or more of the tutorials. Everything you need to complete each tutorial is contained in the tutorial. If you have performed the prerequisite tasks, you can complete each tutorial in under an hour.

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 version 6.1 or WebSphere Enterprise Service Bus

Preparing to run through the tutorial

1 Configuration prerequisites

Before doing any tutorial testing, complete the following tasks:

1 Creating an Authentication Alias

An authentication alias is a feature that encrypts the password used by the adapter to access the database. To create an authentication alias, you must have access to the administrative console. You must also know the user name and password to connect to the database.

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

[pic]

2. To run the administrative console, 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 select Start.

[pic]

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

[pic]

4. If your administrative console requires a user ID and password, type the ID and password and click Log in. If the user ID and password are not required, click Log in.

[pic]

5. In the administrative console, click Security > Secure administration, applications, and infrastructure.

[pic]

6. Under Authentication, click Java Authentication and Authorization Service > J2C Authentication data.

[pic]

7. In the list of J2C authentication aliases that is displayed, click New.

[pic]

8. In the Configuration tab, type the name you want to give for the authentication alias in the Alias field.

9. Type the User ID and password that are required to establish a connection to the database.

10. Optionally type a description of the alias.

11. Click OK. Note that in subsequent configuration windows you must refer to the alias by its full name which includes the node name.

[pic]

12. Click Save.

[pic]

1 Creating the Data Source

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

[pic]

2. To run the administrative console, 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 select Start.

[pic]

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

[pic]

4. If your administrative console requires a user ID and password, type the ID and password and click Log in. If the user ID and password are not required, click Log in.

[pic]

5. Click on Resources > JDBC > JDBC Providers.

[pic]

6. On the right, select Node=widNode from the dropdown list.

[pic]

7. On the right, click New on the JDBC providers window.

[pic]

8. Choose the values below for Database type, Provider type, and Implementation type. Click Next.

|Field |Value |

|Database type |Oracle |

|Provider type |Oracle JDBC Driver |

|Implementation type |Connection pool data source |

[pic]

9. Enter the absolute path of the JDBC driver (ojdbc14.jar) directory. Click Next.

[pic]

10. Click Finish.

[pic]

11. Click on the JDBC Provider that you just created.

[pic]

12. Click on Data sources, under Additional Properties.

[pic]

13. Click New.

[pic]

14. Enter “jdbc/OracleDS” for JNDI name.

15. Under Component-managed authentication alias and XA recovery authentication alias, select the name of the authentication alias you previously created from the dropdown list. Click Next.

[pic]

16. Enter the URL to connect to the database in the URL field. Click Next.

[pic]

17. Click Finish.

[pic]

18. In the Messages box, click on Save link. This will save changes made to the local configuration onto the master configuration.

[pic]

19. Click on the data source you just created.

[pic]

20. On the right, under Additional Properties click on Websphere Application Server data source properties.

[pic]

21. Enter the value “20” in the Statement cache size field. Click Ok.

[pic]

22. In the Messages box click Save.

[pic]

23. Check the checkbox next to the Data source you just created. Click on Test Connection.

[pic]

24. The connection test should succeed as indicated by the message shown in the figure below. For any problems with the connection test, refer to the Troubleshooting section (section 4.2).

[pic]

25. Close the Admin Console tab.

2 Creating the Tables

The following scripts need to be executed before starting the scenario. These scripts are responsible for creating the necessary tables, triggers, and records in the Oracle database such that the scenario works correctly.

1. Open an SQL Editor and connect to the Oracle database.

2. Run the scripts below:

a. Script for creating the tables

CREATE TABLE CUSTOMER (

PKEY VARCHAR2(10) NOT NULL PRIMARY KEY,

FNAME VARCHAR2(20),

LNAME VARCHAR(20) ,

CCODE VARCHAR2(10) ) ;

CREATE SEQUENCE EVENT_SEQ INCREMENT BY 1 START WITH 1 MINVALUE 1 CACHE 20 ;

CREATE TABLE WBIA_JDBC_EVENTSTORE

(

EVENT_ID INTEGER NOT NULL PRIMARY KEY,

XID VARCHAR2(200),

OBJECT_KEY VARCHAR2(80) NOT NULL,

OBJECT_NAME VARCHAR2(40) NOT NULL,

OBJECT_FUNCTION VARCHAR2(40) NOT NULL,

EVENT_PRIORITY INTEGER NOT NULL,

EVENT_TIME TIMESTAMP,

EVENT_STATUS INTEGER NOT NULL,

EVENT_COMMENT VARCHAR2(100)

);

b. Script for creating triggers for Inbound

CREATE OR REPLACE TRIGGER EVENT_CREATE AFTER INSERT ON CUSTOMER

REFERENCING OLD AS OLD NEW AS N

FOR EACH ROW

BEGIN

INSERT INTO wbia_jdbc_eventstore (event_id,object_key, object_name,object_function, event_priority, event_status)

VALUES (event_seq.nextval,:N.pkey, 'RtasserCustomerBG', 'Create', 1, 0);

END;

CREATE OR REPLACE TRIGGER EVENT_DELETE AFTER DELETE ON CUSTOMER

REFERENCING OLD AS OLD NEW AS N

FOR EACH ROW

BEGIN

INSERT INTO wbia_jdbc_eventstore (event_id,object_key, object_name,object_function, event_priority, event_status)

VALUES (event_seq.nextval,:N.pkey, 'RtasserCustomerBG', 'Delete', 1, 0);

END;

This is not consistent with the table name in page 28.

CREATE OR REPLACE TRIGGER EVENT_UPDATE AFTER UPDATE OF PKEY, CCODE, FNAME, LNAME ON CUSTOMER

REFERENCING OLD AS OLD NEW AS N

FOR EACH ROW

BEGIN

INSERT INTO wbia_jdbc_eventstore (event_id,object_key, object_name, object_function, event_priority, event_status)

VALUES (event_seq.nextval,:N.pkey, 'RtasserCustomerBG', 'Update', 1, 0);

END;

c. Script for inserting data in Customer table

CREATE OR REPLACE PROCEDURE RTASSER.INSERTCUSTRECORDS AS

BEGIN

FOR cntr in 1..100 LOOP

INSERT INTO RTASSER.CUSTOMER (pkey,ccode,fname,lname) values(to_char(cntr), 'ANITA','MEHTA','IBM');

End Loop;

END;

Note: After running this procedure, verify that the same number of records have been inserted into the WBIA_JDBC_EVENTSTORE table.

6 Extracting the sample files

Replicas of the artifacts that you create 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 Samples Gallery and unzip IBM_Websphere_Adapter_for_JDBC_6.1_QST_PreparedStatementCache_Oracle.zip into a directory of your choice (you may want to create a new directory).

Sample files provided:

• RtasserCustomer.xsd

• RtasserCustomerBG.xsd

Tutorial 1: Receiving events from the Oracle database (inbound processing)

This tutorial demonstrates how the WebSphere Adapter for JDBC 6.1 is used

to perform inbound processing by using a data source with a prepared statement cache.

1 Configuring the adapter for inbound processing

Run the external service wizard to specify business objects, services, and configuration to be used in this tutorial.

1 Setting connection properties for the external service wizard

Switch to the Business Integration perspective in WebSphere Integration Developer by choosing from the menu: Window > Open Perspective > Business Integration.

[pic]

1. Start the JDBC External Service wizard by choosing: File > New > Other.

[pic]

2. Expand Business Integration, select External Service and click Next.

[pic]

3. Select the Adapters radio button then click Next.

[pic]

4. Select IBM WebSphere Adapter for JDBC (IBM: 6.1). Click Next.

[pic]

5. Select the correct server type for Target Runtime from the dropdown list. Click Next.

[pic]

6. Click Add, to add the JDBC driver jar to the class path.

[pic]

7. Browse to the directory containing the jar file, select it, and click Open.

[pic]

8. The jar file is now displayed in the JDBC driver JAR files panel. Click Next.

[pic]

9. Click the Inbound radio button. Click Next.

[pic]

10. Use the left panel to select the Database vendor, driver, and version that you want to connect to.

11. On the right panel fill out the fields in the table below (some of the information might be provided by default). Click Next.

|Field |Value |

|Database | The name of the database |

|Host name | |

|Port Number | |

|User name |The user name of the database account you are using |

|Password |The password of the database account that you are |

| |using |

[pic]

1 Selecting the business objects and services to be used with the adapter

2 Click Run Query to list the tables, stored procedures, views, and synonyms for each schema in the database.

[pic]

1. Expand the schema name in which you created the CUSTOMER table. Select Tables > CUSTOMER and click the > (Add) button.

[pic]

2. The CUSTOMER table is now listed in the Selected Objects panel. Click Next.

[pic]

1 Generating business object definitions and related artifacts

1. In the Configure Composite Properties screen, leave all the default settings and click Next.

[pic]

2. Make sure that for the Deploy connector project field, the With module for use by single application option is selected from the dropdown list.

3. Click Advanced to see all of the properties under Database system connection information and fill out the Password field, with the password to connect to the database.

[pic]

4. Click on Advanced connection configuration to expand the additional properties.

5. For the Datasource JNDI name field enter the name of the data source you created, or if you used the name this tutorial suggested enter "jdbc/OracleDS".

[pic]

6. Click on Event configuration, and set the values shown below for the Event order by and Event table name fields. Click Next.

[pic]

7. In the Service Location Properties screen, click New next to the Module field.

[pic]

8. Make sure that Create a module project is selected and click Next.

[pic]

9. For the Module Name, type “JDBCInboundTest” and click Finish.

[pic]

10. Back at the Service Location Properties screen, click Finish.

[pic]

11. Verify the results shown below.

[pic]

2 Setting up the components to be part of the Inbound Environment

Next, we add components and set transaction specific properties on them so that they form part of the inbound environment.

1. In the Business Integration tab, under JDBCInboundTest double click on Assembly Diagram to open it. It shows the JDBCInboundInterface that was generated when the artifacts were generated in the previous section.

2. In the Palette, expand Components and click on Untyped Component.

[pic]

3. Click anywhere on the JDBCInboundTest -Assembly Diagram window (white part) to create the Untyped Component, Component1.

[pic]

4. To wire JDBCInboundInterface to Component1 hover the cursor over the right end of JDBCInboundInterface until a yellow wire appears.

[pic]

5. Click on the yellow wire and drag it to the left end of Component1. When the Add Wire pop-up window displays click Ok.

[pic]

6. Click File > Save from the toolbar to save changes made.

In the next step we generate the Java implementation for Component1.

7. Right click on Component1 in the Assembly Diagram and choose Generate implementation... > Java.

[pic]

8. In the Generate Implementation window, highlight the default package and click Ok.

[pic]

9. In the Implement As... pop-up window, click No.

10. The Java Editor will open showing the Component1Impl.java file.

[pic]

11. Scroll down and locate the method below:

public void createRtasserCustomerBG(DataObject createRtasserCustomerBGInput) {

//TODO Needs to be implemented.

}

Replace the entire method so that it looks like the one shown below:

public void createRtasserCustomerBG(DataObject createRtasserCustomerBGInput) {

System.out.println("CREATE Customer");

DataObject bo = createRtasserCustomerBGInput.getDataObject("RtasserCustomer");

System.out.println("Customer Key:" + bo.getString("pkey"));

System.out.println("Customer Code:" + bo.getString("ccode"));

System.out.println("Customer fname:" + bo.getString("fname"));

System.out.println("Customer lname:" + bo.getString("lname"));

System.out.println("CREATE End");

}

12. Scroll down and locate the method below:

public void updateRtasserCustomerBG(DataObject updateRtasserCustomerBGInput) {

//TODO Needs to be implemented.

}

Replace the entire method so that it looks like the one shown below:

public void updateRtasserCustomerBG(DataObject updateRtasserCustomerBGInput) {

System.out.println("UPDATE Customer");

DataObject bo = updateRtasserCustomerBGInput.getDataObject("RtasserCustomer");

System.out.println("Customer Key:" + bo.getString("pkey"));

System.out.println("Customer Code:" + bo.getString("ccode"));

System.out.println("Customer fname:" + bo.getString("fname"));

System.out.println("Customer lname:" + bo.getString("lname"));

System.out.println("UPDATE End");

}

13. Scroll down and locate the method below:

public void deleteRtasserCustomerBG(DataObject deleteRtasserCustomerBGInput) {

//TODO Needs to be implemented.

}

Replace the entire method so that it looks like the one shown below:

public void deleteRtasserCustomerBG(DataObject deleteRtasserCustomerBGInput) {

System.out.println("DELETE Customer");

DataObject bo = deleteRtasserCustomerBGInput.getDataObject("RtasserCustomer");

System.out.println("Customer Key:" + bo.getString("pkey"));

System.out.println("Customer Code:" + bo.getString("ccode"));

System.out.println("Customer fname:" + bo.getString("fname"));

System.out.println("Customer lname:" + bo.getString("lname"));

System.out.println("DELETE End");

}

14. Click on File > Save from the toolbar to save the changes made.

15. Close the Assembly Diagram and the Component1Impl.java class.

3 Deploying the module to the test environment

The result of running the external service wizard is an SCA module that contains an EIS import or export. Install this SCA module in WebSphere Integration Developer integration test client.

1. In WebSphere Integration Developer, switch to the Servers view by selecting from the toolbar Window > Show View > Servers.

2. In the Servers tab in the lower-right pane right click on the server, and then select Start.

3. Add the module you created earlier to the server using the server panel in WebSphere Integration Developer. Right-click on the server, and then select Add and remove projects.

[pic]

4. Add the SCA module to the server, by selecting JDBCInboundTestApp and clicking the Add > button.

[pic]

5. The module moves to the Configured Projects panel. Click Finish.

For any problems with deploying the project refer to the Troubleshooting section (section 4.2).

[pic]

4 Testing the assembled adapter application

1. In the Console view (in the lower right pane) you will see the messages that the application has successfully started and has received and processed the inbound events which were created in WBIA_JDBC_EVENTSTORE table. (These messages will also be reflected in your \profiles\wps\logs\server1\Systemout.log file).

[pic]

2. Since there are various threads retrieving and updating the events from the WBIA_JDBC_EVENTSTORE table, the listed events will not be in numerical order. Note the time difference between the first listed event and the last listed event.

First Event:

[11/7/07 11:51:39:921 PST] 00000055 SystemOut O CREATE Customer

[11/7/07 11:51:39:921 PST] 00000055 SystemOut O Customer Key:11

[11/7/07 11:51:39:921 PST] 00000055 SystemOut O Customer Code:ANITA

[11/7/07 11:51:39:921 PST] 00000055 SystemOut O Customer fname:MEHTA

[11/7/07 11:51:39:921 PST] 00000055 SystemOut O Customer lname:IBM

[11/7/07 11:51:39:921 PST] 00000055 SystemOut O CREATE End

Last Event:

[11/7/07 11:51:56:640 PST] 00000056 SystemOut O CREATE Customer

[11/7/07 11:51:56:640 PST] 00000056 SystemOut O Customer Key:99

[11/7/07 11:51:56:640 PST] 00000056 SystemOut O Customer Code:ANITA

[11/7/07 11:51:56:640 PST] 00000056 SystemOut O Customer fname:MEHTA

[11/7/07 11:51:56:640 PST] 00000056 SystemOut O Customer lname:IBM

[11/7/07 11:51:56:640 PST] 00000056 SystemOut O CREATE End

3. It should be around 17 seconds, which is approximately the amount of time that it takes to update 100 records in the WBIA_JDBC_EVENTSTORE table when using a Data Source with a Prepared Statement cache.

In the next steps, we are going to recreate 100 Customer records and verify that when not using a Data Source with Prepared Statement cache the time difference between the first and last listed events increases.

4. Open the Assembly Diagram again and click on JDBCInboundInterface.

[pic]

5. In the Properties tab, click on End-point configuration.

[pic]

6. Click on the Advanced button, to see the all properties.

[pic]

7. Under Advanced connection configuration, clear the value for the Datasource JNDI name field.

[pic]

8. Click on File > Save from the toolbar to save the changes made.

9. Close the Assembly Diagram.

10. Clean the CUSTOMER table by connecting to the oracle database and executing the following SQL query: TRUNCATE TABLE CUSTOMER;

11. Connect to the database and re-run the script in section 2.1.3 part C, to re-insert 100 records for the CUSTOMER table.

12. In the console view you will see the same messages you saw in step 1, which again will be reflected in your \profiles\wps\logs\server1\Systemout.log file.

[pic]

First Event:

[11/7/07 18:04:30:515 PST] 00000056 SystemOut O CREATE Customer

[11/7/07 18:04:30:515 PST] 00000056 SystemOut O Customer Key:1

[11/7/07 18:04:30:515 PST] 00000056 SystemOut O Customer Code:ANITA

[11/7/07 18:04:30:515 PST] 00000056 SystemOut O Customer fname:MEHTA

[11/7/07 18:04:30:515 PST] 00000056 SystemOut O Customer lname:IBM

[11/7/07 18:04:30:515 PST] 00000056 SystemOut O CREATE End

Last Event:

[11/7/07 18:04:59:703 PST] 00000054 SystemOut O CREATE Customer

[11/7/07 18:04:59:718 PST] 00000054 SystemOut O Customer Key:99

[11/7/07 18:04:59:718 PST] 00000054 SystemOut O Customer Code:ANITA

[11/7/07 18:04:59:718 PST] 00000054 SystemOut O Customer fname:MEHTA

[11/7/07 18:04:59:718 PST] 00000054 SystemOut O Customer lname:IBM

[11/7/07 18:04:59:718 PST] 00000054 SystemOut O CREATE End

13. Like you did before, calculate the time elapsed between the first listed event and the last listed event. It should be around 29 seconds. Obviously, the time taken was less in the first case (17 sec) because the Data Source with Prepared Statement Cache was used.

5 Clearing the sample content

Return the data to its original state by deleting the second set of Customer records you created in the Customer table by connecting to the database and running the SQL query: TRUNCATE TABLE CUSTOMER;

Troubleshooting

1 Test connection Failure

In case the data source test connection fails, ensure that the Class path set for the data source is valid. It should contain the absolute path for the driver jar file.

2 Adapter fails to start

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 directory specified.

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

Cause: The username/password specified is not proper.

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

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

Google Online Preview   Download