This section outlines general best practice guidelines for using database components:

Where Possible Use the Database Communication Point in Preference to Filters

Communication points are much better than filters at handling database connections due to their configurability relating to connection failures. They also have their own queues for processing which consequently do not block message flow, and have a greater ability to ensure FIFO is maintained. In contrast, connection errors at a filter’s level require relatively more effort to ensure FIFO is maintained as a communication point may simply retry the queued message.

  • Use the Database communication point in Out->In mode instead of Database Lookup filter when possible.
  • Use the Database communication point in Input mode rather than Database Message Extraction filter if possible.
  • If a database filter is used, use Error Processing on the filter or the Route Error Handler to handle error messages properly.
  • Special consideration is required when using a database filter to query data that may need updating/inserting by a message that preceded the current message being processed. Even though FIFO is configured, the previous message may not have reached the downstream system and therefore the database filter may be querying data that is not yet current.

Choose the Correct Database Components

  • If you want to retrieve data from a database table, use the Database communication point in Input mode. It polls a database regularly to detect changes using a static query.
  • If you want to insert or update one row into a database table for every message it receives, use the Database Insertion communication point.
  • If you want to retrieve data from a database table for every message it receives, use the Database Message Extraction filter, keeping in mind the caveats of the filter.
  • If you want to insert or update data retrieved from a message and continue processing after that, use the Database communication point in Out->In mode or the Database Lookup filter. They can also be used to insert directly into messages,  populate message properties or replace an entire message body using retrieved values from a database table.

Use Configuration Properties to Handle Connection Failures Properly

  • Set the query timeout limit by configuring the Query Timeout configuration property (only works for MS-SQL and Oracle) to prevent hanging database connections.
  • Set socket timeout to recover from hanging connections.
  • Set the Idle Timeout configuration property for the Database communication point to drop inactive database connections automatically. We recommend using a timeout value less than an hour.
  • If database filters are used, do not use an infinite value (0) for the Maximum Concurrency configuration property. Limiting the level of concurrency limits the number of concurrent databases transactions and the memory used by the filter.
  • When configuring FIFO-enabled routes with database filters, bear in mind that a long-running transaction will block the route and cause message build-up while it waits to commit. So where possible, disable FIFO.

Use Notifications

  • Turn on the Message Potentially Stuck While Sending notification for the Database communication point in Output mode and in Out->In mode to get notified when the sending message process does not complete within the specified time. This notification is turned on by default.
  • Turn on the Message Time In Filter Exceeded notification for the Database Lookup filter and Database Message Extraction filter to get notified when message processing in the filter does not complete within the specified time. This notification is turned on by default.

Use Stored Procedures and Views in Preference to SQL Statements

Use of Stored Procedures and Views can simplify the configuration within the SQL editor of the component and the process of executing multiple statements.

Views should be used for lookup workflows; stored procedures should be used for update workflows, particularly where return values or multiple execution logic steps are required (such as execute an insert then retrieve the resultant row of data)

Calling a pre-configured view or stored procedure supports:

  • Reuse of the query.
  • Simplified layout and parameter/return value passing.
  • Abstraction of database object names in the underlying query that may change per environment.
  • The ability of the database administrator to assess the performance of the SQL statements and executing plans within their database environment.

Configure and Design Database Components Efficiently

  • Avoid unnecessary SQL script or long running SQL queries within the database configuration to avoid database contention (deadlocking and blocking). If the script is generating data that can be done in Rhapsody use the filters instead, in other words do as much pre-processing as possible prior to sending the message to a database component. If a query takes an unreasonable time, it is recommended you run the query outside Rhapsody and let Rhapsody read the results later.
  • Avoid retrieving large result sets. Where possible, break potentially large results sets into several smaller ones.