WebSphere Adapter for JDBC

Inbound processing

The Adapter for JDBC supports inbound event management with asynchronous event delivery. Events are processed by using these two modes: a standard event table and custom queries.

Asynchronous event delivery is accomplished through the use of:

Standard event table

For any changes in the user tables, the application populates the event table, called an event store, in the enterprise information system (EIS). Updates are made by placing triggers on the user tables that record events in the event table corresponding to the updates to the user table.

If you set the Activation specification property AssuredOnceDelivery to true, an xid (transaction id) value is set for each event in the event store. After an event is obtained for processing, the xid value for that event is updated in the event table. The event is then delivered to its corresponding endpoint, and subsequently deleted from the event table. If the database connection is lost or the application is stopped before the event can be delivered to the endpoint, the event may not be processed completely. In this case, the xid column ensures that the event is reprocessed and sent to the endpoint. Once the database connection is re-established or the adapter starts again, the adapter checks for events in the event table that have a value in the xid column. The adapter processes these events first, and then polls the other events during the poll cycles. The event table is described below.

The adapter can filter the events to be processed by business object type. The filter is set by use of the Activation specification property EventFilterType. This property has a comma-delimited list of business object types. Only the types specified in the property are processed. If no value is specified for the property, no filter is applied, and all the events are processed. If the Activation specification property FilterFutureEvents is set to true, the adapter filters events based on timestamp. The adapter compares the system time in each poll cycle to the timestamp on each event. If an event is set to occur in the future, it will not be processed until that time.

Custom event processing

Custom queries can be entered by using:
  • Standard SQL
  • A stored procedure
  • A stored function
All of these entries take an input parameter for PollQuantity, an Activation specification property that the adapter provides at run time. These queries return a resultset, with the poll quantitiy number of records, that has the following columns in order: event_id, object_key, object_name, and object_function. The columns represent the following information:
  • event_id: A unique id for each event; this could be same as the object_key value.
  • object_key: The key value of the record in the table that will be retrieved for event processing.
  • object_name: The name of the business graph generated using enterprise service discovery. The business object within the business graph can be a hierarchical business object. Each business object refers to a table or view.
  • object_function: The Create, Update, or Delete operation to be set on the event.

The three types of custom queries are described as follows.

Standard SQL

The user can enter a SQL select statement that takes in one input parameter for poll quantity. The query can have other input parameters as well, but the values for all those parameters need to be set in the SQL query itself. The SQL query returns a resultset (with poll quantity number of records) that should have the following columns in order: event_id, object_key, object_name, and object_function. The Adapter for JDBC generates the event object and processes the events.

Stored procedure

The custom query can be a stored procedure that returns a result set. The adapter passes the poll quantity value when the stored procedure is called. So, the user-entered procedure should accept an input parameter for the poll quantity. Also, the stored procedure has an output parameter of type resultset. The resultset returned by the stored procedure should have the following columns in order: event_id, object_key, object_name, and object_function. Following is the syntax to use when specifying a stored procedure:

call <sp_name> (?, ?)

where sp_name is the name of the stored procedure that should be run.

Note: The first parameter represents the poll quantity, and the second parameter represents the resultset.

The stored procedure can take in other input parameters as well, but you need to provide values for those input parameters in the call statement itself, for example: call <sp_name> (25, ?, ?)

Stored function

The custom query can also be a stored function that returns a result set. The adapter passes the poll quantity value when the function is called. So, the user-entered function should accept an input parameter for the poll quantity. Also, the return value for the function should be a resultset. The resultset returned by the function should have the following columns in order: event_id, object_key, object_name, and object_function. Following is the syntax to use when specifying a stored function:

? = <sp_name> (?)

where sp_name is the name of the stored function that should be run.

Note: The first parameter represents the resultset, and the second parameter represents the poll quantity.

The stored function can take in other input parameters but you need to provide values for those input parameters in the call statement itself, for example: ? = call <sp_name> (25, ?)

The adapter also provides support for custom update and delete queries. Users would generally use an update query to ensure that the same record does not get picked up for processing during subsequent poll cycles. The delete query is used in case records need to be deleted after each event is processed. Both the update and delete queries are optional.

These queries, if specified in the Activation specification, are run after each event is processed. The user can enter these queries in two ways: either as a standard SQL statement or as a stored procedure. The stored procedure syntax is the same as the one specified for the custom query Activation specification property. Both the standard SQL and the stored procedure take an input parameter for the event id. The adapter provides the value of event id at runtime. The queries can also have additional input parameters, but those need to be provided in the query syntax itself as described for the custom event query.

Custom queries support assured once delivery if you have created the standard event store for storing xid values. The adapter stores the events returned by the custom event query in the event store, and it updates the events with xid values. The adapter processes the events in the same way as for standard event table processing. Since the standard event store is used for custom event processing with the AssuredOnceDelivery property set to true, a custom query cannot query on the standard event table. In addition, in this scenario the event table should not have an automatic generation of event id values, because the adapter populates the event id value it retrieves from the custom query in the event table. With custom queries, no support is provided for event filtering.

Event table

The event table is described in the following table.

Table 1. Event table
Name Type Description
xid String Unique transaction id (xid) value for assured once delivery
event_id Number Unique Event id that is a Primary key for the table
object_key String Delimited string that contains keys for the business object (not null)
object_name String Name of the business object (not null)
object_function String Operation corresponding to the event (Delete, Create, Update, and so on) (not null)
event_priority Number Any positive integer value (not null)
event_time Timestamp Date and time when event was generated
event_status Number The values are as follows:
  • 0 Ready for poll
  • 3 In progress
  • -1 Event processing failed
(not null)
event_comment String Description

Terms of use |

Last updated: Tue 12 Dec 2006 03:32:38

(c) Copyright IBM Corporation 2005, 2006.
This information center is powered by Eclipse technology (http://www.eclipse.org)