TEC616386: Why do I see "Unknown SQL" appearing in the Investigator?

Document created by strgu01 Employee on May 25, 2016Last modified by SamCreek on May 25, 2016
Version 2Show Document
  • View in full screen mode

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
    • APM AGENTS

 

 

 

  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

 

search-kb.jpg

Attachments

    Outcomes