One of the more interesting ways to expand the functionality with STM is to leverage the Java Extension framework. In this example, it will be demonstrated how to provide a generic database query interface via HTTP, through the use of the JDBC interface. JDBC, which stands for Java Database Connector, is a standard API that allows Java applications to access databases in a generic way. In this example, we will leverage the mysql.jar JDBC connector, but any database should operate the same way. The first step is to adjust the java classpath that will be used by the system. In the case of the MySQL jar, we would add the following to the system global settings: java!classpath: /opt/zeus/zxtm/conf/jars/mysql.jar This will insure that the jdbc jar will be available to our Java code. Second, upload the jdbc jar file through the Java Extensions catalog, along with the attached class files (The Java source files are attached as well), while allowing a policy to be automatically created for the webserver class file. From there, configure the webserver policy with the following parameters and modify the values as appropriate: connectstring The connect string necessary to register the jdbc driver and connect, example: jdbc:mysql://192.168.122.21:3306 username The username to connect with password The password of the user connecting The Connect string can (and likely should) point to the IP address and port for a virtual server pointing to your databases, to allow for failover to be configured, in the event of a problem. This is documented for MySQL in the document <insert here>. At this point, create a virtual server, pointing to the pool "discard", as no back-end connectivity will be used by the policy. If everything is setup properly, and a valid query is performed, it should be now possible to perform a query against the virtual server such as: http://192.168.122.21:81/website?select%20*%20from%20referers; The path value (here the value "website") corresponds to the "database" that should be used, and the value after the ? is the SQL query that is to be performed. As coded, this example will not allow modifications to be made to the database, so no insert or update will be allowed. Here is an example output (in JSON format): [ { "count": 9, "data": "/" }, { "count": 3, "data": "/static/test.html" }, { "count": 4, "data": "/icons/apache_pb.gif" }, { "count": 4, "data": "/icons/poweredby.png" }, { "count": 4, "data": "/favicon.ico" } ] ... View more

Often, customers wish to do load balancing of replicating databases, and wish to effectively monitor them.  The attached Java extension leverages JDBC drivers to connect to the database server, then the monitor issues a query of "select 1" to the database server by default.  The monitor accepts the following parameters:  user          The user to connect as (required) password      The user's password (required) type          The type of database supported options: "mysql", "mssql", "oracle", or "postgress" (required) database      The name of the database configured i.e. for a "use <database>" command (required) instance      For mssql, the name of the instance to connect to (optional) service       For Oracle, the name of the service (required) (note 1) query         The query to use.  Use "~" in place of spaces. (optional--defaults to "select 1") (note 2)   Note 1: The service parameter is required for monitoring Oracle databases Note 2: A  valid query parameter is required for monitoring Oracle databases Upload the db-monitor.sh and ConnectDBServer.class files to the monitors section in the extra files catalog, and create an appropriate monitor with the options above.  The db-monitor.sh is a Bash shells script that can be modified to provide additional support for other database types if needed.   ... View more