Monitor

• Introduction
• Monitored SQL Statements 
  • Creating, Editing and Organizing Monitored Statements
  • Monitor table row count
  • Monitor table row count difference
• Monitor Window

Introduction

With the monitor feature, you can track changes in data over time, viewing the results of one or many SQL statements either as grids or graphs. Typically, you configure the monitor to run the statements automatically at certain intervals.

The monitoring feature combined with the charting capability in DbVisualizer Pro is really powerful, delivering real time charts of many result sets simultaneously. For example, you can use monitoring to spot trends in a production database, surveillance, statistics, database metrics, and so on.

Any SQL statement that produces a result set can be monitored, and when you monitor multiple statements, different statements may use different database connections concurrently.

Monitored SQL Statements 

Monitored SQL statements are managed under the Monitors node in the Scripts tab in the tree area to the left in the main DbVisualizer window. A monitored SQL statement is associated with information about the target database connection and (optionally) the catalog (the JDBC term which translates to a database for some databases, like Sybase, MySQL, SQL Server, etc) and schema. It also has a title, a maximum row count (how many results to keep track of) and a visibility status (whether the monitored statement result should be included in the Monitors windows, discussed below). This information is displayed, and can be edited, in the lower part of the Scripts tab, along with information about the file that holds the monitored statement. If you don't want to see these details, you can disable it with the Show Details toggle control in the right-click menu for a node.

Figure: The Scripts tab with Monitors

The figure above shows the Incidents/Day monitored statement and the SQL that is associated with it.

The following is an example of the result set produced by the statement:

Figure: Monitor window showing the result in Grid format

The interesting columns in the result are the Month and Count. The Year and MonthNum are there just to get the correct ascending order of the result.

Creating, Editing and Organizing Monitored Statements

You can create and work with monitored statements in the same way as with a Bookmark. The main difference is how they are used and a couple of additional ways monitored statements can be created. For information about how to manually create, manage and share monitored statements, please see the Bookmarks and History document. The following sections describe how you can get help creating the bookmarks for a couple of cases that are commonly used for monitoring.

Monitor table row count

It is very common to want to keep track of how the number of rows in a table varies over time. The right-click menu in the Data tab grid for a table therefore has a Create Row Count Monitor operation that creates a monitored statement for you automatically.

It creates a monitor with SQL for returning a single row with the timestamp for when the monitor was executed and the total number of rows in the table at that time. Every time the monitor is executed, a new row is added to the grid, up to a specified maximum number of rows. When the maximum row limit is reached, the oldest row is removed when a new row is added. Example:

Figure: Example of the result from a Table Row Count monitor

The SQL for this monitor uses two variables, DbVis-Date and DbVis-Time. These variables are substituted with the current date and time, formatted according to the corresponding Tool Properties settings. The reason for using these variables instead of using SQL functions to retrieve the values is simply that it is almost impossible to get the values in a database-independent way. Another reason is that we want to see the client machine time rather than the database server time. You can, of course, modify the SQL any way you see fit,  as long as the PollTime and RowCount labels are not changed.

select '${DbVis-Date}$ ${DbVis-Time}$' as PollTime,
       count(*) as RowCount
from Computers

Figure: Sample of the SQL for the Table Row Count monitor

DbVisualizer keeps the result for previous executions, up to the specified maximum number of rows, so that you can see how the result changes over time. You define the maximum number of rows in the Max Row Count field in the details area at the bottom of the Scripts tab. This property is initially set to 100 when you use Create Row Count Monitor to create the monitor.

Figure: Details area with the Max Row Count field for a monitored statement

You can change the value to limit or extend the number of rows that DbVisualizer should keep. Setting it to 0 or a negative number tells DbVisualizer to always clear the grid between executions of monitors.

Monitor table row count difference

In addition to tracking the number of rows in a table over time, you may want to see by how many rows the value changes. You can create a monitor for this purpose with the Create Row Count Diff Monitor operation, available in the right-click menu for the grid in the Data tab.

In addition to the Row Count Monitor, the Row Count Diff Monitor reports the difference between the number of rows in the last two executions:

Figure: Example of the result from a Table Row Count Difference monitor

The SQL for this monitor adds a third column, named RowCountChange. It utilizes the fact that DbVisualizer automatically creates variables for the columns in a monitor result set, holding the values from the previous execution. The RowCountChange column is set to the value returned by the count(*) aggregate function for the current execution minus the value from the previous execution, held by the RowCount variable. All columns in a monitor result set can be used like this to reference values from the previous execution of the monitor.

select '${DbVis-Date}$ ${DbVis-Time}$' as PollTime,
       count(*) as RowCount,
       count(*) - ${RowCount||count(*)}$ as RowCountChange 
from Computers

Figure: Sample of the SQL for the Table Row Count Difference monitor

Monitor Window

The Monitor window, launched via the Tools->Monitor menu option, is where you active monitors and look at the results. The monitor tabs can be rearranged in the same way as all other tabs, pretty much any way you like. Please see Working with Tabs for details.

The monitor results can be viewed only as grids in DbVisualizer Free, while DbVisualizer Pro adds the capability to view them as charts or text.

Figure: The Monitor window with all monitors organized as tabs

The Monitor window has toolbar at the top with an Auto Reload Interval field and a Adjust box. The rest of the window holds result areas for each monitored statement with the Visible attribute enabled. Each individual monitor result tab or window may also have a toolbar with controls that apply just to that result. The screenshot is from DbVisualizer Pro, with View buttons in the toolbar for the selected monitor;  these buttons are not included in DbVisualizer Free.

The main toolbar buttons have the following functions:


The Auto Reload Interval field is used to control how often to execute the monitors when auto update is running. Use the field to specify how many seconds to wait between auto-reloads. The specified number of seconds may be increased automatically by DbVisualizer if the total execution time for all monitors is longer than the specified value.
Check the Adjust box and the Monitor feature will automatically increase the number of seconds so that all monitors will complete before next auto-update.

---