Creating stored procedure polling notifications

Run a user-defined stored procedure at defined intervals to detect and retrieve relevant data changes. The procedure returns a result set, which is passed to an integration for downstream processing.

Before you begin

  • Make sure that the messaging capability is enabled for your webMethods Integration tenant.
  • The details of the database that you want to connect to, such as a user account, driver group, database name, credentials, and server name.
  • Make sure that the stored procedure exists in the database and returns an output. For example, stored procedure GET_CUSTOMER_NOTIF_DETAILS.
    
CREATE OR REPLACE PROCEDURE GET_CUSTOMER_NOTIF_DETAILS (
CUSTOMER_DETAIL_OUT_SP OUT SYS_REFCURSOR ) AS
BEGIN
OPEN CUSTOMER_DETAIL_OUT_SP FOR
SELECT CUSTOMERID, LASTNAME, FIRSTNAME, ADDRESS, CITY from CUSTOMER_SP;
END;

About this task

In this example, a polling notification is created to log a message when the stored procedure is started at the specified polling interval. The stored procedure returns the contents of the CUSTOMER_SP table and SamplePollingNotification project is used for this tutorial.

Creating and testing polling notifications involves the following steps:

  1. Create a database account. For more information about creating Database accounts, see Creating accounts.
  2. Create a polling notification for stored procedure.
  3. Create a flow service to run when the notification is received.
  4. Verify that the flow service runs when the notification is received.

Procedure

  1. Go to Projects and select your project.
  2. Go to Events > Polling notifications.
    The list of existing polling notifications are displayed.
  3. Click Add notification.
    The Add Polling notification page is displayed.
    Note:
    • The Add Notification option 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 for creating polling notifications.
  4. Select Database from the Connector type drop-down list.
  5. 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.
  6. 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.
  7. Click Next.
    The Action page is displayed.
  8. Select the StoredProcedure notification action.
  9. Click Next.
    The Calls page appears.
  10. 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. The following values are supported:
    IN
    The parameter is the input type. You can hardcode the input values.
    OUT
    The parameter is the output type.
    INOUT
    The parameter is an input and output type. You can hardcode the input values.
    RETURN
    The parameter is an output type.
    ORACLE CURSOR (INOUT)
    The parameter is an input and output type. Set the parameters in the ResultSet tab as described later in the procedure.
    Note: Parameter type ORACLE CURSOR (INOUT) is not supported.
    Expression
    Value of the parameter, for example 'abc'.
    Note: Stored Procedure notifications cannot be enabled unless a value is provided in the EXPRESSION field for IN parameters.
    Actions
    Edit the field parameters (OUT parameter) such as Output field name and Output field type.
  11. Click Next.
    The ResultSet page appears. ResultSet describes the structure of the output that is returned when the stored procedure is started. The ResultSet object holds the returned data, and you can go through the dataset and access-specific column values by using its internal cursor.
  12. Do the following to add the resultset.
    1. Click Add ResultSet.
    2. Enter the name of the resultset that you want to create.
      Make sure that the resultset name must be the same as the output cursor name provided in the store procedure.
    3. Click Add.
      You are redirected to the ResultSet page.
    4. Click the Add 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.
  13. Click Next.
    The Settings page is displayed.
  14. On the Settings page, provide the information:
    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 a 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 run before the SQL operation is stopped. You cannot add a negative value for Query time out (sec) field. Possible values are:
      Zero
      Notification runs without a time out.
      Value greater than zero
      Notification runs with the specified value as the time out.
      Note: You cannot add a negative value for Query time out (sec) 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. If you do not want to set the limit on the number of rows retrieved, set the value to zero. Not available in StoreProcedure Notification only.
    2. Publish Document tab
      Connection alias name
      Preconfigured and disabled. Connection name (DefautAccount) which is used by the JMS provider.
      Destination name
      Preconfigured and disabled. The name of the queue where the JMS provider publishes the document.
      Destination type
      Preconfigured 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
      Preconfigured and disabled. Name of the subscriber.
      Description
      Preconfigured and disabled. Short description for the subscriber.
      Invocation type
      Preconfigured and disabled. Type of service to start. The default value is Flow service.
      Service invoke
      Name of the flow service to start. You can also create a new flow service by clicking the Add icon. For example, a flow service Customer_SP_FlowService is created.
      Destination name
      Preconfigured and disabled. Subscriber destination name, which is a preconfigured value of cloud messaging.
    5. Click Save.
      Whenever the document is published to the destination (queue), the subscriber starts the configured flow service to process the next set of defined actions.
  15. Click Next. The Summary page is displayed.
  16. Click Done. The message Queue created successfully is displayed.
  17. Review the settings. If you want to modify, then click the Previous button to go to the corresponding page and update the settings.
  18. Click Done.
    The Events>Polling notification page displays listing the newly created notification. By default, it is disabled.
  19. Move the slider on the toggle button in the State column to the right to enable the notification.
    The notification polls the database server at the configured interval of 60 secs to run the stored procedure and retrieve the output. The details are retrieved in a document format and published to the destination (queue), the subscriber starts the Customer_SP_FlowService flow service to process the next set of defined actions.

Creating flow services

About this task

This flow service starts when a notification is received.

Procedure

  1. Go to Integrations > Flow services.
  2. Select the Customer_SP_FlowService flow service to edit.
    The Flow service editor page is displayed.
  3. On the flow service step, type documentToJSONString to convert the incoming document to a JSON string.
  4. Click View/Edit Pipeline to map the input and output fields to the service.
    The Pipeline panel is displayed.
  5. Map the pipeline input JMSMessage > body > data to the input document field in documentToJSONString.
  6. On the flow service step, type logCustomMessage to log the message.
  7. Click View/Edit Pipeline to map the input and output fields to the service.
    The Pipeline panel is displayed.
  8. Map the pipeline input jsonString to the input message field in logCustomMessage.
  9. Click Save to save the service.

Verifying flow services

About this task

Explore the following steps to check if the flow service runs when a notification is received.

Procedure

  1. Go to Monitor > Execution results > Flow service executions.
  2. Filter the results for the project that is used to create the flow service.
  3. Enable the stored procedure polling notification from the Events page if it is disabled.
  4. Click the 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 ran without any errors.
  5. Click the flow service to view the log details. You see one entry for each row in the customer details.
    Note: Disable the polling notifications as the stored procedure runs after every 60 sec, then logging for each row.