System Monitor Guide and Reference

Application Identification and Status

The following elements provide information about databases and their related applications.

• Application Handle (agent ID)
• Application Status
• ID of Code Page Used by Application
• Application Status Change Time
• Application with Oldest Transaction
• Node with Least Available Log Space
• Application Name
• Application ID
• Sequence Number
• Authorization ID
• Configuration NNAME of Client
• Client Product/Version ID
• Database Alias Used by Application
• Host Product/Version ID
• Outbound Application ID
• Outbound Sequence Number
• User Login ID
• DRDA Correlation Token
• Client Process ID
• Client Operating Platform
• Client Communication Protocol
• Database Country Code
• Application Agent Priority
• Application Priority Type
• User Authorization Level
• Node Number
• Coordinating Node
• Connection Request Start Timestamp
• Maximum Number of Concurrent Connections
• Connection Request Completion Timestamp
• Previous Unit of Work Completion Timestamp
• Unit of Work Start Timestamp
• Unit of Work Stop Timestamp
• Most Recent Unit of Work Elapsed Time
• Unit of Work Completion Status
• Previous Transaction Stop Time
• Application Idle Time

Application Handle (agent ID)

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

Application Status

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.

ID of Code Page Used by Application

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.

Application Status Change Time

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.

Application with Oldest Transaction

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.

Node with Least Available Log Space

Description:  This element is only returned for global snapshots and indicates the node with the least amount (in bytes) of available log space.

Usage:  Use this element, in conjunction with Application with Oldest Transaction, to ensure that adequate log space is available for the database. In a global snapshot, Application with Oldest Transaction, Total Log Space Used, and Total Log Available correspond to the values on this node.

Application Name

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:

• IBM Extended Services for OS/2

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.

Application ID

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.

1. APPC

   Format

   Network.LU Name.Application instance

   Example

   CAIBMTOR.OSFDBX0.930131194520

   Details

   This application ID is the displayable format of an actual SNA LUWID (Logical Unit-of-Work ID) that flows on the network when an APPC conversation is allocated. APPC-generated application IDs are made up by concatenating the network name, the LU name, and the LUWID instance number, which create a unique label for the client/server application. The network name and LU name can each be a maximum of 8 characters. The application instance corresponds to the 12-decimal-character LUWID instance number.

2. TCP/IP

   Format

   *TCPIP.IPAddr.Port.Application instance

   Example

   *TCPIP.A12CF9E8.3F0A.930131214645

   Details

   A TCP/IP-generated application ID is made up by concatenating the string "*TCPIP", the IP address in hexadecimal characters, the port number (4 hexadecimal characters), and a unique identifier for the instance of this application. The IP address is a 32-bit number displayed as a maximum of 8 hexadecimal characters.

3. IPX/SPX

   Format

   Netid.nodeid.Application instance

   Example

   C11A8E5C.400011528250.0131214645

   Details

   An IPX/SPX-generated application ID is made up by concatenating a character network ID (8 hexadecimal characters), a node id (12 hexadecimal characters), and a unique identifier for the instance of the application. The application instance corresponds to a 10-decimal-character time stamp of the form MMDDHHMMSS.

4. NetBIOS

   Format

   *NETBIOS.nname.Application instance

   Example

   *NETBIOS.SBOIVIN.930131214645

   Details

   A NetBIOS application ID is made up by concatenating the string "*NETBIOS", the nname defined in the client's database configuration file, and a unique identifier for the instance of this application.

5. Local Applications

   Format

   *LOCAL.DB2 instance.Application instance

   Example

   *LOCAL.DB2INST1.930131235945

   Details

   The application ID generated for a local application is made up by concatenating the string *LOCAL, the name of the DB2 instance, and a unique identifier for the instance of this application.

Use "Client Communication Protocol" to determine which communications protocol the connection is using and, as a result, the format of the application ID.

Sequence Number

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.

Authorization ID

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.

Configuration NNAME of Client

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.

Client Product/Version ID

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:

• PPP identifies the product, which is "SQL" for the DB2 products
• VV identifies a 2-digit version number (with high-order 0 in the case of a 1-digit version)
• RR identifies a 2-digit release number (with high-order 0 in the case of a 1-digit release)
• M identifies a 1-digit modification level.

Database Alias Used by Application

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.

Host Product/Version ID

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:

• PPP identifies the host DRDA product
  • ARI for DB2 for VSE & VM
  • DSN for DB2 for MVS/ESA
  • QSQ for DB2 Universal Database for AS/400
  • SQL for other DB2 products.
• VV identifies a 2-digit version number (with high-order 0 in the case of a 1-digit version)
• RR identifies a 2-digit release number (with high-order 0 in the case of a 1-digit release)
• M identifies a 1-digit modification level

Outbound Application ID

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.

Format

Network.LU Name.Application instance

Example

CAIBMTOR.OSFDBM0.930131194520

Details

This application ID is the displayable format of an actual SNA LUWID (Logical Unit-of-Work ID) that flows on the network when an APPC conversation is allocated. APPC-generated application IDs are made up by concatenating the network name, the LU name, and the LUWID instance number, which creates a unique label for the client/server application. The network name and LU name can each be a maximum of 8 characters. The application instance corresponds to the 12-decimal-character LUWID instance number.

Outbound Sequence Number

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.

User Login ID

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.

DRDA Correlation Token

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 returns the appl_id (see Application ID).

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.

Client Process ID

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.

Client Operating Platform

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.

Client Communication Protocol

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:

API Constant

Communication Protocol

SQLM_PROT_UNKNOWN

(note 1)

SQLM_PROT_LOCAL

none (note 2)

SQLM_PROT_APPC

APPC

SQLM_PROT_TCPIP

TCP/IP

SQLM_PROT_IPXSPX

IPX/SPX

SQLM_PROT_NETBIOS

NETBIOS

Notes:

1. The client is communicating using an unknown protocol. This value will only be returned if future clients connect with a down-level server.

2. The client is running on the same node as the server and no communications protocol is in use.

Database Country Code

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.

Application Agent Priority

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.

Application Priority Type

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.

User Authorization Level

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:

• SQL_SYSADMIN
• SQL_DBADM
• SQL_CREATETAB
• SQL_BINDADD
• SQL_CONNECT
• SQL_CREATE_NOT_FENC
• SQL_SYSCTRL
• SQL_SYSMAINT

The following are indirect authorizations inherited from group or public:

• SQL_SYSADM_GRP
• SQL_DBADM_GRP
• SQL_CREATETAB_GRP
• SQL_BINDADD_GRP
• SQL_CONNECT_GRP
• SQL_CREATE_NOT_FENC_GRP
• SQL_SYSCTRL_GRP
• SQL_SYSMAINT_GRP

See the Administration Guide for detailed information on authority levels.

Node Number

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.

Coordinating Node

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.

Connection Request Start Timestamp

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.

Maximum Number of Concurrent Connections

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 

Connection Request Completion Timestamp

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.

Previous Unit of Work Completion Timestamp

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:

• For applications currently within a unit of work, this is the time that the latest unit of work completed.
• For applications not currently within a unit of work (the application has completed a unit of work, but not yet started a new one), this is the stop time of the last unit of work that completed prior to the one that just completed. The stop time of the one just completed is indicated Unit of Work Stop Timestamp.
• For applications within their first unit of work, this is the database connection request completion time.

Unit of Work Start Timestamp

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:

• For the first unit of work, it is the time of the first database request (SQL statement execution) after "Connection Request Completion Timestamp".
• For subsequent units of work, it is the time of the first database request (SQL statement execution) after the previous COMMIT or ROLLBACK.

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.

Unit of Work Stop Timestamp

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:

• When the application has completed a unit of work and has not yet started a new one (as defined in "Unit of Work Start Timestamp"). this element will be a valid, non-zero timestamp
• When the application is currently executing a unit of work, this element will contain zeros
• When the application first connects to the database, this element is set to "Connection Request Completion Timestamp".

As a new unit of work is started, the contents of this element are moved to "Previous Unit of Work Completion Timestamp".

Most Recent Unit of Work Elapsed Time

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.

Unit of Work Completion Status

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:

• Committed due to a commit statement
• Rolled back due to a rollback statement
• Rolled back due to a deadlock
• Rolled back due to an abnormal termination
• Committed at normal application termination.
• Unknown as a result of a FLUSH EVENT MONITOR command for which units of work were in progress.

Unit of Work Status

Description:  The status of the unit of work.

Usage:  You may use this element to determine the status of a unit of work.

Previous Transaction Stop Time

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.

Application Idle Time

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.

DB2 Agent Information

The following database system monitor elements provide information about agents:

• Process or Thread ID
• Coordinator Agent

Process or Thread ID

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.

Coordinator Agent

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.