Alerts
An alert is an action, such as a beep or message, that is generated by the Performance Monitor when the value of a performance variable reaches a warning or alarm threshold value.
Information about alerts is automatically logged in the Alert Center and on the Alerts page of the Journal notebook.
The application ID is a unique string that is generated when the application connects to the database, or when DB2 Connect receives a request to connect to a DRDA (Distributed Relational Database Architecture) database.
The name of the application running on the client that identifies it to the database manager or DB2 Connect. It is passed from the client to the server to establish the database connection. The application name is the first SQLM_IDENT_SZ bytes of the application program file name, after the last path separator.
A character string that designates a set of privileges. It is used by the database manager for authorization checking and as an implicit qualifier for the names of objects such as tables, views, and indexes.
A configuration parameter provides limits to settings of available resources for a database instance or a database. For example, the configuration parameter maximum number of concurrently active databases (numdb) specifies the number of local databases that can be concurrently active.
The performance monitor uses only configuration parameters that have a numeric value so they can be used in performance variable equations. It automatically refreshes configuration parameters at certain times. You can change configuration parameters at any time, but:
For more information on configuration parameters, refer to the Administration Guide.
When you want to use a database, you issue a CONNECT statement to get a connection. The database grants the connection after it authenticates your access and allocates resources to handle subsequent requests. The connection is active from the time the connection is granted, until the time you disconnect using a CONNECT RESET command.
The performance monitor provides information about database objects at a specific point in time. It returns two types of value elements:
A counter represents information that is cumulative up until the sample is taken. It counts values that increase, such as the number of deadlocks. A gauge indicates the current value for an item. Gauge elements can go up or down depending on database activity (for example, the number of locks held).
Counters are reset to zero when you stop and restart a database instance or a database.
To obtain count information only for the duration of the test, reset the counters. For example, if you are running iterative tests, obtain monitor information for each iteration by resetting the counters at the beginning of each iteration. This provides more meaningful information.
Refer to System Monitor Guide and Reference for more discussion on counters.
Database objects in performance monitor
In the Performance Monitor a database object is anything associated with a database that can be monitored.
The database objects that can be monitored are:
Instance
Node
Database
Database partition
Table
Table partition
Table space
Table space partition
Database connection (a single connection)
Database connection partition
These database objects belong to a database object hierarchy of parent/child relationships.
The database object hierarchy arranges database objects into parent/child relationships. For example, databases are the children of their database instance parent. Tables, table spaces, and connections are the children of the database.
The database system monitor is the component of the DB2 server that gathers performance information about database instances and databases using application programming interfaces (APIs) and event files. The Performance Monitor provides a graphical interface to the information gathered by the database system monitor.
The DB2 engine manages information-gathering by the database system monitor. Information gathering can be started and stopped at different times depending on the type of information being collected.
Some basic monitoring information will always be collected, but you can be selective when determining what other statistical information should be recorded. This flexibility is important because monitoring increases overhead, which can have an impact on performance.
A data element is a single attribute that is used to collect database performance information. Data elements are classified as:
Data element information is collected by the database system monitor using its snapshot and event monitoring capabilities.
In the database manager, contention for locks by processes using the database can result in deadlocks.
A deadlock involves two or more processes, each waiting for a resource that another has locked. In a deadlock, both processes are suspended until the deadlocks are handled in the database manager by the deadlock detector. The detector is periodically activated in the background to determine if there is a deadlock situation. When a deadlock condition is detected, the detector activates one of the processes and forces it to perform a rollback, and the other process proceeds.
Connections that are involved in a deadlock are called deadlocked connections.
Using Event Analyzer, you can select a particular monitored period (from the Monitored Periods View), or a specific deadlock event (from the Deadlocks View) and then view detailed information about the deadlocked connections using the Deadlocked Connections View window.
Deadlocks in the lock system are handled in the database manager by an asynchronous system background process called the deadlock detector.
The deadlock detector becomes active periodically as determined by the dlchktime configuration parameter. When active, it examines the lock system for deadlocks. If it finds one, it selects a deadlocked process to roll back. The database manager rolls back the selected process automatically. When the rollback is complete, the locks belonging to that process are released, and then the other processes involved in the deadlock can continue.
Selecting the proper interval for the deadlock detector ensures good performance. Too short an interval causes unnecessary overhead, and too long an interval allows a deadlock to delay processes unnecessarily.
For more information, refer to the System Monitor Guide and Reference.
The performance variable file, db2pmlst.prf, comes with the supplied default DB2 performance monitors. It contains a comprehensive set of performance variables for you to use in your monitoring sessions. If this file is erased, the Performance Monitor will recreate it at start up.
A sequence of characters enclosed within quotation marks ("). The sequence must consist of a letter followed by zero or more characters, each of which is a letter, digit, or the underscore character.
The event analyzer provides information about database events that have taken place. An event analyzer is used in conjunction with an event monitor file. For example, you can use an event monitor to record information on database activities, such as connections, transactions, statements, and deadlocks. After the Event Monitor has created its file, you can look at the collected performance information using the event analyzer.
An event monitor collects information on database activities over a period of time. This collected information provides a good summary of the activity for a particular database event: for example, a database connection or an SQL statement. Event monitoring records the state of the database at the time specific events occur. If there is a series of events, such as a sequence of SQL statements, an event monitor can record information as each occurs, in this case, at the beginning and end of each statement. This information can be used to determine how well a database activity was performed.
This differs from a performance monitor, which provides performance information on the DB2 database manager and its database applications at a single point in time. Event Monitor data is collected for a single database, while performance monitor data is collected at the database manager and database levels.
IBM-supplied performance variables
The IBM-supplied performance variables are included in the Performance Monitor. They provide a comprehensive set of performance calculations. The name, equation, and text description of an IBM-supplied performance variable cannot be changed. You can, however, change the threshold values and the alert actions.
For information on the IBM-supplied performance variables, refer to the Performance Variable Reference from the Help pull-down menu.
The
icon appears beside the node number in the first column of the Nodes
view. This icon changes color to indicate the state of the node.
The icon color indicators are:
If performance monitoring is started, the icons change color to indicate if any performance variable associated with the node has reached a warning or alarm threshold value. The color always reflects the most severe alert within a group of performance variables.
The cell color indicators are:
| Note: | The node states are also reflected in the status of the instance to which the
nodes belong. That is, the color of the instance's icon,
, in the Control Center, will contain one or more of the colors described
above.
|
The amount of information produced by an event monitor can be quite extensive. You can direct the output to files and analyze the data at a later date.
You can choose between blocked and non-blocked file I/O. If file I/O is being blocked and both of the event monitor I/O buffers are full, database agents will wait for the Event Monitor to write one of its buffers, if required, before attempting to write new data to the buffer.
In the case of non-blocked I/O, database agents will not wait for the event monitor to write an I/O buffer, and if both event monitor buffers are full when the event occurs, the database agent will discard the event data. This is known as an overflow.
When a buffer overflow occurs, the monitor creates overflow event records. The Event Monitor is not turned off, but monitor data is lost. If there are outstanding overflow event records when the monitor is deactivated, a diagnostic message will be logged. Otherwise, the overflow event records will be written to the file when possible.
In Event Analyzer, the overflow event records written to the file can be seen in the Overflows View.
In SQL, a letter which might be followed by zero or more characters, each of which is a letter (a-z and A-Z), a symbol, a number, or the underscore character, used to form a name.
The Performance Monitor provides a graphical interface to the information gathered by the database system monitor. It takes a sample giving you information for a specific point in time. This information provides a picture of the current state of the activity in the database instance.
You can use performance variables to keep track of the performance of a database and its environment. The value of a performance variable is calculated from performance data obtained from the database system monitor.
The equation used to calculate the value of a performance variable can include data elements and configuration parameters from a database instance and its databases, other performance variables, and built-in functions.
Performance Monitor has the following performance variable types:
Performance variables can contain other performance variables and data elements as long as they refer to the same type of database object or belong to the database object's parent in the database object hierarchy. For example, a table performance variable can contain other table performance variables and data elements in addition to those from its parent database or database instance.
For more information on performance variables, refer to the Performance Variable Reference in the Help pull-down menu.
The IBM-supplied variables belong to the following categories:
A category associates each performance variable with a category of variables.
When the database system monitor takes a snapshot, it gathers data elements for a database object at a point in time. The Performance Monitor receives the sampled information from the database system monitor and calculates the performance variables.
The data elements that are returned in a performance snapshot are determined by which monitor switches are turned on. The Performance Monitor automatically turns on monitor switches for all data elements currently used in performance variables.
A portion of an access plan that can be executed concurrently with other subsections. Subsections can occur in access plans generated in a partitioned database environment.
A statement refers to an SQL statement.
A transaction (unit of work) is a sequence of recoverable operations on a database that must conclude successfully before being committed to the database. The sequence is terminated by either a commit or rollback operation.