Monitor and Charts

• Introduction
• Monitored SQL Statements 
  • Creating, Editing and Organizing Monitored Statements
  • Monitor table row count
  • Monitor table row count difference
• Monitor Window
• Charts
  • Chart Controls
    • Data
    • Layout
  • Chart View
    • Zooming
    • Rotating
  • Export

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 Personal 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. At DbVis Software, we have a dedicated workstation that uses the monitoring feature to automatically present live chart information from our Internet servers and customer database.

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 produces 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. 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 monitors can be organized either as tabs or internal windows. The monitor results can be viewed only as grids in DbVisualizer Free, while DbVisualizer Personal 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. The rest of the window holds result areas for each monitored statement with the Visible attribute enabled. Each individual monitor result tab or window also has a toolbar with controls that apply just to that result. The screenshot is from DbVisualizer Personal, 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.

Charts

This section is only applicable to DbVisualizer Personal.

Charts in conjunction with the Monitor feature is really powerful, since monitored data is very often a good candidate to be charted. The charting capability in DbVisualizer Personal is also available in the SQL Commander; everything described here also applies to the grids for the result sets in the SQL Commander.

The basic setup of a chart is really easy. It is just a matter of selecting one or more columns that should appear as series in the chart. The basic requirement is that the monitor has been executed, so that there are columns to choose the series from. The appearance of the charts can be thoroughly customized using the advanced customization editor.

The chart view is controlled by the buttons in toolbar shown in the monitor result area when the graph mode is selected:

Figure: Chart control buttons

The following sections explain the features and how to setup the chart.

Chart Controls

The chart controls are used to customize the Data that shall be displayed in the chart, optional axis labels, titles, etc. It is also used to control the Layout of the chart in terms of chart type, legend type, etc.

Data

Use the controls in the Data tab to customize which data shall appear in the chart.

Figure: Data customizer

Select at least one Series from the list of columns. As soon as you select a series, it is immediately added to the graph. The Label field can be used to specify an optional label for the series as it should appear in a legend. The column name is used if no label is specified.

The X-Axis Label box is used to specify the column in the result that should be used to render the labels of the X-axis. X-Axis Title and Y-Axis Title specifies the titles for the X and Y axis. You can use the Rotation settings if you want the X and Y axis text rotated.

The title for the monitored statement, as defined in the details area in the Scripts tab, is used as the title for the chart. The script file name is used if no title has been specified.

Layout

The layout tab is used to configure the appearance of the chart, primarily the type of chart want to use. Note that all settings are per monitor. The following screen shots show some of the most commonly used chart types.

Figure: Chart type examples

The advanced layout editor can be used to customize every aspect of the layout. The basic layout settings, however, are the following:



Figure: Layout customizer

Show symbols specifies whether each value in a line chart should be represented by a symbol. Show Inverted defines whether the X and Y axis will be switched. 3D specifies if a bar chart will be displayed in 3D. The Chart Type lists all the available chart types. Fill Pattern defines how a bar, area and pie chart shall be filled. Legend Type specifies whether a legend will be displayed or not.

You can use the Advanced Settings editor to customize all the bits and pieces of the chart. This document does not explain all the configurations that can be done using this editor since that would result in a 100 page book. Play around with the different settings and see how the graph changes.

Settings that are made in the Advanced Editor are not saved between invocations of DbVisualizer.

Chart View

Zooming

Charts support zooming by selecting a rectangle in the chart area. Selecting another rectangle in that zoomed area will zoom the chart even further, and so on. To reset the zoom, click the Reset Zoom button or press the "r" keyboard button while the mouse pointer is in the chart area.

Rotating

All 3D chart types support rotating and changing the depth of the chart. Use the following to change the appearance:

• Shift+Left Mouse button
  Changes the depth of the chart
• Ctrl+Left Mouse button
  Changes the rotation of the chart

Examples:

Figure: Example of 3D charts

The above screen shots are just a few examples of the 3D chart types and how depth and rotation settings are used to change the appearance.

Export

The export operation is context sensitive and works on the currently selected chart, graph or grid. The controls in the export dialog also adapt to the currently selected object. If a chart is the current object the following export dialog will appear:

Figure: Export dialog for charts

The default size of the exported image is the same as it appears on the screen. To change the size, either select a pre-defined paper size in the Size list or enter a size in pixels.

---