Java tutorial
/* * NOTE: This copyright does *not* cover user programs that use HQ * program services by normal system calls through the application * program interfaces provided as part of the Hyperic Plug-in Development * Kit or the Hyperic Client Development Kit - this is merely considered * normal use of the program, and does *not* fall under the heading of * "derived work". * * Copyright (C) [2004-2008], Hyperic, Inc. * This file is part of HQ. * * HQ is free software; you can redistribute it and/or modify * it under the terms version 2 of the GNU General Public License as * published by the Free Software Foundation. This program is distributed * in the hope that it will be useful, but WITHOUT ANY WARRANTY; without * even the implied warranty of MERCHANTABILITY or FITNESS FOR A * PARTICULAR PURPOSE. See the GNU General Public License for more * details. * * You should have received a copy of the GNU General Public License * along with this program; if not, write to the Free Software * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 * USA. */ package org.hyperic.hq.plugin.sybase; import java.util.HashMap; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.ResultSetMetaData; import java.util.Properties; import java.util.regex.Matcher; import java.util.regex.Pattern; import org.apache.commons.logging.Log; import org.hyperic.hq.product.JDBCMeasurementPlugin; import org.hyperic.hq.product.Metric; import org.hyperic.hq.product.PluginManager; import org.hyperic.util.StringUtil; import org.hyperic.util.jdbc.DBUtil; import org.hyperic.hq.product.MetricUnreachableException; import org.hyperic.hq.product.MetricInvalidException; import org.hyperic.hq.product.MetricNotFoundException; import org.hyperic.hq.product.MetricValue; import org.hyperic.hq.product.PluginException; import org.hyperic.hq.product.PluginInfo; public class SybaseMeasurementPlugin extends JDBCMeasurementPlugin { private final Log log = getLog(); private static final String JDBC_DRIVER = "com.sybase.jdbc3.jdbc.SybDriver"; private static final String DEFAULT_URL = SybasePluginUtil.DEFAULT_URL; private static final String PROP_INSTANCE = "instance", TYPE_SP_MONITOR_CONFIG = SybasePluginUtil.TYPE_SP_MONITOR_CONFIG, TYPE_STORAGE = SybasePluginUtil.TYPE_STORAGE, PROP_DATABASE = SybasePluginUtil.PROP_DATABASE, PROP_SEGMENT = SybasePluginUtil.PROP_SEGMENT, PROP_PAGESIZE = SybasePluginUtil.PROP_PAGESIZE, PROP_CONFIG_OPTION = SybasePluginUtil.PROP_CONFIG_OPTION, PERCENT_ACTIVE = SybasePluginUtil.PERCENT_ACTIVE, NUM_ACTIVE = SybasePluginUtil.NUM_ACTIVE, MAX_USED = SybasePluginUtil.MAX_USED, NUM_FREE = SybasePluginUtil.NUM_FREE, NUM_REUSED = SybasePluginUtil.NUM_REUSED; private static HashMap syb12Queries = null; // Sybase 12.5 only private static HashMap genericQueries = null; // Any private static HashMap connectionCache = new HashMap(); protected void getDriver() throws ClassNotFoundException { Class.forName(JDBC_DRIVER); } protected Connection getConnection(String url, String user, String password) throws SQLException { String pass = (password == null) ? "" : password; pass = (pass.matches("^\\s*$")) ? "" : pass; java.util.Properties props = new java.util.Properties(); props.put("CHARSET_CONVERTER_CLASS", "com.sybase.jdbc3.utils.TruncationConverter"); props.put("user", user); props.put("password", pass); return DriverManager.getConnection(url, props); } protected String getDefaultURL() { return DEFAULT_URL; } protected void initQueries() { if (genericQueries != null) return; syb12Queries = new HashMap(); genericQueries = new HashMap(); String baseQuery = "SELECT "; String baseTxQuery = "SELECT COUNT(*) FROM systransactions "; String baseIndQuery = "SELECT COUNT(*) FROM sysindexes "; genericQueries.put("NumUserTables", baseQuery + "COUNT(*) FROM sysobjects WHERE type='U'"); genericQueries.put("NumServers", baseQuery + "COUNT(*) FROM sysservers"); // Transactions genericQueries.put("NumTx", baseTxQuery); // Transactions by type genericQueries.put("NumLocalTransactions", baseTxQuery + "WHERE type=1"); genericQueries.put("NumExternalTransactions", baseTxQuery + "WHERE type=3"); genericQueries.put("NumRemoteTransactions", baseTxQuery + "WHERE type=98"); genericQueries.put("NumDtxTransactions", baseTxQuery + "WHERE type=99"); // Uptime genericQueries.put("UpTime", "SELECT MAX(datediff(ss, " + "loggedindatetime, getdate()) * 1000) " + "FROM sysprocesses"); // Instance total, used and free space genericQueries.put("InstanceUsedSpace", "select sum(u.size) * 1024 " + "from sysusages u, sysdevices d " + "where u.vstart between d.low and d.high " + "and UPPER(d.name) = UPPER('%instance%')"); genericQueries.put("InstanceFreeSpace", "select ((d.high - d.low) + 1 - sum(u.size)) " + "* 1024 " + "from sysusages u, sysdevices d " + "where u.vstart between d.low and d.high " + "and UPPER(d.name) = UPPER('%instance%')"); genericQueries.put("InstanceTotalSpace", "select ((d.high - d.low) + 1) * 1024 " + "from sysusages u, sysdevices d " + "where u.vstart between d.low and d.high " + "and UPPER(d.name) = UPPER('%instance%')"); // Page locks (table and page) genericQueries.put("NumActiveLocks", baseQuery + "COUNT(*) FROM syslocks"); genericQueries.put("NumActivePageLocks", baseQuery + "COUNT(*) FROM syslocks WHERE type = 1 OR " + "type = 2"); genericQueries.put("NumActiveTableLocks", baseQuery + "COUNT(*) FROM syslocks WHERE type = 4 OR " + "type = 5 OR type = 6"); // Number of active users genericQueries.put("ActiveUsers", baseQuery + "COUNT(*) FROM sysprocesses WHERE suid > 0"); // Transaction by connection type genericQueries.put("NumAttachedTransactions", baseTxQuery + "WHERE connection=1"); genericQueries.put("NumDetachedTransactions", baseTxQuery + "WHERE connection=2"); // Transaction by state genericQueries.put("NumTxInBegun", baseTxQuery + "WHERE state=1"); genericQueries.put("NumTxInDoneCmd", baseTxQuery + "WHERE state=2"); genericQueries.put("NumTxInDone", baseTxQuery + "WHERE state=3"); genericQueries.put("NumTxInPrepared", baseTxQuery + "WHERE state=4"); genericQueries.put("NumTxInInCmd", baseTxQuery + "WHERE state=5"); genericQueries.put("NumTxInInAbortCmd", baseTxQuery + "WHERE state=6"); genericQueries.put("NumTxInCommitted", baseTxQuery + "WHERE state=7"); genericQueries.put("NumTxInInPostCommit", baseTxQuery + "WHERE state=8"); genericQueries.put("NumTxInInAbortTran", baseTxQuery + "WHERE state=9"); genericQueries.put("NumTxInInAbortSavept", baseTxQuery + "WHERE state=10"); genericQueries.put("NumTxInBegunDetached", baseTxQuery + "WHERE state=65537"); genericQueries.put("NumTxInDoneCmdDetached", baseTxQuery + "WHERE state=65538"); genericQueries.put("NumTxInDoneDetached", baseTxQuery + "WHERE state=65539"); genericQueries.put("NumTxInPrepareDetached", baseTxQuery + "WHERE state=65540"); genericQueries.put("NumTxInHeurCommitted", baseTxQuery + "WHERE state=65548"); genericQueries.put("NumTxInHeurRolledBack", baseTxQuery + "WHERE state=65549"); // Indices genericQueries.put("NumIndexes", baseIndQuery + "WHERE NOT indid=0"); genericQueries.put("NumLobIndexes", baseIndQuery + "WHERE indid = 255"); genericQueries.put("NumLargeRowSize", baseQuery + "MAX(exp_rowsize) FROM sysindexes"); // Transaction log genericQueries.put("NumTxLogs", baseQuery + "COUNT(*) FROM syslogs"); genericQueries.put("LargestUpdateCountOfAnyLog", baseQuery + "MAX(op) FROM syslogs"); //alias for avail. //if we can fetch any metric, consider the server available //XXX this check can be more robust genericQueries.put("Availability", genericQueries.get("NumServers")); } protected String getQuery(Metric metric) { String queryVal = metric.getAttributeName(); String query = (String) genericQueries.get(queryVal); if (query == null) { // Not in the generic queries, check the version specific table // XXX: grab the version from the Metric query = (String) syb12Queries.get(queryVal); } // Do substituion on the user name in the SQL query String instance = metric.getObjectProperties().getProperty(PROP_INSTANCE); if (instance == null) { // Backwards compat instance = metric.getProperties().getProperty(PROP_INSTANCE); } query = StringUtil.replace(query, "%instance%", instance); return query; } public MetricValue getValue(Metric metric) throws PluginException, MetricUnreachableException, MetricInvalidException, MetricNotFoundException { if (log.isDebugEnabled()) log.debug("[getValue] metric=" + metric); initQueries(); String objectName = metric.getObjectName(), alias = metric.getAttributeName(); if (objectName.indexOf(TYPE_SP_MONITOR_CONFIG) == -1 && objectName.indexOf(TYPE_STORAGE) == -1 && !metric.isAvail()) { return super.getValue(metric); } Properties props = metric.getProperties(); String url = props.getProperty(PROP_URL); String user = props.getProperty(PROP_USER); String pass = props.getProperty(PROP_PASSWORD); if (url == null) { throw new MetricUnreachableException("URL = null"); } MetricValue res = null; Connection conn = null; try { // do not close cached connection conn = getCachedConnection(url, user, pass); } catch (SQLException e) { removeCachedConnection(url, user, pass); DBUtil.closeConnection("[getValue]", conn); String msg = "Commection failed for '" + alias + "': " + e.getMessage(); throw new MetricNotFoundException(msg, e); } try { if (objectName.indexOf(TYPE_SP_MONITOR_CONFIG) != -1) { res = getSP_MonitorConfigValue(metric, alias, conn); } else if (objectName.indexOf(TYPE_STORAGE) != -1) { res = getStorageValue(metric, alias, conn); } else if (metric.isAvail()) { res = getAvailability(conn); } } catch (SQLException e) { removeCachedConnection(url, user, pass); DBUtil.closeConnection(null, conn); String msg = "Query failed for '" + alias + "': " + e.getMessage(); if (metric.isAvail()) { res = new MetricValue(Metric.AVAIL_DOWN); } else { throw new MetricNotFoundException(msg, e); } } if (res == null) { throw new MetricNotFoundException("cannot find metric " + metric); } if (log.isDebugEnabled()) { log.debug("[getValue] alias='" + alias + "' res='" + res + "' metric=" + metric); } return res; } private MetricValue getAvailability(Connection conn) { Statement stmt = null; ResultSet rs = null; double res = Metric.AVAIL_DOWN; try { stmt = conn.createStatement(); String sql = (String) genericQueries.get("NumServers"); rs = stmt.executeQuery(sql); res = Metric.AVAIL_UP; } catch (SQLException e) { log.debug("Query failed for Availability " + e.getMessage(), e); } finally { DBUtil.closeJDBCObjects(log, null, stmt, rs); } return new MetricValue(res, System.currentTimeMillis()); } private MetricValue getStorageValue(Metric metric, String attr, Connection conn) throws SQLException, MetricNotFoundException { String database = metric.getObjectProperty(PROP_DATABASE), segment = metric.getObjectProperty(PROP_SEGMENT); int pagesize = Integer.parseInt(metric.getObjectProperty(PROP_PAGESIZE)); Statement stmt = null; ResultSet rs = null; MetricValue res = null; try { stmt = conn.createStatement(); stmt.execute("use " + database); stmt.execute("sp_helpsegment '" + segment + "'"); rs = getResultSet(stmt, "total_pages"); if (rs.next()) { long total_pages = rs.getLong("total_pages"), free_pages = rs.getLong("free_pages"), used_pages = rs.getLong("used_pages"); if (attr.equals("PercentUsed")) { float percent_used = (getSegmentSize(used_pages, pagesize) / getSegmentSize(total_pages, pagesize)); res = new MetricValue(percent_used, System.currentTimeMillis()); } else if (attr.equals("StorageUsed")) { float storage_used = getSegmentSize(used_pages, pagesize); res = new MetricValue(storage_used, System.currentTimeMillis()); } else if (metric.isAvail()) { res = new MetricValue(Metric.AVAIL_UP, System.currentTimeMillis()); } else { throw new MetricNotFoundException("[getStorageValue] Metric => '" + attr + "'"); } } else { res = new MetricValue(Metric.AVAIL_DOWN, System.currentTimeMillis()); } } catch (SQLException e) { if (metric.isAvail()) { res = new MetricValue(Metric.AVAIL_DOWN, System.currentTimeMillis()); } else { throw e; } } finally { if (stmt != null) stmt.execute("use master"); // XXX why? DBUtil.closeJDBCObjects(log, null, stmt, rs); } return res; } private ResultSet getResultSet(Statement stmt, String col) throws SQLException { do { ResultSet rs = null; try { rs = stmt.getResultSet(); if (rs == null) break; rs.findColumn(col); return rs; } catch (SQLException e) { //don't close the resultset!!! } } while (stmt.getMoreResults() == true && stmt.getUpdateCount() != -1); throw new SQLException(); } private void printMetaCols(ResultSetMetaData md) throws SQLException { for (int i = 1; i <= md.getColumnCount(); i++) { System.out.println(md.getColumnName(i)); } } private float getSegmentSize(long pages, int pagesize) { return pages / 1024 * pagesize / 1024; } private MetricValue getSP_MonitorConfigValue(Metric metric, String alias, Connection conn) throws SQLException, MetricNotFoundException, MetricUnreachableException { String configOpt = metric.getObjectProperty(PROP_CONFIG_OPTION); float value = -1; if (alias.equalsIgnoreCase(MAX_USED)) value = getMaxUsed(conn, configOpt); else if (alias.equalsIgnoreCase(NUM_REUSED)) value = getNumReuse(conn, configOpt); else if (alias.equalsIgnoreCase(NUM_FREE)) value = getNumFree(conn, configOpt); else if (alias.equalsIgnoreCase(NUM_ACTIVE)) value = getNumActive(conn, configOpt); else if (alias.equalsIgnoreCase(PERCENT_ACTIVE)) value = getPercentActive(conn, configOpt); else if (metric.isAvail()) value = (float) getAvail(conn, configOpt); else throw new MetricNotFoundException(alias); return new MetricValue(value, System.currentTimeMillis()); } private float getNumActive(Connection conn, String configOpt) throws MetricUnreachableException, MetricNotFoundException { return getMonitorConfigValue(conn, configOpt, "Num_active"); } private double getAvail(Connection conn, String configOpt) { double res = Metric.AVAIL_DOWN; Statement stmt = null; ResultSet rs = null; try { stmt = conn.createStatement(); rs = stmt.executeQuery("sp_monitorconfig '" + configOpt + "'"); if (rs.next()) { res = Metric.AVAIL_UP; } } catch (SQLException e) { log.debug("[getAvail] configOpt='" + configOpt + "' -> " + e.getMessage()); } finally { DBUtil.closeJDBCObjects(log, null, stmt, rs); } return res; } private float getNumFree(Connection conn, String configOpt) throws MetricUnreachableException, MetricNotFoundException { return getMonitorConfigValue(conn, configOpt, "Num_free"); } private float getNumReuse(Connection conn, String configOpt) throws SQLException { Statement stmt = null; ResultSet rs = null; try { stmt = conn.createStatement(); rs = stmt.executeQuery("sp_monitorconfig '" + configOpt + "'"); ResultSetMetaData rsmd = rs.getMetaData(); // I have seen Num_Reuse, Reuse and Reuse_cnt so far for this String reuseCol = "Num_Reuse"; for (int i = 1; i <= rsmd.getColumnCount(); i++) { String name = rsmd.getColumnName(i); if (name.indexOf("Reuse") != -1) { reuseCol = name; } } int col = rs.findColumn(reuseCol); if (rs.next()) { return rs.getFloat(col); } } finally { DBUtil.closeJDBCObjects(log, null, stmt, rs); } throw new SQLException(); } private float getMaxUsed(Connection conn, String configOpt) throws MetricUnreachableException, MetricNotFoundException { return getMonitorConfigValue(conn, configOpt, "Max_Used"); } private float getPercentActive(Connection conn, String configOpt) throws MetricUnreachableException, MetricNotFoundException { return getMonitorConfigValue(conn, configOpt, "Pct_act"); } private float getMonitorConfigValue(Connection conn, String configOpt, String prop) throws MetricUnreachableException, MetricNotFoundException { Statement stmt = null; ResultSet rs = null; try { stmt = conn.createStatement(); rs = stmt.executeQuery("sp_monitorconfig '" + configOpt + "'"); if (rs.next()) { return rs.getFloat(prop); } else { throw new MetricNotFoundException(prop); } } catch (SQLException e) { throw new MetricUnreachableException(e.getMessage(), e); } finally { DBUtil.closeJDBCObjects(log, null, stmt, rs); } } }