Db2 Connect Server at Thread Level Overview Attributes

Use the Db2 Connect Server at Thread Level attributes to create situations to monitor connected gateways of a Db2 subsystem.

Ace Address The Db2 thread ACE address for internal use.

Application Agent ID The system-wide unique ID for the application. On a single-partitioned database, this ID consists of a 16-bit counter. On a multi-partitioned database, it consists of the coordinating partition number concatenated with a 16-bit counter. In addition, it is the same on every partition where the application might make a secondary connection.

Application ID The ID that is generated when the application connects to the database at the database manager or when Distributed Database Connection Services (DDCS) receives a request to connect to a DRDA database.

It is known at both the client and the 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 the Outbound Application ID to correlate the client and server parts of the application. This ID 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, the DDCS, or both, are running. Each of the formats consists of three parts separated by periods.
Protocol Format Example Details
APPC Network.Logical Unit Name.Application instance CAIBMTOR.OSFDBX0. 930131194520 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 Logical Unit name, and the LUWID instance number, which create a unique label for the client/server application. The network name and Logical Unit name can each be a maximum of 8 characters. The application instance corresponds to the 12-decimal-character LUWID instance number.
TCP/IP IPAddr.Port.Application instance A12CF9E8.3F0A. 930131214645 A TCP/IP-generated application ID is made up by concatenating the IP address in hexadecimal characters, the port number (4 hexadecimal characters), and a unique ID for the instance of this application. The IP address is a 32-bit number displayed as a maximum of 8 hexadecimal characters.
IPX/SPX Netid.nodeid.Application instance C11A8E5C.400011528250. 0131214645 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 ID for the instance of the application.

The application instance corresponds to a 10-decimal-character timestamp of the form mmddhhmmss, where: M = Month, D = Day, H = Hour, M = Minute, S = Second.
NetBIOS *NETBIOS.nname. Application instance *NETBIOS.SBOIVIN. 930131214645 For nonpartitioned database systems, a NetBIOS application ID is made up by concatenating the string *NETBIOS, the NNAME defined in the database configuration file for the client, and a unique ID for the instance of this application. For partitioned database systems, a NetBIOS application ID is made up by concatenating the string N xxx.etc where xxx is the partition the application is attached to.
Local applications *LOCAL.DB2 instance.Application instance *LOCAL.DB2INST1. 930131235945 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 ID for the instance of this application.

Application Idle Time The number of seconds since an application issued any requests to the server. This includes applications that have not terminated a transaction, for example, not issued a commit or rollback. You can use this information to implement applications that force users that have been idle for a specified number of seconds.

Application Name The name of the application running at the client as known to the database manager or Db2 Connect. Together with the Application ID counter, it can be used to relate data items with your application.

Application Status Change Time The date and time the application entered its current status. This counter 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 can indicate a problem.

Authorization ID Shows the authorization ID of the user who invoked the application that is being monitored. On a Db2 Connect gateway node, this is the authorization ID of the user on the host.

Authorization ID The authorization ID.

Authid (Unicode) The authorization ID.

Client Communication Protocol The communication protocol that the client application is using to communicate with the server. You can use this counter for problem determination on remote applications.

Valid values for this counter 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
Note: The client is communicating using an unknown protocol. This value is only returned if future clients connect with a down-level server. The client is running on the same node as the server and no communications protocol is in use.

Client Operating Platform The operating system on which the client application is running. You can use this counter for problem determination on remote applications.

Client Process ID The process ID of the client application that made the connection to the database. You can use this counter to correlate monitor information such as CPU and I/O time to your client application. In the case of a DRDA-AS connection, this counter is set to 0.

Client Product Version ID The product and version that is running on the client.

You can use this counter to identify the product and code version of the database client. It is in the form pppvvrrm, where:
  • ppp stands for SQL
  • 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

Code Page Used by Application The code page ID. For snapshot monitor data, this is the code page at the partition where the monitored application started. This ID can be used for problem determination for remote applications. You can 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 coded character set ID (CCSID). For event monitor data, this is the code page of the database for which event data is collected. You can use this counter 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 might need to perform some character conversion to make the data readable.

Commit Statements Attempted The total number of SQL COMMIT statements that have been attempted.

A small change rate in this counter during the monitor period can indicate that applications do not commit frequently, which can lead to problems with logging and data concurrency. You can also use this counter to calculate the total number of units of work by calculating the sum of the following:
(Commit statements attempted) + (Internal commits) + (Rollback statements attempted) + (Internal rollbacks)

Configuration Name of Client The NNAME in the database manager configuration file at the client node. You can use this counter to identify the client node that is running the application.

Db2 Connect Server at Thread Level Overview Attributes Identifies the connection of an application to a Db2 system.

It is an alphanumeric text string, with a maximum length of eight characters.

Corrid The correlation ID.

Database Alias at the Gateway The alias used at the Db2 Connect gateway to connect to the host database. Use this counter for problem determination on DCS applications.

Db2 Connect First Connect The date and time at which the first connection to the host database was initiated from the Db2 Connect gateway. Use this counter for problem determination on DCS applications.

Db2 ID The Db2 subsystem ID.

DCS Application Status The current status of the application. It can help you diagnose potential application problems.

DCS Database Name The name of the remote database as cataloged in the DCS directory. Use this counter for problem determination on DCS applications.

Elapsed Time DB2CONN Execution Shows the time, in seconds and microseconds, at the Db2 Connect gateway to process an application request (since the connection was established), or to process a single statement. Use this counter to determine what portion of the overall processing time is due to Db2 Connect gateway processing.

Failed Statement Percentage The number of statements that were attempted, but failed.

Failed Statements Operations The number of SQL statements that were attempted, but failed.

You can use this counter to calculate the total number of successful SQL statements at the database or application level:
(Dynamic SQL statements attempted) + (Static SQL statements attempted) - (Failed statement operations) = throughput during monitoring period
This count includes all SQL statements that received a negative SQLCODE. This counter might also help you determine the reasons for poor performance because failed statements mean time wasted by the database manager and, as a result, lower throughput for the database.

Gateway Snapshot Time The date and time at which the database system monitor information was collected.

Host Coded Character Set ID The coded character set ID (CCSID) of the host database. Use this counter for problem determination on DCS applications.

Host Database Name The real name of the host database for which information is being collected or to which the application is connected. This is the name that was given to the database when it was created. Use this counter for problem determination on DCS applications.

Host Product Version ID The product and version that is running on the server.

This counter is 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 OS/390 and z/OS
    • QSQ for Db2 UDB 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

Inbound Bytes Received The number of bytes received (measured in 1000-byte increments) by the Db2 Connect gateway from the client, excluding communication protocol overhead (for example, TCP/IP or SNA headers). Use this counter to measure the throughput from the client to the Db2 Connect gateway.

Inbound Communication Address The communication address of the client. For example, it could be an SNA net ID and Logical Unit partner name, or an IP address and port number for TCP/IP. Use this counter for problem determination on DCS applications.

IP Address The current IP address.

Last Reset Timestamp The date and time that the monitor counters were reset for the application issuing the GET SNAPSHOT. You can use this counter to determine the scope of information returned by the database system monitor. If the database manager counters have never been reset, the value for this counter is zero. The database manager counters is only reset if you reset all active databases.

Most Recent Unit of Work Elapsed Time The elapsed execution time of the most recently completed unit of work. It indicates time that it takes for units of work to complete.

MVSID The MVS system identifier.

Name The name in the database manager configuration file at the client node. It identifies the client node that is running the application.

Number of Open Cursors The number of cursors currently open for an application. Use this counter to assess how much memory is being allocated. The amount of memory allocated by the Db2 client, Db2 Connect, or the database agent on the target database is related to the number of cursors that are currently open. Knowing this information can help with capacity planning. For example, each open cursor that is blocking has a buffer size as specified by the RQRIOBLK database manager configuration parameter. If DEFERRED_PREPARE is enabled, two buffers are allocated.

Number of SQL Statements Attempted The number of SQL statements that have been attempted since the latter of: application startup, database activation, or last reset. For a data transmission, this is the number of SQL statements that have been attempted against this DCS database or in this DCS application since the database was activated, the connection to it was established by the application, or RESET MONITOR was issued against the database, and that used this number of data transmissions between the Db2 Connect gateway and the host during statement processing. Use this counter to measure the database activity for a database or application. To calculate the SQL statement throughput for a given period, you can divide this counter by the elapsed time between two snapshots. For a data transmission, use this counter to get statistics on how many statements used two, three, four, etc. data transmissions during their processing. At least two data transmissions are necessary to process a statement: a send and a receive. These statistics can give you a better idea of the database or application activity and network traffic for a database or an application.

Number of Transmissions The number of data transmissions between the Db2 Connect gateway and the host that was used to process this DCS statement. (One data transmission consists of one send or one receive.)

Use this counter to get a better understanding of the reasons why a particular statement took longer to execute. For example, a query returning a large result set might need many data transmissions to complete.

Originating System ID The managed system name of the agent. It is an alphanumeric text string, with a maximum of 32 characters; for example, DB91:SYS1:DB2.

Outbound Application ID This ID 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.

You can use this counter in conjunction with the Application ID counter to correlate the client and server parts of the application information. This ID is unique across the network.
Format Example Details
Network.Logical Unit Name.Application instance CAIBMTOR.OSFDBM0.930131194520 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 Logical Unit name, and the LUWID instance number, which creates a unique label for the client/server application. The network name and Logical Unit name can each be a maximum of 8 characters. The application instance corresponds to the 12-decimal-character LUWID instance number.

Outbound Application ID Internal The interval for the Outbound Application ID.

Outbound Communication Address The communication address of the target database. For example, it could be an SNA net ID and Logical Unit partner name, or an IP address and port number for TCP/IP. Use this counter for problem determination on DCS applications.

Outbound Communication Protocol The communication protocol used between the Db2 Connect gateway and the host. Use this counter for problem determination on DCS applications.

Valid values are:
  • SQLM_PROT_APPC
  • SQLM_PROT_TCPIP

Outbound Sequence Number Reserved for future use. In this release, its value will is always 0001. It can contain different values in future releases of the product.

Previous Unit of Work Completion Timestamp The time when the previous unit of work was completed.

You can use this counter with the Overall transaction data - Unit of work stop timestamp counter to calculate the total elapsed time between COMMIT or ROLLBACK points, and with the Overall transaction data - Unit of work start timestamp counter to calculate the time spent in the application between units of work:
  • For applications currently within a unit of work, this is the time at which 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 by the Overall transaction data - Unit of work stop timestamp counter.
  • For applications within their first unit of work, this is the database connection request completion time.

Plan The plan name.

Rollback Statements Attempted The total number of SQL ROLLBACK statements that have been attempted.

A ROLLBACK can result from an application request, a DEADLOCK, or an error situation. This counter only includes the number of ROLLBACK statements issued from applications. For an application, this counter can help you determine the level of database activity for the application and the amount of conflict with other applications. For a database, it can help you determine the amount of activity in the database and the amount of conflict between applications running on the database.
Note: Try to minimize the number of rollbacks because higher rollback activity results in lower throughput for the database.
You can also use this counter to calculate the total number of units of work, by calculating the sum of the following:
(Commit statements attempted) + (Internal commits) + (Rollback statements attempted) + (Internal rollbacks)

Row Selected The number of rows that have been selected and returned to the application.

You can use this counter to gain insight into the current level of activity within the database. This counter does not include a count of rows read for actions, such as COUNT(*) or joins. For a federated system you can calculate the average time to return a row to the federated server from the data source:
average time = rows returned / aggregate query response time
You can use these results to modify CPU speed or communication speed parameters in =SYSCAT.SERVERS. Modifying these parameters can impact whether the optimizer does or does not send requests to the data source.

Sequence Number Is incremented whenever a unit of work ends, that is, when a COMMIT or ROLLBACK terminates a unit of work.

Together with the Application ID counter, this counter uniquely identifies a transaction.

Server Instance Name The name of the database manager instance for which the snapshot was taken. If a system contains more than one instance of the database manager, this name is used to uniquely identify the instance for which the snapshot call was issued. Along with configuration NNAME at monitoring (server) node, this information can be useful if you are saving your monitor output in a file or database for later analysis, and you need to differentiate the data from different instances of the database manager.

Time Stamp The start time of this interval.

Transaction ID The unique transaction ID across all databases generated by a transaction manager in a two-phase commit transaction.

You can use this ID to correlate the transaction generated by the transaction manager with the transactions executed against multiple databases. In addition, it can help you diagnose transaction manager problems by tying database transactions that involve a two-phase commit protocol with the transactions that are originated by the transaction manager.

Total Inbound Bytes Sent The number of bytes (measured in 1000-byte increments) sent by the Db2 Connect gateway to the client, excluding communication protocol overhead, for example, TCP/IP or SNA headers. Use this counter to measure the throughput from the Db2 Connect gateway to the client.

Total Host Response Time For a DCS statement, this is the elapsed time between the time that the statement was sent from the Db2 Connect gateway to the host for processing and the time when the result was received from the host.

For a DCS database or DCS application, it is the sum of the elapsed times for all the statements that were executed for a particular database or application. For a data transmission, this is the sum of host response times for all the statements that used this many data transmissions. Use this counter with the Overall transaction data - Total outbound bytes sent and Overall transaction data - Total out bytes received counters to calculate the outbound response time (transfer rate):
((Total outbound bytes sent) + (Total out bytes received)) / Total host response time

Total Outbound Bytes Received The number of bytes (measured in 1000-byte increments) received by the Db2 Connect gateway from the host, excluding communication protocol overhead, for example, TCP/IP or SNA headers. For a data transmission, this is the number of bytes received by the Db2 Connect gateway from the host during the processing of all the statements that used this number of data transmissions. Use this counter to measure the throughput from the host databases to the Db2 Connect gateway.

Total Outbound Bytes Sent The number of bytes sent (measured in 1000-byte increments) by the Db2 Connect gateway to the host, excluding communication protocol overhead, for example, TCP/IP or SNA headers. For a data transmission, this is the number of bytes sent by the Db2 Connect gateway to the host during the processing of all the statements that used this number of data transmissions. Use this counter to measure the throughput from the Db2 Connect gateway to the host database.

Total Statement Execution Elapsed Time For a DCS statement, this is the elapsed time spent processing an SQL request on a host database server. This value is reported by this server. In contrast to the Overall transaction data - Total host response time counter, this counter does not include the network elapsed time between Db2 Connect and the host database server. At other levels, this value represents the sum of the host execution times for all the statements that were executed for a particular database or application, or for those statements that used a given number of data transmissions. Use this counter, along with other elapsed time monitor elements, to evaluate the processing of SQL requests by the database server and to help isolate performance issues. Subtract the value for this counter from the value for the Overall transaction data - Total host response time counter to calculate the network elapsed time between Db2 Connect and the host database server.

Uniqueness Value The instance number: When concatenated with the fully qualified network name, it uniquely identifies a distributed thread (Field name: QWHSLUUV).

Uniqueness Value 2 The Db2 thread uniqueness value. For internal use.

Unit of Work Completion Status The status of the unit of work and how it stopped.

You can use this counter to determine if the unit of work ended due to a DEADLOCK or abnormal termination. It can be:
  • 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
Note: API users should refer to the sqlmon.h header file containing definitions of database system monitor constants.

Unit of Work Start Timestamp The date and time at which the unit of work first required database resources.

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 the date and time that a connection request was granted.
  • 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 the time spent in the application before the first SQL statement of that unit of work. The unit-of-work elapsed time does include the time spent running the application between SQL statements within the unit of work. You can use this counter with the Overall transaction data - Unit of work stop timestamp counter to calculate the total elapsed time of the unit of work and with the Overall transaction data - Previous Unit of Work completion timestamp counter to calculate the time spent in the application between units of work. You can use the Overall transaction data - Unit of work stop timestamp and Overall transaction data - Previous Unit of Work completion timestamp counters to calculate the elapsed time for a unit of work.

Unit of Work Stop Timestamp The date and time at which the most recent unit of work completed, which occurs when database changes are committed or rolled back.

You can use this counter with the Overall transaction data - Previous Unit of Work completion timestamp counter to calculate the total elapsed time between COMMIT or ROLLBACK points, and with the Overall transaction data - Unit of work start timestamp counter to calculate the elapsed time of the latest unit of work. The timestamp contents are set as follows:
  • When the application has completed a unit of work and has not yet started a new one (as defined by the Overall transaction data - Unit of work start timestamp counter). this counter is a valid, nonzero timestamp
  • When the application is currently executing a unit of work, this counter contains zeros
  • When the application first connects to the database, this counter is set to the connection completion time.
As a new unit of work is started, the contents of this counter are moved to the Overall transaction data - Previous Unit of Work completion timestamp counter.

User Login ID Shows the ID that the user specified when logging in to the operating system. This ID differs from authorization ID, which the user specifies when connecting to the database. You can use this counter to determine the operating system user ID of the individual running the application that you are monitoring.