Question
Can we use DBTrace to trace activity of multiple requestors in production or UAT environment?
Need to trace the DB activity in production and UAT to identify some issues as they are not logged in the system logs and are hard to identify.
Need to understand if we Can we use DBTrace to trace activity of multiple requestors in production or UAT environment? Please advise.
The application is running on pega platform v6.1 SP2
Pegasystems
IN
You can do system wide db trace. Please see the link below.
https://community.pega.com/sites/default/files/help_v718/procomhelpmain.htm
Excerpt
System-wide database trace
An alternative approach that provides comprehensive tracing of SQL statements sent to the PegaRULES database is the dumpStats parameter in the prconfig.xml file.
To enable this feature:
1. Update the <database> node of the prconfig.xml file to add this element:
<entry key="dumpStats" value="true" />
2. Stop and restart the application server.
Note: You can also use Dynamic System Settings to configure your application. See How to create or update a prconfig setting.
You can do system wide db trace. Please see the link below.
https://community.pega.com/sites/default/files/help_v718/procomhelpmain.htm
Excerpt
System-wide database trace
An alternative approach that provides comprehensive tracing of SQL statements sent to the PegaRULES database is the dumpStats parameter in the prconfig.xml file.
To enable this feature:
1. Update the <database> node of the prconfig.xml file to add this element:
<entry key="dumpStats" value="true" />
2. Stop and restart the application server.
Note: You can also use Dynamic System Settings to configure your application. See How to create or update a prconfig setting.
This setting generates a system-wide database trace file in the ServiceExport directory that can become very large quickly, and can affect system performance. Use this setting only for brief periods, and when a single-requestor DB trace is not suitable.
PEG
GB
As per Rajiv's advice, you can enable system wide DBTrace with the provided setting.
Please note though that these settings are there to capture DML SQL (SELECT, UPDATE,etc...) but not DDL SQL (CREATE, ALTER,etc...).
We have included an option in later versions of the product to allow you to enable Global DB Trace without the need to restart the application server (via SMA).
Unfortunately this option doesn't exist in 6.1SP2, so an application server restart will be necessary to both switch it on, and switch it off when you're done.
Generally when I'm trying to troubleshoot problems relating to the generated SQL, I'm also interested in looking at the values. So you may need to set "alerts/database/operationTimeThreshold/suppressInserts" to false.
prconfig.xml excerpt: -
<env name="database/dumpstats" value="true" />
<env name="database/traceEvents" value="preparedStatementUpdate;preparedStatementQuery;preparedStatement" />
<env name="alerts/database/operationTimeThreshold/suppressInserts" value="false" />
This will generate a file called dbOperations_<date>.txt in the PegaTemp/StaticContent/global/ServiceExport directory.
Finally, a couple of words of warning: -
1. This can produce a lot of information. If you're running on a UNIX based platform and you find that the file has become too big, you may be able to delete the contents with something like: -
As per Rajiv's advice, you can enable system wide DBTrace with the provided setting.
Please note though that these settings are there to capture DML SQL (SELECT, UPDATE,etc...) but not DDL SQL (CREATE, ALTER,etc...).
We have included an option in later versions of the product to allow you to enable Global DB Trace without the need to restart the application server (via SMA).
Unfortunately this option doesn't exist in 6.1SP2, so an application server restart will be necessary to both switch it on, and switch it off when you're done.
Generally when I'm trying to troubleshoot problems relating to the generated SQL, I'm also interested in looking at the values. So you may need to set "alerts/database/operationTimeThreshold/suppressInserts" to false.
prconfig.xml excerpt: -
<env name="database/dumpstats" value="true" />
<env name="database/traceEvents" value="preparedStatementUpdate;preparedStatementQuery;preparedStatement" />
<env name="alerts/database/operationTimeThreshold/suppressInserts" value="false" />
This will generate a file called dbOperations_<date>.txt in the PegaTemp/StaticContent/global/ServiceExport directory.
Finally, a couple of words of warning: -
1. This can produce a lot of information. If you're running on a UNIX based platform and you find that the file has become too big, you may be able to delete the contents with something like: -
cat dbOperations_<date>.txt > /dev/null
2. Probably more obvious (but I'll include it for the sake of completeness), but if this is not a Test system, you'll need to ensure the dboperations file(s) is/are deleted once you've completed your troubleshooting.