Summary

DBTrace is a feature in the Performance Analysis Tool (PAL) that creates a detailed log of calls to the PegaRULES database. DBTrace displays SQL statements sent to the database and the time spent for each operation.

Running DBTrace in a production environment may negatively affect performance. Therefore, depending on the system's production level, the DBTrace tool may not be available.

If the production level (in the system's Data-Admin-System instance) is set to 5, DBTrace is not available. This restriction is intentional. However, when necessary, you can follow the workaround presented here to run the DB Trace tool on a V5.X production system.

Suggested Approach

In unusual situations, running the DBTrace tool in a production environment may be necessary. For example, if an issue can't be reproduced on a test system, a DBTrace on the production system may locate the issue.

. Run DBTrace in production in either of the following ways:

• Start and stop the DBTrace tool in an activity
• View generated SQL statements through the Monitor Servlet

Running DBTrace in an Activity

1. Create an activity with the first and last steps calling the activity Code-Pega-Requestor.SetRequestorLevelDBTrace.
2. Click the arrow next to the first step to expand the step and select the Enabled checkbox under the Parameters title.
   This sets the .pyDBTraceEnabled property on the pxRequestor page to True (start DBTrace).
3. Click the arrow next to the last step to expand the step and ensure that the Enabled check box is not selected.
   This sets the .pyDBTraceEnabled property on the pxRequestor page to False (end DBTrace).
   See the example below:

When the DBTrace is ended (Step 7 in the above example), a text file is generated in the ServiceExport directory. This file is identical to the file generated when you run DBTrace from the Performance Analysis Tool. View this data in one of the following ways:

• Import the text file into the DBTrace Import tab of the Microsoft Excel template attached to this article..
• Copy the text file to the ServiceExport directory on a non-production Process Commander system and start and stop DBTrace. The Download DBTrace to PC window is displayed. Select the file and click Download.

View SQL through the System Management Application

You can view executed SQL statements using the System Management Application, as follows:  

1. Select Tools > System Management Application to display the System Management form.
2. Select the Node for which you want to view the SQL statements. The menu selections for that node are displayed under the node list.

Select Advanced > Database Management to display the Database information form:

Under Active Database, select the radio button next to the database for which you want to view activity.
Click Database Details at the top of the window. The Database Connection details are displayed:

NOTE: This form lists the details for each database connection in the pool. Since you cannot tell which database connection your operation was run on, it is difficult to locate the information you need, especially if there are several connections. Use the information available (Creation date, Last Used Date, etc). to help determine which connection to select. If you select the wrong connection, click Back to return to this form and select another one.

1. Under Managed Connections, select the radio button next to the database connection for which you want to view activity.
2. Click Database Connection Details at the top of the window. All the SQL calls executed by that connection are displayed: