SiteScope User's Guide

Database Monitor

The SiteScope Database Monitor checks that a database is working correctly by connecting to it and performing a query. Optionally, it can check the results of a database query for expected content.

Each time the Database Monitor runs, it returns a status, the time it takes to perform the query, the number of rows in the query result, and the first two fields in the first row of the result and writes them in the monitoring log file.

This section describes:

• Usage guidelines
  • Setup Requirements
• Completing the Database Monitor form
• Accessing Oracle Databases Without Using ODBC
• Monitoring Informix databases
• Monitoring mySQL databases
• Monitoring Sybase databases

Usage Guidelines

If your database application is not working properly, the user may not be able to access web content and forms that depend on the database. Most importantly, the user won't be able to complete e-commerce transactions that are supported by databases. The other reason to monitor database queries is so you can find performance bottlenecks. If the database interaction time and the associated user URL retrieval times are both increasing at about the same amount, the database is probably the bottleneck. If not, the bottleneck is probably somewhere else in the network.

Usually the most important thing to monitor in databases are the queries used by your most frequently used and most important web applications. If more than one database is used, you will want to monitor each of the databases.

You may also choose to monitor internal database statistics. The statistics provided by each database are different but may include items such as database free space, transaction log free space, transactions/second, and average transaction duration.

You may want to monitor your most critical and most common queries frequently, every 2-5 minutes. Database statistics that change less frequently can be monitored every 30 or 60 minutes.

Setup Requirements

The steps for setting up a Database Monitor will vary according to what database software you are trying to monitor. The following is an overview of the requirements for using the Database Monitor:

• You must install or copy a compatible database driver or database access API into the appropriate SiteScope directory location. Many database driver packages are available as compressed (zipped) archive files or .jar files. Database drivers in this form must NOT be extracted and must be installed into the ~SiteScope/java/lib/ext subdirectory. If you extract drivers from their compressed files (not recommended), the individual driver files must be placed in the ~SiteScope/classes subdirectory instead of the java directory.
• You need to know the syntax for accessing the database driver. Examples of database driver path strings are:
  • sun.jdbc.odbc.JdbcOdbcDriver - (JDBC-ODBC Bridge Driver from Sun Microsystems)
  • com.inet.tds.TdsDriver - (TDS driver from i-net Software for MS SQL databases)
  • oracle.jdbc.driver.OracleDriver - (JDBC thin driver for Oracle 7 and 8 databases)
• You need to know the syntax for the Database Connection URL. The Database Connection URL normally includes the class of driver you are using, some key name relating to the supplier of the driver software, followed by a combination of server, host, and port identifiers. Examples of database connection URL's are:
  • jdbc:odbc:dsname - (where dsname is the data source name in the system environment or configuration)
  • jdbc:inetdae:hostname:port - (where hostname is the name of the host where the database is running and port is the port on which the database interfaces with the driver)
  • jdbc:oracle:thin:@hostname:port:dbname - (where hostname is the name of the host where the database is running, port is the port on which the database interfaces with the driver, and dbname is the name of the Oracle database instance)
• The database you want to monitor needs to be running, have a database name defined, and have at least one named table created in the database. In some cases, the database management software needs to be configured to allow connections via the middleware or database driver.
• You need a valid username and password to access and perform a query on the database. In some cases, the machine and user account that SiteScope is running on must be given permissions to access the database.
• You need to know a valid SQL query string for the database instance and database table(s) in the database you want to monitor. Consult your database administrator to work out appropriate queries to test.

Completing the Database Monitor Form

To display the Database Monitor Form, either click the Edit link for an existing Database Monitor in a monitor table, or click the Add a new Monitor to this Group link on a group's detail page and choose the Add Database Monitor link.

Complete the items on the Database Monitor form as follows. When the required items are complete, click the Add Monitor button.

Database Connection URL

Enter a URL to a Database Connection. The easiest way to create a database connection is to use ODBC to create a named connection to a database. For example, first use the ODBC control panel to create a connection called test. Then, enter jdbc:odbc:test in this box as the connection URL.

Query

Enter the SQL query to test. For example, select * from sysobjects.

Update every

Enter how frequently the monitor should check the Database server. The drop-down list to the right of the text box lets you specify time increments of seconds, minutes, hours, or days. You must specify a time increment of at least 15 seconds.

Title (Optional)

Enter a name for this monitor. This name appears in the Name text box on the monitor table when you open the group's detail page. If you don't enter a name, a default name will be created.

Database Driver

Enter the java class name of the JDBC database driver. The default, sun.jdbc.odbc.JdbcOdbcDriver, uses ODBC to make Database connections. If a custom driver is used, the driver must also be installed in the SiteScope/java directory.

Advanced Options

The advanced options give you the ability to customize error and warning thresholds. If you choose not to set them, SiteScope will use preset defaults if available. If a default is not available, SiteScope will not be able to utilize the condition.

Disable

Check this box to temporarily disable this monitor and any associated alerts. To enable the monitor again, clear the box.

Match Content

Enter a string of text to check for in the query result. If the text is not contained in the result, the monitor will display no match on content. The search is case sensitive. This works for XML tags as well.

You may also perform a Perl regular expression match by enclosing the string in forward slashes, with an "i" after the trailing slash indicating case-insensitive matching. (for example, /href=Doc\d+\.html/ or /href=doc\d+\.html/i). If you want a particular piece of text to be saved and displayed as part of the status, use parentheses in a Perl regular expression. For example /Temperature: (\d+)/. This would return the temperature as it appears on the page and this could be used when setting an Error if or Warning if threshold.

Database Username

Enter the username used to login to the database. If you are using Microsoft SQL server, you can leave this blank and choose NT Authentication when you setup the ODBC connection. With NT Authentication, SiteScope will connect using the login account of the SiteScope service.

Database Password

Enter a password used to login to the database. If you are using Microsoft SQL server, you can leave this blank and choose NT Authentication when you create the ODBC connection. With NT Authentication, SiteScope will connect using the login account of the SiteScope service.

File Path

The Database Monitor can read a database query from a file. Enter the name of the file that contains the query you want to run. The file should be a simple text format. Use this feature as an alternative to the Query field above for complex queries or queries that change and are updated by an external application.

Connection Timeout

Enter a timeout value, in seconds, that the monitor should wait for a database connection.
NoteThe sum of the Connection Timeout value and Query Timeout value should always be less than the Update every value for the monitor.

Query Timeout

Enter a timeout value, in seconds, that the monitor should wait for a database query to return results.
Note: The sum of the Connection Timeout value and Query Timeout value should always be less than the Update every value for the monitor.

Note: Some commonly used databases and database drivers do not support the query timeout feature. In these cases the Query Timeout value should be set to zero.

Column Labels

Enter the field labels for the two columns returned by the query, separated by a ",". The field labels should be two of the labels that are returned by the Query string entered above. These column labels are used as data labels in SiteScope reports for Database monitors.

Verify Error

Check this box if you want SiteScope to automatically run this monitor again if it detects an error. When an error is detected, the monitor will immediately be scheduled to run again once.

Note: In order to change the run frequency of this monitor when an error is detected, use the Update every (on errors) option below.

Note: The status returned by the Verify Error run of the monitor will replace the status of the originally scheduled run that detected an error. This may cause the loss of important performance data if the data from the verify run is different than the initial error status.

Warning: Use of this option across many monitor instances may result in significant monitoring delays in the case that multiple monitors are rescheduled to verify errors at the same time.

Update Every (on error)

This options allows you to set a new monitoring interval for monitors that have registered an error condition. For example, you may want SiteScope to monitor this item every 10 minutes normally, but as often as every 2 minutes if an error has been detected. Note that this increased scheduling will also affect the number of alerts generated by this monitor.

Schedule (Optional)

By default, SiteScope's monitors are enabled every day of the week. You may, however, schedule your monitors to run only on certain days or on a fixed schedule. Choose the Edit schedule link to create or edit a monitor schedule. For information about creating schedules, read these instructions.

Monitor Description (Optional)

Enter any additional information to describe this monitor. The Monitor Description can include HTML tags such as the <BR> <HR>, and <B> tags to control display format and style. The description will appear on the Monitor Detail page.

Report Description (Optional)

Enter a description for the report for this monitor that will make it easier to understand what this monitor does. The description will appear on Management Reports and on the info list for the monitor.

Depends On (Optional)

To make the running of this monitor dependent on the status of another monitor or monitor group, use the drop-down list to select the monitor or group on which this monitor is dependent. Select None to remove any dependency.

Depends Condition (Optional)

If you choose to make the running of this monitor dependent on the status of another monitor, choose the status condition that the other monitor or monitor group should have in order for the current monitor to run normally. The current monitor will be run normally as long as the monitor or group on which it depends reports the condition selected in this option.

List Order (Optional)

By default, new monitors are listed last on the Monitor Detail page. You may use the drop-down list to choose a different placement for this monitor.

Error if

Set the conditions under which the Database Monitor should report an error status. Use the drop-down list to select a criteria based on the content of the results or a performance value. Next select the logic operator(s) for the error criteria. Then enter the value to be used as the threshold or trigger for this condition.

Warning if

Set the conditions under which the Database Monitor should report a warning status. Use the steps outlined in the "Error if" section above.

Good if

Set the conditions under which the Database Monitor should report a status of "good". Use the steps outlined in the "Error if" section above.

Index

Accessing Oracle Databases Without Using ODBC

If you want to monitor an Oracle database without using ODBC, a good alternative is to use the Oracle Thin JDBC Drivers. To set up SiteScope for to use the JDBC Thin Drivers:

1. Download the Oracle Thin JDBC drivers from
   http://technet.oracle.com/software/download.htm (may require service/support agreement with Oracle)
2. Copy the downloaded driver package into the ~SiteScope/java/lib/ext subdirectory. Note: Do not extract the files from the archive file.
3. Stop and restart the SiteScope service.
4. Now, use your browser to add a Database Monitor within SiteScope.
   • The Database Connection URL format for the Oracle JDBC driver is:

           jdbc:oracle:thin:@<tcp address>:<tcp port>:<database SID>

     For example to connect to the ORCL database on a machine using port 1521 you would use:

          jdbc:oracle:thin:@206.168.191.19:1521:ORCL

     Note: After the word "thin" is a colon (:) and then the "@" symbol

   • The Database Driver for the Oracle thin JDBC driver is:

           oracle.jdbc.driver.OracleDriver

     Enter this string into the Database Driver text box under the Advanced Options section of the Add Database Monitor form.

Possible errors using the Oracle Thin Driver:

• "error, connect error, No suitable driver": check for syntax errors in "Database Connection URL", such as dots instead of colons
• "error, connect error, Io exception: The Network Adapter could not establish the connection": in "Database Connection URL", check jdbc:oracle:thin:@206.168.191.19:1521:ORCL
• "error, connect error, Io exception: Invalid connection string format, a valid format is: "host:port:sid": in "Database Connection URL", check jdbc:oracle:thin:@206.168.191.19:1521:ORCL
• "error, connect error, Invalid Oracle URL specified: OracleDriver.connect": in "Database Connection URL", check for a colon before the '@' jdbc:oracle:thin@206.168.191.19:1521:ORCL
• "Refused:OR=(CODE=12505)(EMFI=4))))": in "Database Connection URL", check the database SID is probably incorrect (ORCL part). This error can also occur when the tcp address, or tcp port is incorrect. If this is the case, verify the tcp port and check with the your database administrator to verify the proper SID.
• "String Index out of range: -1": in "Database Connection URL", check for the database server address, port, and the database SID.
• "error, driver connect error, oracle.jdbc.driver.OracleDriver": check syntax in item "Database Driver"
• "error, driver connect error, oracle.jdbc.driver.OracleDriver": check that driver is loaded in correct place
• "error, connect error, No suitable driver": check driver specified in item "Database Driver"
• "error, connect error, No suitable driver": check for syntax errors in "Database Connection URL", such as dots instead of colons

Monitoring Informix Databases

Monitoring a Informix database requires the use of a JDBC driver. To enable SiteScope to monitor an Informix database:

1. Download the Informix JDBC driver from Informix. See the Informix web site for details.
2. Uncompress the distribution file
3. Open a DOS window and go to the jdbc140jc2 directory
4. Unpack the driver by running the following command:

        c:\SiteScope\java\bin\java -cp . setup

5. Copy ifxjdbc.jar to the ~SiteScope\java\ext\bin\ subdirectory.
6. Stop and restart SiteScope
7. Now, use your browser to add a Database Monitor within SiteScope.
   • The Database Connection URL format for the Informix JDBC driver is:

           jdbc:informix-sqli://<database hostname>:<tcp port><database server>:INFORMIXSERVER=<database>

     If you require a username and password the Database Connection URL format for the Informix JDBC driver is:

           jdbc:informix-sqli://<database hostname>:<tcp port><database server>:INFORMIXSERVER=<database>;user=myuser;password=mypassword

     For example to connect to the Database Server sysmaster running on the machine called pond.thiscompany.com and the Database called maindbase, you would use:

          jdbc:informix-sqli://pond.thiscompany.com:1526/sysmaster:INFORMIXSERVER=maindbase;

   • The Database Driver for the Informix JDBC driver is:

          com.informix.jdbc.IfxDriver

     Enter this string into the Database Driver text box under the Advanced Options section of the Add Database Monitor form.

Monitoring mySQL Databases

1. Download the JDBC driver from http://www.mysql.com/downloads/api-jdbc.html
2. Uncompress the distribution file
3. Among all the other files, you should find a file with a .jar extension.
4. Copy the .jar file into the SiteScope/java/lib/ext directory
5. Stop and restart SiteScope
6. Now, use your browser to add a Database Monitor within SiteScope.
   • The Database Connection URL format for the MySQL JDBC driver is:

          jdbc:mysql://<database hostname>[:<tcp port>]/<database>

     For example to connect to the MySQL database "aBigDatabase" on a machine using the standard MySQL port number 3306 you would use:

          jdbc:mysql://206.168.191.19/aBigDatabase

     If you are using a different port to connect to the database then you should include that port number as part of the IP address.

   • The specification for the MySQL JDBC driver is:      org.gjt.mm.mysql.Driver

     Enter this string into the Database Driver text box under the Advanced Options section of the Add Database Monitor form.

Possible errors using the mySQL Driver:

If, after setting this up, you get an authorization error in the Database Monitor, then you may have to grant rights for the SiteScope machine to access the MySQL database. Consult the MySQL Database administrator for setting up privileges for the SiteScope machine to access the MySQL server.

Index

Monitoring Sybase Databases

Possible errors with the Sybase database monitoring:

• Verify you are using the correct driver for the version of Sybase you are monitoring. For example:

        com.sybase.jdbc2.jdbc.SybDriver is the driver for Sybase version 5.x

        com.sybase.jdbc.SybDriver is the driver for Sybase version 4.x

• If you get the error: error, driver connect error, com/sybase/jdbc/SybDriver

  Click edit for the monitor and verify that there are no spaces at the end of the driver name in the text entry field. Then save the changes and try the monitor again.

• If you get the error: connect error, JZ006: Caught IOException: java.net.UnknownHostException: dbservername

  Verify the name of the database server you have entered in the Database Connection URL field is correct.

Index

-->

Copyright © 2003 Mercury Interactive Corporation.
All rights reserved.