Configure SQL Parameter Monitoring

You can extend Java agent SQL monitoring to provide SQL parameter information in transaction traces and metrics.
apmdevops106
You can extend Java agent SQL monitoring to provide SQL parameter information in transaction traces and metrics.
SQL Parameter monitoring tracks JDBC PreparedStatements and records bind parameters. SQL Parameter monitoring also tracks ResultSet objects, counts processed rows, and reports the Average Result Rows Processed metric.
SQL Parameter monitoring is disabled by default.
Variables in SQL statements frequently represent confidential or secure data. For example, an account number or a social security number. SQL Parameter monitoring makes all parameters visible in transaction traces.
Do not install a CA APM 10.5 or older CA APM Marketplace SQLAgent with Parameters extension in addition to a 10.7 or later SQL Parameter Monitoring. Having both versions that are installed simultaneously can cause serious problems for the Java agent.
SQL Parameter Monitoring Bundle
You can add SQL Parameter monitoring to any APM Command Center Java agent package. Include the SQLParamAgent bundle when creating your package.
Upgrade SQL Parameter Monitoring from CA APM 10.5.2 to CA APM 10.7
When upgrading CA APM from 10.5.2 to CA APM 10.7, you must upgrade SQL Parameter Monitoring.
Follow these steps:
  1. Navigate to the <Agent_Home>/extensions/deploy directory.
  2. Examine the directory to see if the SQLParams.tar.gz file is present.
  3. If you find the file, remove it.
Configure SQL Parameter Monitoring
You configure the SQL Parameter monitoring properties in the IntroscopeAgent.profile file.
Follow these steps:
  1. Navigate to the <Agent_Home>/core/config directory and open the IntroscopeAgent.profile in a text editor.
  2. (Optional) Enable SQL Parameter monitoring by setting i
    ntroscope.agent.sqlagent.showparams=true
    .
  3. Configure the following properties as required.
    • introscope.agent.sqlagent.cacheConnectionsURLs
      Type:
      Boolean
      Description:
      Enables Connection URL parameter caching. The cache prevents the  Connection URL  from causing overhead and thread starvation when displaying transaction traces.
      Default:
      false
      Restart monitored application
      : Yes
    • introscope.agent.sqlagent.countrows
      Type:
      Boolean
      Description:
      Enables counting the number of rows in each result set and reporting the Average Result Set Rows Processed metric.
      Default:
      false
      Restart monitored application
      : Yes
    • introscope.agent.sqlagent.maxparamlength
      Type:
      integer
      Description:
      The maximum SQL parameter length. Any string longer than this value is truncated with a suffix of
      ...
      three periods.
      Default:
      100
      Restart monitored application
      : Yes
    • introscope.agent.sqlagent.rawsql
      Type:
      Boolean
      Description:
      Enables transaction traces to display full SQL queries. This property reports the SQL at Initial Prepare, SQL Resolved, and SQL with Variables parameters.
      Default:
      false
      Restart monitored application
      : Yes
    • introscope.agent.sqlagent.resolveBindParams
      Type:
      Boolean
      Description:
      Calculates SQL query statement with resolved bind parameters. Adds this statement to transaction trace as the SQL Resolved parameter.
      Default:
      true
      Restart monitored application
      : Yes
      Note:
      You must enable the
      introscope.agent.sqlagent.rawsql
      and
      introscope.agent.sqlagent.maxparamlength
      properties to enable the
      introscope.agent.sqlagent.resolveBindParams
      property.
    • introscope.agent.sqlagent.resultsets.max
      Type:
      integer
      Description:
      The maximum number of concurrent result sets that SQL Parameter monitoring tracks for the row count.
      Default:
      1000
      Restart monitored application
      : Yes
    • introscope.agent.sqlagent.showparams
      Type:
      Boolean
      Description:
      Enables prepared statement bind parameter tracking and displaying of bind parameter values in transaction traces.
      Default:
      false
      Restart monitored application
      : Yes
    • introscope.agent.sqlagent.showurlType:
      Boolean
      Description:
      Enables the Connection URL parameter in transaction trace. When this property is enabled, also enable the
      Introscope.agent.sqlagent.cacheConnectionsURLs
      property.
      Default:
      false
      Restart monitored application
      : Yes
    • introscope.agent.sqlagent.sql.maxlength
      Type:
      integer
      Description:
      Maximum SQL  query length. Any string longer than this value is truncated with a suffix of
      ...
      three periods.
      Default:
      500
      Restart monitored application
      : Yes
    • introscope.agent.sqlagent.statements.maxType:
      integer
      Description:
      Maximum number of concurrent prepared statements that are tracked for bind parameters
      Default:
      1000
      Restart monitored application
      : Yes
  4. Save the file.
  5. Restart the managed application.
Troubleshooting SQL Parameter Monitoring
Configure DEBUG Logs
Configure this property to turn on DEBUG logs for SQL Parameter monitoring:
log4j.logger.IntroscopeAgent.SqlParamAgent=DEBUG, console, logfile
Symptom:
I do not see the Average Result Rows Processed metric.
Solution:
Ensure the
introscope.agent.sqlagent.countrows
property is enabled.
When the property is enabled, the metric displays only when two actions occur. When the application iterates through a SQL result set, and when the application closes a SQL result set.
Symptom:
What does this warning in the agent log mean?
Result set cache max limit exceeded
Prepared statement cache max limit exceeded
Solution:
SQL Parameter monitoring tracks all open prepared statements and result sets, but the caches are limited by default to 1000 each. The number of prepared statements or result sets that the application opens can exceed the cache limit value. In this situation, SQL Parameter monitoring ignores the statements and result sets. SQL Parameter monitoring logs the warning once in the agent log, which is located in the <Agent_Home>/logs directory.
View SQL Statement Parameter Attributes in Trace View
  1. Open Team Center, WebView, or Workstation.
  2. Select an agent or node in the Metric Browser tree.
  3. Select the
    Traces
    tab, then select a trace.
  4. Select and examine a SQL statement in the transaction trace
    Trace
    view.
    These attributes display in the
    Properties
    section of an element representing a SQL statement:
    • Prepared Statement Bind Variables
    • Prepared Statement initial prepared and resolved SQL
    • Full Dynamic Statements With Variables
    • Database Connection URL
SQL Parameter Metrics
You can view the average result rows that are processed per SQL statement using the Average Result Rows Processed metric.