iAnywhere Plugin

iAnywhere Plugin is a database plugin. It is used to monitor the services provided by SQLAnywhere.

It consists of 3 files:

Overview:

1) ServerDetector (iAnywhereServerDetector.java). It is used for detecting the SQLAnywhere server and also the services provided by it.

2) MeasurementPlugin (iAnywhereMeasurementPlugin.java). It is used to collect different statistics of server and services of SQLAnywhere.

3) The XML file is HQ Plugin XML.

Usage:

To use this plugin
1) Start the database server (SQLAnywhere 10 (dbsrv10 or dbeng10)).

2) After that follow plugin deployment steps provided by Hyperic.

ServerDetector

package org.hyperic.hq.plugin.iAnyWhere;

import java.io.File;
import java.util.ArrayList;
import java.util.List;
import java.sql.*;
import org.hyperic.hq.product.AutoServerDetector;
import org.hyperic.hq.product.PluginException;
import org.hyperic.hq.product.ServerDetector;
import org.hyperic.hq.product.ServerResource;
import org.hyperic.hq.product.ServiceResource;
import org.hyperic.util.config.ConfigResponse;
import org.hyperic.sigar.win32.RegistryKey;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hyperic.util.jdbc.DBUtil;

public class iAnyWhereServerDetector
    extends ServerDetector
    implements AutoServerDetector
{
    private Log log;
    private static final String SERVER_NAME = "iAnyWhere";
    private static final String PROCESS_NAME = "dbeng9";
  	private static final String PTQL_QUERY = "State.Name.eq=dbeng9";

  	public iAnyWhereServerDetector()
    {
        log = LogFactory.getLog("iAnyWhereServerDetector");
    }
   private static List getServerProcessList()
    {
        List servers = new ArrayList();
        long[] pids = getPids(PTQL_QUERY);
        for (int i=0; i<pids.length; i++)
        {
            String exe = getProcExe(pids[i]);
            if (exe == null)
                continue;
            File binary = new File(exe);
            if (!binary.isAbsolute())
                continue;
	     servers.add(binary.getAbsolutePath());
        }
      return servers;
    }
   	public List getServerResources(ConfigResponse platformConfig)
        throws PluginException
    {
        List servers = new ArrayList();
        List paths = getServerProcessList();
        for (int i=0; i<paths.size(); i++)
        {
            String dir = (String)paths.get(i);
            List found = getServerList(dir);
            if (!found.isEmpty())
                servers.addAll(found);
        }
	  log.info("Server Resources :"+servers);
      return servers;
    }


    public List getServerResources(ConfigResponse platformConfig, String path) throws PluginException
    {
          return getServerList(path);
    }

    public List getServerResources(ConfigResponse platformConfig, String path, RegistryKey current) throws PluginException
    {
	        return getServerList(path);
    }
   	public List getServerList(String path) throws PluginException
    {
        ConfigResponse productConfig = new ConfigResponse();
        productConfig.setValue("jdbcUrl","jdbc:sybase:Tds:localhost:2638");
        productConfig.setValue("jdbcUser", "DBA");
        productConfig.setValue("jdbcPassword", "sql");

        List servers = new ArrayList();
        String version = "";
    	   if (path.indexOf(PROCESS_NAME) == -1)
	  {  
             return servers;
	  }

       if (path.indexOf("10") != -1)
	          version = "10.0";
	      else if (path.indexOf("9") != -1)
	          version = "9.0";
	     else
	    {
            return servers;
	   }
       String installdir = getParentDir(path,3);
       ServerResource server = createServerResource(installdir);
       ConfigResponse cprop = new ConfigResponse();
       cprop.setValue("version", version);
       server.setCustomProperties(cprop);
       server.setProductConfig(productConfig);
       server.setMeasurementConfig();
       server.setName(SERVER_NAME+" "+version);
       servers.add(server);
	  log.info("servers :"+servers);
	  log.info("server name:"+SERVER_NAME);
       return servers;
    }
  protected List discoverServices(ConfigResponse config) throws PluginException
   {
        ArrayList services;
        String url = config.getValue("jdbcUrl");
        String user = config.getValue("jdbcUser");
        String pass = config.getValue("jdbcPassword");
        try
        {
            Class.forName("com.sybase.jdbc3.jdbc.SybDriver");
            log.info("Driver detected");
        }
        catch(ClassNotFoundException e)
        {
        	 // No driver.  Should not happen.
         String msg = "Unable to load JDBC "+"Driver: "+e.getMessage();  
        	throw new PluginException("Unable to load JDBC Driver: " + e.getMessage());
        }
        services = new ArrayList();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
         try
        {
	         conn = DriverManager.getConnection(url, user, pass);
           // Discover all SQL AnyWhere Tables
            stmt = conn.createStatement();
            ServiceResource service;
            rs = stmt.executeQuery("SELECT * FROM sys.SYSTABLE");
            while(rs != null && rs.next())
            {
                String instance = rs.getString("table_name");
                service = new ServiceResource();
		       service.setType(this, "SQL AnyWhere Instance");
                service.setServiceName(instance);
		       ConfigResponse productConfig = new ConfigResponse();
                productConfig.setValue("instance", instance);
                service.setProductConfig(productConfig);
                service.setMeasurementConfig();
                service.setControlConfig();
                services.add(service);
            }
        }
        catch(SQLException e)
        {
            throw new PluginException("Error querying for services: " + e.getMessage());
        }
        finally
        {
            DBUtil.closeJDBCObjects(log, conn, stmt, rs);
	    }
          return services;
   }
}

Measurement Plugin

package org.hyperic.hq.plugin.iAnyWhere;

import java.sql.*;
import java.util.HashMap;
import java.util.*;
import org.hyperic.util.jdbc.DBUtil;
import org.hyperic.hq.product.*;
import org.hyperic.util.StringUtil;
import org.hyperic.util.config.*;
import org.apache.commons.logging.Log;

public class iAnyWhereMeasurementPlugin extends JDBCMeasurementPlugin
{
	private Log log;
    public iAnyWhereMeasurementPlugin()
    {
        setName("iAnyWhereMeasurementPlugin ");
    }

    protected void getDriver()
        throws ClassNotFoundException
    {
        Class.forName(JDBC_DRIVER);
    }

    protected Connection getConnection(String url, String user, String password)
        throws SQLException
    {
        return DriverManager.getConnection(url, user, password);
    }

    protected String getDefaultURL()
    {
        return DEFAULT_URL;
    }
	protected void initQueries()
    {
        if(uniqueQueries != null)
        {
        	    return;
        } 
	else
        {
		  iAny12Queries = new HashMap();
		  uniqueQueries = new HashMap();
       
           uniqueQueries.put("NumUserTables", "SELECT count(*) FROM SYSOBJECTS WHERE type='U'");
           uniqueQueries.put("NumIndexes","SELECT count(*) FROM sys.SYSINDEX");
           uniqueQueries.put("NumServers", "SELECT count(*) FROM sys.SYSSERVERS");
           // Number of active users
           uniqueQueries.put("ActiveUsers", "SELECT count(*) FROM sysusers WHERE suid > 0");
           //Indices
           uniqueQueries.put("NumIndexes","SELECT count(*) from sysindex where not index_id=0");
           uniqueQueries.put("NumLobIndexes","SELECT count(*) from sysindex where not index_id=255");
            uniqueQueries.put("NumLargeRowSize", "SELECT Max(index_id) from sys.sysindex");
            uniqueQueries.put("NumTxLogs", "SELECT count(*) FROM syslogins");
           uniqueQueries.put("LargestUpdateCountOfAnyLog", "SELECT MAX(suid) FROM syslogs");
           //alias for avail.
           //if we can fetch any metric, consider the server available
           //XXX this check can be more robust
           uniqueQueries.put("Availability",  uniqueQueries.get("NumServers"));
           return;
        }
    }

    public ConfigSchema getConfigSchema(TypeInfo info, ConfigResponse config)
    {
        if(info.getType() == 3)
        	{
	            SchemaBuilder builder = new SchemaBuilder(config);
        	        builder.add(PROP_INSTANCE, "Database instance to monitor", "");
	            return builder.getSchema();
        	} 
		else
        	{
	            return super.getConfigSchema(info, config);
        	}
    }

    protected String getQuery(Metric metric)
    {

           String queryVal = metric.getAttributeName();
		  String query = (String)uniqueQueries.get(queryVal);
	       if(query == null)
       	      query = (String)iAny12Queries.get(queryVal);
	   	      String instance = metric.getObjectProperties().getProperty(PROP_INSTANCE);
	       if(instance == null)
        		    instance = metric.getProperties().getProperty(PROP_INSTANCE);
  	       
	        query = StringUtil.replace(query, "%instance%", instance);
	    	    return query;
    }
   
    public MetricValue getValue(Metric metric)    throws PluginException,
           MetricUnreachableException,
           MetricInvalidException,
           MetricNotFoundException
{
    		initQueries();
    		String objectName = metric.getObjectName(),
    		alias  =  metric.getAttributeName();
    if ( !alias.equalsIgnoreCase(AVAIL_ATTR))
            return super.getValue(metric);
     try
    {
        Connection conn = getCachedConnection(metric);
        if (alias.equalsIgnoreCase("Availability"))
            return getAvailability(metric, alias, conn);
    }
    catch (SQLException e) {
        String msg = "Query failed for "+alias+": "+e.getMessage();
        throw new MetricNotFoundException(msg, e);
    }
	return null;
}
private MetricValue getAvailability(Metric metric, String attr,Connection conn)
    throws MetricUnreachableException
{
    Statement stmt = null;
    ResultSet rs = null;
     try
     {
        stmt = conn.createStatement();
        String sql = (String)uniqueQueries.get("NumServers");
        rs = stmt.executeQuery(sql);
        return new MetricValue(Metric.AVAIL_UP, System.currentTimeMillis());
    }
    catch (SQLException e) {
        String msg = "Query failed for Availability "+e.getMessage();
        throw new MetricUnreachableException(msg, e);
    }
    finally {
        DBUtil.closeJDBCObjects(log, null, stmt, rs);
    }
}
protected Connection getCachedConnection(Metric metric)
throws SQLException
{
Properties props = metric.getProperties();
String url  = props.getProperty(PROP_URL),
       user = props.getProperty(PROP_USER),
       pass = props.getProperty(PROP_PASSWORD);
return getCachedConnection(url, user, pass);
}

protected Connection getCachedConnection(String url, String user,
                                     String pass)
throws SQLException
{
String cacheKey = url + user + pass;
Connection conn = (Connection)connectionCache.get(cacheKey);

if (conn == null) {
    conn = getConnection(url, user, pass);
    
    synchronized (connectionCache) {
        connectionCache.put(cacheKey, conn);
    }
}
return conn;
}
	private static final String JDBC_DRIVER = "com.sybase.jdbc3.jdbc.SybDriver";
	private static final String DEFAULT_URL = "jdbc:sybase:Tds:localhost:2638";
	private static final String PROP_INSTANCE = "instance";
        private static HashMap iAny12Queries = null;
        private static HashMap uniqueQueries = null;
	private static HashMap connectionCache = new HashMap();
}

HQ Plugin

<?xml version="1.0"?>
<plugin name="iAnyWhere" package="org.hyperic.hq.plugin.iAnyWhere">
  <classpath>
    <include name="/pdk/lib/jdbc/jconn3.jar"/>
  </classpath>

  <!-- we use a dummy jmx object name -->
  <filter name="domain" value="iAnyWhere"/>
<property name="DEFAULT_URL"
            value="jdbc:sybase:Tds:localhost:2638"/>
 <!-- appended to each template by MeasurementInfoXML -->
  <property name="template-config"
            value="jdbcUrl=%jdbcUrl%,jdbcUser=%jdbcUser%,jdbcPassword=%jdbcPassword%"/>
<filter name="template"
          value="${domain}:Type=Server:${alias}"/>
  <metrics name="SQL AnyWhere 9.x">
    <metric name="Availability"
            alias="Availability"
            category="AVAILABILITY"
            indicator="true"
            units="percentage"
            collectionType="dynamic"/>
      </metrics>
  <filter name="template"
          value="${domain}:Type=Service,instance=%instance%:${alias}"/>
  <metrics name="SQL AnyWhere 9.x Instance">
    
      <metric name="Availability"
            alias="Availability"
            category="AVAILABILITY"
            indicator="true"
            units="percentage"
            collectionType="dynamic"/>

        <metric name="Number of User Tables"
            alias="NumUserTables"
            category="UTILIZATION"
            indicator="true"
            units="none"
            collectionType="trendsup"/>

       <metric name="Number of Servers"
            alias="NumServers"
            category="UTILIZATION"
             indicator="true"
            units="none"
            collectionType="dynamic"/>

    <metric name="Number of Active Users"
            alias="ActiveUsers"
            category="UTILIZATION"
            indicator="true"
            units="none"
            collectionType="dynamic"/>
            
    <metric name="Largest Update Count Of Any Log"
            alias="LargestUpdateCountOfAnyLog"
            category="UTILIZATION"
            units="none"
            collectionType="dynamic"/>
            
    <metric name="Number of Large Row Size"
            alias="NumLargeRowSize"
            category="UTILIZATION"
            indicator="true"
            units="none"
            collectionType="dynamic"/>
            
    <metric name="Number of Least bound Indexes"
            alias="NumLobIndexes"
            category="UTILIZATION"
            units="none"
            collectionType="dynamic"/>

     <metric name="Number of Indexes"
            alias="NumIndexes"
            category="UTILIZATION"
            units="none"
            indicator="true"
            collectionType="dynamic"/>

    <metric name="Number of Tx Logs"
            alias="NumTxLogs"
            category="UTILIZATION"
            units="none"
            collectionType="dynamic"/>
            
 </metrics>   
 <server name="iAnyWhere" 
          version="9.0.x" description=" SQL AnyWhere">
    <plugin type="measurement"
            class="iAnyWhereMeasurementPlugin"/>
    <plugin type="autoinventory"
            class="iAnyWhereServerDetector"/>
       <metrics include="SQL AnyWhere 9.x"/>
    <config>
      <option name="jdbcUrl" 
              description="JDBC Connection URL" default="jdbc:sybase:Tds:localhost:2638"/>
      <option name="jdbcUser" 
		  description="JDBC User" default="DBA"/>
      <option name="jdbcPassword" type="secret" optional="true" description="JDBC Password"/>
    </config>
    <properties>
       <property name="version" description="SQL AnyWhere Version"/>
    </properties>

    <!-- Define user service -->
   <service name="SQL AnyWhere Instance" description="SQL AnyWhere Service">
   <plugin type="measurement"
              class="iAnyWhereMeasurementPlugin"/>
    <config>
        <option name="instance" description="SQL AnyWhere Instance"/>
    </config>
      <metrics include="SQL AnyWhere 9.x Instance"/>
    </service>
     <service name="DataServer Process Metrics">
      <config>
        <option name="process.query"
                default="State.Name.eq=dbeng9"
                description="PTQL for iAnyWhere DataServer Process"/>
      </config>
       </service>
  </server>
  <server name="iAnyWhere"
          version="10.x">
          include="9.0.x"/>
  </server>
 <!-- ==================== Plugin Help =========================== -->
  <help name="SQLAnyWhere">
    <![CDATA[
    <p>
    <h3>Configure SQLAnyWhere ${product.version} for Monitoring</h3>
    </p>
    <p>
      Please run the HQ agent on the local SQLAnyWhere server as the iAnyWhere user.
      This is neccessary to get the proper ENV vars setup in order to execute
      the sp_sysmon procedure.
    </p>
    ]]>
  </help>
</plugin>

iAnywhere Plugin

Browse Space

Explore Confluence

Your Account

Other Features

Add Content