The following elements provide information about databases and their related applications.
Snapshot Level Application Lock DCS Application |
Logical Data Grouping appl_id_info appl_lock dcs_appl_info |
Monitor Switch Basic Basic Basic |
Resettable |
No |
|
Event Type Connection Statement |
Logical Data Grouping connheader_event stmt_event subsection_event |
|
Element Name Element Type |
agent_id information | |
| Related Information |
| |
Description: A system-wide unique ID for the application. On multi-node systems, where a database is partitioned, this ID will be the same on every node where the application may make a secondary connection.
Usage: The application handle can be used to uniquely identify an active application (application handle is synonymous with agent Id).
| Note: | The Application Handle (agent ID) data element has different behavior depending on your version of DB2. When taking snapshots from DB2 with version SQLM_DBMON_VERSION1 or SQLM_DBMON_VERSION2 to a DB2 Universal Database (Version 5 or greater) database, the agent_id returned is not usable as an application identifier, rather it is the agent_pid of the agent serving the application. In these cases an agent_id is still returned for back-level compatibility, but internally the DB2 Universal Database server will not recognize the value as an agent_id. |
This value can be used as input to GET SNAPSHOT commands that require an agent Id.
When reading event traces, it can be used to match event records with a given application.
It can also be used as input to the FORCE APPLICATION command or API. On multi-node systems this command can be issued from any node where the application has a connection. Its effect is global
Snapshot Level Application Lock |
Logical Data Grouping appl_id_info appl_lock |
Monitor Switch Basic Basic |
Resettable |
No |
|
Element Name Element Type |
appl_status information | |
| Related Information |
| |
Description: The current status of the application.
Usage: This element can help you diagnose potential
application problems. Values for this field are:
| API Constant | Description |
|---|---|
| SQLM_CONNECTPEND | Database Connect Pending: The application has initiated a database connection but the request has not yet completed. |
| SQLM_CONNECTED | Database Connect Completed: The application has initiated a database connection and the request has completed. |
| SQLM_UOWEXEC | Unit of Work Executing: The database manager is executing requests on behalf of the unit of work. |
| SQLM_UOWWAIT | Unit of Work waiting: The database manager is waiting on behalf of the unit of work in the application. This status typically means that the system is executing in the application's code. |
| SQLM_LOCKWAIT | Lock Wait: The unit of work is waiting for a lock. After the lock is granted, the status is restored to its previous value. |
| SQLM_COMMIT_ACT | Commit Active: The unit of work is committing its database changes. |
| SQLM_ROLLBACK_ACT | Rollback Active: The unit of work is rolling back its database changes. |
| SQLM_RECOMP | Recompiling: The database manager is recompiling (that is, rebinding) a plan on behalf of the application. |
| SQLM_COMP | Compiling: The database manager is compiling an SQL statement or precompiling a plan on behalf of the application. |
| SQLM_INTR | Request Interrupted: An interrupt of a request is in progress. |
| SQLM_DISCONNECTPEND | Database Disconnect Pending: The application has initiated a database disconnect but the command has not yet completed executing. The application may not have explicitly executed the database disconnect command. The database manager will disconnect from a database if the application ends without disconnecting. |
| SQLM_TPREP | Transaction Prepared: The unit of work is part of a global transaction that has entered the prepared phase of the two-phase commit protocol. |
| SQLM_THCOMT | Transaction Heuristically Committed: The unit of work is part of a global transaction that has been heuristically committed. |
| SQLM_THABRT | Transaction Heuristically Rolled Back: The unit of work is part of a global transaction that has been heuristically rolled-back. |
| SQLM_TEND | Transaction Ended: The unit of work is part of a global transaction that has ended but has not yet entered the prepared phase of the two-phase commit protocol. |
| SQLM_CREATE_DB | Creating Database: The agent has initiated a request to create a database and that request has not yet completed. |
| SQLM_RESTART | Restarting Database: The application is restarting a database in order to perform crash recovery. |
| SQLM_RESTORE | Restoring Database: The application is restoring a backup image to the database. |
| SQLM_BACKUP | Backing Up Database: The application is performing a backup of the database. |
| SQLM_LOAD | Data Fast Load: The application is performing a "fast load" of data into the database. |
| SQLM_UNLOAD | Data Fast Unload: The application is performing a "fast unload" of data from the database. |
| SQLM_IOERROR_WAIT | Wait to Disable Table space: The application has detected an I/O error and is attempting to disable a particular table space. The application has to wait for all other active transactions on the table space to complete before it can disable the table space. |
| SQLM_QUIESCE_TABLESPACE | Quiescing a Table space: The application is performing a quiesce table space request. |
| SQLM_WAITFOR_REMOTE | Wait for Remote Node: The application is waiting for a response from a remote node in a partitioned database instance. |
Snapshot Level Application Lock DCS Application |
Logical Data Grouping appl_id_info appl_lock dcs_appl_info |
Monitor Switch Basic Basic Basic |
Resettable |
No |
|
Event Type Event Log Header Connection |
Logical Data Grouping event_log_header connheader_event |
|
Element Name Element Type |
codepage_id information | |
| Related Information |
| |
Description: The code page identifier.
Usage: For snapshot monitor data, this is the code page at the node where the monitored application started. This identifier may be used for problem determination for remote applications. You may use this information to ensure that data conversion is supported between the application code page and the database code page (or for DRDA host databases, the host CCSID). For information about supported code pages, see the Administration Guide.
For event monitor data, this is the code page of the database for which event data is collected. You can use this element to determine whether your event monitor application is running under a different code page from that used by the database. Data written by the event monitor uses the database code page. If your event monitor application uses a different code page, you may need to perform some character conversion to make the data readable.
Snapshot Level Application Lock DCS Application |
Logical Data Grouping appl_id_info appl_lock dcs_appl_info |
Monitor Switch Unit of Work Unit of Work Unit of Work |
Resettable |
No |
|
Element Name Element Type |
status_change_time timestamp | |
| Related Information |
| |
Description: The date and time the application entered its current status.
Usage: This element allows you to determine how long an application has been in its current status. If it has been in the same status for a long period of time, this may indicate that it has a problem.
Snapshot Level Database |
Logical Data Grouping dbase |
Monitor Switch Basic |
Resettable |
No |
|
Element Name Element Type |
appl_id_oldest_xact information | |
| Related Information |
| |
Description: The application ID (which corresponds to the agent_id value from the application snapshot) of the application that has the oldest transaction.
Usage: This element can help you determine which application has the oldest active transaction and is therefore holding the most log space in the database. This application can be forced to free up log space. You should examine the application to determine if it could be modified to commit more frequently.
There are times when there is not a transaction holding up logging, or the oldest transaction does not have an application ID (for example, indoubt transaction or inactive transaction). In these cases, this application's ID is not returned in the data stream.
Snapshot Level Application Lock DCS Application |
Logical Data Grouping appl_id_info appl_lock dcs_appl_info |
Monitor Switch Basic Basic Basic |
Resettable |
No |
|
Event Type Connection |
Logical Data Grouping connheader_event |
|
Element Name Element Type |
appl_name information | |
| Related Information |
| |
Description: The name of the application running at the client as known to the database manager or DB2 Connect.
Usage: This element may be used with "Application ID" to relate data items with your application.
In a client/server environment, this name is passed from the client to the server to establish the database connection. For DRDA-AS connections, this name is the DRDA external name.
The application name is not available for applications running on the following down-level database client products:
In situations where the client application code page is different from the code page under which the database system monitor is running, you can use "ID of Code Page Used by Application" to help translate Application Name.
Snapshot Level Application DCS Application Lock |
Logical Data Grouping appl_id_info dcs_appl_info appl_lock |
Monitor Switch Basic Basic Basic |
Resettable |
No |
|
Event Type Connection Statement Transaction Deadlock |
Logical Data Grouping conn_event connheader_event stmt_event xaction_event dlconn_event |
|
Element Name Element Type |
appl_id information | |
| Related Information |
| |
Description: This identifier is generated when the application connects to the database at the database manager or when DDCS receives a request to connect to a DRDA database.
Usage: This ID is known on both the client and server, so you can use it to correlate the client and server parts of the application. For DDCS applications, you will also need to use "Outbound Application ID" to correlate the client and server parts of the application.
This identifier is unique across the network. There are different formats for the application ID, which are dependent on the communication protocol between the client and the server machine on which the database manager and/or DDCS are running. Each of the formats consists of three parts separated by periods.
Use "Client Communication Protocol" to determine which communications protocol the connection is using and, as a result, the format of the application ID.
Snapshot Level Application DCS Application |
Logical Data Grouping appl_id_info dcs_appl_info |
Monitor Switch Basic Basic |
Resettable |
No |
|
Event Type Connection Statement Transaction Deadlock |
Logical Data Grouping conn_event connheader_event stmt_event xaction_event dlconn_event |
|
Element Name Element Type |
sequence_no information | |
| Related Information |
| |
Description: This element is reserved for future use. In this release, its value always be "0001". It may contain different values in future releases of the product.
Snapshot Level Application Lock DCS Application |
Logical Data Grouping appl_id_info appl_lock dcs_appl_info |
Monitor Switch Basic Basic Basic |
Resettable |
No |
|
Event Type Connection |
Logical Data Grouping connheader_event |
|
Element Name Element Type |
auth_id information | |
| Related Information |
| |
Description: The authorization ID of the user who invoked the application that is being monitored. On a DB2 Connect gateway node, this is the user's authorization ID on the host.
Usage: You can use this element to determine who invoked the application.
Snapshot Level Application DCS Application |
Logical Data Grouping appl_id_info dcs_appl_info |
Monitor Switch Basic Basic |
Resettable |
No |
|
Event Type Connection |
Logical Data Grouping connheader_event |
|
Element Name Element Type |
client_nname information | |
| Related Information |
| |
Description: The nname in the database manager configuration file at the client node.
Usage: You can use this element to identify the client node that is running the application.
Snapshot Level Application DCS Application |
Logical Data Grouping appl_id_info dcs_appl_info |
Monitor Switch Basic Basic |
Resettable |
No |
|
Event Type Connection |
Logical Data Grouping connheader_event |
|
Element Name Element Type |
client_prdid information | |
| Related Information |
| |
Description: The product and version that is running on the client.
Usage: You can use this element to identify the product and code version of the database client. It is in the form PPPVVRRM, where:
Snapshot Level Application Lock |
Logical Data Grouping appl_id_info appl_lock |
Monitor Switch Basic Basic |
Resettable |
No |
|
Event Type Connection |
Logical Data Grouping connheader_event |
|
Element Name Element Type |
client_db_alias information | |
| Related Information |
| |
Description: The alias of the database provided by the application to connect to the database.
Usage: This element can be used to identify the actual database that the application is accessing. The mapping between this name and "Database Name" could be done by using the database directories at the client node and the database manager server node.
This is the alias defined within the database manager where the database connection request originated.
This element can also be used to help you determine the authentication type, since different database aliases can have different authentication types.
Snapshot Level DCS Application |
Logical Data Grouping dcs_appl_info |
Monitor Switch Basic |
Resettable |
No |
|
Element Name Element Type |
host_prdid information | |
| Related Information |
| |
Description: The product and version that is running on the server.
Usage: Used to identify the product and code version of the DRDA host database product. It is in the form PPPVVRRM, where:
Snapshot Level DCS Application |
Logical Data Grouping dcs_appl_info |
Monitor Switch Basic |
Resettable |
No |
|
Element Name Element Type |
outbound_appl_id information | |
| Related Information |
| |
Description: This identifier is generated when the application connects to the DRDA host database. It is used to connect the DB2 Connect gateway to the host, while the "Application ID" is used to connect a client to the DB2 Connect gateway.
Usage: You may use this element in conjunction with "Application ID" to correlate the client and server parts of the application information.
This identifier is unique across the network.
Snapshot Level DCS Application |
Logical Data Grouping dcs_appl_info |
Monitor Switch Basic |
Resettable |
No |
|
Element Name Element Type |
outbound_sequence_no information | |
| Related Information |
| |
Description: This element is reserved for future use. In this release, its value will always be "0001". It may contain different values in future releases of the product.
Snapshot Level Application DCS Application |
Logical Data Grouping appl_info appl dcs_appl_info |
Monitor Switch Basic Basic Basic |
Resettable |
No |
|
Event Type Connection |
Logical Data Grouping connheader_event |
|
Element Name Element Type |
execution_id information | |
| Related Information |
| |
Description: The ID that the user specified when logging in to the operating system. This ID is distinct from "Authorization ID", which the user specifies when connecting to the database.
Usage: You can use this element to determine the operating system userid of the individual running the application that you are monitoring.
Snapshot Level Application |
Logical Data Grouping appl_info appl |
Monitor Switch Basic Basic |
Resettable |
No |
|
Event Type Connection |
Logical Data Grouping connheader_event |
|
Element Name Element Type |
corr_token information | |
| Related Information |
| |
Description: The DRDA AS correlation token.
Usage: The DRDA correlation token is used for correlating the processing between the application server and the application requester. It is an identifier dumped into logs when errors arise, that you can use to identify the conversation that is in error. In some cases, it will be the LUWID of the conversation.
If communications are not using DRDA, this element is blank.
If you are using the database system monitor APIs, note that the API constant SQLM_APPLID_SZ is used to define the length of this element.
Snapshot Level Application DCS Application |
Logical Data Grouping appl_info appl dcs_appl_info |
Monitor Switch Basic Basic Basic |
Resettable |
No |
|
Event Type Connection |
Logical Data Grouping connheader_event |
|
Element Name Element Type |
client_pid information | |
| Related Information |
| |
Description: The process ID of the client application that made the connection to the database.
Usage: You can use this element to correlate monitor information such as CPU and I/O time to your client application.
In the case of a DRDA AS connection, this element will be set to 0.
Snapshot Level Application DCS Application |
Logical Data Grouping appl_info appl dcs_appl_info |
Monitor Switch Basic Basic Basic |
Resettable |
No |
|
Event Type Connection |
Logical Data Grouping connheader_event |
|
Element Name Element Type |
client_platform information | |
| Related Information |
| |
Description: The operating system on which the client application is running.
Usage: This element can be used for problem determination for remote applications. Values for this field can be found in the header file sqlmon.h.
Snapshot Level Application DCS Application |
Logical Data Grouping appl_info appl dcs_appl_info |
Monitor Switch Basic Basic Basic |
Resettable |
No |
|
Event Type Connection |
Logical Data Grouping connheader_event |
|
Element Name Element Type |
client_protocol information | |
| Related Information |
| |
Description: The communication protocol that the client application is using to communicate with the server.
Usage: This element can be used for problem determination for remote applications. Values for this field are:
Notes:
Snapshot Level Application |
Logical Data Grouping appl_info appl |
Monitor Switch Basic Basic |
Resettable |
No |
|
Event Type Event Log Header Connection |
Logical Data Grouping event_log_header connheader_event |
|
Element Name Element Type |
country_code information | |
| Related Information |
| |
Description: The country code of the database for which the monitor data is collected.
Usage: Country code information is recorded in the database configuration file (see the Administration Guide).
For DRDA AS connections, this element will be set to 0.
Snapshot Level Application |
Logical Data Grouping appl |
Monitor Switch Basic |
Resettable |
No |
|
Event Type Connection |
Logical Data Grouping conn_event |
|
Element Name Element Type |
appl_priority information | |
| Related Information |
| |
Description: The priority of the agents working for this application.
Usage: You can use this element to check if applications are running with the expected priorities. Application priorities can be set by an administrator. They can be changed by the governor utility (db2gov).
The governor is used by DB2 to monitor and change the behavior of applications running against a database. This information is used to schedule applications and balance system resources.
A governor daemon collects statistics about the applications by taking snapshots. It checks these statistics against the rules governing applications running on that database. If the governor detects a rule violation, it takes the appropriate action. These rules and actions were specified by you in the governor configuration file.
If the action associated with a rule is to change an application's priority, the governor changes the priority of the agents in the partition where the violation was detected.
See the Administration Guide for more information on the governor.
Snapshot Level Application |
Logical Data Grouping appl |
Monitor Switch Basic |
Resettable |
No |
|
Event Type Connection |
Logical Data Grouping conn_event |
|
Element Name Element Type |
appl_priority_type information | |
| Related Information |
| |
Description: Operating system priority type for the agent working on behalf of the application.
Usage: Dynamic priority is recalculated by the operating system based on usage. Static priority does not change.
Snapshot Level Application |
Logical Data Grouping appl appl_info |
Monitor Switch Basic Basic |
Resettable |
No |
|
Event Type Connection |
Logical Data Grouping conn_event |
|
Element Name Element Type |
authority_lvl information | |
| Related Information |
| |
Description: The highest authority level granted to an application.
Usage: The operations allowed by an application are granted either directly or indirectly in the sql.h.
These are the authorizations granted explicitly to a user:
The following are indirect authorizations inherited from group or public:
See the Administration Guide for detailed information on authority levels.
Snapshot Level Database Manager Table Space |
Logical Data Grouping collected fcm_node rollforward |
Monitor Switch Basic Basic Basic |
Resettable |
No |
|
Event Type Connection Overflow |
Logical Data Grouping connheader_event overflow_event |
|
Element Name Element Type |
node_number information | |
| Related Information |
| |
Description: The number assigned to the node in the db2nodes.cfg file.
Usage: This value identifies the current node number, which can be used when monitoring multiple nodes.
Snapshot Level Application |
Logical Data Grouping appl |
Monitor Switch Basic |
Resettable |
No |
|
Event Type Connection |
Logical Data Grouping conn_event |
|
Element Name Element Type |
coord_node information | |
| Related Information |
| |
Description: In a multi-node system, the node number of the node where the application connected or attached to the instance.
Usage: Each connected application is served by one coordinator node.
Snapshot Level Application |
Logical Data Grouping appl |
Monitor Switch Basic |
Resettable |
No |
|
Element Name Element Type |
appl_con_time timestamp | |
| Related Information |
| |
Description: The date and time that an application started a connection request.
Usage: Use this element to determine when the application started its connection request to the database.
Snapshot Level Database |
Logical Data Grouping dbase |
Monitor Switch Basic |
Resettable |
No |
|
Event Type Database |
Logical Data Grouping db_event |
|
Element Name Element Type |
connections_top water mark | |
| Related Information |
| |
Description: The highest number of simultaneous connections to the database since the database was activated.
Usage: You may use this element to evaluate the setting of the maxappls configuration parameter, which is described in the Administration Guide.
If the value of this element is the same as the maxappls parameter, it is likely that some database connection requests were rejected, since maxappls limits the number of database connections allowed.
The current number of connections at the time the snapshot was taken can be calculated using the following formula:
remote connections to database manager + local connections
Snapshot Level Application |
Logical Data Grouping appl |
Monitor Switch Basic |
Resettable |
No |
|
Element Name Element Type |
conn_complete_time timestamp | |
| Related Information |
| |
Description: The date and time that a connection request was granted.
Usage: Use this element to determine when a connection request to the database was granted.
Snapshot Level Application DCS Application |
Logical Data Grouping appl dcs_appl |
Monitor Switch Unit of Work Unit of Work |
Resettable |
No |
|
Element Name Element Type |
prev_uow_stop_time timestamp | |
| Related Information |
| |
Description: This is the time the unit of work completed.
Usage: You may use this element with "Unit of Work Stop Timestamp" to calculate the total elapsed time between COMMIT/ROLLBACK points, and with "Unit of Work Start Timestamp" to calculate the time spent in the application between units of work. The time of one of the following:
Snapshot Level Application DCS Application |
Logical Data Grouping appl dcs_appl |
Monitor Switch Unit of Work Unit of Work |
Resettable |
No |
|
Element Name Element Type |
uow_start_time timestamp | |
| Related Information |
| |
Description: The date and time that the unit of work first required database resources.
Usage: This resource requirement occurs at the first SQL statement execution of that unit of work:
| Note: | The SQL Reference defines the boundaries of a unit of work as the COMMIT or ROLLBACK points. |
The database system monitor excludes the time spent between the COMMIT/ROLLBACK and the next SQL statement from its definition of a unit of work. This measurement method reflects the time spent by the database manager in processing database requests, separate from time spent in application logic before the first SQL statement of that unit of work. The unit of work elapsed time does include the time spent running application logic between SQL statements within the unit of work.
You may use this element with "Unit of Work Stop Timestamp" to calculate the total elapsed time of the unit of work and with "Previous Unit of Work Completion Timestamp" to calculate the time spent in the application between units of work.
You can use the "Unit of Work Stop Timestamp" and the "Previous Unit of Work Completion Timestamp" to calculate the elapsed time for the SQL Reference's definition of a unit of work.
Snapshot Level Application DCS Application |
Logical Data Grouping appl dcs_appl |
Monitor Switch Unit of Work Unit of Work |
Resettable |
No |
|
Element Name Element Type |
uow_stop_time timestamp | |
| Related Information |
| |
Description: The date and time that the most recent unit of work completed, which occurs when database changes are committed or rolled back.
Usage: You may use this element with "Previous Unit of Work Completion Timestamp" to calculate the total elapsed time between COMMIT/ROLLBACK points, and with "Unit of Work Start Timestamp" to calculate the elapsed time of the latest unit of work.
The timestamp contents will be set as follows:
As a new unit of work is started, the contents of this element are moved to "Previous Unit of Work Completion Timestamp".
Snapshot Level Unit of Work DCS Unit of Work |
Logical Data Grouping appl dcs_appl |
Monitor Switch Unit of Work Unit of Work |
Resettable |
No | |
Element Name Element Type |
uow_elapsed_time time | |
| Related Information |
| |
Description: The elapsed execution time of the most recently completed unit of work.
Usage: Use this element as an indicator of the time it takes for units of work to complete.
Snapshot Level Application DCS Application |
Logical Data Grouping appl dcs_appl |
Monitor Switch Unit of Work Unit of Work |
Resettable |
No |
|
Event Type Transaction |
Logical Data Grouping xaction_event |
|
Element Name Element Type |
uow_comp_status information | |
| Related Information |
| |
Description: The status of the unit of work and how it stopped.
Usage: You may use this element to determine if the unit of work ended due to a deadlock or abnormal termination. It may have been:
| Note: | API users should refer to the header file (sqlmon.h) containing definitions of database system monitor constants. |
Event Type Transaction |
Logical Data Grouping xaction_event |
|
Element Name Element Type |
uow_status information | |
| Related Information |
| |
Description: The status of the unit of work.
Usage: You may use this element to determine the status of a unit of work.
Event Type Transaction |
Logical Data Grouping xaction_event |
|
Element Name Element Type |
prev_stop_time timestamp | |
| Related Information |
| |
Description: The completion time of the last unit of work.
Usage: You may use this element to calculate the time spent in the application between units of work.
This is the unit of work that completed prior to the unit of work for which this transaction event is generated.
For applications within their first unit of work, this is the database connection request completion time.
Snapshot Level Application DCS Application/lines> |
Logical Data Grouping appl dcs_appl |
Monitor Switch Statement Statement |
Resettable |
No |
|
Element Name Element Type |
appl_idle_time information | |
| Related Information |
| |
Description: Number of seconds since an application has issued any requests to the server. This includes applications that have not terminated a transaction, for example not issued a commit or rollback.
Usage: This information can be used to implement applications that force users that have been idle for a specified number of seconds.
The following database system monitor elements provide information about agents:
Snapshot Level Application |
Logical Data Grouping agent |
Monitor Switch Statement |
Resettable |
No |
|
Element Name Element Type |
agent_pid information | |
| Related Information |
| |
Description: The process Id (UNIX systems) or thread Id (OS/2 or Windows systems) of a DB2 agent.
Usage: You can use this element to link database system monitor information to other sources of diagnostic information, such as system traces. You can also use it to monitor how agents working for a database application use system resources.
Snapshot Level Application |
Logical Data Grouping appl_info |
Monitor Switch Basic |
Resettable |
No |
|
Element Name Element Type |
coord_agent_pid information | |
| Related Information |
| |
Description: The process Id (UNIX systems) or thread Id (OS/2 or Windows systems) of the coordinator agent for the application.
Usage: You can use this element to link database system monitor information to other sources of diagnostic information, such as system traces.