Create a SQL Agent Extension to Rename an Unsupported Database

Document ID : KB000044510
Last Modified Date : 14/02/2018
Show Technical Document Details

Environment:

APM 10.3

 

Question:

How to create a SQL Agent Extension to Rename an Unsupported Database

 

Answer:

When an application makes a database connection, the SQL agent provides a display name in WebView and Workstation. For supported databases, the SQL agent uses a SQL agent-formatted name for the database resource name.

The SQL agent metrics appear in the Investigator under the Backends node in WebView and Workstation. SQL statement metrics appear under the Backends|<Backend_Name>|SQL node in WebView and Workstation.

For unsupported JDBC databases, the SQL agent does not create a display name. Instead the SQL agent uses the database URL. Typically a database URL does not include a formatted database name or type, making identification difficult. The SQL agent cannot provide a different name because it does not have a nameformatter for that database. 

Here is an example of an unformatted name: jdbc:sap://XXXXXX:12345.

When a SQL agent or a SQL agent extension include defined database nameformatters, WebView and Workstation display meaningful database names. By default, the SQL agent includes nameformatters that are already defined for supported databases.

As an APM Administrator, you can create a SQL agent extension to display a formatted database name. In the extension, you create a custom nameformatter to name an unsupported database.

You can create as many SQL agents extensions for as many unique database names as you need. The custom nameformatters that you create do not affect any existing Java agent name formatters.

Example: SQL Agent Discovers Unknown JDBC Connection

The monitored application makes a JDBC connection to an SAP High-Performance Analytic Appliance (HANA) database in your environment. The SQL agent does not recognize this database. The SQL agent uses the JDBC backend resource URL of jdbc:sap://XXXXXX:12345 for the database name. 

As the APM Administrator, you happen to know that XXXXXX:12345 is an SAP HANA database. This database uses 12345 as the default port. However, your CA APM users cannot identify XXXXXX:12345 as an SAP HANA database. They would expect to see a resource URL database name such as XXXXXX-12345 (HANA DB).

You create a custom HANA Database Nameformatter extension, and include a custom nameformatter for the HANA database. After deploying your extension, diagnosers and triagers see XXXXXX-12345 (HANA DB) as the database resource name.

Create the Custom SQL Agent Extension

You configure a custom database name by creating a nameformatter using custom classes, methods, and an interface. You create the custom extension and then add the nameformatter. After you deploy the extension, the custom database name displays in WebView and Workstation.

Create the Custom Entry Point Class

The Entry Point class provides the custom database name details and the corresponding nameformatter for the custom SQL agent extension. The Entry Point class implements the IGenericDBNameFormatter interface. 

Follow these steps:

  1. Create a custom entry point class.

  2. Implement two methods that the IGenericDBNameFormatter interface defines.

    1. The GetDBprefix():String method returns a string that is the database name prefix that you want to use. For example, jdbc:sqlserver.
    2. The GetNameFormatter method returns an instance of IDBNameFormatter
      Both methods are shown in this code sample. 
      package com.wily.introscope.agent.extensions.sample;
      import com.wily.introscope.agent.trace.jdbc.IDBNameFormatter;
      import com.wily.introscope.agent.trace.jdbc.IGenericDBNameFormatter; public class MyNewDBEntryPoint implements IGenericDBNameFormatter { public IDBNameFormatter getNameFormatter() { return new SampleDBNameFormatter(); }
      public String getDBPrefix() { return "jdbc:sqlserver"; } }

Create a Custom Nameformatter Class

You implement the nameformatter method to return the desired database name.

  1. Create a custom dbnameformatter class that implements the IDBNameFormatter interface.
  2. Implement the format() method, which resolves the database name, as shown in the following example.

    public class SampleDBNameFormatter implements IDBNameFormatter {
     
        @Override
        public String format(String inString, IStringLocalizer localizer) {
            String result = inString;
            String host = "localhost";
            String port = "1234";
            String dbame = "My Sample DB";
            if (result != null) {
                result =
                    localizer.IStringLocalizer_getFormattedLocalizedString(
                        KAgentStringsKeys.kAgentSQLBackendSQLServerDBNameTemplate, dbame, host, port);
                String a = null;
                a.charAt(1);
            }
            return result;
         }

When The SQLNameFormatter code calls the format() method, the localizer instance formats the string into the host name and port. The SQLNameFormatter code assumes a colon symbol ":" always follows the DBPrefix. The code removes the colon while resolving the URL.

For example, for the database URL that is shown, the GetDBprefix():String method must return jdbc:sap. The localizer value in theformat() method formats the string into //myhost:31015/?autocommit=false.

jdbc:sap://myhost:31015/?autocommit=false

(Optional) Use IStringLocalizer to Provide Methods to Format the Database Name

You can use the IStringLocalizer utility that provides various methods to format the database name.
For example, the SQL agent getFormattedLocalizedString method of the IStringLocalizer interface accepts the database template, database name, host, and port and returns a formatted database name.

  1. Code a line that includes a IStringLocalizer method.
    For example, use IStringLocalizer_getFormattedLocalizedString to provide display information about the database host as shown in this code snippet:

    public String IStringLocalizer_getFormattedLocalizedString(String key, String parameterOne, String parameterTwo, String parameterThree);
  2. Use a text editor to replace variables with the information that you want to display within the database name.
    For example, to create a database name using the string in the previous example, substitute String key and String parameter<Number> with values for content you want to display as part of the database name, as shown in this code snippet: 
    localizer.IStringLocalizer_getFormattedLocalizedString(KAgentStringsKeys.kAgentSQLBackendSQLServerDBNameTemplate, “localhost”, “1234”, “My New DB”)

          When you deploy the custom extension, “My new DB on localhost-1234 (MS SQL Server DB)" displays as the database name. Administrators, triagers, and diagnosers see this display name in WebView and Workstation.

Create the Manifest

You create the custom extension manifest.mf file that includes the extension custom methods, classes, types, and other information.

 

  • Use the following code sample as a template or create your own manifest. Include the required information as indicated in the following example:
    Manifest-Version: <Verson_Number>
    com-wily-Extension-Plugin-dbnameformatter-Name: <Custom_Extension_.jar _Filename
    com-wily-Extension-Name: <Extension_Namecom-wily-Extension-Plugins-List: dbnameformatter
    Built-By: <Any_Name>
    Created-By: <Any_Name>
    com-wily-Extension-Plugin-dbnameformatter-Version: com-wily-Name: <Any_Name>
    Build-Jdk: <JDK_Version> com-wily-Extension-Type: Introscope-Agent com-wily-Extension-Plugin-dbnameformatter-Type: dbnameformatter com-wily-Extension-Version: 1 com-wily-Extension-Plugin-dbnameformatter-Entry-Point-Class: <Custom_Extension_Entry_Point_Class_Name> Archiver-Version: Plexus Archiver

Example: Manifest with Example Names and Required Variables

 

Manifest-Version: <Verson-Number>
com-wily-Extension-Plugin-dbnameformatter-Name: SampleDBExtension 
com-wily-Extension-Name: SampleDBExtension com-wily-Extension-Plugins-List: dbnameformatter (Note: Do not change from template.)
Built-By: <Any_Name>
Created-By: <Any_Name>
com-wily-Extension-Plugin-dbnameformatter-Version: 1 (Note: Do not change from template.) com-wily-Name: sample database extension
Build-Jdk: 1.7.0_51 com-wily-Extension-Type: Introscope-Agent (Note: Do not change from template.) com-wily-Extension-Plugin-dbnameformatter-Type: dbnameformatter (Note: Do not change from template.) com-wily-Extension-Version: 1 (Note: Do not change from template.) com-wily-Extension-Plugin-dbnameformatter-Entry-Point-Class: com.wily.introscope.agent.extensions.sample.SampleDBEntryPoint Archiver-Version: Plexus Archiver (Note: Do not change from template.)

 

Deploy the Custom SQL Agent Extension

  1. Create a custom .jar file. 

  2. Put the custom extension .jar file into the <EM_Home>core/ext folder.
  3. (Optional) Create more custom name formatters and define them in the same manifest.mf file. Include the corresponding classes in the custom extension .jar file.
  4. (Optional) Create more custom SQL agent extensions for as many unique database names as you want to display.
    1. Repeat the steps to Create a Custom SQL Agent Extension for each database.   
    2. Repeat steps one and two in this topic to deploy the custom SQL extension.
  5. Restart the managed application.

 

The custom database name displays in WebView and Workstation.

 

Troubleshooting the Deployment

If you cannot see the custom database name, you can review the extension for proper implementation and loading.

Follow these steps:

  1. Ensure that the custom extension is getting loaded.

    1. Review the custom SQL agent extension log file messages.

      For example, based on the log level, review the debug messages such following verbose and debug messages:

      [VERBOSE] [IntroscopeAgent.Agent] Identified extension <Extension name>
      [DEBUG] [IntroscopeAgent.Agent] Loaded Extensions ########### ->{ List of extension names}
    2. Ensure that the manifest.mf file entries are correct.
  2. If the custom extension is loaded, review the code in all the extension files.

    1. Review that you implemented the extension properly.
    2. Ensure that the DBFormatter name is not mis-spelled.
    3. Look for extra or incorrect code, file names, and so on.