SNAP_GET_APPL_V95 table function - Retrieve appl logical data group snapshot information

The SNAP_GET_APPL_V95 table function returns information about applications from an application snapshot, in particular, the appl logical data group.

Important: The SNAP_GET_APPL_V95 table function is deprecated and has been replaced by the MON_GET_CONNECTION table function - Get connection metrics.
Refer to Table 1 for a complete list of information that can be returned.

Syntax

Read syntax diagramSkip visual syntax diagramSNAP_GET_APPL_V95(dbname , member)

The schema is SYSPROC.

Table function parameters

dbname
An input argument of type VARCHAR(128) that specifies a valid database name in the same instance as the currently connected database. Specify a database name that has a directory entry type of either "Indirect" or "Home", as returned by the LIST DATABASE DIRECTORY command. Specify an empty string to take the snapshot from the currently connected database. Specify a NULL value to take the snapshot from all databases within the same instance as the currently connected database.
member
An optional input argument of type INTEGER that specifies a valid database member number. Specify -1 for the current database member, or -2 for an aggregate of all active database members. If dbname is not set to NULL and member is set to NULL, -1 is set implicitly for member. If this input option is not used, that is, only dbname is provided, data is returned from all active database members. An active database member is a member where the database is available for connection and use by applications.

If both dbname and member are set to NULL, an attempt is made to read data from the file created by SNAP_WRITE_FILE procedure. Note that this file could have been created at any time, which means that the data might not be current. If a file with the corresponding snapshot API request type does not exist, then the SNAP_GET_APPL_V95 table function takes a snapshot for the currently connected database and database member number.

Authorization

One of the following authorizations is required:
  • EXECUTE privilege on the SNAP_GET_APPL_V95 table function
  • DATAACCESS 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, EXECUTE privilege is granted to PUBLIC when the function is automatically created.

Example

Retrieve details on rows read and written for each application for all active databases. 
SELECT SUBSTR(DB_NAME,1,8) AS DB_NAME, AGENT_ID, ROWS_READ, ROWS_WRITTEN 
   FROM TABLE (SNAP_GET_APPL_V95(CAST(NULL AS VARCHAR(128)),-1)) AS T
The following is an example of output from this query. 
DB_NAME  AGENT_ID       ROWS_READ      ROWS_WRITTEN 
-------- --------...--- ---------...-- ------------...-
WSDB                679              0                0
WSDB                461              3                0
WSDB                460              4                0
TEST                680              4                0
TEST                455              6                0
TEST                454              0                0
TEST                453             50                0

Information returned

Table 1. Information returned by the SNAP_GET_APPL_V95 table function
Column name Data type Description or corresponding monitor element
SNAPSHOT_TIMESTAMP TIMESTAMP The date and time that the snapshot was taken.
DB_NAME VARCHAR(128) db_name - Database name
AGENT_ID BIGINT agent_id - Application handle (agent ID)
UOW_LOG_SPACE_USED BIGINT uow_log_space_used - Unit of work log space used
ROWS_READ BIGINT rows_read - Rows read
ROWS_WRITTEN BIGINT rows_written - Rows written
STMT_HISTORY_LIST_SIZE BIGINT stmt_history_list_size - Statement history list size
POOL_DATA_L_READS BIGINT pool_data_l_reads - Buffer pool data logical reads
POOL_DATA_P_READS BIGINT pool_data_p_reads - Buffer pool data physical reads
POOL_DATA_WRITES BIGINT pool_data_writes - Buffer pool data writes
POOL_INDEX_L_READS BIGINT pool_index_l_reads - Buffer pool index logical reads
POOL_INDEX_P_READS BIGINT pool_index_p_reads - Buffer pool index physical reads
POOL_INDEX_WRITES BIGINT pool_index_writes - Buffer pool index writes
POOL_TEMP_DATA_L_READS BIGINT pool_temp_data_l_reads - Buffer pool temporary data logical reads
POOL_TEMP_DATA_P_READS BIGINT pool_temp_data_p_reads - Buffer pool temporary data physical reads
POOL_TEMP_INDEX_L_READS BIGINT pool_temp_index_l_reads - Buffer pool temporary index logical reads
POOL_TEMP_INDEX_P_READS BIGINT pool_temp_index_p_reads - Buffer pool temporary index physical reads
POOL_TEMP_XDA_L_READS BIGINT pool_temp_xda_l_reads - Buffer Pool Temporary XDA Data Logical Reads
POOL_TEMP_XDA_P_READS BIGINT pool_temp_xda_p_reads - Buffer Pool Temporary XDA Data Physical Reads monitor element
POOL_XDA_L_READS BIGINT pool_xda_l_reads - Buffer Pool XDA Data Logical Reads
POOL_XDA_P_READS BIGINT pool_xda_p_reads - Buffer Pool XDA Data Physical Reads
POOL_XDA_WRITES BIGINT pool_xda_writes - Buffer Pool XDA Data Writes
POOL_READ_TIME BIGINT pool_read_time - Total buffer pool physical read time
POOL_WRITE_TIME BIGINT pool_write_time - Total buffer pool physical write time
DIRECT_READS BIGINT direct_reads - Direct reads from database
DIRECT_WRITES BIGINT direct_writes - Direct writes to database
DIRECT_READ_REQS BIGINT direct_read_reqs - Direct read requests
DIRECT_WRITE_REQS BIGINT direct_write_reqs - Direct write requests
DIRECT_READ_TIME BIGINT direct_read_time - Direct read time
DIRECT_WRITE_TIME BIGINT direct_write_time - Direct write time
UNREAD_PREFETCH_PAGES BIGINT unread_prefetch_pages - Unread prefetch pages
LOCKS_HELD BIGINT locks_held - Locks held
LOCK_WAITS BIGINT lock_waits - Lock waits
LOCK_WAIT_TIME BIGINT lock_wait_time - Time waited on locks
LOCK_ESCALS BIGINT lock_escals - Number of lock escalations
X_LOCK_ESCALS BIGINT x_lock_escals - Exclusive lock escalations
DEADLOCKS BIGINT deadlocks - Deadlocks detected
TOTAL_SORTS BIGINT total_sorts - Total sorts
TOTAL_SORT_TIME BIGINT total_sort_time - Total sort time
SORT_OVERFLOWS BIGINT sort_overflows - Sort overflows
COMMIT_SQL_STMTS BIGINT commit_sql_stmts - Commit statements attempted
ROLLBACK_SQL_STMTS BIGINT rollback_sql_stmts - Rollback statements attempted
DYNAMIC_SQL_STMTS BIGINT dynamic_sql_stmts - Dynamic SQL statements attempted
STATIC_SQL_STMTS BIGINT static_sql_stmts - Static SQL statements attempted
FAILED_SQL_STMTS BIGINT failed_sql_stmts - Failed statement operations
SELECT_SQL_STMTS BIGINT select_sql_stmts - Select SQL statements executed
DDL_SQL_STMTS BIGINT ddl_sql_stmts - Data definition language (DDL) SQL statements
UID_SQL_STMTS BIGINT uid_sql_stmts - UPDATE/INSERT/DELETE SQL statements executed
INT_AUTO_REBINDS BIGINT int_auto_rebinds - Internal automatic rebinds
INT_ROWS_DELETED BIGINT int_rows_deleted - Internal rows deleted
INT_ROWS_UPDATED BIGINT int_rows_updated - Internal rows updated
INT_COMMITS BIGINT int_commits - Internal commits
INT_ROLLBACKS BIGINT int_rollbacks - Internal rollbacks
INT_DEADLOCK_ROLLBACKS BIGINT int_deadlock_rollbacks - Internal rollbacks due to deadlock
ROWS_DELETED BIGINT rows_deleted - Rows deleted
ROWS_INSERTED BIGINT rows_inserted - Rows inserted
ROWS_UPDATED BIGINT rows_updated - Rows updated
ROWS_SELECTED BIGINT rows_selected - Rows selected
BINDS_PRECOMPILES BIGINT binds_precompiles - Binds/precompiles attempted
OPEN_REM_CURS BIGINT open_rem_curs - Open remote cursors
OPEN_REM_CURS_BLK BIGINT open_rem_curs_blk - Open remote cursors with blocking
REJ_CURS_BLK BIGINT rej_curs_blk - Rejected block cursor requests
ACC_CURS_BLK BIGINT acc_curs_blk - Accepted block cursor requests
SQL_REQS_SINCE_COMMIT BIGINT sql_reqs_since_commit - SQL requests since last commit
LOCK_TIMEOUTS BIGINT lock_timeouts - Number of lock timeouts
INT_ROWS_INSERTED BIGINT int_rows_inserted - Internal rows inserted
OPEN_LOC_CURS BIGINT open_loc_curs - Open local cursors
OPEN_LOC_CURS_BLK BIGINT open_loc_curs_blk - Open local cursors with blocking
PKG_CACHE_LOOKUPS BIGINT pkg_cache_lookups - Package cache lookups
PKG_CACHE_INSERTS BIGINT pkg_cache_inserts - Package cache inserts
CAT_CACHE_LOOKUPS BIGINT cat_cache_lookups - Catalog cache lookups
CAT_CACHE_INSERTS BIGINT cat_cache_inserts - Catalog cache inserts
CAT_CACHE_OVERFLOWS BIGINT cat_cache_overflows - Catalog cache overflows
NUM_AGENTS BIGINT num_agents - Number of agents working on a statement
AGENTS_STOLEN BIGINT agents_stolen - Stolen agents
ASSOCIATED_AGENTS_TOP BIGINT associated_agents_top - Maximum number of associated agents
APPL_PRIORITY BIGINT appl_priority - Application agent priority
APPL_PRIORITY_TYPE VARCHAR(16) appl_priority_type - Application priority type . This interface returns a text identifier, based on defines in sqlmon.h, and is one of:
  • DYNAMIC_PRIORITY
  • FIXED_PRIORITY
PREFETCH_WAIT_TIME BIGINT prefetch_wait_time - Time waited for prefetch
APPL_SECTION_LOOKUPS BIGINT appl_section_lookups - Section lookups
APPL_SECTION_INSERTS BIGINT appl_section_inserts - Section inserts
LOCKS_WAITING BIGINT locks_waiting - Current agents waiting on locks
TOTAL_HASH_JOINS BIGINT total_hash_joins - Total hash joins
TOTAL_HASH_LOOPS BIGINT total_hash_loops - Total hash loops
HASH_JOIN_OVERFLOWS BIGINT hash_join_overflows - Hash join overflows
HASH_JOIN_SMALL_OVERFLOWS BIGINT hash_join_small_overflows - Hash join small overflows
APPL_IDLE_TIME BIGINT appl_idle_time - Application idle time
UOW_LOCK_WAIT_TIME BIGINT uow_lock_wait_time - Total time unit of work waited on locks
UOW_COMP_STATUS VARCHAR(14) uow_comp_status - Unit of work completion status . This interface returns a text identifier, based on defines in sqlmon.h, and is one of:
  • APPL_END
  • UOWABEND
  • UOWCOMMIT
  • UOWDEADLOCK
  • UOWLOCKTIMEOUT
  • UOWROLLBACK
  • UOWUNKNOWN
AGENT_USR_CPU_TIME_S BIGINT agent_usr_cpu_time - User CPU time used by agent (in seconds)*
AGENT_USR_CPU_TIME_MS BIGINT agent_usr_cpu_time - User CPU time used by agent (fractional, in microseconds)*
AGENT_SYS_CPU_TIME_S BIGINT agent_sys_cpu_time - System CPU time used by agent (in seconds)*
AGENT_SYS_CPU_TIME_MS BIGINT agent_sys_cpu_time - System CPU time used by agent (fractional, in microseconds)*
APPL_CON_TIME TIMESTAMP appl_con_time - Connection request start timestamp
CONN_COMPLETE_TIME TIMESTAMP conn_complete_time - Connection request completion timestamp
LAST_RESET TIMESTAMP last_reset - Last reset timestamp
UOW_START_TIME TIMESTAMP uow_start_time - Unit of work start timestamp
UOW_STOP_TIME TIMESTAMP uow_stop_time - Unit of work stop timestamp
PREV_UOW_STOP_TIME TIMESTAMP prev_uow_stop_time - Previous unit of work completion timestamp
UOW_ELAPSED_TIME_S BIGINT uow_elapsed_time - Most recent unit of work elapsed time
UOW_ELAPSED_TIME_MS BIGINT uow_elapsed_time - Most recent unit of work elapsed time
ELAPSED_EXEC_TIME_S BIGINT elapsed_exec_time - Statement execution elapsed time
ELAPSED_EXEC_TIME_MS BIGINT elapsed_exec_time - Statement execution elapsed time
INBOUND_COMM_ADDRESS VARCHAR(32) inbound_comm_address - Inbound communication address
LOCK_TIMEOUT_VAL BIGINT lock_timeout_val - Lock timeout (seconds)
PRIV_WORKSPACE_NUM_OVERFLOWS BIGINT priv_workspace_num_overflows - Private workspace overflows
PRIV_WORKSPACE_SECTION_INSERTS BIGINT priv_workspace_section_inserts - Private workspace section inserts
PRIV_WORKSPACE_SECTION_LOOKUPS BIGINT priv_workspace_section_lookups - Private workspace section lookups
PRIV_WORKSPACE_SIZE_TOP BIGINT priv_workspace_size_top - Maximum private workspace size
SHR_WORKSPACE_NUM_OVERFLOWS BIGINT shr_workspace_num_overflows - Shared workspace overflows
SHR_WORKSPACE_SECTION_INSERTS BIGINT shr_workspace_section_inserts - Shared workspace section inserts
SHR_WORKSPACE_SECTION_LOOKUPS BIGINT shr_workspace_section_lookups - Shared workspace section lookups
SHR_WORKSPACE_SIZE_TOP BIGINT shr_workspace_size_top - Maximum shared workspace size
DBPARTITIONNUM SMALLINT dbpartitionnum - Database partition number monitor element
CAT_CACHE_SIZE_TOP BIGINT cat_cache_size_top - Catalog cache high water mark
TOTAL_OLAP_FUNCS BIGINT total_olap_funcs - Total OLAP Functions monitor element
OLAP_FUNC_OVERFLOWS BIGINT olap_func_overflows - OLAP Function Overflows monitor element
* To calculate the total time spent for the monitor element that this column is based on, you must add the full seconds reported in the column for this monitor element that ends with _S to the fractional seconds reported in the column for this monitor element that ends with _MS, using the following formula: (monitor-element-name_S × 1,000,000 + monitor-element-name_MS) ÷ 1,000,000. For example, (ELAPSED_EXEC_TIME_S × 1,000,000 + ELAPSED_EXEC_TIME_MS) ÷ 1,000,000.