SQL Performance Monitor for Databases

You can issue SQL queries to databases and measure the response time for the query, or even verify that the return value matches any specified value. Note that this is separate from monitoring the internal metrics of databases, which is done using WMI or SNMP. Standard JDBC drivers are included for the most commonly used databases: DB2, Microsoft, Oracle, Sybase, MySQL, PostgreSQL.

Adding a Database Specific Test

1. Navigate to Administration > Devices.
2. Click Tests link for a database server.
3. Click on Create New Standard Tests.
4. Select Create new tests by selecting specific monitors option.
5. Select sql_value and/or sql_query monitors.
6. Click on Add Tests.
7. Select the type of database on the next screen.
8. On the next screen select the parameters used to create the test.

Creating sql_value test

This monitor performs a synthetic transaction and retrieves a numeric result that is then compared against the configured thresholds. The SQL query specified must return a single column with numeric value. The following parameters must be provided for successful test execution:

Note: Unlike MySQL, the DB2 JDBC driver does not require that you terminate the query with a semi-colon (;).

Creating sql_query test

This monitor performs a synthetic transaction and measures the time required to complete the operation. The parameters are similar to the ones in sql_value test.

Make sure to provide a meaningful name for the test and select/enable the checkbox next to the test name.

Troubleshooting

Once the test(s) has been configured with appropriate parameters, Traverse DGE will start to perform the synthetic query at specified interval. In the event the DGE is unable to communicate with the database, the test will be shown with UNKNOWN or FAIL icon (depending on the nature of the problem). Clicking on the icon should open a pop-up window with useful diagnostic message.

Additionally, the TRAVERSE_HOME/logs/monitor.log on any system hosting a DGE extension will show any errors during test execution.

2012-09-24 16:22:17,252 sqlquery.SQLQueryResultFetcher[ThreadPool[ParallelPluginTestIssuer$PluginSynchronizer]]: (INFO) 192.168.9.119: testConfig=3190004; Unable to connect to database jdbc:db2://192.168.9.119:50002/SAMPLE

2012-09-24 16:23:17,221 clients.NetworkClient[ThreadPool[SynchronousNetworkMonitorCommunicator]]: (INFO) Problem while trying to get connection to jdbc:db2://192.168.9.119:50002/SAMPLE: [jcc][t4][2043][11550][3.63.123] Exception java.net.ConnectException: Error opening socket to server /192.168.9.119 on port 50,002 with message: Connection refused. ERRORCODE=-4499, SQLSTATE=08001