Thread DB2 Connect Statement Attributes

Use the DB2 Connect Statement attributes to create situations to identify any statement with long elapsed time.

Ace Address The DB2 thread ACE address for internal use.

Application Creator The authorization ID of the user who precompiled the application. You can use this counter to identify the SQL statement that is processing, in conjunction with the CREATOR column of the package section information in the catalogs.

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 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.

Authorization ID The authorization ID.

Authid (Unicode) The authorization ID.

Blocking Cursor Indicates whether the statement being executed is using a blocking cursor. Using blocking for data transfer for a query can improve its performance. The SQL used for a query can affect the use of blocking and might require some modification.

Corrid The correlation ID.

Thread DB2 Connect Statement Attributes Identifies the connection of an application to a DB2 system.

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

DB2 ID The DB2 subsystem ID.

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

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 a DCS application, it is the sum of the elapsed times for all the statements that were executed for a particular application or database. 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 Network statistic - Outbound number of bytes sent and Network statistic - Outbound bytes received counters to calculate the outbound response time (transfer rate):
(outbound number of bytes sent + outbound bytes received) / host response time

Inbound Number of Bytes Received The number of bytes received 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 Number of Bytes Sent Shows the number of bytes 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.

IP Address The current IP address.

Local: System CPU Time The total system CPU time, in seconds and microseconds, used by the statement that is currently executing.

Together with the other related CPU-time counters, it can help you understand the level of activity within an application and identify applications that could benefit from additional tuning. This counter includes time spent on SQL and non-SQL statements and on any fenced user-defined functions (UDF) or stored procedures executed by the application. System CPU represents the time spent in system calls. User CPU represents time spent executing database manager code.
Note: If this information is not available for your operating system, this counter is set to 0.

Local: User CPU Time The total user CPU time, in seconds and microseconds, used by the currently executing statement. Together with the other related CPU-time counters, it can help you understand the level of activity within an application and identify applications that could benefit from additional tuning. This counter includes time spent on SQL and non-SQL statements and on any fenced user-defined functions (UDF) or stored procedures executed by the application. System CPU represents the time spent in system calls. User CPU represents time spent executing database manager code.

Most Recent Statement Elapsed Time The elapsed execution time of the most recently completed statement. Use this counter as an indicator of the time that it takes for a statement 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 Successful Fetches For statement snapshot monitoring and the statement event type, this is the number of successful fetches performed on a specific cursor. For DCS statement snapshot monitoring, this is the number of attempted physical fetches during the execution of a statement regardless of how many rows were fetched by the application. That is, this counter shows the number of times the server needed to send a reply data back to the gateway while processing a statement. You can use this counter to gain insight into the current level of activity within the database manager.

Outbound Application ID (Internal) 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.

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.

Outbound Blocking Cursor Indicates whether blocking is used for data transfer from the DRDA server to the DB2 Connect gateway for a particular query. Using blocking for data transfer for a query can improve its performance. The SQL used for a query can affect the use of blocking and might require some modification.

Outbound Bytes Received The number of bytes 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.
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 Number of Bytes Sent The number of bytes sent 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. Use this counter to measure the throughput from the host databases to the DB2 Connect gateway.

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.

Package Name The name of the package that contains the SQL statement that is currently executing. You can use this counter to identify the application program and the SQL statement that is executing.

Plan The plan name.

Query Cost Estimate The estimated cost, in timerons, for a query, as determined by the SQL compiler. It allows correlation of actual run-time with the compile-time estimates.

In addition, it returns information for the following SQL statements when you are monitoring DB2 Connect.
  • PREPARE represents the relative cost of the prepared SQL statement.
  • FETCH contains the length of the row retrieved if the DRDA server is DB2 for OS/400.
If information is not collected for a DRDA server, this counter is set to zero.
Note: If the DRDA server is DB2 for OS/390 and z/OS, this estimate could be higher than 2**32 - 1 (the maximum integer number that can be expressed through an unsigned long variable). In that case, the value returned by the monitor for this counter is 2**32 - 1.

Query Number of Rows Estimate The estimated number of rows that is returned by a query.

This estimate by the SQL compiler can be compared with the run-time actuals. This counter also returns information for the following SQL statements when you are monitoring DB2 Connect.
  • INSERT, UPDATE, and DELETE indicate the number of rows affected.
  • PREPARE estimates the number of rows that are returned if the DRDA server is DB2 Universal Database, DB2 for VM and VSE, or DB2 for OS/400.
  • FETCH sets to the number of rows fetched if the DRDA server is DB2 for OS/400.
If information is not collected for a DRDA server, this counter is set to zero.

Section Number The internal section number in the package for the SQL statement that is currently processing or has processed most recently.

For a static SQL, you can use this counter together with the creator, package version, and package name to query the SYSCAT.STATEMENTS system catalog table and obtain the static SQL statement text, using the sample query as follows:
SELECT SEQNO, SUBSTR(TEXT,1,120)
FROM SYSCAT.STATEMENTS
WHERE PKGNAME = 'package_name' AND
PKGSCHEMA = 'creator' AND
VERSION = 'package_version_id' AND
SECTNO = section_number
ORDER BY SEQNO
Note: This query can cause lock contentions. Therefore, try to use it only when there is little other activity against the database.

Server Instance Name The name of the database manager instance for which the snapshot was taken.

Statement Operation The statement operation that is currently being processed or has processed most recently (if none is currently running).

You can use this counter to determine the operation that is executing or recently finished. It can be one of the following.
  • For SQL operations:
    • SELECT
    • PREPARE
    • EXECUTE
    • EXECUTE IMMEDIATE
    • OPEN
    • FETCH
    • CLOSE
    • DESCRIBE
    • STATIC COMMIT
    • STATIC ROLLBACK
    • FREE LOCATOR
    • PREP_COMMIT
    • CALL
    • PREP_OPEN
    • PREP_EXEC
    • COMPILE
  • For non-SQL operations:
    • RUN STATISTICS
    • REORG
    • REBIND
    • REDISTRIBUTE
    • GET TABLE AUTHORIZATION
    • GET ADMINISTRATIVE AUTHORIZATION
Note: API users should refer to the sqlmon.h header file containing definitions of database system monitor constants.

Statement Start Timestamp The date and time at which the statement operation started executing. You can use this counter with the SQL statements - Statement operation counter to calculate the elapsed statement operation execution time.

Statement Stop Timestamp The date and time at which the statement operation stopped executing. You can use this counter with the SQL statements - Statement operation counter to calculate the elapsed statement operation execution time.

Elapsed Execution 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 Times - 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.

No. of Statements 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.

No. 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.

Time Spent on Gateway Processing 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 because of DB2 Connect gateway processing.

Time Stamp The start time of this interval.

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

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