Document ID: TEC616386
Last Modified Date: 12/17/2015
Author: strgu01
- Products
- CA Application Performance Management
- Releases
- CA Application Performance Management:Release:CA APM 9.5
- CA Application Performance Management:Release:CA APM 9.6
- CA Application Performance Management:Release:CA APM 9.7
- Components
Summary:
This article discusses some potential causes and fixes when metrics or trace components report as "Unknown SQL" in the Investigator.
Instructions:
There are many reasons why "Unknown SQL" can appear as a metric or as part of a transaction trace in the Investigator.Generally, this message is associated with the JDBC driver version used by the application reporting "Unknown SQL". Always check the APM Compatibility Guide to ensure your JDBC driver is supported for the agent release being used. The instrumentation responsible for JDBC drivers (sqlagent.pbd) is updated in every major APM release. Consequently, for the most current support for your JDBC driver, always use the latest released version of the java agent.
In anything that is not a dynamic SQL statement, the SQL call name must be stored during the prepareStatement() or prepareCall() invocation, and then retrieved during the execute() invocation. If this lookup fails, you will see instead an "Unknown SQL".
Why would the lookup fail? One possible cause is that the prepareCall() or prepareStatement() APIs are not traced, because their implementation class(es) are not instrumented in sqlagent.pbd. In this case, verify that the JDBC driver used is supported (as mentioned earlier) or consider adding additional entries in sqlagent.pdb to instrument these classes. You will need to choose the class used by the driver for the JDBC connection and then add this class as a SQLAgentConnections tracer group, (using either IdentifyInheritedAs or IdentifyClassAs). For examples, see the Connection configuration section of the sqlagent.pbd file .
Introscope is monitoring and collecting all real SQL statements, and you can disregard the metrics under Unknown SQL. The "Unknown SQL" message is usually associated with certain JDBC drivers or certain versions of them. Different versions of our sqlagent.pbd have been modified over time to try to address this.
To get the SQL from any statement that is not a dynamic SQL statement, the SQL text must be stored during the prepareStatement() or prepareCall() invocation, and then looked up during the execute() invocation. If the lookup fails, you get Unknown SQL.
Here is a common scenario:
ClassA implements a PreparedStatement incorrectly.
ClassB is "good" implementation of the PreparedStatement (you can get the SQL string).
The call stack looks like this:
ClassA.execute(...) delegates execution to ClassB and calls
Since it only passes job to ClassB, it usually takes very short time to execute itself, except for the part from ClassB. Even though the class returns "Unknown SQL," it is only a wrapper class and you can disregard it.
ClassB.execute(...) really calls the database.
This is where you see the clear SQL statement and corresponding metrics.
In the Workstation, you will see both Unknown SQL and the real SQL statement.If you have a transaction trace, you should see:
ClassA calls ClassB
The corresponding clear SQL string from ClassB.
You will not see the SQL string in Transaction Traces Query View for Class, but you will see it for ClassB.
Introscope gets the statement information from JDBC statement object. However, the return value from the incorrectly implemented wrapper class cannot be recognized, thus resulting in the Unknown SQL.
The "Unknown SQL" message is usually associated with certain JDBC drivers or certain versions of them. Different versions of our sqlagent.pbd have been modified over time to try to address this.
Additional Information:
http://www.ca.com/us/support/ca-support-online/product-content/status/compatibility-matrix/application-performance-management-compatibility-guide.aspx
Search the Entire CA APM Knowledge Base
