RDBMS Events

This section provides a brief overview of RDBMS events and provides information you should consider before you begin creating data sources and RDBMS events, and defining channel definition rules.

RDBMS Events

Events are asynchronous, one-way messages received from an RDBMS. Events post an XML document/XML as Java String/Serialized CachedRowSet from an RDBMS whenever a specific event of interest is triggered. The event could contain data about a Select, Insert, Update, or Delete operation that has occurred on a table in the RDBMS.

Supported Event Types

Trigger - A Trigger event provides notification of an Insert, Update, or Delete event occurring in a database table.
Query/Post Query - A Query/Post Query event notifies records of interest based on a Select Query given on a database table and executes the SQL specified in the Post Query for each event posted.

General Information

Before you begin creating data sources and RDBMS events, and defining channel definition rules, consider the following:

You can configure a maximum of 3 Trigger type events per Table - 1 each for Insert, Delete, and Update events.
Trigger type events have only been tested on Tables. They have not been tested on View, Synonyms, etc.
Default JMS connection factories must be present for event generators to work.
RDBMS event generators log errors, warnings, and other messages to a file called rdbmsEG.log in the server's domain directory. Refer to this file to troubleshoot RDBMS event generator problems.
When you are working with Trigger type events, once you have configured a channel rule, a trigger keeps copying the event type (Insert/Delete/Update) rows into another Table from the Table you specified when you defined the channel rule. If you delete the channel rule while it is still publishing events, data loss may occur. To prevent any data loss, you should only delete the channel rule definition when it has stopped publishing events and when there is no event activity on the Table you specified when you defined the channel rule.

If Post Query is either "no-op" (No Operation) or SQL (which does not delete the Selected Rows), then:

The first "x" rows will always keep getting published, where, "x" is Max Rows Per Poll.
Sybase ignores the Max Rows Per Poll and returns all the rows matching the SELECT Query in every poll.
For DB2 and Informix, automatic-delete is not supported.
You must specify the correct DELETE Post Query.

Post Query is executed for each row returned by SELECT Query.
Post Query for a row is executed after it gets published to Message Broker.
If Post Query is vague, for example "DELETE FROM USER_TBL" and SELECT Query is "SELECT FIRST_NAME, LAST_NAME WHERE STATUS=`TEMP'", then all rows will be deleted after publishing only the first row.
The @ prefixed variables in the Post Query works just like "bind variables" in PreparedStatements.

Architecture Overview

RDBMS Event Generator is designed to replace the RDBMS Adapter's events features (RDBMS Adapter Service feature is not a part of the RDBMS Event Generator). This section provides a brief overview of RDBMS Event Generator architecture and presents some of the benefits that the RDBMS Event Generator has over the RDBMS Adapter.

The major difference between the RDBMS Event Generator and the RDBMS Adapter is in the architecture of Trigger Type events. Query/Post Query Type events are essentially the same.

For Trigger Type events, the RDBMS Adapter runs in a single thread and polls and publishes table rows in a single thread. If the publish operation takes longer than the poll interval to complete, then subsequent polls are delayed. Since this is essentially a single-threaded approach, the maximum throughput is limited. In contrast, the RDBMS Event Generator utilizes a multi-threaded approach. Polling and publishing are performed by separate processes. This way, even if some of the publishing threads are delayed, polling can continue and the free threads can publish new rows. Also, you can specify the number of processing/publishing threads to match the rate-of-activity on the database table.

The following example uses an "Insert Trigger Type Event" to illustrate this polling benefit. It takes 10 seconds for 2 threads to process and publish 50 Rows each. This equates to a throughput of (50 x 2)/10 = 10 rows per second, or 10 x 60 x 60 = 36,000 rows per hour. To make maximum use of the processing threads occupied all the time and to always obtain a consistent throughput, a polling interval of 7-8 seconds is selected. The database table on which this Trigger Event has been configured must have at least 100 rows being Inserted every 7-8 seconds to obtain this constant throughput of 10 rows per second.

You should only use the above example as a thumb-rule to help configure events. The actual numbers depend on several factors like application server hardware, database hardware, data types chosen for publication, number of columns chosen for publication, application server execute queue size, etc.

Clusters

In a cluster, polling is performed by each managed node in a round-robin fashion. Processing/Publishing of the polled rows is also load-balanced in a round-robin fashion. The Connection Pool and the Data Source must be targeted to the entire Cluster. For more information on deploying RDBMS Event Generators in clusters, see "Event Generator Resources" under "Key Resources" in Introduction in Deploying WebLogic Integration Solutions.

RDBMS Event Generator User Guide

RDBMS Events

RDBMS Events

Supported Event Types

General Information

Architecture Overview

Clusters