1 - IBM
IBM WebSphere Adapter for JDBC
Quick Start Tutorial V 6.1
Configuring WebSphere adapter for JDBC to use XA Datasource to establish connection to database
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 29
3.0 Tutorial 1: Sending data to the DB2 database (outbound processing) 30
3.1 Configuring the adapter for outbound processing 30
3.1.1 Setting connection properties for the external service wizard 30
3.1.2 Selecting the business objects and services to be used with the adapter 40
3.1.3 Generating business object definitions and related artifacts 43
3.1.4 Setting up the components to be part of an XA environment 43
3.3 Deploying the module to the test environment 70
3.4 Testing the assembled adapter application 73
3.5 Clearing the sample content 78
4.0 Troubleshooting 79
4.1 Test connection failure
4.2 Adapter fails to start
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 |DB2 |
|Provider type |DB2 Universal JDBC Driver Provider |
|Implementation type |XA data source |
[pic]
9. Enter the absolute path of the JDBC driver (db2jcc.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/DB2XADS” 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 values below for Database name and Server name. Click Next.
|Field |Value |
|Database name |The name of the database you want to connect to. |
|Server name |The name of the server where the database is located. |
[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. Check the checkbox next to the Data source you just created. Click on Test Connection.
[pic]
20. 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.1).
[pic]
21. Close the Admin Console tab.
1 Creating the Tables
The following table needs to be created in the DB2 database.
1. Open an SQL Editor and connect to the DB2 database.
2. Run the script below to create the CUSTOMER table in the database. This table is needed to run the scenario.
Script for creating the CUSTOMER table:
CREATE TABLE CUSTOMER (
"PKEY" INTEGER NOT NULL PRIMARY KEY,
"FNAME" VARCHAR(20) ,
"LNAME" VARCHAR(20) ,
"CCODE" VARCHAR(10) ) ;
7 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_4_DB2.zip into a directory of your choice (you may want to create a new directory).
Sample files provided:
• Db2adminCustomer.xsd
• Db2adminCustomerBG.xsd
• Db2adminCustomerContainer.xsd
Tutorial 1: Sending data to the DB2 database (outbound processing)
This tutorial demonstrates how the WebSphere Adapter for JDBC 6.1 is used
to perform XA transactions using an XA data source.
1 Configuring the adapter for outbound 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 Outbound 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.
4. Click on Alternate ways to specify connection information 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/DB2XADS". Click Next.
[pic]
6. In the Service Location Properties screen, click New next to the Module field.
[pic]
7. Make sure that Create a module project is selected and click Next.
[pic]
8. For the Module Name, type “JDBCXA” and click Finish.
[pic]
9. Back at the Service Location Properties screen, click Finish.
[pic]
10. Verify the results shown below.
[pic]
2 Setting up the components to be part of the XA environment
Next, we add components and set transaction specific properties on them so that they form part of the XA environment. Specifically in this section we will create two additional components (Component1 and Component2) and a new business object type called Source. This newly created business object type will contain all the attributes of the Customer business object as well as additional attributes needed to create the Customer business object and call the appropriate method on the adapter. We will wire Component1 to Component2 and Component2 to the JDBCOutboundInterface. We will create interfaces for Component1 and Component2. The interface for Component1 will have a method that takes in a business object of type Source. Component1 will create a Customer business object and pass it to the Component2's interface method. Component2 determines what method has to be invoked and calls this method on the JDBCOutboundInterface by passing the Customer business object as an input.
1. In the Business Integration tab, under JDBCXA double click on Assembly Diagram to open it. It shows the JDBCOutboundInterface 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 JDBCXA-Assembly Diagram window (white part) to create the Untyped Component, Component1.
[pic]
4. Following the same procedure add a second Component, Component2.
[pic]
5. To wire Component2 to JDBCOutboundInterface hover the cursor over the right end of Component2 until a yellow wire appears.
[pic]
6. Click on the yellow wire and drag it to the left end of JDBCOutboundInterface. When the Add Wire pop-up window displays click Ok.
[pic]
7. To add an Interface for Component2, right click on Component2 and select Add > Interface.
[pic]
8. In the Add Interface window, click New.
[pic]
9. In the New Interface Wizard, enter "CustomerInterface" in the Name field. Click Finish. Click on File > Save from the toolbar to save the changes made.
[pic]
10. Wire Component1 to Component2 in the same way you wired Component2 to JDBCOutboundInterface in steps 5 and 6.
[pic]
11. Before we create an interface for Component1 we will create the Source BO which will be the input to the Component1 interface. To create a new BO, right click on Data Types under JDBCXA in the Business Integration tab and select New > Business Object.
[pic]
12. In the New Business Object window, type "Source" in the Name field. Click Next.
[pic]
13. From the list of Available business objects on the left, select the checkbox for Customer to add all of the Customer BO's attributes to the Source BO. Click Finish.
[pic]
14. The new Source BO is displayed with the four attributes from the Customer BO. We will add three additional attributes. To do this follow the steps below:
a. Click on the Add a field to a business object icon. A new attribute is added to the BO called Field1.
[pic]
b. Rename the attribute to "operation" and make sure that the type is set as string.
c. Repeat these steps to create the two remaining attributes. In step 14b rename one attribute to "objectName" and the other to "namespace" and make sure their types are set as string.
[pic]
Note: The attribute operation is to specify the method to be called on the adapter. For the current scenario, we only support the following values for this attribute: Create, Update, Delete, and Retrieve. The namespace and objectName attributes are needed for the Component1 implementation to generate the Customer data object to be passed to Component2.
15. Click on File > Save from the toolbar to save the changes made.
16. Create the interface for Component1 like you did for Component2 in steps 7 through 9. In step 9, enter "TriggerInterface" in the Name field.
17. To add a method to the TriggerInterface, click on Component1 in the Assembly Diagram and in the Properties tab below, click on Details.
[pic]
18. Expand Interfaces, right click on TriggerInterface and choose Open Interface.
[pic]
19. In the TriggerInterface window click on the Add One Way Operation button.
[pic]
20. This shows the default method Name as "operation1". Rename it to "start" and click on the type for input1 and select Source from the list.
[pic]
21. Click on File > Save from the toolbar to save the changes made.
22. To add a method to the CustomerInterface, we will follow the same procedure as we did for Component1. Click on Component2 in the Assembly Diagram and in the Properties tab below, click on Details.
23. Expand Interfaces, right click on CustomerInterface and choose Open Interface.
24. In the CustomerInterface window click on the Add One Way Operation button.
25. This shows the default method Name as "operation1". Rename it to "invoke" and click on the type for input1 and select CustomerBG from the list.
[pic]
26. Click on File > Save from the toolbar to save the changes made.
In the next steps we create the Java classes that will contain the implementation for the interface methods of Component1 and Component2.
27. Right click on Component1 in the Assembly Diagram and choose Generate implementation... > Java.
[pic]
28. In the Generate Implementation window, choose default package and click Ok.
29. In the Implement As... pop-up window, click No.
30. The generated class contains the start() method which we will implement. To do this replace the start() method with the code that is given below.
public void start(DataObject input1) {
// Create CustomerBO
String adapterNamespace = input1.getString("namespace");
String objName = input1.getString("objectName");
DataObject customerBO = null;
try
{
customerBO = SDOFactory.createDataObject(adapterNamespace, objName);
}
catch(Exception ex)
{
System.out.println("Invalid value provided for Namespace and/or ObjectName: " + ex.getMessage());
ex.printStackTrace();
}
if (customerBO != null)
{
DataObject customerBG = customerBO.getContainer();
System.out.println(" *** Customer Object Initialized *** ");
// Populate CustomerBO
String operation = input1.getString("operation");
if (operation.equalsIgnoreCase("create"))
{
customerBG.set("verb", operation);
}
if (operation.equalsIgnoreCase("update"))
{
customerBG.set("verb", operation);
}
if (operation.equalsIgnoreCase("delete"))
{
customerBG.set("verb", operation);
}
customerBO.set("pkey", input1.getString("pkey"));
customerBO.set("fname", input1.getString("fname"));
customerBO.set("lname", input1.get("lname"));
customerBO.set("ccode", input1.get("ccode"));
System.out.println(" *** Customer Object created *** ");
//Call the invoke method of Component2 and pass it the Customer object
Service customerService = locateService_CustomerInterfacePartner();
customerService.invoke("invoke", customerBG);
}
}
31. Add the following import statement below the other import statements in the java class: "import com.ibm.j2ca.base.SDOFactory;"
[pic]
32. Click on File > Save from the toolbar to save the changes made.
33. Repeat steps 27 - 32 for Component2 but skip step 31. In step 30 the generated class will contain the method invoke(). Replace this method with the code given below.
public void invoke(DataObject input1) {
Service customerService = locateService_JDBCOutboundInterfacePartner();
//Check the verb and call the appropriate method on JDBCOutboundInterface
String verb = input1.getString("verb");
String objType = input1.getType().getName();
if (verb.equalsIgnoreCase("create"))
{
customerService.invoke("create" + objType, input1);
}
if (verb.equalsIgnoreCase("update"))
{
customerService.invoke("update" + objType, input1);
}
if (verb.equalsIgnoreCase("delete"))
{
customerService.invoke("delete" + objType, input1);
}
if (verb == null || verb.equals(""))
{
customerService.invoke("retrieve" + objType, input1);
}
}
In the next steps, we set the transaction properties for Component2 and the JDBCOutboundInterface.
34. To set the transaction properties for Component2, click on Component2 in the Assembly Diagram and in the Properties tab click on Implementation.
[pic]
35. In the Qualifiers tab, click on the Add button.
[pic]
36. In the Add Qualifier window, choose Transaction and click Ok.
[pic]
37. In the Qualifiers tab, click on Transaction and under Properties of Qualifier Transaction select Global from the dropdown list for the Value field.
[pic]
38. In Component2's Properties tab, click on Details. Expand Interfaces and click on CustomerInterface.
[pic]
39. In the Qualifiers tab, click on the Add button.
[pic]
40. In the Add Qualifier window that opens up, select Join transaction and click Ok.
[pic]
41. In the Qualifiers tab, click on Join transaction and under Properties of Qualifier Join Transaction select True from the dropdown list for the Value field.
[pic]
42. To set the transaction properties for JDBCOutboundInterface, click on JDBCOutboundInterface in the Assembly Diagram and in the Properties tab click on Details. Expand Interfaces and click on JDBCOutboundInterface.
43. Continue by repeating steps 39 – 41.
44. Click on File > Save from the toolbar to save the changes made.
45. This is how the module looks after adding the components and their interfaces.
[pic]
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 JDBCXAApp and clicking the Add > button.
[pic]
5. The module moves to the Configured Projects panel. Click Finish.
[pic]
4 Testing the assembled adapter application
Test the assembled adapter application using the WebSphere Integration Developer integration test client:
1. From the Business Integration view, right click on JDBCXA and select Test > Test Module.
[pic]
2. For the Component field select “Component1” from the dropdown list.
3. Populate values for the input business object (input1) by selecting the values shown in the figure below.
[pic]
Note: You will set the operation value to "Create" meaning that the create operation will be called on the adapter. You will set the objectName value to CustomerBG. Lastly, you will set the namespace value to the target namespace value for the CustomerBG. You can open the CustomerBG.xsd with the text editor and check the value for the target namespace. It will be something like "".
4. Execute the service by clicking on the green Continue button.
[pic]
5. In the Deployment Location window select your server, and then click Finish.
[pic]
6. Check the output of the service with the one below, and check that the Customer record was created in your DB2 database by connecting the database and running the SQL query: SELECT * FROM CUSTOMER WHERE pkey = '300';
[pic]
5 Clearing the sample content
Return the data to its original state by deleting the Customer record you created in the Customer table by connecting to the database and running the SQL query: DELETE FROM CUSTOMER WHERE pkey = '300';
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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.