System Monitor Guide and Reference

DB2 Connect

The following elements provide DB2 Connection information at the database, application, transaction, and statement levels:

"DCS Database Name"
"Host Database Name"
"Database Alias at the Gateway"
"DB2 Connect Gateway First Connect Initiated"
"Maximum Number of Concurrent Connections"
"Total Number of Attempted Connections for DB2 Connect"
"Current Number of Connections for DB2 Connect"
"Number of Connections Waiting for the Host to Reply"
"Number of Connections Waiting for the Client to Send Request"
"Elapsed Time Spent on DB2 Connect Gateway Processing"
"Number of SQL Statements Attempted"
"Number of Open Cursors"
"DCS Application Status"
"DCS Application Agents"
"Host Coded Character Set ID"
"Outbound Communication Protocol"
"Outbound Communication Address"
"Inbound Communication Address"
"Inbound Number of Bytes Received"
"Outbound Number of Bytes Sent"
"Outbound Number of Bytes Received"
"Inbound Number of Bytes Sent"
"Transaction ID"
"Host Response Time"
"Most Recent Response Time for Connect"
"Most Recent Connection Elapsed Time"
"Communication Errors"
"Communication Error Time"
"Blocking Cursor"
"Outbound Blocking Cursor"
"Statement Execution Elapsed Time"

DCS Database Name

Snapshot Level DCS Database DCS Application	Logical Data Grouping dcs_dbase dcs_appl_info	Monitor Switch Basic Basic
Resettable	No
Element Name Element Type	dcs_db_name information
Related Information	"Host Database Name" "Database Alias at the Gateway"

Description: The name of the DCS database as catalogued in the DCS directory.

Usage: Use this element for problem determination on DCS applications.

Host Database Name

Snapshot Level DCS Database DCS Application	Logical Data Grouping dcs_dbase dcs_appl_info	Monitor Switch Basic Basic
Resettable	No
Element Name Element Type	host_db_name information
Related Information	"DCS Database Name" "Database Alias at the Gateway"

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

Usage: Use this element for problem determination on DCS applications.

Database Alias at the Gateway

Snapshot Level DCS Application	Logical Data Grouping dcs_appl_info	Monitor Switch Basic
Resettable	No
Element Name Element Type	gw_db_alias information
Related Information	"DCS Database Name" "Host Database Name"

Description: The alias used at the DB2 Connect gateway to connect to the host database.

Usage: Use this element for problem determination on DCS applications.

DB2 Connect Gateway First Connect Initiated

Snapshot Level DCS Database DCS Application	Logical Data Grouping dcs_dbase dcs_appl	Monitor Switch Basic Basic
Resettable	No
Element Name Element Type	gw_con_time timestamp
Related Information	None

Description: The date and time when the first connection to the host database was initiated from the DB2 Connect gateway.

Usage: Use this element for problem determination on DCS applications.

Maximum Number of Concurrent Connections

Snapshot Level DCS Database	Logical Data Grouping dcs_dbase	Monitor Switch Basic
Resettable	No
Element Name Element Type	gw_connections_top water mark
Related Information	"Total Number of Attempted Connections for DB2 Connect" "Current Number of Connections for DB2 Connect"

Description: The maximum number of concurrent connections to a host database that have been handled by the DB2 Connect gateway since the first database connection.

Usage: This element will help you understand the level of activity at the DB2 Connect gateway and the associated use of system resources.

Total Number of Attempted Connections for DB2 Connect

Snapshot Level Database Manager DCS Database	Logical Data Grouping db2 dcs_dbase	Monitor Switch Basic Basic
Resettable	Yes
Element Name Element Type	gw_total_cons water mark
Related Information	"Maximum Number of Concurrent Connections" "Current Number of Connections for DB2 Connect"

Description: The total number of connections attempted from the DB2 Connect gateway since the last db2start command or the last reset.

Usage: This element will help you understand the level of activity at the DB2 Connect gateway and the associated use of system resources.

Current Number of Connections for DB2 Connect

Snapshot Level Database Manager DCS Database	Logical Data Grouping db2 dcs_dbase	Monitor Switch Basic Basic
Resettable	No
Element Name Element Type	gw_cur_cons gauge
Related Information	"Maximum Number of Concurrent Connections" "Total Number of Attempted Connections for DB2 Connect"

Description: The current number of connections to host databases being handled by the DB2 Connect gateway.

Usage: This element will help you understand the level of activity at the DB2 Connect gateway and the associated use of system resources.

Number of Connections Waiting for the Host to Reply

Snapshot Level Database Manager DCS Database	Logical Data Grouping db2 dcs_dbase	Monitor Switch Basic Basic
Resettable	No
Element Name Element Type	gw_cons_wait_host gauge
Related Information	"Current Number of Connections for DB2 Connect" "Number of Connections Waiting for the Client to Send Request"

Description: The current number of connections to host databases being handled by the DB2 Connect gateway that are waiting for a reply from the host.

Usage: This value can change frequently. It should be sampled at regular intervals over an extended period in order to obtain a realistic view of gateway usage.

Number of Connections Waiting for the Client to Send Request

Snapshot Level Database Manager DCS Database	Logical Data Grouping db2 dcs_dbase	Monitor Switch Basic Basic
Resettable	No
Element Name Element Type	gw_cons_wait_client gauge
Related Information	"Current Number of Connections for DB2 Connect" "Number of Connections Waiting for the Host to Reply"

Description: The current number of connections to host databases being handled by the DB2 Connect gateway that are waiting for the client to send a request.

Usage: This value can change frequently. It should be sampled at regular intervals over an extended period in order to obtain a realistic view of gateway usage.

Elapsed Time Spent on DB2 Connect Gateway Processing

Snapshot Level DCS Application DCS Statement	Logical Data Grouping dcs_appl dcs_stmt	Monitor Switch Statement Statement
Resettable	Yes (at application) No (at other levels)
Element Name Element Type	gw_exec_time time
Related Information	None

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

Usage: Use this element to determine what portion of the overall processing time is due to DB2 Connect gateway processing.

Number of SQL Statements Attempted

Snapshot Level DCS Database DCS Application	Logical Data Grouping dcs_dbase dcs_appl	Monitor Switch Basic Basic
Resettable	Yes
Element Name Element Type	sql_stmts counter
Related Information	Snapshot Time

Description: The number of SQL statements that have been attempted since the latter of: application start up, database activation, or last reset.

Usage: Use this element to measure database activity at the database or application level. To calculate the SQL statement throughput for a given period, you can divide this element by the elapsed time between two snapshots.

Number of Open Cursors

Snapshot Level DCS Application	Logical Data Grouping dcs_appl	Monitor Switch Basic
Resettable	No
Element Name Element Type	open_cursors gauge
Related Information	None

Description: The number of cursors currently open for an application.

Usage: Use this element 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 doing blocking has a buffer size of RQRIOBLK. If deferred_prepare is enabled, then two buffers will be allocated.

DCS Application Status

Snapshot Level DCS Application	Logical Data Grouping dcs_appl_info	Monitor Switch Basic
Resettable	No
Element Name Element Type	dcs_appl_status information
Related Information	"Host Coded Character Set ID" "Outbound Communication Protocol" "Outbound Communication Address" "Inbound Communication Address"

Description: The status of a DCS application at the DB2 Connect gateway.

Usage: Use this element for problem determination on DCS applications. Values are:

SQLM_DCS_CONNECTPEND_OUTBOUND
The application has initiated a database connection from the DB2 Connect gateway to the host database, but the request has not completed yet.
SQLM_DCS_UOWWAIT_OUTBOUND
The DB2 Connect gateway is waiting for the host database to reply to the application's request.
SQLM_DCS_UOWWAIT_INBOUND
The connection from the DB2 Connect gateway to the host database has been established and the gateway is waiting for SQL requests from the application. Or the DB2 Connect gateway is waiting on behalf of the unit of work in the application. This usually means that the application's code is being executed.

DCS Application Agents

Snapshot Level DCS Application	Logical Data Grouping dcs_appl_info	Monitor Switch Basic
Resettable	No
Element Name Element Type	agent_status information
Related Information	"DCS Application Status"

Description: In a connection concentrator environment, this value shows which applications currently have associated agents.

Usage: Values are:

SQLM_AGENT_ASSOCIATED
The agent working on behalf of this application is associated with it.
SQLM_AGENT_NOT_ASSOCIATED
The agent that was working on behalf of this application is no longer associated with it and is being used by another application. The next time work is done for this application without an associated agent, an agent will be re-associated.

Host Coded Character Set ID

Snapshot Level DCS Application	Logical Data Grouping dcs_appl_info	Monitor Switch Basic
Resettable	No
Element Name Element Type	host_ccsid information
Related Information	"DCS Application Status" "Outbound Communication Protocol" "Outbound Communication Address" "Inbound Communication Address"

Description: This is the coded character set identifier (CCSID) of the host database.

Usage: Use this element for problem determination on DCS applications.

Outbound Communication Protocol

Snapshot Level DCS Application	Logical Data Grouping dcs_appl_info	Monitor Switch Basic
Resettable	No
Element Name Element Type	outbound_comm_protocol information
Related Information	"DCS Application Status" "Host Coded Character Set ID" "Outbound Communication Address" "Inbound Communication Address"

Description: The communication protocol used between the DB2 Connect gateway and the host.

Usage: Use this element for problem determination on DCS applications. Valid values are:

SQLM_PROT_APPC
SQLM_PROT_TCPIP

Outbound Communication Address

Snapshot Level Application DCS Application	Logical Data Grouping appl_info dcs_appl_info	Monitor Switch Basic Basic
Resettable	No
Element Name Element Type	outbound_comm_address information
Related Information	"DCS Application Status" "Host Coded Character Set ID" "Outbound Communication Protocol" "Inbound Communication Address"

Description: This is the communication address of the target database. For example, it could be an SNA net ID and LU partner name, or an IP address and port number for TCP/IP.

Usage: Use this element for problem determination on DCS applications.

Inbound Communication Address

Snapshot Level Application DCS Application	Logical Data Grouping appl_info dcs_appl_info	Monitor Switch Basic Basic
Resettable	No
Element Name Element Type	inbound_comm_address information
Related Information	"DCS Application Status" "Host Coded Character Set ID" "Outbound Communication Protocol" "Outbound Communication Address"

Description: This is the communication address of the client. For example, it could be an SNA net ID and LU partner name, or an IP address and port number for TCP/IP.

Usage: Use this element for problem determination on DCS applications.

Inbound Number of Bytes Received

Snapshot Level DCS Application DCS Statement	Logical Data Grouping dcs_appl dcs_stmt	Monitor Switch Basic Statement
Resettable	Yes (at application) No (at other levels)
Element Name Element Type	inbound_bytes_received counter
Related Information	"Outbound Number of Bytes Sent" "Outbound Number of Bytes Received" "Inbound Number of Bytes Sent"

Description: The number of bytes received by the DB2 Connect gateway from the client, excluding communication protocol overhead (for example, TCP/IP or SNA headers).

Usage: Use this element to measure the throughput from the client to the DB2 Connect gateway.

Outbound Number of Bytes Sent

Snapshot Level DCS Database DCS Application DCS Statement	Logical Data Grouping dcs_dbase dcs_appl dcs_stmt	Monitor Switch Basic Basic Statement
Resettable	No (at statement) Yes (at other levels)
Element Name Element Type	outbound_bytes_sent counter
Related Information	"Inbound Number of Bytes Received" "Outbound Number of Bytes Received" "Inbound Number of Bytes Sent"

Description: The number of bytes sent by the DB2 Connect gateway to the host, excluding communication protocol overhead (for example, TCP/IP or SNA headers).

Usage: Use this element to measure the throughput from the DB2 Connect gateway to the host database.

Outbound Number of Bytes Received

Snapshot Level DCS Database DCS Application DCS Statement	Logical Data Grouping dcs_dbase dcs_appl dcs_stmt	Monitor Switch Basic Basic Statement
Resettable	No (at statement) Yes (at other levels)
Element Name Element Type	outbound_bytes_received counter
Related Information	"Inbound Number of Bytes Received" "Outbound Number of Bytes Sent" "Inbound Number of Bytes Sent"

Description: The number of bytes received by the DB2 Connect gateway from the host, excluding communication protocol overhead (for example, TCP/IP or SNA headers).

Usage: Use this element to measure the throughput from the host databases to the DB2 Connect gateway.

Inbound Number of Bytes Sent

Snapshot Level DCS Application DCS Statement	Logical Data Grouping dcs_appl dcs_stmt	Monitor Switch Basic Statement
Resettable	Yes (at application) No (at other levels)
Element Name Element Type	inbound_bytes_sent counter
Related Information	"Inbound Number of Bytes Received" "Outbound Number of Bytes Sent" "Outbound Number of Bytes Received"

Description: The number of bytes sent by the DB2 Connect gateway to the client, excluding communication protocol overhead (for example, TCP/IP or SNA headers).

Usage: Use this element to measure the throughput from the DB2 Connect gateway to the client.

Transaction ID

Snapshot Level DCS Application	Logical Data Grouping dcs_appl	Monitor Switch Unit of Work
Resettable	No
Element Name Element Type	xid information
Related Information	None

Description: A unique transaction identifier (across all databases) generated by a transaction manager in a two-phase commit transaction.

Usage: This identifier can be used to correlate the transaction generated by the transaction manager with the transactions executed against multiple databases. It can be used to help diagnose transaction manager problems by tying database transactions involving a two-phase commit protocol with the transactions originated by the transaction manager.

Host Response Time

Snapshot Level DCS Database DCS Application DCS Statement	Logical Data Grouping dcs_dbase dcs_appl dcs_stmt	Monitor Switch Statement Statement Statement
Resettable	No (at statement) Yes (at other levels)
Element Name Element Type	host_response_time time
Related Information	"Outbound Number of Bytes Received" "Outbound Number of Bytes Sent"

Description: At the DCS statement level, 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. At other levels, it is the sum of the elapsed times for all the statements that were executed for a particular application or database.

Usage: Use this element with Outbound Number of Bytes Sent and Outbound Number of Bytes Received to calculate the outbound response time (transfer rate):

   (outbound bytes sent + outbound bytes received) / host response time

Most Recent Response Time for Connect

Snapshot Level DCS Database	Logical Data Grouping dcs_dbase	Monitor Switch Basic
Resettable	No
Element Name Element Type	con_response_time time
Related Information	"Package Cache Overflows"

Description: The elapsed time between the start of connection processing and actual establishment of a connection, for the most recent DCS application that connected to this database.

Usage: Use this element as an indicator of the time it currently takes applications to connect to a particular host database.

Most Recent Connection Elapsed Time

Snapshot Level DCS Database	Logical Data Grouping dcs_dbase	Monitor Switch Basic
Resettable	No
Element Name Element Type	con_elapsed_time time
Related Information	"Package Cache Overflows"

Description: The elapsed time that the DCS application that most recently disconnected from this host database was connected.

Usage: Use this element as an indicator of the length of time that applications are maintaining connections to a host database.

Communication Errors

Snapshot Level DCS Database	Logical Data Grouping dcs_dbase	Monitor Switch Basic
Resettable	Yes
Element Name Element Type	gw_comm_errors counter
Related Information	"Communication Error Time" "Most Recent Statement Elapsed Time"

Description: The number of times that a communication error (SQL30081) occurred while a DCS application was attempting to connect to a host database, or while it was processing an SQL statement.

Usage: By monitoring the number of communication errors over time, you can assess whether your DB2 Connect gateway has connectivity problems with a particular host database. You can establish what you consider to be a normal error threshold, so that any time the number of errors exceeds this threshold an investigation of the communication errors should be made.

Use this element for problem determination, in conjunction with the communication error logged in db2diag.log.

Communication Error Time

Snapshot Level DCS Database	Logical Data Grouping dcs_dbase	Monitor Switch Basic
Resettable	No
Element Name Element Type	gw_comm_error_time timestamp
Related Information	"Communication Errors"

Description: The date and time when the most recent communication error (SQL30081) occurred while a DCS application was attempting to connect to a host database, or while it was processing an SQL statement.

Usage: Use this element for problem determination, in conjunction with Communication Error and the communication error logged in db2diag.log.

Blocking Cursor

Snapshot Level Statement DCS Statement	Logical Data Grouping stmt dcs_stmt	Monitor Switch Statement Statement
Resettable	No
Event Type Statement	Logical Data Grouping stmt_event
Element Name Element Type	blocking_cursor information
Related Information	"Outbound Blocking Cursor"

Description: This element indicates if the statement being executed is using a blocking cursor.

Usage: 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 Blocking Cursor

Snapshot Level DCS Statement	Logical Data Grouping dcs_stmt	Monitor Switch Statement
Resettable	No
Element Name Element Type	outbound_blocking_cursor information
Related Information	"Blocking Cursor"

Description: This element indicates whether blocking is used for data transfer from the DRDA server to the DB2 Connect gateway for a particular query.

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

Statement Execution Elapsed Time

Snapshot Level Database Application DCS Database DCS Application DCS Statement	Logical Data Grouping dbase appl dcs_dbase dcs_appl dcs_stmt	Monitor Switch Statement Statement Statement Statement Statement
Resettable	No (at statement) Yes (at other levels)
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	elapsed_exec_time information
Related Information	"Most Recent Statement Elapsed Time" "Elapsed Statement Execution Time" "Host Response Time"

Description: At the DCS statement level, this is the elapsed time spent processing an SQL request on a host database server. At other levels, it is the sum of the elapsed times for all the statements that were executed for a particular application or database.

Usage: Use this element, along with other elapsed time data elements, to evaluate the database server's processing of SQL requests and help to isolate performance issues.

For DB2 Connect, the network elapsed time can be obtained by subtracting this element from the "Host Response Time".

This data element is only available for statements using DB2 Connect. The elapsed time for other SQL statements can be obtained by using Statement Operation Start Timestamp and Statement Operation Stop Timestamp.

Transaction Processor Monitoring

In a transaction monitor or application server (multi-tier) environment, application users do not issue SQL requests directly. Instead, they request the transaction processor monitor (for example, CICS, TUXEDO, or ENCINA running on a UNIX, OS/2, or Windows NT server) or application server to execute a business transaction. Each business transaction is an application part that issues SQL requests to the database server. Because the SQL requests are issued by an intermediate server, the database server has no information about the original client that caused the execution of the SQL request.

Developers of transaction processor monitor (TP monitor) transactions or application server code can use the sqleseti - Set Client Information API to provide information about the original client to the database server. This information can be found in the following data elements:

"TP Monitor Client User ID"
"TP Monitor Client Workstation Name"
"TP Monitor Client Application Name"
"TP Monitor Client Accounting String".

TP Monitor Client User ID

Snapshot Level Application DCS Application	Logical Data Grouping applinfo dcs_appl	Monitor Switch Basic Basic
Resettable	No
Element Name Element Type	tpmon_client_userid information
Related Information	"TP Monitor Client Workstation Name" "TP Monitor Client Application Name" "TP Monitor Client Accounting String"

Description: The client user ID generated by a transaction manager and provided to the server, if the sqleseti API is used.

Usage: Use this element in application server or TP monitor environments to identify the end-user for whom the transaction is being executed.

TP Monitor Client Workstation Name

Snapshot Level Application DCS Application	Logical Data Grouping applinfo dcs_appl	Monitor Switch Basic Basic
Resettable	No
Element Name Element Type	tpmon_client_wkstn information
Related Information	"TP Monitor Client User ID" "TP Monitor Client Application Name" "TP Monitor Client Accounting String"

Description: Identifies the client's system or workstation (for example CICS EITERMID), if the sqleseti API was issued in this connection.

Usage: Use this element to identify the user's machine by node ID, terminal ID, or similar identifiers.

TP Monitor Client Application Name

Snapshot Level Application DCS Application	Logical Data Grouping applinfo dcs_appl	Monitor Switch Basic Basic
Resettable	No
Element Name Element Type	tpmon_client_app information
Related Information	"TP Monitor Client User ID" "TP Monitor Client Workstation Name" "TP Monitor Client Accounting String"

Description: Identifies the the server transaction program performing the transaction, if the sqleseti API was issued in this connection.

Usage: Use this element for problem determination and accounting purposes.

TP Monitor Client Accounting String

Snapshot Level Application DCS Application	Logical Data Grouping applinfo dcs_appl	Monitor Switch Basic Basic
Resettable	No
Element Name Element Type	tpmon_acc_str information
Related Information	"TP Monitor Client User ID" "TP Monitor Client Workstation Name" "TP Monitor Client Application Name"

Description: The data passed to the target database for logging and diagnostic purposes, if the sqleseti API was issued in this connection.

Usage: Use this element for problem determination and accounting purposes.

[ Top of Page | Previous Page | Next Page ]