About polling notifications

Polling notifications are components in a system, that actively monitor and retrieve updates or changes from a data source, at regular intervals.

Example

  • Alert the accounts representative to add a salary account for a new employee when someone joins the company and a new record is inserted in the Employee table.
  • Generate an invoice when an item is purchased.

The primary purpose of polling notifications is to detect changes in a data source, often a database table, and respond to those changes. Once changes are identified, polling notifications typically publish documents or messages, which contain information about the modifications. These published documents can then trigger further actions, such as starting integration flow services that process the updated data.

Polling notifications play a key role in building event-driven integrations that respond to data events, making it easier to automate business processes and integrations based on external inputs. The choice of notifications depends on the specific requirements of your integration project and the data sources that you need to connect to.

The polling interval is the time duration for the recurring check, and you can define this duration while you are scheduling a notification.

Note:

  • Polling notifications are enabled only for the Database by default.

  • Messaging service must be enabled for polling notifications to work.

  • In deploy anywhere flow services, polling notifications are not supported for the Database.

Polling notification types

Polling notifications vary somewhat in how they work, depending on the type of notification.

The notification types are:

Insert Notification

Monitors insert operations with one or more tables and publish the notification data. Notification data is the data, which is inserted in the insert operation.

Update Notification

Monitors update operations with one or more tables and publishes notification data. Notification data is the data, which is updated in the update operation.

Use Update Notification to:

  • Retrieve either the old, new, or both the old and new values from the database table as output values. The old value is the value that existed before update in the selected column of the database table.
  • Set the required output value options when you configure the Update Notification in the Data fields page.

Delete Notification

Monitors delete operations with one or more tables and publishes notification data. Notification data is the data, which is deleted in the delete operation.

Basic Notification

Polls the tables to detect changes and publishes notification data. It is designed to track modifications in the tables in databases, which do not support triggers. Notification data is the data, which exists in the tables that are monitored.

StoredProcedure Notification

  • Obtains the stored procedure's parameters through introspection and lists the signature of the stored procedure when you configure the notification.
  • Runs a stored procedure inside a database, retrieves data and publishes the notification data. Notification data is the data returned by the stored procedure, when it is invoked.

Ordered Notification

Monitors multiple inserts, update, or delete operations on one or more tables for a database, by creating a single notification that uses the same publishable document. Like Insert Notifications, Update Notifications, and Delete Notifications, Ordered Notifications use triggers and buffer tables to capture events that happen on specific tables in a database.

Tip: Use the Ordered Notification only if you need to preserve the order in which the operations occur; else use Insert Notifications, Update Notifications, and Delete Notifications, because they have better performance.

Polling notifications operational workflow

Tasks that are performed by IBM® webMethods Integration upon creation of polling notifications are as follows:

  1. For Insert, Update, Delete and Ordered notifications:

    • Buffer table to hold the changed data before publishing.

    • Trigger table to capture change when the specific operation is performed on the table.

    • Sequence to uniquely identify the data in the table.

      Note:
      • In Basic Notification, you must manually create the table by following the guidelines of your data source. After you create the table, create a basic notification, and ensure that the table is populated with the notification data.
      • In StoredProcedure Notification, buffer table, trigger table and sequence are not created.

  2. Schedules the polling notification.

  3. Configures the JMS provider details such as account and destination (queue) to which the data received must be published. The data is published as a document.

  4. Configures the subscriber details such as subscriber name, invocation type, and destination name. Here, you can configure the flow service that must be invoked when the document is received. The flow service contains the next set of actions that must be performed upon receiving the notification data.

  5. Publishes the notification data to the messaging destination.

Accessing polling notifications

  1. Log on to IBM webMethods Integration.

  2. Go to Projects and select your project.

  3. Go to Events > Polling notifications. The list of existing polling notifications appears.

    Note:

    • The Add Notification button is displayed if the messaging service is enabled. Contact IBM support to enable cloud messaging.

    • Ensure that the database user has privileges to create the table, trigger and sequence, before creating the polling notifications.

You can view, create, update, delete, enable or disable polling notifications in the Events page.

Creating insert, update, delete, basic or ordered database polling notifications

  1. Go to Projects > <<YOUR_PROJECT>> > Events > Polling notifications. The list of existing polling notifications appears.

  2. Click Add notification. The Add Polling notification page appears.

  3. Select Database from the Connector type drop-down list.

  4. Click Next. The Add polling notification | Database wizard appears, consisting of several pages. The initial two pages, namely Account and Action, are shared across all scenarios. Subsequent pages in the wizard appear based on the notification type that is chosen on the Action page. On the Account page, you must configure the user account to establish the connection between the system and the database.

  5. Provide the details in the Connect account page:

    • Action name: Name of the account.
    • Description: Description for the account.
    • Connect to account Database: Database account to connect.

  6. Click Next. The Action page appears.

  7. Select the notification type. For example, Insert Notification.

  8. Click Next. The Tables page appears.

  9. Do the following to add tables:
    1. Click the Add icon to add a new table. The Add tables page appears.
    2. Select the table from the catalog.
    3. Click Add. The table is added and listed in the Tables page.
  10. Click Next. The Joins page appears. The Joins page is not available in case of Ordered Notification.
  11. Define the joins, if any to retrieve data from different tables.
  12. Click Next. The Data fields page appears.
    1. Click the Add icon to add data fields. The Add data fields page appears listing all the columns of the selected tables.
    2. Select the data fields. The selected data fields are added in the document, which is received by the notification.
    3. Click Add. The fields are added.
    4. Click the corresponding field's Edit button to configure the fields:
      • Field/Expression: Name of the field.
      • Output field type: Data type of the output field.
      • Output field name: Name of the output field. This name appears in the flow services Pipeline Mapping panel.
      • Actions: You can edit or delete the field by clicking the corresponding Edit or Delete icons.

      For Update Notification, you can publish the new or old or both the values. Click Add field to add a field and retrieve the old values by selecting Old in the Output value type field.

      Output value type: Available in Update Notification only. Select Old to retrieve the old values, NEW to retrieve new values. Use Add Fields to retrieve both the old and new values from the database table as output values.

      In the Update Notification example, you can now see both the old and new values of the FIRSTNAME field.

  13. Click Next. The Condition page appears. You can add the criteria or expressions to filter and retrieve specific data from the table.

  14. Click Next. The Settings page appears.

  15. Enter the following details in the Settings page:
    1. Execution parameters:
      • Delete selected records: Flag to delete the records after they are published. Available for Basic Notfication only.
      • Mark ID column: Field/column of CHAR(1) data type to uniquely identify the record. The selected column must be CHAR(1) data type. You can create a new column or use an existing column that meets the criteria. Available for Basic Notfication only.
      • Query time out (sec): Time in seconds that the system waits for the notification to execute before stopping the SQL operation. You cannot add a negative value for this field.

        Possible values are:

        0: Notification executes without a time out.

        Value greater than 0: Notification executes with the specified value as the time out.

        Note: You cannot add a negative value for this field.
      • Maximum row: Number of rows to retrieve from the buffer table. Use the Maximum row field if you are retrieving many records and want to limit the number of documents sent each time the notification polls. Set the value to 0 if you do not want to set the limit on the number of rows retrieved. Not available in StoreProcedure Notification only.
    2. Publish Document
      • Connection alias name: Pre-configured and disabled. Connection name (DefautAccount) which is used by the JMS provider.
      • Destination name: Pre-configured and disabled. The name of the queue where the JMS provider publishes the document.

      • Destination type: Pre-configured and disabled. Type of destination where the JMS provider publishes the document. The default value is Queue.

    3. Schedule
      • Polling interval (sec): Duration in seconds between each poll. The minimum value is 60 seconds.

    4. Subscriber: You can view subscriber details and configure the flow service to invoke.

      • Name: Pre-configured and disabled. Name of the subscriber.

      • Description: Pre-configured and disabled. Short description for the subscriber.

      • Invocation type: Pre-configured and disabled. Type of service to invoke. The default is Flow service.

      • Service invoke: Name of the flow service to invoke. You can also create a new flow service by clicking the (Plus) icon.

      • Destination name: Pre-configured and disabled. Subscriber destination name, which is a pre-configured value of cloud messaging.

  16. Click Next. The Summary page displays the configurations and settings that are made by you throughout the wizard's various pages. The success messages about queue and subscriber being created appear.
  17. Review the details and click Done.

    You are redirected to the Events page and the polling notification is listed. You can observe that the notification is in disabled state.

  18. Move the slider on the toggle button in the State column to the right to enable the notification.

    The notification now starts to poll the database at the configured time interval and if any events occur, the configured flow service is started to perform the next set of defined operations.

Creating stored procedure database polling notifications

  1. Go to Projects > <<YOUR_PROJECT>> > Events > Polling notifications. The list of existing polling notifications appears.

  2. Click Add notification. The Add Polling notification page appears.

  3. Select Database from the Connector type drop-down list.

  4. Click Next. The Add polling notification | Database wizard appears, comprising a series of pages. The initial two pages, namely Account and Action, are shared across all scenarios. Subsequent pages in the wizard appear based on the notification type that is chosen on the Action page. On the Account page, you must configure the user account to establish the connection between the system and the database.

  5. Provide the details in the Connect account page:

    • Action name: Name of the account.
    • Description: Description for the account.
    • Connect to account Database: Database account to connect.
  6. Click Next. The Action page appears.
  7. Select the StoredProcedure Notification type.
  8. Click Next. The Calls page appears.
  9. Provide the following details:
    • Catalog names: Name of the catalog. The default is the current catalog.
    • Schema name: Name of the schema. The default is the current schema.
    • Stored procedure name: Name of the stored procedure that you want to call every time that the polling occurs. When the stored procedure is selected, the input signature is listed with the following fields:
      • Parameter name: Name of the parameter.
      • SQL type: SQL type of the parameter.
      • JDBC type: JDBC type of the parameter.
      • Parameter type: Type of parameter. Values are:

        IN: Parameter is input type. You can hardcode the input values.

        OUT: Parameter is output type.

        INOUT: Parameter is input and output type. You can hardcode the input values.

        RETURN: Parameter is output type.

        ORACLE CURSOR (INOUT): Parameter is input and output type. Set the parameters in the ResultSet tab as described later in the procedure.

        Note: Parameter types IN, INOUT, and ORACLE CURSOR (INOUT) are not supported.
    • Actions: Edit the field parameters (OUT parameter) such as Output field name and Output field type.
  10. Click Next. The ResultSet page appears. ResultSet describes the structure of the output that is returned when the stored procedure is invoked. ResultSet object received stores the data returned, which allows you to traverse the data set and access specific column values by using the cursor that is maintained by the ResultSet object.
  11. Do the following steps:
    1. Click Add ResultSet and enter the following details:

      • Name: Name of the resultset you want to create. Ensure that the resultset name must be the same as the output cursor name provided in the store procedure.

    2. Click Add. You are redirected to the ResultSet page.
    3. Click the plus icon to add columns you want to view.
      • Column name: Name of column in the result set.
      • JDBC type: JDBC type of the result set column.
      • Output type: Java™ type of the result column.
  12. Click Next. The Settings page appears.
  13. Enter the following details in the Settings page:
    1. Execution parameters tab

      • Delete selected records: Flag to delete the records after they are published. Available for Basic Notfication only.

      • Mark ID column: Field/column of CHAR(1) data type to uniquely identify the record. The selected column must be CHAR(1) data type. You can create a new column or use an existing column that meets the criteria. Available for Basic Notfication only.

      • Query time out (sec): Time in seconds that the system waits for the notification to execute before stopping the SQL operation. You cannot add a negative value for this field.

        Possible values are:

        0 : Notification executes without a time out.

        Value greater than 0: Notification runs with the specified value as the time out.
        Note: You cannot add a negative value for this field.
      • Maximum row: Number of rows to retrieve from the buffer table. Use the Maximum row field if you are retrieving many records and want to limit the number of documents sent each time the notification polls. Set the value to 0 if you do not want to set the limit on the number of rows retrieved. Not available in StoreProcedure Notification only.
    2. Publish Document tab
      • Connection alias name: Pre-configured and disabled. Connection name (DefautAccount) which is used by the JMS provider.
      • Destination name: Pre-configured and disabled. The name of the queue where the JMS provider publishes the document.
      • Destination type: Pre-configured and disabled. Type of destination where the JMS provider publishes the document. The default value is Queue.
    3. Schedule tab

      • Polling interval (sec): Duration in seconds between each poll. The minimum value is 60 seconds.

    4. Subscriber tab. You can view subscriber details and configure the flow service to start.

      • Name: Pre-configured and disabled. Name of the subscriber.

      • Description: Pre-configured and disabled. Short description for the subscriber.

      • Invocation type: Pre-configured and disabled. Type of service to start. The default is Flow service.

      • Service invoke: Name of the flow service to start. You can also create a new flow service by clicking (Plus) icon.

      • Destination name: Pre-configured and disabled. Subscriber destination name, which is a pre-configured value of cloud messaging.

  14. Click Next. The Summary page displays the configurations and settings that are made by you throughout the wizard's various pages. The success messages about queue and subscriber created appear.
  15. Review the details and click Done.

    You are redirected to the Events page and the polling notification is listed. You can observe that the notification is in a disabled state.

  16. Move the slider on the toggle option in the State column to the right to enable the notification.

    The notification now starts to call the stored procedure at the configured time interval, and starts the configured flow service to do the next set of defined operations.

Limitations

For more information about Database limitations, see Limitations.