[ Master documentation index ]
Introduction
The monitor feature in both editions of DbVisualizer is used to show
the results of one or many SQL statements in the Monitor window.
These monitors can then be re-loaded (executed) manually or by
the auto reloader which will
automatically re-load all monitors at a given interval. A monitored SQL
statement is an SQL Bookmark and the definition and management of which
SQL Bookmarks are monitored is controlled in the Bookmark Editor.
Any SQL Bookmark that produces a result set (data in a grid) can be
monitored. The monitor feature supports monitoring SQL Bookmarks for
different database connections concurrently.
The monitoring feature in conjunction with the charting capability in
DbVisualizer Personal is really powerful since it delivers real time
charts of one or several monitored SQL Bookmarks simultaneously.
Typical
scenarios when this is useful are to see live trends in a production
database, surveillance, statistics, etc. It is just a matter of
imagination and the level of SQL expertise that sets the limit! We at
Minq Software as an example have a dedicated work station that
automatically presents various charts from our database.
Charts can also be exported to JPEG and PNG files.
Note: Charts cannot be printed directly. You must first export
and then use another tool to print.
Note: The chart customization
covered
in this document is also applicable in the SQL Commander (DbVisualizer
Personal).
|
|
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)
|
Monitor an SQL
statement
An SQL statement to be monitored must be defined as an SQL
Bookmark in
DbVisualizer. A bookmark is briefly an SQL statement with associated
information about its database connection and an optional catalog
(generic JDBC denomination which translates to a database in for example Sybase,
MySQL, SQL Server, etc). The Bookmark Editor supports organizing SQL
bookmarks in a tree structured folder view and the complete structure
and all SQL Bookmarks are saved in the XML file between invocations of
DbVisualizer. It is the Bookmark Editor that is used to enable a SQL
Bookmark to become a monitored SQL Statement.
Figure: Bookmark
Editor
The figure 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 or not. Just click on the check mark and the SQL Bookmark will
appear in the Monitor window. Uncheck it to remove the monitor.
The following is an example of what 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.
There are alternative operations to simplify creation of monitored SQL
Bookmarks. Read the following sections to find out how this is done.
Monitor table row
count
The Monitor Table Row Count operation is activated in the Data tab for
a table (left button below):
Figure: Data tab tool bar buttons that are used to create monitors
It is used to create a monitor that displays in a single row the
current time stamp for when the monitor is executed and the total
number
of rows (count(*)) in the table. Each execution of the monitor will
result in one row being added to the grid. The monitoring feature in
this example keeps a pre defined number of rows until the oldest rows
are
removed. Example:
Computers:
Row Count
|
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 introduces two
variables, DbVis-Date and DbVis-Time. These variables are
substituted with the current date and time formatted according to the
formats in Tool Properties. The reason these variables are used instead
of using appropriate SQL functions to retrieve them is simply because
it
is almost impossible to get the values of these in a database
independent way. Another reason is that we want to set the time of the
client machine rather than the database. The SQL can of course be
modified to contain whatever SQL that is appropriate as long as the PollTime and RowCount labels are not changed.
Figure: Sample of
the SQL for the Table Row Count monitor
The above does not introduce any big news for an SQL hacker.
The magic of this monitor is that it keeps a pre defined number of
rows in the grid. This is managed by the Allowed Row Count property in the
Bookmark Editor. This property is automatically set when creating a row
count monitor. The default value is to keep the 100 latest rows added to
the
grid (one per execution).
Figure: Allowed
Row Count property pane
The above setting can be modified to limit or extend the number of rows
that the monitored grid will 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
The Monitor Table Row Count Difference operation is activated in the
Data tab for a table buttons (right button below):
Figure: Data tab
tool bar buttons that are used to create monitors
Its purpose is similar to Monitor Table Row Count except that this
monitor reports the difference between the two latest executions in the
result grid:
Computers:
Row Count Change
|
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 field
which is the RowCountChange.
It
is rather simple since the current count(*) in the table is is used
when
subtracting the RowCount in the previous execution round or count(*) if
there are no previous rows in the grid. This gives the difference. The
trick here is that DbVisualizer always keeps all values of the last row
that was added in the grid. Any of its fields can be referenced in the
succeeding execution of the monitor.
Figure: Sample of
the SQL for the Table Row Count Difference monitor
Monitor window
The Monitor feature launched via the Tools->Monitor menu option is used to browse the active monitors. The
monitors can be organized either as tabs or internal windows. In
DbVisualizer Free the monitor results can be viewed as grids while
DbVisualizer Personal adds the capability to view them as charts. The
following figure is a screen shot of the Monitor window:
Figure: The
Monitor window with all monitors organized as tabs
The screen shot is from DbVisualizer Personal as the selected monitor
has the Text and Chart sub tabs which are
not there in DbVisualizer Free. The Auto
Reload feature at the bottom of the main window is used to
control whether auto update of all monitors is enabled or not. The Seconds
field specifies how many
seconds the Monitor feature should wait before doing an auto reload. If
auto reloading is enabled then the monitor toolbar icon in the main
window is displayed to
indicate its state. The Edit Bookmark
button is used to open the Bookmark Editor for the currently selected
monitor. The Bookmark Editor will automatically locate the actual SQL
Bookmark for the monitor.
Note: The specified number of
seconds may be increased automatically by DbVisualizer if the total
execution time for all monitors is longer.
The Window menu contains choices to control the appearance in the
Monitor:
Figure: Window
menu operations
The Show
Grids, Show Texts and Show Charts
toggles the monitors to display the monitors in the selected view. Cascade and Tile are used to automatically
arrange the windows in the Windows view.
Charts
This section is only applicable for
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 feature even though this document does not cover it.
The basic setup of a chart is really easy since 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 sub toolbar for each monitor:
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
Specify in the Data customization which data shall appear in the
chart.
Figure: Data
customizer
Select at least one Series from
the list of columns and the chart is ready! Selecting several series
will show them accordingly in the chart. The Label
field can be used to specify an optional label for the serie as it will
appear in a legend. The name of the column 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. This is the same title as the
SQL
Bookmark in the Bookmark Editor. X-Axis
Title and Y-Axis Title
specifies the titles for the X and Y axis. The Rotation settings are used to set
the
rotation of the X and Y axis.
Layout
The layout tab is used to configure the appearance of the chart and
primarily what type of chart that will be displayed. 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 will 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.
The Advanced Settings editor
is
used 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.
Note: 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 then just press the Reset Zoom
button or 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 © 2005 Minq Software AB. All rights reserved.
