In this section: |
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 RDBMS listener can:
The SQL listener also supports customized user exits with Java classes to define custom operations on the row sets.
The default operation is to delete the rows that were transferred to the workflow. However, other options may include moving the rows to an archive table or marking the rows with an SQL UPDATE.
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.
The RDBMS listener requires the JDBC driver for the database being monitored. Contact your DBMS vendor to obtain the appropriate JDBC driver.
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:
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.
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.
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.
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 process repeats itself. To implement this event processing technique, see the following procedure.
To implement standard event processing with row tracking:
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.
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.
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.
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.
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.
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 |
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 |
User ID employed to access the input table. |
Password |
Password for the user ID. |
Table (required if SQL Query is not selected) |
Name of the database table to query with the default SELECT statement if the SQL Query property is omitted. This is ignored if SQL Query is specified. |
Maximum Rows |
Maximum number of rows to include in each document. |
SQL Query (required if Table is not selected) |
SQL statement that the listener issues to poll the table. If this is omitted, it defaults to SELECT * FROM table where:
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, 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:
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 |
Comma-separated list of key columns to be used in the default DELETE statement. DELETE operates on keys, so specify the table's key columns. 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 |
Format can be field or column. |
Transaction Isolation Level |
Transaction isolation level to be set if possible |
Root Name |
Default is the listener name. |
Column Limit |
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 true, listener expects flat (non-XML). Preparsers do not run. |
Optimize Favoring |
Selection of memory is useful for large input document. |
Multithreading |
Number of documents that can be processed in parallel. |
Execution Time Limit |
Time limit for document execution (in seconds) before it is terminated. (Also see system property "kill interval"). |
Polling Interval |
Interval, in seconds, at which to check for new input. |
Default Java File Encoding |
Default encoding if incoming message is not self-declaring (that is, XML). |
Always reply to listener default |
If true, the default reply definition is used in addition to defined replies. |
Error Documents treated normally |
If true, 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 true, agents run within a local transaction. |
Record in Activity Log(s) |
If set, activity on this channel will be recorded in the activity logs, else the activity will not be recorded. |
iWay Software |