4.3 Briefly describe the screens in the overview subsystem
Key points
- The DBEnvironment must be active to obtain any screen in the subsystem (in
other words, at least one user must be connected to the DBE). A warning is
returned if you try to access one of these screens if nobody is connected.
Menu
SQLMONITOR SUBSYSTEMS (and abbreviations):
OVERVIEW IO LOAD LOCK SAMPLEIO STATIC
/o /i /loa /loc /sa /st
CURRENT SUBSYSTEM SCREENS:
OVERVIEW SESSION PROGRAM
o s p
Overview screen
SQLMONITOR OVERVIEW => o /* From the current subsystem */
SQLMONITOR LOCK => /o /* From another subsystem */
This screen provides an overall view of some of the most interesting aspects
of the performance of the DBEnvironment, including the data buffer pool miss
rate, the current size of the runtime control block, and the current size of
the log file. Some information on this screen is examined in greater detail
on the LOAD and IO screens.
The screen shows that 3 sessions (SESSIONS) are connected to the DBE. The
maximum number of transactions (MAX XACT) configured for the DBE is 50, and
currently there are two active transactions (ACTIVE XACT). No transactions
are blocked because of locks held by other transactions (IMPEDE XACT).
NOTE that the ACTIVE XACT and IMPEDE XACT bars are displayed graphically using
a scale determined by the MAX XACT value.
WARNING - SQLMON does not show locking problems on IMAGE data sets. It only
shows locking problems on SQL tables (tables stored under DBCORE). You should
still use DBUTIL to investigate/resolve locking problems on IMAGE data sets.
After you visit any screen, you can type "help tune" to obtain a list
of tuning suggestions that you might take based on the information presented
on the screen. For example:
SQLMONITOR OVERVIEW => help tune
1 MONITOR THE SIZE OF THE RUNTIME CONTROL BLOCK.
2 MONITOR THE SIZE OF THE ARCHIVE MODE LOG FILE.
Type TUNE <number> for detailed information about any tuning suggestion
listed ( Example: Type TUNE 1 for information about MONITOR THE SIZE OF
THE RUNTIME CONTROL BLOCK ).
Continue? ([YES],NO) <Hit return for YES>
SUBSYSTEMS: OVERVIEW, IO, LOAD, LOCK, SAMPLEIO, STATIC
SUBSYSTEM SCREENS: OVERVIEW, SESSION, PROGRAM
HELP KEYWORDS: CONTROL, INFO, SUBSYSTEM, TUNE
Type one of the HELP KEYWORDS for more information about the current help
screen. Type one of the SUBSYSTEM SCREENS for help about another screen
in the current help subsystem. Type one of the SUBSYSTEMS for help about
another subsystem. Type MAIN for a list of all SQLMONITOR commands. Type
HELP for more information about the help facility. Type EXIT, QUIT, or //
to leave the help facility.
SQLMONITOR HELP OVERVIEW => tune 1
MONITOR THE SIZE OF THE RUNTIME CONTROL BLOCK
The size of the runtime control block is a parameter that may be modified
using the SQLUTIL ALTDBE command. If too small of a value is specified, the
transactions that cannot obtain space will be rolled back and ALLBASE/SQL
returns the error code -4008 ("ALLBASE/SQL shared memory lock allocation
failed in DBCORE").
Lock management is the single greatest user of shared memory (the runtime
control block is a shared memory structure used internally by ALLBASE/SQL).
The greater the number of concurrent locks held, the greater the number of
runtime control block pages needed to manage these locks. Consequently,
a program that manages locks well is less likely to deplete the amount of
shared memory available. Obtaining locks at the table level, rather than
at the page or row level will reduce the shared memory requirements for a
transaction, but may reduce concurrency. Type LOCK TUNE 2, LOCK TUNE 8,
or LOCK TUNE 9 for more information about LOCK GRANULARITIES, CHANGE THE
IMPLICIT LOCKING STRUCTURE ON A TABLE, or USE THE LOCK TABLE STATEMENT TO
OVERRIDE IMPLICIT LOCKING.
The LOCK MEMORY screen allows you to identify how many locks are allocated
to each session connected to the DBEnvironment, and the LOCK TABSUMMARY
screen allows you to identify how many locks are allocated for each table.
If you obtain error code -4008 frequently, or if you want to minimize
the amount of shared memory used by the DBEnvironment, you can use the
LOCK MEMORY screen to identify the session(s) using the most number of
locks. Then use the LOCK TABSUMMARY screen to determine the tables on
which the largest number of locks have been allocated, and the program that
each session is running. These programs are good candidates for tuning for
lock optimization.
SQLMONITOR HELP OVERVIEW => //
Type "//" to leave the help facility and return back to the main
SQLMON prompt.
Want to know more about the SQLMON on-line help
facility?
Overview session screen
SQLMONITOR OVERVIEW => s /* From the current subsystem */
SQLMONITOR LOCK => /o s /* From another subsystem */
This screen identifies all sessions connected to the DBEnvironment.
We again see that there are 3 sessions (SESSIONS) connected to the DBE, but
now more information is available about each session. The CID (connection id)
uniquely identifies each database process. The PIN is the operating system
process identification number of the session. Multiple CIDs may exist for a
single PIN (for example, in ISQL it is possible to have multiple connections
to the same DBEnvironment. Each connection has a unique CID, but all of the
connections would have the same PIN).
STATUS is the status of the internal (DBCORE) function call that is in
progress for the session. Possible values are "Run", "Wait",
and "Idle". Sessions whose status is "Wait" are highlighted to
graphically flag the amount of waiting in the DBEnvironment. XID is a
system-generated identifier for the transaction that is active for the
session. Smaller values indicate older transactions. In addition the
isolation level (ISO) and priority (PRI) of the transaction are always
displayed. In an SQL program, each transaction can be uniquely identfied
if the LABEL clause is used on the BEGIN WORK or the
SET TRANSACTION statement.
SQLMON displays the label of the transaction that the session is currently
executing, which can be useful for debugging.
SQLMONITOR OVERVIEW => help tune
No additional tuning suggestions are available for this screen.
Overview program screen
SQLMONITOR OVERVIEW => p /* From the current subsystem */
SQLMONITOR LOCK => /o p /* From another subsystem */
This screen groups together all sessions running the same program. This
information can help you determine if a performance problem is related to a
particular program, as opposed to simply related to a particular session.
SQLMONITOR OVERVIEW => help tune
No additional tuning suggestions are available for this screen.
Page last updated on November 29, 1995
|
Printable version
