Monitoring Database State


GUI Object	Description	Related Information
File menu	Allows you to set the refresh rate.	Setting Refresh Options
Window menu	Allows you to display the database window of the PCC and individual Monitor tabs, and to set preferences.	Setting Monitor Preferences
Help menu	Allows you to access the documentation and logs.	PSQL Event Log (pvsw.log)
Tab Set area	Includes five tabs with grids that display information about activity occurring at that moment in the database. When you select a tab, a selection of icons that apply to that tab appear at the right of the row of tab labels.	Tab Functionality

Setting Monitor Preferences

You can set preferences for the Monitor from either Monitor itself or from PCC. In either utility, select Window > Preferences > Monitor to open the Monitor tab of the Preferences dialog box.

There are two types of preferences you can set. First, you can set the layout of the Monitor user interface, so that when you reopen Monitor, its tabs are arranged just as you left them. You can set this individually for each server that you access. Second, you can set the features for any particular grid, for example, column width, sort order, and column order. If you open that particular grid on another server, it follows the same settings. That way, you can easily compare the same grid on different servers.

Setting Refresh Options

Information in the Monitor can be refreshed automatically at a configured interval, as desired, or not at all. Be aware that refreshing too many windows at a short interval may slow performance.

Automatic refresh

Use the Set Automatic Refresh icon to turn on automatic refresh.

Either select File > Set Automatic Refresh Rate or click the Set Refresh Rate icon to open the Set Refresh Rate dialog box.

In the Set Refresh Rate dialog box, enter the number of second between each refresh and click OK.

Refresh as desired

Either select File > Automatic Refresh or click the Refresh icon.

No refresh

Use the Set Automatic Refresh icon to turn off automatic refresh.

Tab Functionality

Tabs can be rearranged, separated, and reaggregated for your convenience. To move a tab, put the cursor on the tab label, hold down the left mouse button, and pull the tab label where you want the tab to be.

Because of the different nature of the data on each tab, different operations can be performed on each tab. Those operations are initiated by the icons that appear at the right end of the row of tab labels. The following table describes the icons.

Table 35

GUI Icons

Icon

Meaning

Set Automatic Refresh

Toggles between automatic refresh on and automatic refresh off. See Setting Refresh Options.

Set Refresh Rate

Displays a dialog box in which you can set the number of seconds between each repopulation of the user interface with current information. See Setting Refresh Options.

Refresh

Repopulates the user interface with the current information.

Select Columns to Display

Displays a dialog box in which you can select which columns to display. Because each tab includes different columns, each dialog box includes a different list of column names. All dialog boxes include the choices Select All and Deselect All.

Hide/Show Handles

Toggles between displaying or hiding the Handle Information grid at the bottom of the tab. The Handle Information grid includes different information, depending on the tab.

The Handle Information grid also has a Select Columns to Display icon, so that you can hide or display columns as desired.

Delete Selected Session

Removes a highlighted session from the tab. This icon becomes activated only after a session is selected.

Caution: This procedure actually terminates a session, so you are interrupting someone’s work in progress. Consequently, a message asks you to confirm that you actually want to do this.

Delete All Sessions

Removes all sessions from the tab.

Caution: This procedure actually terminates all sessions, so you are interrupting work in progress. Consequently, a message asks you to confirm that you actually want to do this.

Reset the Deltas

Deletes a statistic and restarts the count at zero.

Monitoring Active Files

The Active Files tab provides information about MicroKernel files that are currently open. To select columns to monitor, see Select Columns to Display.

Table 36

Selectable Columns for Monitoring MicroKernel Active Files

Path

Provides the directory and all subdirectories to the location of the file.

File

Indicates the name, including suffix, of the file.

Page Size

Indicates the size in bytes of each page in the file.

Read-Only

Indicates whether the file is flagged as read-only by the operating system.

Record Locks

Indicates whether any of the active handles for the selected file have record locks. Any application can read a locked record, but only the application that placed the lock can modify or delete the record. A record lock exists only as long as the application that opened the file is updating a record. “Yes” indicates that one or more record locks are applied to the file. “No” indicates that no records are locked.

Transaction Lock

Indicates whether any of the active handles for the selected file have a transaction lock. A transactional file lock exists only as long as the application that opened the file is processing a transaction.

Physical File Size

Indicates the size of the file in kilobytes (KB). This information is particularly useful for the capacity-based license model if you want to review data in use on a file-by-file basis. See also Capacity-based License Model in PSQL User's Guide.

Monitor uses kilobytes (KB) for the size of an individual file and megabytes (MB) as the units for resource usage (Monitoring Resource Usage). License Administrator uses gigabytes (GB) as the units because that is how data in use is associated with a key. The different contexts require units appropriate for each context.

If a file is immediately closed after you insert a large number of records, Monitor does not immediately reflect the changes in file size. For example, the statistics for Physical File Size KB are not refreshed for that file until the next time the file is opened for reading or writing.

You can view the handle information for any active file. See Hide/Show Handles. Active file handles include the following information.

Table 37

Selectable Columns for Active File Handles

Client

Indicates the name (typically the login ID of the user) or an index to the Client list of the database server.

Connection Number

Displays the network connection number of the client. If the client does not have a network connection, this field displays NA for “not applicable.”

Task Number

Displays the process-supplied task number for processes originating at the server or a Windows Client.

Site

Specifies the location of the user process (local or remote).

Network Address

Identifies the location of the calling process on the network. If the calling process is SPX, the network node/network address is preceded by S: such as S: 65666768 00000000001.

If the calling process is TCP/IP, the address is preceded by T4 for IPv4 addresses, T6 for IPv6 addresses, and T for the fully qualified domain name of a client machine. Examples:
T4: 180.150.1.24
T6: 1234:5678:0000:0000:0000:0000:9abc:def0
T: <mymachine.mydomain.mycompany>.com

Open Mode

Indicates the method the application uses to open the specified handle of the file. Valid open modes are the following:

•

Normal – The application that opened the file has normal shared, read/write access to it.

•

Accelerated – The application that opened the file has shared read/write access.

•

Read-only – The application that opened the file has read-only access; it cannot modify the file.

•

Exclusive – The application that opened the file has exclusive access. Other applications cannot open the file until the calling application closes it.

Monitor also specifies all open modes as nontransactional or shared locking when applicable.

Record Lock Type

Displays the type of record lock(s) currently held by the handle. The possible record lock types are Single, Multiple, and None.

Single-record locks enable a user to lock only one record at a time. Multiple-record locks enable a user to lock more than one record at a time.

Wait State

Indicates whether the user is waiting because of some type of lock on this handle: Waits for Record Lock, Waits for File Lock, or None.

Transaction State

Displays the state of the transaction lock currently held by the handle. The possible transaction types are Exclusive, Concurrent, or None.

Monitoring MicroKernel Sessions

The MicroKernel Sessions tab provides information about current connections to the MicroKernel Engine. To select columns to monitor, see Select Columns to Display.

Table 38

Selectable Columns for Monitoring MicroKernel Sessions

Session

Provides a unique identifier for the connection. A session is defined as a client ID used by the MicroKernel Engine or a connection to the Relational Engine. Client ID is defined as a 16-byte structure that combines elements provided by the application, by the client platform, and by the database engine to uniquely identify a database transaction context.

Session information reflects the sessions established through the MicroKernel Engine and through the Relational Engine. If you want to view sessions established only through the Relational Engine, see Monitoring SQL Active Sessions.

This tab allows you to delete a session or all sessions. See Delete Selected Session and Delete All Sessions.

Connection Number

Displays the network connection number of the session. If the session does not have a network connection, this field displays NA for “not applicable.”

Task Number

Displays the process-supplied task number for processes originating at the server, or from a Windows Client.

Site

Specifies the location of the session process (local or remote).

Network Address

Identifies the location of the calling process on the network. If the calling process is SPX, the network node/network address is preceded by S: such as S: 65666768 00000000001.

If the calling process is TCP/IP, the address is preceded by T4 for IPv4 addresses, T6 for IPv6 addresses, and T for the fully qualified domain name of a client machine.
Examples:
T4: 180.150.1.24
T6: 1234:5678:0000:0000:0000:0000:9abc:def0
T: <mymachine.mydomain.mycompany>.com

If multiple clients from a single machine connect by different TCP/IP addresses, each address is valid for that client. However, internally to the database engine, an address associated with a client may not be the actual address used by that client. This is because of the way the database engine identifies and manages multiple clients from the same machine. Consequently, since Monitor is reporting engine information, the utility may display an associated address instead of the actual address.

Locks Used

Indicates the number of locks the session is currently using.

Transaction State

Displays the type of transaction lock the session currently holds. The possible transaction types are Exclusive, Concurrent, or None.

Read Records

Displays the number of records read since the session first opened a file.

Inserted Records

Displays the number of records the session has inserted.

Deleted Records

Displays the number of records the session has deleted.

Updated Records

Displays the number of records the session has updated.

Disk Accesses

Indicates the number of times the session required a disk access. You will not see any information for disk accesses for files that have just been opened.

Cache Accesses

Indicates the number of times this client finds data in L1 or L2 cache in order to fulfill the request.

You can view the handle information for any MicroKernel session. See Hide/Show Handles. MicroKernel session handles include the following information.

Table 39

Selectable Columns for MicroKernel Session Handle Information

Path

Provides the directory and all subdirectories to the location of the file.

File

Indicates the name, including suffix, of the file.

Open Mode

Indicates the method the application uses to open the specified handle of the file. Valid open modes are:

Normal – The application that opened the file has normal shared, read/write access to it.

Accelerated – The application that opened the file has shared read/write access.

Read-only – The application that opened the file has read-only access; it cannot modify the file.

Exclusive – The application that opened the file has exclusive access. Other applications cannot open the file until the calling application closes it.

Monitor also specifies all open modes as nontransactional or shared locking when applicable.

Record Lock Type

Displays the type of record lock(s) currently held by the handle. The possible record lock types are Single, Multiple, and None.

Single-record locks enable a user to lock only one record at a time. Multiple-record locks enable a user to lock more than one record at a time.

Wait State

Indicates whether the user is waiting because of some type of lock on this handle: Waits for Record Lock, Waits for File Lock, or None.

Transaction State

Displays the state of the transaction lock currently held by the handle. The possible transaction types are Exclusive, Concurrent, or None.

Monitoring Resource Usage

The Resource Usage tab displays the resources in use by the MicroKernel since the engine was last started. To select columns to monitor, see Select Columns to Display.

The database engine dynamically controls the maximum values for some of these resources. The maximum value for User Count, Session Count, and Data In Use depends on the product license. See License Models in PSQL User's Guide.

If a resource does not apply to the type of PSQL product being monitored, “n/a” (“not applicable”) appears for each statistic. For example, User Count does not apply to PSQL Vx Server. Therefore, “n/a” appears as the Current, Peak, and Maximum value for User Count if PSQL Vx Server is being monitored. Similarly, “n/a” appears as the Maximum value for Session Count and Data in Use MB if PSQL Server is being monitored.

If you are considering using PSQL Vx Server, you need the ability to estimate Current and Peak values for Session Count and Data in Use MB. Consequently, those statistics are displayed for PSQL Server without being enforced. No notifications are sent about them regardless of their values.


Engine Uptime	Lists the amount of time in weeks, days, hours, and minutes that the MicroKernel engine has been running. Engine Uptime is not a selectable column for Resource Usage. It is part of the grid title.

Table 40

Selectable Columns for Monitoring MicroKernel Resource Usage

Resource

Indicates the type of resource being monitored. See Resource Types for Resource Usage Monitoring.

Current

Indicates the current usage by a resource.

Peak

Indicates the the highest value for a resource since the MicroKernel was started.

Maximum

Indicates the highest allowed value for a resource.

The following table lists the types of resources for usage monitoring.

Table 41

Resource Types for Resource Usage Monitoring

Files

Indicates the number of files currently open by the MicroKernel. The maximum for this resource is unlimited.

Handles

Indicates the number of active handles. The MicroKernel creates a handle each time a user opens a file. A single session can have several handles for the same file. The maximum for this resource is unlimited.

Clients

Indicates the number of clients accessing the MicroKernel. A machine can have multiple clients accessing the database engine simultaneously. The engine dynamically manages the client list. The maximum for this resource is unlimited (the number of clients is limited only by the memory in the computer).

“Client” indicates a session established by a client ID (transactional engine interface) or a connection to the relational engine interface. The database engine uses various client sessions for its own internal processes, such as for accessing PSQL system files, metadata files, dbnames.cfg, and default system databases. The number of clients indicates both internal client sessions and non-internal client sessions (see Monitoring MicroKernel Sessions).

Worker Threads

Indicates the number of concurrent MicroKernel processes.

User Count

Indicates the number of concurrently connected users. The maximum value shows the maximum permitted users as granted by a license agreement.

Session Count

Indicates the number of sessions in use by the database engine. For brevity, “number of sessions in use” is also referred to “session count.” The maximum value (also called the “session count limit”) shows the maximum permitted sessions as granted by a license agreement.

Session count reflects all sessions whether established through the MicroKernel Engine or through the Relational Engine.

Messages pertaining to session count are logged to the various PSQL logging repositories. See Reviewing Message Logs.

The database engine uses various sessions for its own internal processes, such as for accessing PSQL system files, metadata files, dbnames.cfg, and default system databases. These internal sessions do not consume any session counts.

Data In Use MB

Indicates in megabytes (MB) the size of all concurrently open data files. The maximum value is the maximum permitted amount of all concurrently open data files as granted by a license agreement. The maximum is also called the “data in use limit.”

The value for data in use increases when a data file is first opened. Subsequent opens to an already open data file do not add to the total. Data in use also increases if an open file increases in size. Operations on an already open file continue to be permitted even if the size of the open file increases beyond the data in use limit.

The value for data in use decreases when a data file is closed by the final user to have the file open. Since more than one user can access the same data file, all opens must be closed before data in use decreases.

Messages pertaining to data are logged to the various PSQL logging repositories. See Reviewing Message Logs.

The database engine uses various files for its own internal processes, such as PSQL system files, metadata files, dbnames.cfg, and default databases. Files used for internal processes do not increase the value for data in use.

If a file is immediately closed after you insert a large number of records, Monitor does not immediately reflect the changes in file size. For example, the statistics for “Data in Use MB” are not refreshed for that file until the next time the file is opened for reading or writing.

Transactions

Indicates the number of transactions. The maximum for this resource is unlimited.

Locks

Indicates the number of record locks. The maximum for this resource is unlimited.

Monitoring MicroKernel Communication Statistics

The MicroKernel Communication Statistics tab displays information about communication with the MicroKernel Engine. It includes separate sections for Communications Statistics and Resource Usage Information. Communication statistics are calculated in terms of total number of occurrences processed since the database engine was started.

To select columns to monitor, see Select Columns to Display.


Engine Uptime	Lists the amount of time in weeks, days, hours, and minutes that the MicroKernel engine has been running. Engine Uptime is not a selectable column for MicroKernel Communications Statistics. It is part of the grid title.

Communications Statistics

Table 42

Selectable Columns for Monitoring MicroKernel Communications Statistics

Resource

Indicates the type of resource being monitored. See Resource Types for MicroKernel Communications Statistics.

Total

Indicates the total number of occurrences processed since the database engine was started.

Delta

Indicates the number of occurrences processed since you last accessed the MicroKernel Communications Statistics tab. To restart the count of the delta number, see Reset the Deltas.

Table 43

Resource Types for MicroKernel Communications Statistics

Total Requests Processed

Indicates the number of requests the database engine has handled from workstations or remote, server-based applications.

SPX Requests Processed

Indicates the number of SPX requests the database engine has handled from clients or remote, server-based applications.

TCP/IP Requests Processed

Indicates the number of TCP/IP requests the database engine has handled from clients or remote, server-based applications.

NetBIOS Requests Processed

Indicates the number of NetBIOS requests the database engine has handled from clients or remote, server-based applications.

Connection Timeouts

Indicates the number of times Auto Reconnect has timed out when attempting to reconnect to Clients. See also Auto Reconnect Timeout.

Connection Recoveries

Indicates the number of times the AutoReconnect feature has successfully recovered from a connection timeout.

Resource Usage Information

Resource usage information provides current, peak, and maximum values for resource occurrences.

Table 44

Selectable Columns for Monitoring Resource Usage Pertaining to MicroKernel Communications Statistics

Resource

Indicates the type of resource being monitored. See Resource Types for Resource Usage Pertaining to MicroKernel Communications Statistics.

Current

Indicates the current usage by a resource.

Peak

Indicates the highest value for a resource since the MicroKernel was started.

Maximum

Indicates the highest value allowed for a resource.

Table 45

Resource Types for Resource Usage Pertaining to MicroKernel Communications Statistics

Communications Threads

Indicates the number of remote requests that the MicroKernel is currently processing. Local requests are not included in this statistic. For the total number of remote and local threads being processed, see Monitoring Resource Usage.

The database engine dynamically increases the number of communications threads as needed up to the maximum allowed. For Windows, Linux, and OS X, the maximum is 1,024.

Worker threads are also used to process Monitor requests, so you might not see the number of current worker threads drop below one. This is normal.

Total Remote Sessions

Indicates the number of remote clients connected to the database engine. The maximum number is dynamic and displays as zero.

SPX Remote Sessions

Indicates the number of remote clients connected through the SPX protocol to the database engine.

TCP/IP Remote Sessions

Indicates the number of remote clients connected through the TCP/IP protocol to the database engine.

NetBIOS Remote Sessions

Indicates the number of remote clients connected through the NetBIOS protocol to the database engine.

Monitoring SQL Active Sessions

The SQL Active Sessions tab provides information about current connections to the Relational Engine. This tab also allows you to delete a SQL session. See Delete Selected Session. To select columns to monitor, see Select Columns to Display.

Table 46

Selectable Columns for Monitoring SQL Active Sessions

User Name

Provides the login name of the user.

Client Host Name

Identifies the name of the Client machine for the selected User Name. If unavailable, this is set to “Unknown.”

Network Address

Identifies the Client machine’s IP or SPX address for the selected User Name. If unavailable, this is set to “Unknown.”

Values displayed include IP, SPX, Shared Memory and Unknown.

Client Application

Identifies the connected application or module. If unavailable, this is set to “Unknown.”

Data Source Name

Identifies the name of the DSN referenced by the Client application.

Connection Status

Specifies the connection status for the selected User Name. A status can be any of the following:

•

Active – The session has files open. and that Idle means that the session has no files open.

•

Idle – The session has no files open.

•

Dying – A temporary status that indicates an active session has been deleted but has not finished processing the SQL code. At a suitable termination point, the session is no longer listed on the SQL Active Session dialog.

•

Unknown – Status is unavailable.

Active/Idle Period

Displays the duration of time, in milliseconds, since the connection has been active or idle.

Total ConnectionTime

Displays the duration of time, in seconds, since the connection has been established

Command Line Interface Monitor

The command line interface (CLI) version of Monitor provides the same monitoring functionality as the GUI version.

This subsection contains the following topics:

•

Accessing CLI Monitor

•

Configuration File

•

Monitoring Output

Accessing CLI Monitor

CLI Monitor runs on the Windows, Linux, and OS X platforms supported by PSQL:

•

On Windows, the executable program is bmon.bat and is installed, by default, in the \bin directory of the PSQL installation directory. See Where are the PSQL files installed? in Getting Started with PSQL.

•

On Linux and OS X, the executable program name is bmon and is located, by default, in the /usr/local/psql/bin directory. Certain requirements must be met before you can run bmon on Linux or OS X. These requirements are the same as for another Java utility, bcfg. See Requirements for Running bcfg, and Troubleshooting Guide for Running bcfg.

Configuration File

Bmon requires a configuration file to provide its settings. PSQL provides a sample configuration file named monconfig.txt. It is located, by default, in the \bin directory of the PSQL installation directory. See Where are the PSQL files installed? in Getting Started with PSQL.

Refer to the comments in the sample configuration file for the settings that you can configure.

Monitoring Output

Output from Bmon can be directed to the console, a log file, or both. An application could, for example, check for a particular condition from the console or in a log file, then take appropriate action.

The configuration file specifies where to direct the output.

Command Syntax

bmon -f [filepath]config_file [-runonce]

Options


-f	This is a required parameter to specify that a configuration file is providing input to the utility.
filepath	The path to the configuration file. If omitted, Bmon checks the local directory for the configuration file.
config_file	The name of the configuration file. The file name is of your choosing.
-runonce	This is an optional parameter that instructs the utility to run once, then exit. The runonce parameter is particularly useful when Bmon is used in a batch file. See also Keyboard Key Response If Runonce Parameter Omitted.

Keyboard Key Response If Runonce Parameter Omitted

The runonce parameter is optional. If omitted, the utility executes the settings in the configuration file, then pauses for the duration of the refresh rate. During the pause, you can send the utility a valid keyboard key response as shown in Bmon Refresh Rates and Keyboard Key Responses.

If the refresh rate is set to zero, the utility pauses indefinitely until it receives a valid keyboard key response. The refreshrate setting in the configuration file specifies how many seconds to pause. By default, refreshrate is set to the minimal allowed value of 5 seconds.

Table 47

Bmon Refresh Rates and Keyboard Key Responses

Refresh Rate

Keyboard Key Response

refreshrate=0
(pause until valid keyboard key response received)

Q (or q) + Enter stops execution of bmon

R (or r) + Enter refreshes the monitoring (runs bmon again)

refreshrate=seconds_to_pause

where seconds_to_pause is a whole number 5 or greater
(pause for seconds_to_pause seconds)

Q (or q) + Enter stops execution of bmon