Database event listener

Database event listeners enable Asset Lifecycle Manager to use databases to initiate workflow processes. A database event listener monitors its designated data source and initiates the process whenever the predefined conditions are met.

Setting up database event listeners

To set up a database event listener, you first need to select which workflow to initiate when the conditions of the event listener are met. Then you provide the name and authentication information for the ODBC database connection on the server where the servicehost is installed. Once you've specified the database, you need to configure the query settings, which tell the database where to look for new records, what to specifically look for, and what to do with new records.

If you're configuring a database listener in an additional console that isn't on your Asset Lifecycle Manager server, that console needs to have ODBC installed. Also, the ALM server needs to have an ODBC system DSN.

Select query

The first query setting is the select query. The first part of the select query specifies the record or record type for the database listener to watch for. The second part specifies one or more tables and columns in the database for the event listener to monitor. Finally, it defines the state of the column. The syntax of your select query should resemble the following:

select * from CallLog where CallStatus ='Open'

In this example, CallLog is the table and CallStatus the column. The database returns any record from the designated table and column currently in an open state.

NOTE: If you don't know the name of the column, you can enter a select query for the table(s) and click Get database columns, which will give you a list of columns. Then you can complete your select query with the appropriate columns. You can also use the columns list to help you create the update query.

Update query

The second query setting to configure is the update query. The update query lets you change the status of the record, so it can be removed from the queue and not be initiated again. The syntax of your update query should resemble the following:

update CallLog set CallStatus ='Pending' where CallID={|CallID|}

In this example, the first part of the update query changes the status of the CallStatus column in the CallLog table from open to pending. The second part of the query tells the database which record to update. The syntax of the where clause tells the database to use the value of the column ({|CallID|}) from the select query to know which record to update. Now, when the event listener runs the first query again, a workflow won't be initiated for the same record since it's no longer in an open state.

External unique system identifier

Finally, you need to select or provide an external system identifier on the Mappings tab. You select a database column, which is actually a cell or container of a value. Asset Lifecycle Manager retrieves that value and inserts it into the listener. The value serves as the external system identifier within all Asset Lifecycle Manager applications. The external system identifier pinpoints the application that initiated the workflow and serves as a reference number for the client application. By having this unique reference number to the client application, Asset Lifecycle Manager knows where to obtain the most current information from the database and can continually pull up-to-date information at each stage of the workflow process.

NOTE: The external system identifier should normally be a unique value. If an earlier record exists with the same external system identifier, it will be terminated when the new record initiates a workflow process. You should only intentionally use a duplicate external system identifier if you want to terminate the previous instance of the workflow process.

Once you start the database listener, it continually runs the query and watches for new records in the database column(s). Each time a value is returned from the query, the listener initiates a workflow process.

To set up a database event listener
  1. Click the Process tab, and click the Event listeners panel.
  2. Right-click the Event listeners folder, click New folder, and enter a name for the folder.
  3. Right-click the new folder and select New database listener.
  4. Enter a name for the listener.
  5. Double-click the listener you created.
  6. Click the Details tab and select the workflow you want to be initiated when the conditions of the event listener are met.
  7. Click the Settings tab and provide the ODBC data source name, User ID, and Password.
  8. In the Select query text box, insert your select query.
  9. In the Update query text box, insert your update query.
  10. Click Test, then click OK if the listener was successful in connecting to the database. If not, check the settings and query syntax you entered and try again.
  11. Click the Mappings tab, then click the Autogenerate external unique system identifier checkbox or use the drop-down list box to select the identifier.
  12. (Optional) In the Requester field, use the drop-down list box to include a requester, if you want this information to be included in the audit history and other reports.
  13. Click Autogenerate field mappings, then click Yes to save the changes to the event listener. If you want to define your own field mappings, skip this step and use the field mapping tool (see Process fields). If you select a field to set up the integration with an LDMS core, make sure the field is mapped to a Listener value that matches the Display name you provided for the LDMS core in the Asset Lifecycle Manager Settings utility.
  14. Click the Save toolbar icon.
To start (or stop) a database event listener
  1. Double-click the listener you want to start or stop.
  2. Click the Details tab.
  3. Click Start listener (or Stop listener).

You can also use the spin box to indicate the interval (in minutes) at which you want the listener to poll the database.