How to detect lengthy PegaRULES database operations
(PEGA0002, PEGA0003, PEGA0005, PEGA0042 alerts)

Benefits

PRPC adds a line to the alert log when the PegaRULES database takes an unusually long time to respond to a request from the server. The message has one of these formats, with additional details about the date, time, and requestor:

PEGA0002 Database commit operation has exceeded a threshold setting.
PEGA0003 Database rollback operation has exceeded a threshold setting.
PEGA0005 Database query operation has exceeded a threshold setting.
PEGA0042 Packaging of database query has exceeded operation time threshold (ms)

For your own requestor session, a count of database requests that take longer than a fixed threshold value appears in the Requestor Summary section of the Details display of the Performance tool,

By default, the threshold value is set to 500 milliseconds. Through an entry in the prconfig.xml file or Dynamic System Setting, you can choose a larger or smaller threshold, or disable this feature.

In a development environment, such monitoring allows you to identify and isolate those database requests that are complex or place heavy demands on the database. Such requests may affect overall system response, and may indicate areas of the database or of the application that need attention.

A PEGA0005 alert may arise from various sources, such as the list view reports, summary view reports, and the RDB-Browse method operating on an external database.

Changing the threshold values

1. Edit the prconfig.xml file to set a cutoff value for the following elements:

• operationTimeThreshold — Value is a positive integer in milliseconds. Sets the alert threshold for the following alerts: PEGA0002, PEGA0003, PEGA0005, and PEGA0042.
• warnMs — Value is a positive integer in milliseconds. If the warnMs element is included, its value overrides the operationTimeThreshold value for PEGA0042 only.
• suppressInserts — Optional value is a boolean. Causes the values of properties in SQL statements to be replaced by a single ? character in the alert log. When omitted, the default value is true.
  

  If you explicitly set this value to false, the PEGA0005 alert may contain the value of passwords, account numbers, or other sensitive property values.

For example, to set the database operation time threshold to 850ms, the packaging time threshold (for PEGA0042) to 500ms, and enable the suppress inserts option, add these elements:

<env name="alerts/database/operationTimeThreshold" value="850" />
<env name="alerts/database/packagingTime/warnMs" value="500" />
<env name="alerts/database/operationTimeThreshold/suppressInserts"
     value="true" />
<env name="alerts/general/includeParameterPage" value="false" />

2. Stop and restart the application server to make this change effective.

3. Monitor the alert log for messages identified by the PEGA00XX tags listed above. The alert log identifies the elapsed time, the original SQL statement, and the converted SQL statement.

If the operationTimeThreshold element is not present in the prconfig.xml file, the default value is 500 milliseconds. To disable this feature completely, set a large value such as 20,000 milliseconds.

As an alternative to the prconfig.xml file, you can use Dynamic System Settings to configure your application.
See How to create or update a prconfig setting.

PDN Articles

For additional examples and explanation of these facilities, consult these PDN articles:

• Understanding the PEGA0002 Alert
• Understanding the PEGA0003 Alert
• Understanding the PEGA0005 Alert
• How to detect and remedy the performance impact of Obj-List methods and List View reports.

For more information about the PEGA0042 alert and settings, consult the PDN.

	Alert log, PegaRULES database, Storage Stream
	Working with the PegaRULES database Understanding alerts

SysAdmin category