APPLICATIONS administrative view - Retrieve connected database application information

The APPLICATIONS administrative view returns information about the connected database applications. The view is an SQL interface for the LIST APPLICATIONS SHOW DETAIL CLP command, but only for the currently connected database. Its information is based on the SNAPAPPL_INFO administrative view.
Important: The SNAPAPPL administrative view and the SNAP_GET_APPL table function are deprecated and have been replaced by the MON_GET_CONNECTION table function - Get connection metrics.

The schema is SYSIBMADM.

Authorization

One of the following authorizations is required:
  • SELECT privilege on the APPLICATIONS administrative view
  • CONTROL privilege on the APPLICATIONS 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

Example 1: List information for all the active applications in the single-partitioned database SAMPLE. 
SELECT AGENT_ID, SUBSTR(APPL_NAME,1,10) AS APPL_NAME, AUTHID, 
   APPL_STATUS FROM SYSIBMADM.APPLICATIONS WHERE DB_NAME = 'SAMPLE'
The following is an example of output for this query. 
AGENT_ID             APPL_NAME  AUTHID    APPL_STATUS
-------------------- ---------- ---------- ---------------------
                  23 db2bp.exe  JESSICAE   UOWEXEC

  1 record(s) selected.
Example 2: List the number of agents per application on database partition 0 for the multi-partition database SAMPLE. 
SELECT SUBSTR(APPL_NAME, 1, 10) AS APPL_NAME, COUNT(*) AS NUM 
   FROM SYSIBMADM.APPLICATIONS WHERE DBPARTITIONNUM = 0 
   AND DB_NAME = 'SAMPLE' GROUP BY APPL_NAME
The following is an example of output for this query. 
APPL_NAME  NUM
---------- -----------
db2bp.exe            3
javaw.exe            1

  2 record(s) selected.

Usage notes

The view does not support the GLOBAL syntax available from the CLP. However, aggregation can be done using SQL aggregation functions as data from all database partitions is returned from the view.

Information returned

Table 1. Information returned by the APPLICATIONS administrative view
Column name Data type Description or corresponding monitor element
SNAPSHOT_TIMESTAMP TIMESTAMP The date and time that the snapshot was taken.
CLIENT_DB_ALIAS VARCHAR(128) client_db_alias - Database alias used by application
DB_NAME VARCHAR(128) db_name - Database name
AGENT_ID BIGINT agent_id - Application handle (agent ID)
APPL_NAME VARCHAR(256) appl_name - Application name
AUTHID VARCHAR(128) auth_id - Authorization ID
APPL_ID VARCHAR(128) appl_id - Application ID
APPL_STATUS VARCHAR(22) appl_status - Application status . This interface returns a text identifier based on defines in sqlmon.h, and is one of:
  • BACKUP
  • COMMIT_ACT
  • COMP
  • CONNECTED
  • CONNECTPEND
  • CREATE_DB
  • DECOUPLED
  • DISCONNECTPEND
  • INTR
  • IOERROR_WAIT
  • LOAD
  • LOCKWAIT
  • QUIESCE_TABLESPACE
  • RECOMP
  • REMOTE_RQST
  • RESTART
  • RESTORE
  • ROLLBACK_ACT
  • ROLLBACK_TO_SAVEPOINT
  • TEND
  • THABRT
  • THCOMT
  • TPREP
  • UNLOAD
  • UOWEXEC
  • UOWWAIT
  • WAITFOR_REMOTE
STATUS_CHANGE_TIME TIMESTAMP status_change_time - Application status change time
SEQUENCE_NO VARCHAR(4) sequence_no - Sequence number
CLIENT_PRDID VARCHAR(128) client_prdid - Client product/version ID
CLIENT_PID BIGINT client_pid - Client process ID
CLIENT_PLATFORM VARCHAR(12) client_platform - Client operating platform . This interface returns a text identifier based on defines in sqlmon.h, and is one of:
  • AIX
  • AIX64
  • AS400_DRDA
  • DOS
  • HP
  • HP64
  • HPIA
  • HPIA64
  • LINUX
  • LINUX390
  • LINUXIA64
  • LINUXPPC
  • LINUXPPC64
  • LINUXX8664
  • LINUXZ64
  • MAC
  • MVS_DRDA
  • NT
  • NT64
  • OS2
  • OS390
  • SCO
  • SNI
  • SUN
  • SUN64
  • UNKNOWN
  • UNKNOWN_DRDA
  • VM_DRDA
  • VSE_DRDA
  • WINDOWS
  • WINDOWS95
CLIENT_PROTOCOL VARCHAR(10) client_protocol - Client communication protocol . This interface returns a text identifier based on the defines in sqlmon.h,
  • CPIC
  • LOCAL
  • NPIPE
  • TCPIP
  • TCPIP4
  • TCPIP6
CLIENT_NNAME VARCHAR(128) client_nname - Client name monitor element
COORD_NODE_NUM SMALLINT coord_node - Coordinating node
COORD_AGENT_PID BIGINT coord_agent_pid - Coordinator agent
NUM_ASSOC_AGENTS BIGINT num_assoc_agents - Number of associated agents
TPMON_CLIENT_USERID VARCHAR(256) tpmon_client_userid - TP monitor client user ID
TPMON_CLIENT_WKSTN VARCHAR(256) tpmon_client_wkstn - TP monitor client workstation name
TPMON_CLIENT_APP VARCHAR(256) tpmon_client_app - TP monitor client application name
TPMON_ACC_STR VARCHAR(255) tpmon_acc_str - TP monitor client accounting string
DBPARTITIONNUM SMALLINT dbpartitionnum - Database partition number monitor element
MEMBER SMALLINT member - Database member monitor element
COORD_MEMBER SMALLINT coord_member - Coordinator member monitor element
COORD_DBPARTITIONNUM SMALLINT The coordinating database partition number.