Defining a Database Monitor assigns the required supporting objects and determines trigger responses based on specifics of the database being monitored and the data being returned to Clarify.

1. Select the Data Source.

    This is an object that defines the information Clarify needs to connect to a single database. It also specifies which tables, views, and columns Clarify can interact with. If one doesn't exist, use the link to create a Data Source directly from the editor.

    
   Using the selected Data Source, the Database Type field should automatically display the corresponding supported database (Oracle, MSSQL, or DB2) that the monitor will interact with. If Clarify can not determine the database type, then unsupported displays. While the object may be saved at this point, it will not deploy to a server until the supported database has been indicated.
   Note: Global Variables, when used in the Data Source, prevent the editor from automatically determining the database type, resulting in unsupported as the displayed type. Users must manually set the correct database type.
2. Select the Event.

    

   This object initiates and passes data to a Business Process. If one doesn't exist, use the link to create an Event directly from the editor. The following Event parameters will be available:

    

   • payload: The storage node containing the raw XML message from the database.
   • databaseAction: The database type (insert, delete, or update).
   • datasourceDesc: The name of the data source being used.
   • databaseTable: The name of the table being monitored.
   • databaseMonitorName: The name of the Database Monitor object.
   • eventName: The name of the selected Event.
   • databaseUrl: The Data Source URL of the database being monitored.

    

   Cleo provides an XML schema that may be used to further process the payload being returned. For example, returned XML data can be transformed to another format or file, if needed. This schema -ExtolDBMonPayloadSchema.xsd -can be obtained as described here.

   • As part of the com.extol.Clarify.shippedObjects Project, available for download in your Cleo Repository. For more information, please see Connecting to the Cleo Repository.

   • By creating your own XML Schema object using the ExtolDBMonPayloadSchema.xsd, which can be found in the runtime/utils directory of your Studio install. Select Trigger as the root element. For more information, please see Creating XML Schemas (for Version 2 Rulesets).

    

3. From the Monitor section, follow the next several steps to determine which tables, rows and columns are to be monitored, and the data to be returned in the message payload (upon an insert, delete or update to those rows and columns). Using the Database Table drop-down, select the specific table you wish to have monitored. All available options are based on the Data Source (selected in Step 1).
   Note:

   Different options described in the steps below depend on the selected interaction type.

   • For update interactions, both the Monitored Columns and Returned Columns sections appear. These will allow for the selection of specific (or any) columns to monitor, and indicate specific (or all) data to be returned based on the selection.
   • For insert and delete interactions, only the Returned Columns section appears. Both interactions (return specific or return all columns) occur at the row-level only.

    

4. Determine the Selection Options for the Monitored Data (for update interactions only), which include:

    

   Option	Description
   Monitor Any Column	With this option, all columns for the selected database table will be monitored. Any change to these columns will result in the trigger sending a message payload to the Database Monitor.
   Monitor Specific Column(s)	With this option, only the columns defined for the selected database will be monitored. Changes to these selected columns will result in the trigger sending a message payload to the Database Monitor. Note: Use the Add Column option () to choose specific columns. These columns display in the Selected Monitored Columns section.

    
5. Determine the Selection Options for the Returned Data (for all interactions), which include:

    

   Option	Description
   Return All Columns	With this option selected, the returned message payload contains row-level data values for all columns.
   Return Specific Columns	With this option selected, the returned message payload contains row-level data values for only columns that have been added to the Selected Returned Columns section. Note: Use the Add Column option () to choose specific columns. These columns display in the Selected Returned Columns section.
   Return No Columns	With this option selected, the message payload does not contain row-level data values for any columns. An example payload might look like this: <Event>updateiSeries</Event> <MonitorID>Update_DB2_iSeries_EXPAP71</MonitorID> <DatabaseUrl>jdbc:as400://EXPAP71</DatabaseUrl> <TimestampGenerated>2015-11-19-16.53.05.276</TimestampGenerated> <TimestampSent>2015-11-19-16.53.05.310</TimestampSent>

   Return Data options: Old and New

   The exact data returned in the payload message to Clarify may be determined by the Old and New checkboxes as part of the Returned Columns section. The image below shows an example of the default setting for an insert interaction.

   

   • For insert interactions, the Return Data: New checkbox is selected by default, and can not be configured. In this case, New indicates the row-level data being inserted.
   • For delete interactions, the Return Data: Old checkbox is selected by default and can not be configured. In this case, Old indicates the row-level data being deleted from the table.
   • For update interactions, both checkboxes are configurable, providing several options:
     • Old and New selected: In this case, New indicates the data after the update, and Old indicates the data prior to the update.
     • Old selected only: Indicates the data prior to the update.
     • New selected only: Indicates the data after the update.
     • Neither selected (only when Return No Columns selected): The message payload does not contain data values for any columns.

    

    

6. Determine the level at which the database trigger sends a notification (message payload) for the update, insert, or delete. Drop-down options include Row or Statement levels.
   
    

   For each Statement:

   Statement-level notifications may be an option for bulk processing of records.

   • This option can be used for all supported database types.
   • When selecting this option, the Selection (Criteria) field becomes disabled.

    

   For each Row:

    

   Row-level notifications may be an option when processing specific column and field database activity.

   • Use the Selection field to determine which specific data will execute the trigger.
   • This option is not available for MSSQL.

    

7. Use the optional Selection field to specify column criteria, via a query, for determining which column-specific data match will execute the trigger.

    

    

   Three examples

   The following examples show query statements for three different notification results, using each of the monitor types available.

   1. Return (execute trigger) when new record is inserted and the Quantity column is greater than 1000. (Specify a range for a specific column)

      Monitor type used: Insert

      

   2. Return (execute trigger) when a record or column is updated where the old Amount value was less than $100 and the new Amount is greater than $10000.

      Monitor type used: Update

      

   3. Return (execute trigger) when a record is deleted and the Vendor name column matches a specific Trading Partner. In this case, Acme.

      Monitor type used: Delete

      

   1.