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

Important: The APPL_PERFORMANCE administrative view is deprecated and has been replaced by the MON_CONNECTION_SUMMARY - Retrieve metrics for all connections.

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
DBADM authority
SQLADM authority

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

SYSMON
SYSCTRL
SYSMAINT
SYSADM

Default PUBLIC privilege

In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.

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 is an example of 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	The date and time that the snapshot was taken.
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	dbpartitionnum - Database partition number monitor element
MEMBER	SMALLINT	member - Database member monitor element