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 will not 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 install path>/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 install path>/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 Microsoft 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 click 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

Select how often the monitor should check the Database server. The default interval is to run or update the monitor once every 10 minutes. Use the drop-down list to the right of the text box to specify another update interval in increments of seconds, minutes, hours, or days. The update interval must be 15 seconds or longer.

Title

Enter a title text for this monitor. This text is displayed in the group detail page, in report titles, and other places in the SiteScope interface. If you do not enter a title text, SiteScope will create a title based on the host, server, or URL being monitored.

Database Driver

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

Advanced Options

The Advanced Options section presents a number of ways to customize monitor behavior and display. Use this section to customize error and warning thresholds, disable the monitor, set monitor-to-monitor dependencies, customize display options, and enter other monitor specific settings required for special infrastructure environments. The options for this monitor type are described below. Complete the entries as needed and click the Add or Update button to save the settings.

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 box 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.

Note:The 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.

DB Machine Name

If you are reporting monitor data to an installation of Mercury Interactive's Topaz, enter a text identifier describing the database server that this monitor is monitoring. This text descriptor is used to identify the database server when the monitor data is viewed in a Topaz report. You should only use alphanumeric characters for this entry. You can enter the name of the monitored server or a description of the database.

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)

You use this option 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

By default, SiteScope 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. Click the Edit schedule link to create or edit a monitor schedule. For more information about working with monitor schedules, see the section on Schedule Preferences for Monitoring.

Monitor Description

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

Enter an optional description for this monitor that will make it easier to understand what the monitor does. For example, network traffic or main server response time. This description will be displayed on with each bar chart and graph in Management Reports and appended to the tool-tip displayed when you pass the mouse cursor over the status icon for this monitor on the monitor detail page.

Depends On

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 on which this monitor is dependent. Select None to remove any dependency.

Depends Condition

If you choose to make the running of this monitor dependent on the status of another monitor, select 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 on which it depends reports the condition selected in this option.

List Order

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 install path>/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.

Troubleshooting with the Oracle Thin Driver

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 install path>\SiteScope\java\lib\ext\ 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 db.thiscorp.com and the Database called maindb, you would use:

   jdbc:informix-sqli://db.thiscorp.com:1526/sysmaster:INFORMIXSERVER=maindb;

   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

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

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 install path>/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

To use JDBC drivers with your Sybase SQL server, please following the following steps:

1. Finding the driver: Obtain the driver for the version of Sybase that you are using. For example, for version 5.X databases you will need jconn2.jar. If you have Jconnect, you should be able to find a driver in the Jconnect directory. Mercury Interactive does not provide the drivers. Most drivers can be downloaded from the internet.
2. Where to put the driver: Place the zip file in the
   <SiteScope install path>\SiteScope\java\lib\ext directory. Note: Do NOT extract the zip file)
3. Stop and restart the SiteScope service
4. Add a Database Monitor in SiteScope
5. For the database connection use the syntax of:

   jdbc:sybase:Tds:hostname:port

   For example to connect to SQL server named bgsu97 listening on port 2408, you would enter:

   jdbc:sybase:Tds:bgsu97:2408

6. You can specify a database by using the syntax:

   jdbc:sybase:Tds:hostname:port#/database

   For example to connect to SQL server named bgsu97 listening on port 2408 and to the database of quincy, you would enter:

   jdbc:sybase:Tds:bgsu97:2408/quincy

7. Enter a query string for a database instance and table in the Sybase database you want to monitor

   For example, Sp_help should work and return something similar to:

   good, 0.06 sec, 27 rows, KIRK1 , dbo, user table

   Alternately, the query string select * from spt_ijdbc_mda should return something similar to:

   Monitor: good, 0.06 sec, 175 rows, CLASSFORNAME, 1, create table #tmp_class_for_name (xtbinaryoffrow image null), sp_ijdbc_class_for_name(?), select * from #tmp_class_for_name, 1, 7, 12000, -1

8. Enter the database user name and password
9. For the Database driver, enter:

   com.sybase.jdbc.SybDriver (for Sybase version 4.x)

   com.sybase.jdbc2.jdbc.SybDriver (for Sybase version 5.x)

10. Click the Add Monitor button

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 box. 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 box is correct.

Copyright © 2004 Mercury Interactive Corporation.
All rights reserved.

Documentation comments or suggestions?
Please send feedback to documentation@merc-int.com