IBM WebSphere Adapter for JDBC

Quick Start Tutorial V 6.1

Creating business objects for user-defined insert/update/delete SQL statements and executing the batch of SQL statements using execute operation

Table of Contents

1.0Introduction

1.1Learning Objectives

1.2Audience

1.3Software prerequisites

2.0Preparing to run through the tutorial

2.1Configuration prerequisites

2.1.1Creating the tables

2.1.2Creating the authentication alias

2.2Creating the data source

2.3Extracting the sample files

3.0Sending Data to EIS (Outbound processing)

3.1Configuration prerequisites

3.2Configuring the adapter for inbound processing

3.2.1Setting properties for the external service wizard

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

3.2.3Generating business object definitions and related artifacts

3.3Deploying the module to the test environment

3.4Testing the assembled adapter application

3.5Clearing the sample content

4.0Troubleshooting

4.1Adapter fails to start up

4.2Failure during adapter processing

1.0Introduction

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

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.1Learning 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.

1.2Audience

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

1.3Software prerequisites

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

  • WebSphere Integration Developer version 6.1
  • WebShpere Process Server version 6.1or WebSphere Enterprise Service Bus.

2.0Preparing to run through the tutorial

2.1Configuration prerequisites

Before doing any tutorial testing, complete the following tasks:

2.1.1Creating the tables

The sample has been created for Oracle database. The following table needs to be created in the database to run the scenario.

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 CUSTINFO

(

CCODE VARCHAR2(10) NOT NULL PRIMARY KEY,

CDATA VARCHAR2(20),

);

c. Inserting/Updating data in tables

Insert arecord in Customer table.

INSERT INTO <SCHEMA NAME>.CUSTOMER (pkey,ccode,fname,lname) values(' Test','ANITA','MEHTA','IBM');

Insert a record in CustInfo table.

INSERT INTO <SCHEMA NAME>.CUSTINFO (ccode,cdata) values(' Test1', 'ABC');

Note: Please replace <SCHEMA NAME> with the name of the schema where you created the Customer and CustInfo tables.

2.1.2Creating the authentication alias

The authentication alias needs to be set since the data source created in the next section 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.

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

Figure 1

  1. 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

Figure 2

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

Figure 3

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

Figure 4

  1. Click on Security  Secure Administration, applications and infrastructure

Figure 5

  1. On the right, click on J2C Authentication Data under Java Authentication and Authorization Service

Figure 6

  1. It gives the list of existing aliases.

Figure 7

  1. Click new to create new Authentication Entry. Enter Alias name and proper username/password that can connect to the database as shown in the figure below. Click OK.

Figure 8

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

Figure 9

  1. We have created an authentication alias that we will use when we configure the adapter properties.

Figure 10

2.2Creating the data source

We will create a datasource in WebSphere Application Server that the adapter will use to connect to the database. Here are the steps to create the data source in WPS admin console. This data source will be used later when generating the artifacts for the module.

Note: We will be using Oracle as the database and oracle thin driver, ojdbc.jar

  1. In the admin Console, click on Environment  WebSphere Variables

Figure 11

  1. On the right, click ORACLE_JDBC_DRIVER_PATH and specify the path of ojdbc.jar in the value text field. Click Ok.

Figure 12

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

Figure 13

  1. Now, click on Resources  JDBC -> JDBC Providers

Figure 14

  1. On the right, click on the New button on the JDBC providers window.

Figure 15

  1. Choose the values for steps 1 to 3 as shown in the figure below. Choose an Oracle database with Connection pool data source for the Oracle JDBC driver.

Figure 16

  1. Click Next. In the General Properties, enter the following value for Class path: $(ORACLE_JDBC_DRIVER_PATH)/ojdbc14.jar, where $(ORACLE_JDBC_DRIVER_PATH) is the runtimes lib path. Since we’ve added the ojdbc14.jar to this path, we specify that path here. Click the ‘Next’ button.

Figure 17

  1. In the Next Screen, Click Finish.

Figure 18

  1. Click On Data sources links under the Additional Properties. Click on the first Data sources link. Click on the ‘New’ button.

Figure 19

  1. Enter the value for JNDI name and select Authentication Alias “Auth_Alias_Orcale” created in earlier Section. Click Next.

Figure 20

  1. Provide the appropriate URL value and select Data store helper class name as shown in the figure below. Click the Next button.

Figure 21

  1. The Summary of the values enter for datasource will be show. Click Finish.

Figure 22

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

Figure 23

  1. In the next window, choose the checkbox for the newly created data source and click on the Test connection button.

Figure 24

  1. The connection should succeed as indicated by the message shown in the figure below. For any problems with the Test connection, refer Troubleshooting section 6.1

Figure 25

Note: Thedata source is created which will be used by the adapter to connect to the database.

2.3Extracting 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_BatchSQL.zip into a directory of your choice (you may want to create a new directory).

File name / Description
JDBCBatchSQLTest/
JDBCOutboundInterface.export / Contains the SCA export for the resource adapter.
JDBCBatchSQLTest/
TestBatchSQL.xsd / Business Object definition for the RtasserCustomer business function.
JDBCBatchSQLTest/
TestBatchSQLBG.xsd / Business Object definition for the business object graph.
JDBCBatchSQLTest/
JDBCOutboundInterface.wsdl / Service interface to invoke the resource adapter.
JDBCBatchSQLTest /
MatchesExceededLimitFault.xsd / Fault Schema
JDBCBatchSQLTest /
MissingDataFault.xsd / Fault Schema
JDBCBatchSQLTest /
PrimaryKeyPairType.xsd / Fault Schema
JDBCBatchSQLTest /
RecordNotFoundFault.xsd / Fault Schema
JDBCBatchSQLTest /
ObjectNotFoundFault.xsd / Fault Schema
JDBCBatchSQLTest /
MultipleMatchingRecordsFault.xsd / Fault Schema
JDBCBatchSQLTest /
UniqueConstraintFault.xsd / Fault Schema
JDBCBatchSQLTest /
IntegrityConstraintFault.xsd / Fault Schema
JDBCBatchSQLTest /
WBIFault.xsd / Fault Schema

3.0 Sending Data to EIS (Outbound processing)

This tutorial demonstrates how to create BatchSQL Business Object to execute the multiple SQL Statements using WebSphere Adapter forJDBC

3.1Configuration prerequisites

3.2Configuring the adapter for inbound processing

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

  1. Switch to the Business Integration Perspective in WebSphere Integration Developer by choosing from the menu: Window -> Open Perspective Business Integration.
  2. Start the JDBC EMD by choosing: File-> New -> External Service
  3. Select Adapters radio button then click Next

Figure26

  1. Click on “IBM WebSphere Adapter for JDBC (IBM: 6.1) and click Next.

Figure27

  1. Next, Adapter Import will be displayed, keep all the default options and click Next

Figure28

  1. Select JDBC Driver file for Oracle and Click Next

Figure29

  1. Select the processing direction as “Outbound” and Click Next.

Figure30

3.2.1Setting properties for the external service wizard

To connect to the Database, the following information is necessary: hostname, database name (SID in case of Oracle DB), username and password.

Select Oracle -> 9 on the left side of the tree and provide the mandatory information in the next wizard page. Then click Next.

Figure31

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

Follow these steps to select the data for Inbound Processing:

  1. In the Object Discovery and Selection screen, click the Edit Query button.

Figure32

  1. Click “Create batch SQL business object...” checkbox and keep the default value 1 for Number of batch SQL business objects to be created. Click Ok.

Figure33

  1. In the Object Discovery and Selection screen, click the Run Query button.

Figure 34

  1. Expand the Node Batch SQL Statements.

Figure35

  1. Choose the Batch SQL Statement1 and click the Add to import list icon. The window to specify the configuration properties for Batch SQL will be displayed. Specify the following in the window:
  • Batch SQL business object name: TestBatchSQL
  • SQL Statements: Insert into <schema name>.customer values(?,?,?,?);update custinfo set cdata=? Where ccode=?;delete from customer where pkey=?

Note: Please replace <schema_name> with the name of the schema where you created the customer table.

Figure36

  1. Click Generate Parameters checkbox in the above window. Parameter Fields correcponding to each ‘?’ in the SQL Statements will be generated as shown in the figure below:

Figure37

  1. Select Parameter Type and specify the Sample Value for each parameter in all the statements. Scroll Down and click validate the syntax of batch SQL statements using the sample values.

Figure38

  1. Result: Validation wasSuccessful is displayed as shown in the figure below. Click Ok.

Figure39

  1. The Batch SQL Statement1 will be listed in the Selected Objects. Click Next.

Figure 40

3.2.3Generating business object definitions and related artifacts

Follow these steps to generate the business object definitions.

  1. In the Configure Composite Properties screen, use the default values.

Figure 41

  1. Click the Next >.
  2. On the Service Generation and Deployment Configuration screen, J2C Authentication Data Entry. Click Advanced >

Figure41

  1. Specify DataSource JNDIName under Alternate ways to specify connection information as shown in the figure. Click Next >

Figure42

  1. In the Service Location Properties screen, click the New button next to the Module field to create a new module.

Figure 43

  1. If the New Integration Project screen appears, select Create a module project, then click Next

Figure 44

  1. In the New Module screen, type JDBCBatchSQLTest in the Module Name field, then click Finish.

Figure 45

  1. Click Finish.
  2. Verify the results.

Figure 46

3.3Deploying the moduleto the test environment

After running the external service wizard, you will have an SCA module that contains an EIS import or export. You must install this SCA module in the WebSphere Integration Developer integration test client.
1.Add the SCA module to the server using the server panel in WebSphere Integration Developer. Right-click on the server, and select Add and remove projects.

Figure 47

2.Add the SCA module to the server.

Figure 48

3.4Testing the assembled adapter application

Test the assembled adapter application using the WebSphere Integration Developer integration test client.

  1. In the console view, you will see the messages that the application has successfully started.
  2. Select the JDBCBatchSQLTestModule, right-click on it, and select Test > Test Module

Figure 49

  1. The Test Client Window is displyed.

Figure 50

  1. Populate data for parameters as shown in the figure below:

Figure 51

  1. Execute the service by clicking continue icon.
  2. In the “Select Deployment location” screen, select the server, then click Finish.

Figure 52

  1. Check the output of the service, and check the data in the EIS to ensure it matches expected values.

Figure 53

3.5Clearing the sample content

Nothing is required to clean up after this tutorial.

4.0Troubleshooting

4.1Adapter 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.

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

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

4.2Failure 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.

  1. 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.

10/6/2018Page 1 of 52