DB2 Version 9.7 for Linux, UNIX, and Windows

stmt_text - SQL statement text monitor element

The text of the SQL statement.

Table 1. Table Function Monitoring Information
Table Function	Monitor Element Collection Level
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document)	Always collected
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache	Always collected
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries	Always collected

Table 2. Snapshot Monitoring Information
Snapshot Level	Logical Data Grouping	Monitor Switch
Application	stmt	Statement
Dynamic SQL	dynsql	Basic
DCS Statement	dcs_stmt	Statement

Table 3. Event Monitoring Information
Event Type	Logical Data Grouping	Monitor Switch
Locking	-	-
Deadlocks with Details¹	event_detailed_dlconn	-
Deadlocks with Details History¹	event_stmt_history	-
Statements	event_stmt	-
Activities	event_activitystmt	-
Package cache	-	COLLECT BASE DATA

1: This option has been deprecated. Its use is no longer recommended and might be removed in a future release. Use the CREATE EVENT MONITOR FOR LOCKING statement to monitor lock-related events, such as lock timeouts, lock waits, and deadlocks.

Usage

For application snapshots, this statement text helps you identify what the application was executing when the snapshot was taken, or most recently processed if no statement was being processed right at the time the snapshot was taken.

The information returned by this element is taken from the SQL statement cache and it might not be available if the cache has overflowed. The only guaranteed way to capture the SQL text of a statement is to use an event monitor for statements.

For dynamic SQL statements, this element identifies the SQL text associated with a package.

For statement event monitors, this element is returned only for dynamic statements. If a statement event monitor record cannot fit into the size of the buffer specified by the BUFFERSIZE option of a statement event monitor, the value of the stmt_text monitor may be truncated so that the record can fit.

For the EVENT_STMT_HISTORY event monitor, this element is returned only for dynamic statements. For remaining event monitors, stmt_text is returned for dynamic and static statements only if it is available in the SQL statement cache.

For information on how to query the system catalog tables to obtain static SQL statement text that is not provided due to performance considerations, see the section_number monitor element.