iWay Adapter for RDBMS

The iWay Adapter for RDBMS supports integration with applications that can write to a relational table. If an application cannot send data or messages to iWay Service Manager using a queue or other standard protocol, such as TCP, iWay Service Manager can periodically retrieve the data directly from any JDBC data source. The listener reads one or more rows from the table and creates an XML document representing the column data in each row. The standard server business logic facilities are then applied to the constructed XML documents, including transformation, validation, security management, and application processing. Each row in the table is optionally deleted or updated if the business logic completed properly.

The iWay Adapter for RDBMS can function in either a destructive or non-destructive read mode. In the destructive read mode, it is presumed that all data in the table is new and is to be consumed by iWay Service Manager. This is analogous to a message in a queue that is deleted after it is read. The non-destructive read mode provides a mechanism to preserve the data and track which rows were not yet processed.

Software Requirements

The RDBMS listener requires the JDBC driver for the database being monitored. Contact your DBMS vendor to obtain the appropriate JDBC driver.

Choosing an RDBMS Listening Technique

You can detect an RDBMS event and propagate it into a workflow process using an RDBMS listener.

An elaborate polling technology enables the specification of SQL SELECT statements to be executed on a periodic basis. After data is polled, it passes through the adapter to the iWay Service Manager for further processing.

The following are techniques you can use to listen to an RDBMS event:

Standard technique with row tracking. This technique listens to a table, sends each newly inserted row to the Reply-to destination, and uses a control table to track the most recently read row. The control table prevents the most recently read row from being reread during the next listening cycle.
This is a flexible, yet simple technique that you can apply in most situations.

For more information, see Standard Event Processing With Row Tracking. For information about Reply-to, see the iWay Service Manager User's Guide.
Standard technique with row removal. This technique listens to a table, sends each newly inserted row to the Reply-to destination, and then deletes the new row from the table to prevent it from being reread during the next listening cycle.
You apply this technique when the source table is used as a conduit to pass data to the adapter, and the table rows are not required to persist. Rows are deleted as they are processed.
Trigger-based technique. This technique assigns triggers to a joined group of tables, has the triggers write information about table changes to a common control table, listens to the control table, and sends the information about the table changes to the Reply-to destination. It deletes new rows from the control table to prevent them from being reread during the next listening cycle.
You apply this technique when listening for events in a group of large joined tables, or when you need to know about updated or deleted rows.

Before you configure an RDBMS listener, register JDBC driver libraries. You must register JDBC driver libraries only once for each instance of the iWay Service Manager.

Standard Event Processing With Row Tracking

The standard event processing with row tracking technique enables you to listen to the source table without removing its rows. It requires that you create a single-row control table that tracks the last new row the listener reads from the source table.

The control table has a single column that corresponds to a column (or a group of columns) in the source table that is unique, sortable, and indicates how recently the row was added to the source table relative to the other rows. For example, the first row added to the source table has the lowest value, and the last row added has the highest value. You can consider this value the event key. When you create the control table, initialize this field to the value of the most recently added row of the source table. You configure the listener SQL Post-query property to update the event key in the control table.

Each time the listener queries the source table, it looks for rows added since the last query. That is, for rows whose event key is greater than the current value of the field in the control table. It reads each of these rows and returns them to the Reply-to destination through an XML document. To ensure that a row is not read again the next time the listener queries the table, the listener updates the field in the control table to match the value of the row just read from the source table.

The standard event processing with row tracking technique is shown in the following figure.

In this figure:

The listener queries the source table and copies each source table row whose event key is greater than the control table event key. The listener copies the row to an XML document that it sends to the Reply-to destination.
The listener then updates the event key in the control table to match the row it most recently read.
The listener copies the next source table row to an XML document.

The process repeats itself. To implement this event processing technique, see the following procedure.

Top of page

Procedure: How to Implement Standard Event Processing With Row Tracking

To implement standard event processing with row tracking:

Create the control table. For an example, see Creating the Control Table Required for an RDBMS (Oracle) Event.
Configure the listener in the iWay Service Manager console.
In addition to the required listener properties for standard event processing with row tracking, you also must provide values for the following optional properties:

SQL Query. The SQL SELECT statement that identifies the source table to which the adapter listens and with which it queries the table.

SQL Post-query. The SQL statements that maintain the field in the control table.

For information on how to configure a listener, see How to Configure the RDBMS Listener.

Top of page

Example: Creating the Control Table Required for an RDBMS (Oracle) Event

This example uses an Oracle E-Business Suite (also known as Oracle Applications) table. You can apply the same technique in a similar way to other types of relational data sources.

Follow the steps in this example to create an Oracle E-Business Suite table named TEMP_NEW_YORK_ORDER_ENTITY that has a single field named WIP_ENTITY_ID. You specify this table when you configure the RDBMS listener, as described in How to Configure the RDBMS Listener.

When discrete jobs are created through the Oracle E-Business Suite graphical interface, an entry is created in the WIP.WIP_DISCRETE_JOBS table. For this example, you configure an event to detect new entries to this table. Do not rely on a destructive read of this table, because Oracle E-Business Suite processing cannot delete rows from the table. To accomplish a non-destructive poll, first create a simple table to track the records processed.

From within Oracle SQL*PLUS, run the following SQL:
```
CREATE TABLE WIP.TEMP_NEW_WORK_ORDER_ENTITY_ID
(
   WIP_ENTITY_ID   NUMBER
)
```
This creates a single table with a single field.
Note: Oracle SQL*Plus is part of the Oracle client software. If it is not installed, contact your Oracle database administrator.
You must be logged in under the APPS schema or a similar ID that has access rights to the Oracle E-Business Suite WIP schema.
Create a single record in this table and provide it with the highest WIP_ENTITY_ID ID from your system. You can obtain this from the WIP.WIP_DISCRETE_JOBS table.
This sets the value at which to start detecting events as records enter the WIP_DISCRETE_JOBS table.

After creating a simple table in Oracle, the iWay Service Manager portion of the RDBMS listener must be configured. For configuration steps, see How to Configure the RDBMS Listener.

Top of page

Procedure: How to Configure the RDBMS Listener

This procedure describes how to configure an RDBMS listener that is used with the row tracking technique. In the scenario used to illustrate this procedure, the event propagates a message that contains record data when a new WIP discrete job is created.

Note: Ensure that you already created the table for tracking records that were processed. For more information on creating the table, see Creating the Control Table Required for an RDBMS (Oracle) Event.

Top of page

Reference: RDBMS Listener Properties

The following table lists and describes the RDBMS listener properties. For instructions on creating a listener, see Configuring Listeners.

Property Value	Description
Driver	Fully-qualified name of the JDBC driver used to access the database, for example: com.microsoft.jdbc.sqlserver.SQLServerDriver com.ibm.db2.jdbc.app.DB2Driver oracle.jdbc.driver.OracleDriver com.mysql.jdbc.Driver com.sybase.jdbc2.jdbc.SybDriver com.informix.jdbc.IfxDriver
URL	The URL of the JDBC driver, for example: jdbc:microsoft:sqlserver://PMSNJC:1433;DatabaseName=dbname; selectMethod=cursor jdbc:db2://host:port/DatabaseName;translatebinary=true;trace=true jdbc:oracle:thin:@myhost:1521:ORADB1 jdbc:mysql://myhost:3306/inventory jdbc:sybase:Tds:host:port/DatabaseName?DYNAMIC_PREPARE=true jdbc:informix-sqli://HOST:PORT/DB:INFORMIXSERVER=SERVER_NAME
User Name	The user ID employed to access the input table.
Password	The password for the user ID.
Table (required if SQL Query is not selected)	The name of the database table to query with the default SELECT statement if the SQL Query property is omitted. This is ignored if the SQL Query is specified.
Maximum Rows	The maximum number of rows to include in each document.
SQL Query (required if Table is not selected)	The SQL statement that the listener issues to poll the table. If this is omitted, it defaults to: SELECT * FROM table where: table Is the value of the Table property. Note: If the SQL statement includes a date column or long text column, you must provide a value for the SQL Post-query property or Delete Keys property, and the value you provide must not contain a date column or a long text column. This applies whether you provide an SQL statement here or rely upon the default. For example, the following SELECT statement retrieves all unprocessed records from the DISCRETE_JOBS table: SELECT * FROM WIP_DISCRETE_JOBS DJ WHERE DJ.WIP_ENTITY_ID > (SELECT WIP_ENTITY_ID FROM WIP.TEMP_NEW_WORK_ORDER_ENTITY_ID)
SQL Post-query	One or more SQL statements that are executed after each new record has been read from the table. If you specify more than one statement, then terminate each with a semicolon (;). Case-sensitive; the case used to specify the column names must match the case used in the SELECT statement that polled the table. If the SQL Query property was omitted so that a default SELECT statement polled the table, the case used to specify the column names must match the case used to define the columns in the DBMS native schema. If you do not specify a value for SQL Post-query, each record read from the table is deleted after it has been read. How this happens depends on whether you specify the Delete Keys property. If you: Specify the Delete Keys property. By default, the adapter issues a DELETE statement with a WHERE clause containing every key column specified for the Delete Keys property. At run time this is faster than if you do not specify the Delete Keys property if there is an index on the key, or if there are fewer key columns than there are columns in the SELECT statement that polled the table. Do not specify the Delete Keys property. By default, the adapter issues a DELETE statement with a WHERE clause that specifies every column from the SELECT statement that polled the table. You can choose to retain the table data after it is read by specifying a value for this property. Note that the SQL Post-query and Delete Keys properties are mutually exclusive, as Delete Keys applies to the default DELETE statement, and SQL Post-query overrides the default DELETE statement. You can provide a value for one or the other, but not for both. There are two field operators, ? and ^, that you can use in a Post-query SQL statement.
Delete Keys	A comma-separated list of key columns to be used in the default DELETE statement. DELETE operates on keys, so specify the key columns of the table. Case-sensitive. The case used to specify the column names must match the case used in the SELECT statement that polled the table. If the SQL Query property was omitted so that a default SELECT statement polled the table, the case used to specify the column names must match the case used to define the columns in the DBMS native schema. The Delete Keys and SQL Post-query properties are mutually exclusive, as Delete Keys applies to the default DELETE statement, and SQL Post-query overrides the default DELETE statement. You can provide a value for one or the other, but not for both. For more information, see the description of the SQL Post-query property in this table.
Generated XML format	The format can be field or column.
Include xLOBs in Tree	If set to true, the contents of all BLOB/CLOB are included in the tree and not as external files. BLOBs are base 64 encoded and enclosed in base64() markers. The inclusion of xLOBs in the tree can result in significant memory use.
Transaction Isolation Level	The transaction isolation level to set if possible
Root Name	The default is the listener name.
Column Limit	The Varchars exceeding this limit are carried as external files.
Whitespace Normalization	Specifies how the parser treats whitespace in Element content. Choose preserve to turn off all normalization as prescribed by the XML Specification. Choose condense to remove extra whitespace in pretty printed documents and for compatibility with earlier versions.
Accepts non-XML (flat) only	If set to true, the listener expects flat (non-XML). Preparsers do not run.
Optimize Favoring	The selection of memory is useful for large input document.
Multithreading	The number of documents that can be processed in parallel.
Execution Time Limit	The time limit for document execution (in seconds) before it is terminated. (Also see system property kill interval).
Polling Interval	The interval, in seconds, at which to check for new input.
Default Java File Encoding	The default encoding if the incoming message is not self-declaring (that is, XML).
Always reply to listener default	If set to true, then the default reply definition is used in addition to defined replies.
Error Documents treated normally	If set to true, the, the error documents are processed by any configured preemitters. Note: There is no default emitter location. You must add a protocol emitter to your channel.
Listener is Transaction Manager	If set to true, then the agents run within a local transaction.
Record in Activity Log(s)	If set to true, then the activity on this channel will be recorded in the activity logs, else the activity will not be recorded.

Top of page

Reference: RDBMS Listener Special Registers

The following table lists and describes the special registers (SREGs) available on the RDBMS listener.

Name	Level	Type	Description
eos	Document	Integer	Set to 1 at the end of a set.
iwayconfig	System	String	The current active configuration name.
msgsize	Document	Integer	The physical length of the message payload.
name	System	String	The assigned name of the master (listener).
protocol	System	String	The protocol on which this message was received.
recordcount	Document	Integer	The number of records in this feed.
tid	Document	String	Unique transaction ID.