[ Master documentation index ]
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
Minq 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 defined as an SQL Bookmark that produces a result
set can be monitored, and when you monitor multiple
statements, different statements may use different database connections
concurrently.
|
|
| The
Monitor window with four monitored SQL Bookmarks. The results can be
viewed as windows or tabs. This example shows the grid data as returned
from each SQL statement.
|
The
same monitored SQL Bookmarks as in the left figure but here presented
as charts. (DbVisualizer Personal)
|
The chart customization covered
in this document is also applicable to the charts for result sets in
the SQL Commander (DbVisualizer
Personal).
Monitor
an SQL
statement
To monitor an SQL statement, you first define it as an SQL
Bookmark in
DbVisualizer. A bookmark is an SQL statement with associated
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). You use the Bookmark Editor to create and
organize SQL
bookmarks in a tree structured folder view. This is also where you mark
which SQL
Bookmarks should be available for monitoring. All information about the
SQL Bookmarks is saved in an XML file between invocations of
DbVisualizer.
You can read more about SQL Bookmarks in general in the SQL Bookmarks
chapter; here we focus on the parts of interest for monitoring.
Starting and stopping the monitoring takes place in the Monitor window,
described in the next section. But first, let's see how you enable
monitoring of an SQL Bookmark.
Figure: Bookmark
Editor
The figure above shows the Computers
Sold per Month bookmark
and
the SQL that is associated with it. The Monitor
field in the tree is used to determine whether the SQL Bookmark is a
monitor. Click on the checkmark to make the bookmark appear in the
Monitor window, i.e., make it possible to monitor. Uncheck it to remove
it from the Monitor window.
The following is an example of the result set the above SQL Bookmark
produces:
Figure: Monitor 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.
You can read more about how to manually create and edit bookmarks in
the SQL Bookmarks
chapter. 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
therefore has a Create
Row Count Monitor operation that creates a monitoring
enabled SQL Bookmark 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:
| PollTime |
RowCount |
| 2003-01-23 12:19:10 |
43123 |
| 2003-01-23 12:11:40 |
43139 |
| 2003-01-23 12:21:10 |
43143 |
| 2003-01-23 12:22:40 |
43184 |
| ... |
... |
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
correspondng 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.
Figure: Sample of
the SQL for the Table Row Count monitor
DbVisualizer keeps the result for previous executions of the
SQL Bookmark, 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 with the Allowed
Row Count property in the
Bookmark Editor, under the Monitor tab. This property is automatically
set to 100 when you use Create Row Count Monitor to create the monitor.
Figure: Allowed
Row Count property pane
You can change the value in the Bookmark Editor, to limit or extend the
number of rows
that DbVisualizer should keep. Setting it to 0 or a negative number
tells the DbVisualizer to always clear the grid between executions of
monitors. Click the Apply
Edit button to save the new value.
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 righ-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:
| PollTime |
RowCount |
RowCountChange |
| 2003-01-23 12:19:10 |
43123 |
0 |
| 2003-01-23 12:11:40 |
43139 |
16 |
| 2003-01-23 12:21:10 |
43143 |
4 |
| 2003-01-23 12:22:40 |
43184 |
41 |
| ... |
... |
... |
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 latest
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.
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 browse the active monitors. 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. The
following figure is a screenshot of the Monitor window:
Figure: The
Monitor window with all monitors organized as tabs
The Monitor window has a menu and a toolbar at the top, an area for the
monitors (i.e., monitoring enabled SQL Bookmarks) in the middle, and an
Auto Reload Seconds
field and status bar at the bottom. The screenshot is from DbVisualizer
Personal, with View
buttons in a
toolbar for the selected monitor; these buttons are
not included in DbVisualizer Free.
The main toolbar buttons have the following functions:
| Toolbar Button |
Description |
| Reload |
Reloads all grids (i.e., executes all monitors and
updates the result set grids) |
| Clear Current |
Clears the currently selected grid. To clear all grids
at the same time, use the Edit->Clear
All Monitors menu choice |
| Show Bookmarks |
Launches the Bookmark Editor |
| Start Monitors |
Starts auto-update of all monitors, repeatedly
executing all monitors at the intervals specified by the Auto Reload
Seconds field |
| Stop Monitors |
Stops the auto-update |
| Show as Tabs |
Shows the monitors as tabs |
| Show as Windows |
Shows the monitors as internal windows |
The Auto
Reload Seconds field at the bottom of the main window 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 status bar shows the time
until the next auto-update.
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.
The Window menu contains choices to control the appearance in the
Monitor:
Figure: Window
menu operations
Show
Grids, Show Texts and Show
Charts
toggle the monitor display between the selected views. Cascade and Tile are used to
automatically
arrange the windows in the Windows view.
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 area when the graph mode is selected:
Figure: Chart
control buttons
The controls are from the left:
- Show/Hide chart controls pane
- Reset any zoom
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 the 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
will
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. Chart
Title
specifies the main title of the chart. Thie
SQL
Bookmark name, as defined in the Bookmark Editor, is used as the
default title. 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.
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 image that is about to be exported is the same
as it appears on the screen. To change the size then either select a
pre-defined paper size in the Size
list or enter a size in pixels.
Copyright © 2008 Onseven Software AB. All rights reserved.
