Polling Notifications

Learn about polling notifications supported in Integration.

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. For 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 containing information about the modifications. These published documents can then trigger further actions, such as invoking 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 you need to connect to.

Polling interval is the time duration for the recurring check, and you can define this duration when 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 publishes 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. Using an UpdateNotification, you can:

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 while configuring 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 being 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 which is returned by the stored procedure when it is invoked.

Ordered Notification

Monitors multiple insert, update, or delete operations on one or more tables for a given database by creating a single notification using 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.

Note

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

Polling notifications operational workflow

Tasks performed by Integration system upon creation of polling notifications are as follows:

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 case of 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 case of StoredProcedure Notification, buffer table, trigger table and sequence are not created.
Schedules the polling notification.
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.
Configures the subscriber details such as subscriber name, invocation type, and destination name. Here, you have the option to 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.
Publishes the notification data to the messaging destination.

Accessing polling notifications

Log on to the Integration.
Go to Projects and select your project.
Go to Events > Polling notifications. The list of existing polling notifications appears.
Note
- The Add Notification button is not displayed if the messaging service is not enabled. Contact IBM Account Executive to enable cloud messaging.
- Ensure that the database user has privileges to create table, trigger and sequence before creating 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

Go to Projects > <<YOUR_PROJECT>> > Events > Polling notifications. The list of existing polling notifications appears.
Click Add notification. The Add Polling notification page appears.
Select Database from the Connector type drop-down list.
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 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.
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.
Click Next. The Action page appears.
Select the notification type.
Click Next. The Tables page appears.
Do the following to add tables:

a. Click the icon to add a new table. The Add tables page appears.

b. Select the table from the catalog.

c. Click Add. The table is added and listed in the Tables page.
Click Next. The Joins page appears. Note that the Joins page is not available in case of Ordered Notification.
Define the joins, if any to retrieve data from different tables.
Click Next. The Data fields page appears.

a. Click the icon to add data fields. The Add data fields page appears listing all the columns of the selected tables.

b. Select the data fields. The selected data fields are added in the document which is received by the notification.

c. Click Add. The fields are added.

d. 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 new 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.
Click Next. The Condition page appears. You can add the criteria or expressions to filter and retrieve specific data from the table.
Click Next. The Settings page appears.
Enter the following details in the Settings page:

a. 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 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 a large number of 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.
b. 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. 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. Default value is Queue.
c. Schedule tab
- Polling interval (sec): Duration in seconds between each poll. Minimum value is 60 seconds.
d. Subscriber tab. 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. 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.
Click Next. The Summary page displays the configurations and settings made by you throughout the wizard’s various pages. The success messages about queue and subscriber being created appear.
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.
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 invoked to perform the next set of defined operations.

Creating stored procedure database polling notifications

Go to Projects > <<YOUR_PROJECT>> > Events > Polling notifications. The list of existing polling notifications appears.
Click Add notification. The Add Polling notification page appears.
Select Database from the Connector type drop-down list.
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 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.
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.
Click Next. The Action page appears.
Select the StoredProcedure Notification type.
Click Next. The Calls page appears.
Provide the following details:
- Catalog names: Name of the catalog. Default is current catalog.
- Schema name: Name of the schema. Default is current schema.
- Stored procedure name: Name of the stored procedure you want to call every time 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.
Click Next. The ResultSet page appears. ResultSet describes the structure of the output returned when the stored procedure is invoked. ResultSet object received stores the data returned, which allows you to travese the data set and access specific column values using the cursor maintained by the ResultSet object.
Do the following:

a. Click Add ResultSet and enter the following details:
- Name: Name of the resultset you want to create.
  
  Note
  
  Ensure that the resultset name must be the same as the output cursor name provided in the store procedure.
b. Click Add. You will be redirected to the ResultSet page.

c. Click (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.
Click Next. The Settings page appears.
Enter the following details in the Settings page:

a. 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 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 a large number of 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.
b. 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. 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. Default value is Queue.
c. Schedule tab
- Polling interval (sec): Duration in seconds between each poll. Minimum value is 60 seconds.
d. Subscriber tab. 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. Default is Flow service.
- Service invoke: Name of the flow service to invoke. 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.
Click Next. The Summary page displays the configurations and settings made by you throughout the wizard’s various pages. The success messages about queue and subscriber being created appear.
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.
Move the slider on the toggle button in the State column to right to enable the notification.

The notification now starts to call the stored procedure at the configured time interval and invoke the configured flow serviceto perform the next set of defined operations.

Limitations

For more information about Database limitations, see Limitations.

Creating an insert polling notification

Let’s create a polling notification that logs a message when a new customer is added to the CUSTOMER table. A sample representation of the table is as follows:

Assumptions

SamplePollingNotification project is used for this tutorial.

Before you begin

Ensure that the messaging capability is enabled for your Integration tenant.
Ensure that you have the required details of the database that you want to connect to such as user account, driver group, database name, credentials, and server name.
Ensure that the database user has privileges to create table, trigger and sequence.

Basic Flow

Creating and testing polling notifications involves the following steps:

Create a database account.
Create a polling notification.
Create a flow service to run when the notification is received.
Verify that the flow service runs when the notification is received.

Create a database account

For more information about creating Database accounts, see Creating a Database Account. For example, CustomerInfoAccDB.

Create a database polling notification

Go to Projects and select your project.
Go to Events > Polling notifications. The list of existing polling notifications appear.
Click Add notification. The Add Polling notification page appears.
Select Database from the Connector type drop-down list.
Click Next. The Add polling notification | Database wizard appears.
Configure the database account details on the Account page. For more information about creating the Database accounts, see Creating a Database Account.
Click Next. The Action page appears.
Select the Insert Notification action.
Click Next. The Tables page appears.
Do the following to add tables:

a. Click the (Plus) icon to add a new table. The Add tables page appears.

b. Select the CUSTOMER table from the catalog.

c. Click Add. The table is added and listed in the Tables page.
Click Next. The Joins page appears.

Here, in our example, joins were not added.
Click Next. The Data fields page appears.

a. Click the (Plus) icon to add data fields. The Add data fields page appears.

b. Select the data fields to be added in the table.

c. Click Add.

You can also change the Output Field Type and Sort Order for a data field by clicking the corresponding Edit button. Here, in our example, we have changed the Output Field Type of the field DOB to the java.lang.String.
Click Next. The Condition page appears. You can see all conditions created and also add conditions.

In this example no conditions are specified.
Click Next. The Settings page appears.

a. The Execution parameters settings page appears. Configure the fields.

b. Click Publish Document to view the JMS provider details. You cannot modify any values. The newly added customer details are retrieved from the customer table and sent as a document to the destination (queue).

c. Click Schedule to set the polling interval.

d. Click Subscriber to configure the subcriber settings. You can view subscriber details and configure the flow service to invoke.
- Service invoke: Click the (Plus) icon to create a new flow service. Here in our example, a flow service InsertCustomerFS is created.
Click Save.

Whenever the document is published to the destination (queue), the subscriber invokes the configured flow service to process the next set of defined actions.
Click Next. The Summary page appears.
Review the settings. If you want to modify, then click the Previous button to go to the corresponding page and update the settings.
Click Done. The Events > Polling notification page appears listing the newly created notification. By default, it is disabled.
Move the slider on the toggle button in the State column to right to enable the notification

Now, this notification polls the database server at the configured interval of 60 secs and retrieves the details of the newly added customers. The details are retrieved in a document format and published to the destination (queue), the subscriber invokes the InsertCustomerFS flow service to process the next set of defined actions.

Create a flow service to invoke when notification is received

The flow service converts the document to a JSON format and logs the details.

Go to Integrations > Flow services.
Select the InsertCustomerFS flow service to edit. The flow service editor page appears.
On the flow service step, type documentToJSONString to convert the incoming document to a JSON string.
Click (View/Edit Pipeline) to map the input and output fields to the service. The Pipeline panel appears.

Map the pipeline input JMSMessage > body > data to documentToJSONString input document field.
On the flow service step, type logCustomMessage to log the message.
Click (View/Edit Pipeline) to map the input and output fields to the service. The Pipeline panel appears.

Map the pipeline input jsonString to logCustomMessage input message field.
Click (Save) to save the service.

Verify that flow service runs when notification is received

Go to Monitor > Execution results > Flow service executions.
Filter the results for the project used and flow service created.
Use any database client and insert a record in the table monitored.
Click (Refresh) icon in the Monitor > Execution results > Flow service executions page.

You can notice that the status is Success which means that the flow service has run without any errors.
Click the flow service to view the log details. You can see the customer details that were inserted using the database client.

Creating a stored procedure polling notification

Summary

Let’s create a polling notification that logs a message when the stored procedure is invoked at the polling interval specified. The stored procedure returns the contents of the CUSTOMER_SP table. A sample representation of the table is as follows: