What's New

System Monitoring for DB2 Connect

This section lists the system monitor data elements that can be monitored for DB2 Connect in DB2 Universal Database Version 5.2. These data elements fall into two groupings:

existing: Data elements that were available in Version 5, but not for DB2 Connect. Starting with Version 5.2, these elements can be used to monitor DB2 Connect activity.
new: Data elements with DB2 Connect implications that have been added to the system monitor in Version 5.2.

These DB2 Connect data elements (both existing and new) will be returned in the following data structures. These data structures also fall into two categories:

changed

These data structures existed in Version 5, but have been modified in Version 5.2 to include data elements for DB2 Connect. They are:

sqlm_db2
sqlm_appl
sqlm_dcs_applinfo.

new

These data structures that return DB2 Connect data elements have been added to the system monitor in Version 5.2:

sqlm_dcs_appl
sqlm_dcs_appl_stats
sqlm_dcs_appl_xid
sqlm_dcs_applid_info
sqlm_dcs_dbase
sqlm_dcs_stmt
sqlm_tpmon_info

Note:

The data structures are defined in the sqlmon.h header file.

Changed Commands

The following commands associated with system monitoring have been enhanced to return more DB2 Connect information:

GET SNAPSHOT

Depending on the specified clause, information can be collected on:

A specific DCS database
All DCS databases
All DCS applications
All DCS applications currently connected to a specific DCS database
A specific DCS application running on the DB2 Connect gateway.

LIST DCS APPLICATIONS

The EXTENDED parameter returns additional DCS information:

DCS application status
Status change time
Client platform
Client protocol
Client code page
Process ID of the client application
Host coded character set ID (CCSID).
Database alias at the gateway
DCS database name

RESET MONITOR

The DCS parameter has been added to this command to indicate that monitor data should only be reset for DCS databases.

For command details see the Command Reference.

The following table lists the Version 5 system monitor data elements that can collect DB2 Connect information starting in Version 5.2. These data elements will be returned in the data structures listed in the API Structure column in Version 5.2, in addition to their original Version 5 data structures.
Note: You should refer to the Version 5 System Monitor Guide and Reference for detailed information on these data elements.

Data Element API Element Name Level API Structure
Database Identification and Status
Database Name db_name
DCS Database DCS Application

sqlm_dcs_dbase sqlm_dcs_applid_info

Application Identification and Status
Application Handle (agent ID) agent_id DCS Application sqlm_dcs_applid_info
ID of Code Page Used by Application codepage_id DCS Application sqlm_dcs_applid_info
Application Status Change Time status_change_time DCS Application sqlm_dcs_applid_info
User Login ID execution_id DCS Application sqlm_dcs_applid_info
Client Process ID client_pid DCS Application sqlm_dcs_applid_info
Client Operating Platform client_platform DCS Application sqlm_dcs_applid_info
Client Communication Protocol client_protocol DCS Application sqlm_dcs_applid_info
Previous Unit of Work Completion Timestamp prev_uow_stop_time DCS Application sqlm_dcs_appl
Unit of Work Start Timestamp uow_start_time DCS Application sqlm_dcs_appl
Unit of Work Stop Timestamp uow_stop_time DCS Application sqlm_dcs_appl
Unit of Work Completion Status uow_comp_status DCS Application sqlm_dcs_appl
Application Idle Time appl_idle_time DCS Application sqlm_dcs_appl
SQL Statement Activity
Failed Statement Operations failed_sql_stmts
DCS Database DCS Application

sqlm_dcs_dbase sqlm_dcs_appl_stats

Commit Statements Attempted commit_sql_stmts
DCS Database DCS Application

sqlm_dcs_dbase sqlm_dcs_appl_stats

Rollback Statements Attempted rollback_sql_stmts
DCS Database DCS Application

sqlm_dcs_dbase sqlm_dcs_appl_stats

SQL Statement Details
Statement Operation stmt_operation
DCS Application DCS Statement

sqlm_dcs_appl sqlm_dcs_stmt

Package Name package_name
DCS Application DCS Statement

sqlm_dcs_appl sqlm_dcs_stmt

Section Number section_number
DCS Application DCS Statement

sqlm_dcs_appl sqlm_dcs_stmt

Application Creator creator
DCS Application DCS Statement

sqlm_dcs_appl sqlm_dcs_stmt

Statement Operation Start Timestamp stmt_start
DCS Application DCS Statement

sqlm_dcs_appl sqlm_dcs_stmt

Statement Operation Stop Timestamp stmt_stop
DCS Application DCS Statement

sqlm_dcs_appl sqlm_dcs_stmt

SQL Dynamic Statement Text n/a DCS Statement sqlm_dcs_stmt
Number of Successful Fetches fetch_count DCS Statement sqlm_dcs_stmt
Query Cost Estimate query_cost_estimate DCS Statement sqlm_dcs_stmt
Query Number of Rows Estimate query_card_estimate DCS Statement sqlm_dcs_stmt
SQL Snapshot Monitor Elements
Last Reset Timestamp last_reset
DCS Database DCS Application

sqlm_dcs_dbase sqlm_dcs_appl

Data Element	API Element Name	Level	API Structure
Database Identification and Status
Database Name	db_name	DCS Database DCS Application	sqlm_dcs_dbase sqlm_dcs_applid_info
Application Identification and Status
Application Handle (agent ID)	agent_id	DCS Application	sqlm_dcs_applid_info
ID of Code Page Used by Application	codepage_id	DCS Application	sqlm_dcs_applid_info
Application Status Change Time	status_change_time	DCS Application	sqlm_dcs_applid_info
User Login ID	execution_id	DCS Application	sqlm_dcs_applid_info
Client Process ID	client_pid	DCS Application	sqlm_dcs_applid_info
Client Operating Platform	client_platform	DCS Application	sqlm_dcs_applid_info
Client Communication Protocol	client_protocol	DCS Application	sqlm_dcs_applid_info
Previous Unit of Work Completion Timestamp	prev_uow_stop_time	DCS Application	sqlm_dcs_appl
Unit of Work Start Timestamp	uow_start_time	DCS Application	sqlm_dcs_appl
Unit of Work Stop Timestamp	uow_stop_time	DCS Application	sqlm_dcs_appl
Unit of Work Completion Status	uow_comp_status	DCS Application	sqlm_dcs_appl
Application Idle Time	appl_idle_time	DCS Application	sqlm_dcs_appl
SQL Statement Activity
Failed Statement Operations	failed_sql_stmts	DCS Database DCS Application	sqlm_dcs_dbase sqlm_dcs_appl_stats
Commit Statements Attempted	commit_sql_stmts	DCS Database DCS Application	sqlm_dcs_dbase sqlm_dcs_appl_stats
Rollback Statements Attempted	rollback_sql_stmts	DCS Database DCS Application	sqlm_dcs_dbase sqlm_dcs_appl_stats
SQL Statement Details
Statement Operation	stmt_operation	DCS Application DCS Statement	sqlm_dcs_appl sqlm_dcs_stmt
Package Name	package_name	DCS Application DCS Statement	sqlm_dcs_appl sqlm_dcs_stmt
Section Number	section_number	DCS Application DCS Statement	sqlm_dcs_appl sqlm_dcs_stmt
Application Creator	creator	DCS Application DCS Statement	sqlm_dcs_appl sqlm_dcs_stmt
Statement Operation Start Timestamp	stmt_start	DCS Application DCS Statement	sqlm_dcs_appl sqlm_dcs_stmt
Statement Operation Stop Timestamp	stmt_stop	DCS Application DCS Statement	sqlm_dcs_appl sqlm_dcs_stmt
SQL Dynamic Statement Text	n/a	DCS Statement	sqlm_dcs_stmt
Number of Successful Fetches	fetch_count	DCS Statement	sqlm_dcs_stmt
Query Cost Estimate	query_cost_estimate	DCS Statement	sqlm_dcs_stmt
Query Number of Rows Estimate	query_card_estimate	DCS Statement	sqlm_dcs_stmt
SQL Snapshot Monitor Elements
Last Reset Timestamp	last_reset	DCS Database DCS Application	sqlm_dcs_dbase sqlm_dcs_appl

New Data Elements

This section lists all the new Version 5.2 data elements that can be monitored for DB2 Connect. You should refer to the Version 5 System Monitor Guide and Reference for instructions on how to collect information with the DB2 system monitor.

DCS Database Name

Snapshot Information Level DCS Database DCS Application	API Structure(s) sqlm_dcs_dbase sqlm_dcs_applid_info	Monitor Switch Basic Basic
Resettable	No
API 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 Information Level DCS Database DCS Application	API Structure(s) sqlm_dcs_dbase sqlm_dcs_applid_info	Monitor Switch Basic Basic
Resettable	No
API 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 Information Level DCS Application	API Structure(s) sqlm_dcs_applid_info	Monitor Switch Basic
Resettable	No
API 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 Timestamp

Snapshot Information Level DCS Database DCS Application	API Structure(s) sqlm_dcs_dbase sqlm_dcs_appl	Monitor Switch Basic Basic
Resettable	No
API Element Name Element Type	gw_con_time timestamp
Related Information	None

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

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

Maximum Number of Concurrent Connections

Snapshot Information Level DCS Database	API Structure(s) sqlm_dcs_dbase	Monitor Switch Basic
Resettable	No
API 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 Information Level Database Manager DCS Database	API Structure(s) sqlm_db2 sqlm_dcs_dbase	Monitor Switch Basic Basic
Resettable	No
API 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 for DB2 Connect attempted at the DB2 Connect gateway since the 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 Information Level Database Manager DCS Database	API Structure(s) sqlm_db2 sqlm_dcs_dbase	Monitor Switch Basic Basic
Resettable	No
API 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 Information Level Database Manager DCS Database	API Structure(s) sqlm_db2 sqlm_dcs_dbase	Monitor Switch Basic Basic
Resettable	No
API 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 Information Level Database Manager DCS Database	API Structure(s) sqlm_db2 sqlm_dcs_dbase	Monitor Switch Basic Basic
Resettable	No
API 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 Information Level DCS Application DCS Statement	API Structure(s) sqlm_dcs_appl sqlm_dcs_stmt	Monitor Switch Statement Statement
Resettable	Yes (at application) No (at other levels)
API Element Name Element Type	gw_exec_time time
Related Information	None

Description: The time (in 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 Information Level DCS Database DCS Application	API Structure(s) sqlm_dcs_dbase sqlm_dcs_appl	Monitor Switch Basic Basic
Resettable	Yes
API 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 Information Level DCS Application	API Structure(s) sqlm_dcs_appl	Monitor Switch Basic
Resettable	No
API 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 Information Level DCS Application	API Structure(s) sqlm_dcs_applinfo	Monitor Switch Basic
Resettable	No
API 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 a 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: (1) 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.
(2) 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.

Host Coded Character Set ID

Snapshot Information Level DCS Application	API Structure(s) sqlm_dcs_applid_info	Monitor Switch Basic
Resettable	No
API 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 Information Level Application DCS Application	API Structure(s) sqlm_applinfo sqlm_dcs_applid_info	Monitor Switch Basic Basic
Resettable	No
API 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 Information Level Application DCS Application	API Structure(s) sqlm_applinfo sqlm_dcs_applid_info	Monitor Switch Basic Basic
Resettable	No
API 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 Information Level Application DCS Application	API Structure(s) sqlm_applinfo sqlm_dcs_applid_info	Monitor Switch Basic Basic
Resettable	No
API 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 Information Level DCS Application	API Structure(s) sqlm_dcs_appl sqlm_dcs_stmt	Monitor Switch Basic Statement
Resettable	Yes
API 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 Information Level DCS Application	API Structure(s) sqlm_dcs_appl sqlm_dcs_stmt	Monitor Switch Basic Statement
Resettable	Yes
API 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 Information Level DCS Application	API Structure(s) sqlm_dcs_appl sqlm_dcs_stmt	Monitor Switch Basic Statement
Resettable	Yes
API 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 Information Level DCS Application	API Structure(s) sqlm_dcs_appl sqlm_dcs_stmt	Monitor Switch Basic Statement
Resettable	Yes
API 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 Information Level Application DCS Application	API Structure(s) sqlm_appl_xid sqlm_appl_xid	Monitor Switch Unit of Work Unit of Work
Resettable	No
API 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.

TP Monitor Client User ID

Snapshot Information Level Application DCS Application	API Structure(s) sqlm_tpmon_info sqlm_tpmon_info	Monitor Switch Basic Basic
Resettable	No
API 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 Information Level Application DCS Application	API Structure(s) sqlm_tpmon_info sqlm_tpmon_info	Monitor Switch Basic Basic
Resettable	No
API 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 Information Level Application DCS Application	API Structure(s) sqlm_tpmon_info sqlm_tpmon_info	Monitor Switch Basic Basic
Resettable	No
API 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 Information Level Application DCS Application	API Structure(s) sqlm_tpmon_info sqlm_tpmon_info	Monitor Switch Basic Basic
Resettable	No
API Element Name Element Type	acc_str_length acc_str_offset 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.
Note: Two variables in the sqlm_tpmon_info data structure are used to determine the TP monitor client accounting string:

acc_str_length
length of the accounting string
acc_str_offset
relative offset from the start of the sqlm_tpmon_info data structure

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

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

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]