Standard Event Processing With Row Tracking

How to:

Reference:

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

The single column of the control table corresponds to a column (or to 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. This value is called the event key.

When you create the control table, initialize it to the event key of the row most recently added to the source table. When you specify the listener properties, configure the SQL Post-query property of the listener to automatically update the control table event key.

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 row of this type and returns it to the specific destination using an XML document. To ensure that the 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 that was just read from the source table.

Note: Event processing may be limited for some non-relational databases due to the functionality of the database and its interaction with the iWay server component. For more information on the iWay server component, see the iWay Data Adapter Administrator User's Guide or consult with your DBA.

The following image illustrates standard event processing with row tracking.

In the previous figure:

  1. 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 and sends it to the destination defined in the port disposition using the File protocol.
  2. The listener updates the event key in the control table to match the row it most recently read.
  3. The listener copies the next source table row to an XML document.

The process repeats.


Top of page

x
Procedure: How to Implement Standard Event Processing With Row Tracking

To implement standard event processing with row tracking:

  1. Create a control table. For an example, see Creating the Control Table for an (Oracle) Event.
  2. Configure a .Net Table Listener in the iWay Web 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 detailed instructions about configuring a listener, see Creating a Channel. For information on post query parameters, see The Post-query Parameter Operators.



Example: Creating the Control Table for an (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 databases.

You can 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 Table Listener, as described in The Post-query Parameter Operators.

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. You use the standard event processing with row tracking technique. (Oracle E-Business Suite processing cannot delete rows from the table.)

You first create a simple table to track the records processed.

  1. 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 with access rights to the Oracle E-Business Suite WIP schema.

  2. Create a single record in the table and provide it with the highest WIP_ENTITY_ID ID from your system.

    You can obtain this ID from the WIP.WIP_DISCRETE_JOBS table.

    This sets the value at which to start detecting events as records enter the WIP_DISCETE_JOBS table.

  3. After you create a simple table in Oracle, you must configure the listener.

Top of page

x
Reference: The Post-query Parameter Operators

When you configure a Table Listener, you can use two special field operators, ? and ^, with the SQL Post-query parameter. Both of these operators dynamically substitute database values in the SQL post-query statement at run time:



Example: Listening to trans_event Using the Row Tracking Technique

In this example, you listen to the trans_event table using the row tracking technique and use last_trans as the control table that contains the last value of the primary key read from trans_event.

For more information on configuring a listener, see How to Create a File Channel.

last_trans is to contain a single value in a single row and must be set up prior to configuring the Table Listener. The last_trans column must have the same name as the primary key in the trans_event table. This key must be unique and sortable.

The table schemas for this example are:

Sample Schema foSQL> describe trans_event
Name                            Null?         Type
----------------------------------------------------------------
EVENT_ID                         NOT NULL      NUMBER(38)
LAST_NAME                                      VARCHAR2(50)
TRANS_ID                                       CHAR(2)
SQL> describe last_trans
Name                            Null?        Type
---------------------------------------------------------------
EVENT_ID                                      NUMBER

The last_trans single field value must contain the starting value of the primary key.

The listener generates XML response documents for each record found in the trans_event table with a primary key greater than the value found in the last_trans table.

  1. Using a SQL query/data manipulation tool supplied by the database vendor, insert a record into the trans_event table based on the following information.
    • EVENT_ID=1
    • LAST_NAME='Kaplan'
    • TRANS_ID='03'

    When setting up the port, a specific path is configured for a disposition using the File protocol. A response document with the record data is deposited into the directory after the insert is made.

    The following is an example of a response document for the listener deposited into a directory specified when the Port is configured.

    <Oracle>
       <row>
                <EVENT_ID>1</EVENT_ID>
                <LAST_NAME>Kaplan</LAST_NAME>
                <TRANS_ID>03</TRANS_ID>
       </row>
    </Oracle>
  2. Configure the listener by specifying the following properties when creating the channel.

    Parameter

    Description

    Host

    Name or URL of the machine on which the database is installed.

    Port

    Port on which the Host database is listening.

    User Name

    User name that is registered with the back-end RDBMS.

    Password

    Password associated with the user name.

    SQL Query

    SELECT * FROM TRANS_EVENT WHERE EVENT_ID>(select 
    EVENT_ID from LAST_TRANS)

    Post Query

    UPDATE LAST_TRANS SET ?EVENT_ID

    Polling Interval

    Interval in seconds.


iWay Software