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
  • ACCESSCTRL authority
  • SECADM 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