The following elements provide information about databases and their related applications.
Snapshot Information Level Application Lock DCS Application |
API Structure(s) sqlm_appl_id_info sqlm_appl_lock sqlm_dcs_applinfo |
Monitor Switch Basic Basic Basic |
Resettable |
No |
|
Event Type Connection Statement |
Event Record(s) sqlm_connheader_event sqlm_stmt_event sqlm_subsection_event |
|
API 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 name agent_id is still used in the APIs, so that old applications can still be compiled with the Version 5 header files. |
It 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 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 Information Level Application Lock |
API Structure(s) sqlm_appl_id_info sqlm_appl_lock |
Monitor Switch Basic Basic |
Resettable |
No |
|
API 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. |
Snapshot Information Level Application Lock DCS Application |
API Structure(s) sqlm_appl_id_info sqlm_appl_lock sqlm_dcs_applinfo |
Monitor Switch Basic Basic Basic |
Resettable |
No |
|
Event Type Event Log Header Connection |
Event Record(s) sqlm_event_log_header sqlm_connheader_event |
|
API 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 Information Level Application Lock |
API Structure(s) sqlm_appl_id_info sqlm_appl_lock |
Monitor Switch Unit of Work Unit of Work |
Resettable |
No |
|
API 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 Information Level Application Lock DCS Application |
API Structure(s) sqlm_appl_id_info sqlm_appl_lock sqlm_dcs_applinfo |
Monitor Switch Basic Basic Basic |
Resettable |
No |
|
Event Type Connection |
Event Record(s) sqlm_connheader_event |
|
API 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 DDCS.
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.
| Note: | Although the name of the application that is executing could be more than 20 bytes, only the first 20 bytes after the last path separator are used to set this element. |
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 Information Level Application DCS Application Lock |
API Structure(s) sqlm_appl_id_info sqlm_dcs_applinfo sqlm_appl_lock |
Monitor Switch Basic Basic Basic |
Resettable |
No |
|
Event Type Connection Statement Transaction Deadlock |
Event Record(s) sqlm_conn_event sqlm_connheader_event sqlm_stmt_event sqlm_xaction_event sqlm_dlconn_event |
|
API 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 Information Level Application DCS Application |
API Structure(s) sqlm_appl_id_info sqlm_dcs_applinfo |
Monitor Switch Basic Basic |
Resettable |
No |
|
Event Type Connection Statement Transaction Deadlock |
Event Record(s) sqlm_conn_event sqlm_connheader_event sqlm_stmt_event sqlm_xaction_event sqlm_dlconn_event |
|
API 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 Information Level Application Lock DCS Application |
API Structure(s) sqlm_appl_id_info sqlm_appl_lock sqlm_dcs_applinfo |
Monitor Switch Basic Basic Basic |
Resettable |
No |
|
Event Type Connection |
Event Record(s) sqlm_connheader_event |
|
API 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 DDCS gateway node, this is the user's authorization ID on the host.
Usage: You can use this element to determine who is performing the monitoring, and to uniquely identify the application that is running.
If you are using the database system monitor APIs, note that the API constant SQLM_IDENT_SZ is used to define the length of this element. Only the first 8 characters are currently used.
Snapshot Information Level Application DCS Application |
API Structure(s) sqlm_appl_id_info sqlm_dcs_applinfo |
Monitor Switch Basic Basic |
Resettable |
No |
|
Event Type Connection |
Event Record(s) sqlm_connheader_event |
|
API 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.
If you are using the database system monitor APIs, note that the API constant SQLM_IDENT_SZ is used to define the length of this element. Only the first 8 characters are currently used.
Snapshot Information Level Application DCS Application |
API Structure(s) sqlm_appl_id_info sqlm_dcs_applinfo |
Monitor Switch Basic Basic |
Resettable |
No |
|
Event Type Connection |
Event Record(s) sqlm_connheader_event |
|
API 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:
If you are using the database system monitor APIs, note that the API constant SQLM_IDENT_SZ is used to define the length of this element. Only the first 8 characters are currently used.
Snapshot Information Level Application Lock DCS Application |
API Structure(s) sqlm_appl_id_info sqlm_appl_lock sqlm_dcs_applinfo |
Monitor Switch Basic Basic Basic |
Resettable |
No |
|
Event Type Connection |
Event Record(s) sqlm_connheader_event |
|
API 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.
If you are using the database system monitor APIs, note that the API constant SQLM_IDENT_SZ is used to define the length of this element. Only the first 8 characters are currently used.
This element can also be used to help you determine the authentication type, since different database aliases can have different authentication types.
Snapshot Information Level DCS Application |
API Structure(s) sqlm_dcs_applinfo |
Monitor Switch Basic |
Resettable |
No |
|
API 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:
This field is defined as SQLM_IDENT_SZ characters long to allow for future expansion, but only the first 8 characters are presently used.
Snapshot Information Level DCS Application |
API Structure(s) sqlm_dcs_applinfo |
Monitor Switch Basic |
Resettable |
No |
|
API 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 DDCS gateway to the host, while the "Application ID" is used to connect a client to the DDCS 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 Information Level DCS Application |
API Structure(s) sqlm_dcs_applinfo |
Monitor Switch Basic |
Resettable |
No |
|
API 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 Information Level Application |
API Structure(s) sqlm_applinfo sqlm_appl |
Monitor Switch Basic Basic |
Resettable |
No |
|
Event Type Connection |
Event Record(s) sqlm_connheader_event |
|
API 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 who is running the application that you are monitoring.
For operating systems such as OS/2 that do not support the concept of "logging in", this field will be the same as "Authorization ID".
If you are using the database system monitor APIs, note that the API constant SQLM_IDENT_SZ is used to define the length of this element. Only the first 8 characters are currently used.
Snapshot Information Level Application |
API Structure(s) sqlm_applinfo sqlm_appl |
Monitor Switch Basic Basic |
Resettable |
No |
|
Event Type Connection |
Event Record(s) sqlm_connheader_event |
|
API 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 Information Level Application |
API Structure(s) sqlm_applinfo sqlm_appl |
Monitor Switch Basic Basic |
Resettable |
No |
|
Event Type Connection |
Event Record(s) sqlm_connheader_event |
|
API 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 Information Level Application |
API Structure(s) sqlm_applinfo sqlm_appl |
Monitor Switch Basic Basic |
Resettable |
No |
|
Event Type Connection |
Event Record(s) sqlm_connheader_event |
|
API 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 Information Level Application |
API Structure(s) sqlm_applinfo sqlm_appl |
Monitor Switch Basic Basic |
Resettable |
No |
|
Event Type Connection |
Event Record(s) sqlm_connheader_event |
|
API 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 Information Level Application |
API Structure(s) sqlm_applinfo sqlm_appl |
Monitor Switch Basic Basic |
Resettable |
No |
|
Event Type Event Log Header Connection |
Event Record(s) sqlm_event_log_header sqlm_connheader_event |
|
API Element Name Element Type |
country_code information | |
| Related Information |
| |
Description: The country code of the database for which the monitor data is collected. 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 Information Level Application |
API Structure(s) sqlm_appl |
Monitor Switch Basic |
Resettable |
No |
|
Event Type Connection |
Event Record(s) sqlm_conn_event |
|
API 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 Information Level Application |
API Structure(s) sqlm_appl |
Monitor Switch Basic |
Resettable |
No |
|
Event Type Connection |
Event Record(s) sqlm_conn_event |
|
API Element Name Element Type |
appl_priority_type information | |
| Related Information |
| |
Description: Operating system priority for the application that is running.
Usage: Dynamic priority is recalculated by the operating system based on usage. Static priority does not change.
Snapshot Information Level Application |
API Structure(s) sqlm_appl sqlm_applinfo |
Monitor Switch Basic Basic |
Resettable |
No |
|
Event Type Connection |
Event Record(s) sqlm_conn_event |
|
API 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 Information Level Application |
API Structure(s) sqlm_appl |
Monitor Switch Basic |
Resettable |
No |
|
Event Type Connection |
Event Record(s) sqlm_conn_event |
|
API 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 Information Level Application |
API Structure(s) sqlm_appl |
Monitor Switch Basic |
Resettable |
No |
|
API 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 Information Level Application |
API Structure(s) sqlm_appl |
Monitor Switch Basic |
Resettable |
No |
|
API 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 Information Level Application |
API Structure(s) sqlm_appl |
Monitor Switch Unit of Work |
Resettable |
No |
|
API 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 Information Level Application |
API Structure(s) sqlm_appl |
Monitor Switch Unit of Work |
Resettable |
No |
|
API 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 Information Level Application |
API Structure(s) sqlm_appl |
Monitor Switch Unit of Work |
Resettable |
No |
|
API 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 Information Level Application |
API Structure(s) sqlm_appl |
Monitor Switch Unit of Work |
Resettable |
No |
|
Event Type Transaction |
Event Record(s) sqlm_xaction_event |
|
API Element Name Element Type |
uow_comp_status (Snapshot) status (Event) 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 |
Event Record(s) sqlm_xaction_event |
|
API 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 Information Level Application |
API Structure(s) sqlm_appl |
Monitor Switch Statement |
Resettable |
No |
|
API 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 Information Level Application |
API Structure(s) sqlm_appl sqlm_agent |
Monitor Switch Basic Statement |
Resettable |
No |
|
API Element Name Element Type |
coord_agent_pid 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.