DB2 Version 9.7 for Linux, UNIX, and Windows

APPL_PERFORMANCE administrative view - Retrieve percentage of rows selected for an application

The APPL_PERFORMANCE administrative view displays information about the percentage of rows selected by an application. The information returned is for all database partitions for the currently connected database. This view can be used to look for applications that might be performing large table scans or to look for potentially troublesome queries.

The schema is SYSIBMADM.

Authorization

One of the following authorizations is required:

SELECT privilege on the APPL_PERFORMANCE administrative view
CONTROL privilege on the APPL_PERFORMANCE administrative view
DATAACCESS authority

In addition, to access snapshot monitor data, one of the following authorities is also required:

SYSMON
SYSCTRL
SYSMAINT
SYSADM

Example

Retrieve the report on application performance.

SELECT SNAPSHOT_TIMESTAMP, SUBSTR(AUTHID,1,10) AS AUTHID, 
   SUBSTR(APPL_NAME,1,10) AS APPL_NAME,AGENT_ID, 
   PERCENT_ROWS_SELECTED, DBPARTITIONNUM 
   FROM SYSIBMADM.APPL_PERFORMANCE

The following example is a sample output for this query.

SNAPSHOT_TIMESTAMP         AUTHID      APPL_NAME ... 
-------------------------- ---------- ---------- ... 
2006-01-07-17.01.15.966668 JESSICAE   db2bp.exe  ... 
2006-01-07-17.01.15.980278 JESSICAE   db2taskd   ... 
2006-01-07-17.01.15.980278 JESSICAE   db2bp.exe  ... 
                                                 ... 
   3 record(s) selected.                         ...

Output for this query (continued).

... AGENT_ID      PERCENT_ROWS_SELECTED DBPARTITIONNUM 
... --------...-- --------------------- -------------- 
...            67                     -              1 
...            68                     -              0 
...            67                 57.14              0 
...

Information returned

Table 1. Information returned by the APPL_PERFORMANCE administrative view
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	snapshot_timestamp - Snapshot timestamp monitor element
AUTHID	VARCHAR(128)	auth_id - Authorization ID
APPL_NAME	VARCHAR(256)	appl_name - Application name
AGENT_ID	BIGINT	agent_id - Application handle (agent ID)
PERCENT_ROWS_SELECTED	DECIMAL(5,2)	The percent of rows read from disk that were actually returned to the application. Note: The percentage shown will not be greater than 100.00 percent.
DBPARTITIONNUM	SMALLINT	The database partition from which the data was retrieved for this row.