Scripting & Automation
System Monitor
This section describes the AQT System Monitor. This component allows you to monitor internal information in your database, such as active sessions, bufferpool activity and other information. It has been configured for DB2/UDB (v8 and above), Oracle, SQL Server and Sybase Enterprise.
The System Monitor is part of the Extended Edition of AQT, and is in evaluation mode for the Standard Edition. When in Evaluation Mode, you are only shown one display and cannot refresh it.
The System Monitor display is as below. This will show you different information for each database-type, depending on what internal information is available.
The System Monitor can monitor a number of different part of your system. You can use the Toolbar buttons to switch between the monitor displays. If you wish to monitor multiple parts of your system, hit File > New Window (or Ctrl + N) to open another Monitor window.
Window Layout
The system monitor window consists of the following parts:
the main display. This will show you the items being monitored.
right and bottom grids. These show you details of the item selected in the main grid (these are discussed in the next section)
action buttons. These allow you to perform an action on one of the monitored object (eg. cancel a session etc).
The right and bottom grids and action buttons may or may not be displayed, depending on what part of the system you are monitoring and how it has been configured.
Main Grid
This shows you the items being monitored.
hitting the Refresh Toolbar button will refresh the data in the grid (and in the right/bottom grids also)
if you only wish to show particular items, use the Filter box. AQT will only show rows that contain the specified text. Double-click this box for a larger window.
Right and Bottom Grids
As you move to different items in the main grid, the right and bottom grids will be populated with data for that item.
There is sometimes a choice about what details are shown in the right and bottom grids. These are selected with the drop-downs above the grids.
Clicking on the Refresh buttons above the grids will refresh those grids only.
You can close the right or bottom grids by clicking on the x at the top-right of the panel. You may wish to do this if you are not interested in the detailed displays and you wish to maximize screen real-estate for the main grid. Closing the detailed grids will also improve performance, as AQT won't need to fetch the data for these displays.
If you close the right / bottom grids, you can re-open them with View > Show Right Grid (Ctrl + R) or View > Show Bottom Grid (Ctrl + B).
Auto-Refresh
The display can auto-refresh. This is done by setting View > Auto Refresh. You can set the auto-refresh period with View > Set auto-refresh period.
Note that AQT can only run one SQL statement against a database at once. If you have auto-refresh switched on, and are using other functions in AQT against this database, these may fail if they run when the monitor is refreshing.
Other notes
you can sort the data in the grids by clicking on a column header. The sort-order will be reset at the next Refresh. If you wish to permanently change the order in which data is displayed, you will need to customize the display (see the next section).
you can click on View > Two-Color Display to show the grids in a two-color mode. However, when you do this any highlighting of cells disappears (this is overwritten by the two-color style).
clicking on the Refresh toolbar button will refresh the displays.
Technical Notes
AQT doesn't have any low-level hooks into the database. The information displayed in the system monitor is that which is available through normal SQL queries.
for DB2/UDB, AQT uses the Snapshot Monitor information, which is available though a series of system functions. These were introduced in DB2 v8 and have been enhanced in v9. For more details, see DB2 Monitor Switches.
to use the System Monitor with DB2, you need to be part of the SYSADM, SYSCTRL, or SYSMAINT groups. These groups are defined in the DB2 cfg; users are made members of these groups with operating system commands.
for Oracle, Sybase and SQL Server, AQT runs queries against the "dynamic" system tables.
When will the System Monitor work with DB2 z/OS?
We are often asked this! This is currently not possible, as information on active threads etc is not available through normal SQL statements. Should IBM provide a means to do this (as they have with DB2/UDB) then we will very rapidly be able to display this information.
Customizing Your Displays
You can customize your system monitor displays. From the System Monitor window, go Customize > Main Grid (or Right Grid / Bottom Grid).
You will be shown the following window:
This window shows how the System Monitor window is defined within AQT. There are two parts to this:
Columns in the Display. This specifies the columns in the system monitor display, and how they are defined
Query. This show the query that is run to populate the data in the display
Columns in the Display
Caption. This gives the caption (column heading) of the column in the display
Query Col. This specifies the column (in the query) which populates the column. You can either specify a column-number or column-name.
Function. In some circumstances, AQT formats the returned data value using an internal AQT function; this specifies this function. Generally you would not to wish to change this.
Display Type. How the value is to be displayed. You would normally specify Normal. Use Check Box to specify a check box (in which case the query should return 1 or 0). Use Text Box to display a large text value (this is only used for displaying SQL text).
Data Type. Specify char (character), num (numeric) or perc (percentage). perc will take a value such as 0.4512 and display it as 45.12%
Hidden. Whether the column is to be displayed. If you wish to remove a column from the display, it is recommended that you check this box rather than removing the column.
Warning. Specifies a warning condition - when this is met the value is highlighted in yellow. This is discussed below.
Crit. Specifies a critical condition - when this is met the value is highlighted in red. This is discussed below.
You can:
change the order of columns in the display by dragging them up or down. You should not move the first column away from that position, as it is often used as a key into other displays.
delete columns (though it is recommended that you Hide them instead)
add new columns. You would generally need to amend the Query (to return the extra data) as well as doing this.
Query
This gives the query that is run to populate the data in the display. It is recommended that you do not change this except:
you can change the Order By statement to display the data in a different order
you can add more columns to the query. Do these at the end of the current column list
you can add a Where clause to restrict the number of rows returned by the query. Use this with caution however, as some queries (particularly those used for the right/bottom grids) automatically have Where clauses added to them by AQT. If you add a Where clause, a syntax error will result.
Make sure that you test the query thoroughly before making changes to it.
Warn and Crit
These give conditions when a cell value will be highlighted in Yellow and Red respectively. These are coded as follows:
Specification | What it Does |
|---|---|
x | Cell value equals x |
*x | Cell value contains x |
<>x | Cell value is not x |
>x | Cell value is greater than x |
<x | Cell value is less than x |
>=x | Cell value is greater or equal to x |
<=x | Cell value is less than or equal to x |
You can have multiple conditions separated by commas, eg. x,y,*z (there will be a match if the cell value is either x or y or contains z).
If you use View > Two-Color Display, the cells will not show the highlight color (this is overridden by the two-color display).
OK
When you click on OK, the display is saved as a Customized display.It will be held in a user-customized administration configuration file. The name will be (for instance) oracle_adm_usr.cfg in your Config File Directory.
Reload
Click on this if you have amended the display and want it returned to it's original state (eg the state it was when you opened this window).
Revert to Default
This button is active if the display you are dealing with is a customized display. When you click on this button, the display will revert back to the original state.
Further Customization
The system monitor is configured as part of the Administration Component of AQT. The "full" configuration window can be accessed from the Database Explorer Window Tools > Configure Admin Component. The Customize Display window is a cut-down version of Configure Admin Component window. If you wish to configure further aspects of the System Monitor, you should use the Configure Admin Component window.
DB2 Monitor switches
In order for AQT to display the Snapshot Monitor information, the Monitor flags need to be switched on. There are two ways in which this can be done:
switch the flags on permanently. This is done by setting some flags in the DBM CFG (Database Manager Configuration)
switch on the flags temporarily. This is done by issuing the update monitor switches command .
A summary of the flags, and how they are switched on, is as follows:
Monitor Element | Switch on Permanently | Switch on Temporarily |
|---|---|---|
UOW | update dbm cfg using dft_mon_uow on | update monitor switches using uow on |
Statement | update dbm cfg using dft_mon_stmt on | update monitor switches using statement on |
Timestamp | update dbm cfg using dft_mon_timestamp on | update monitor switches using timestamp on |
Lock | update dbm cfg using dft_mon_lock on | update monitor switches using lock on |
Bufferpool | update dbm cfg using dft_mon_bufpool on | update monitor switches using bufferpool on |
Table | update dbm cfg using dft_mon_table on | update monitor switches using table on |
Sort | update dbm cfg using dft_mon_sort on | update monitor switches using sort on |
These commands are run through the DB2 Command Window. Any changes to the dbm cfg only come into effect when the database manager instance is next started, and this will apply to all databases within that instance.
When the flags are switched on temporarily, they are only in effect for the duration of the task that issued the commands. So if you switch them on using a DB2 Command Window, they will be switched off when you close the Command window.
Switching on the Monitor Switches using AQT
Within the System Monitor, you can go View > DB2 Monitor Switches. This will take you to a window which shows you the current state of the switches. You can change them then click on Update.
DB2 doesn't provide an easy mechanism for programs (such as AQT) to change the monitor switches. While there is an API call, this is difficult to use. Instead, AQT changes the monitor switches by opening a DB2 Command Window and issuing the appropriate update monitor switches command.
this command requires an Instance Attachment (not just a database connection). AQT will prompt you for the name of the instance. If you don't know this:
you will can find this out by running list database directory in a DB2 command window. This can be done from the Run SQL window with Run > Run as DB2 Command
if the database is on your machine, the instance name is most probably db2
as discussed earlier, the monitor switches only stay switched on while this db2 command window remains open. Only close this once you have finished monitoring the system.
It is hoped that in future releases of DB2, a better mechanism for switching on the monitor flags will be provided.
Further Comment
During testing of the System Monitor, it was found that the monitor information was sometimes not displayed when the switches were set on using the update monitor switches command. It is not known why this is the case. The monitor information displayed much more reliably when the monitor switches were set on in the dbm cfg.