SNAP_GET_APPL_INFO_V95 table function - Retrieve appl_info logical data group snapshot information
The SNAP_GET_APPL_INFO_V95 table function returns information about applications from an application snapshot, in particular, the appl_info logical data group.
Important: The SNAP_GET_APPL_INFO_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
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_INFO_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_INFO_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.
Examples
Retrieve the status of all applications on the connected database member.
SELECT SUBSTR(DB_NAME,1,8) AS DB_NAME, AGENT_ID,
SUBSTR(APPL_NAME,1,10) AS APPL_NAME, APPL_STATUS
FROM TABLE(SNAP_GET_APPL_INFO_V95(CAST(NULL AS VARCHAR(128)),-1)) AS TThe following is an example of output from this query.
DB_NAME AGENT_ID APPL_NAME APPL_STATUS
-------- -------------------- ---------- ----------------------
TOOLSDB 14 db2bp.exe CONNECTED
SAMPLE 15 db2bp.exe UOWEXEC
SAMPLE 8 javaw.exe CONNECTED
SAMPLE 7 db2bp.exe UOWWAIT
4 record(s) selected.The following shows what you obtain when you SELECT from the result of the table
function.
SELECT SUBSTR(DB_NAME,1,8) AS DB_NAME, AUTHORITY_LVL
FROM TABLE(SNAP_GET_APPL_INFO_V95(CAST(NULL AS VARCHAR(128)),-1)) AS TThe following is an example of output from this query.
DB_NAME AUTHORITY_LVL
-------- -----------------------------------------------------....
TESTDB SYSADM(GROUP) + DBADM(USER) + CREATETAB(USER, GROUP) +
BINDADD(USER, GROUP) + CONNECT(USER, GROUP) +
CREATE_NOT_FENC(USER) + IMPLICIT_SCHEMA(USER, GROUP) +
LOAD(USER) + CREATE_EXT_RT(USER) + QUIESCE_CONN(USER)
TESTDB SYSADM(GROUP) + DBADM(USER) + CREATETAB(USER, GROUP) +
BINDADD(USER, GROUP) + CONNECT(USER, GROUP) +
CREATE_NOT_FENC(USER) + IMPLICIT_SCHEMA(USER, GROUP) +
LOAD(USER) + CREATE_EXT_RT(USER) + QUIESCE_CONN(USER)
TESTDB SYSADM(GROUP) + DBADM(USER) + CREATETAB(USER, GROUP) +
BINDADD(USER, GROUP) + CONNECT(USER, GROUP) +
CREATE_NOT_FENC(USER) + IMPLICIT_SCHEMA(USER, GROUP) +
LOAD(USER) + CREATE_EXT_RT(USER) + QUIESCE_CONN(USER)
3 record(s) selected.Information returned
| Column name | Data type | Description or corresponding monitor element |
|---|---|---|
| SNAPSHOT_TIMESTAMP | TIMESTAMP | The date and time that the snapshot was taken. |
| AGENT_ID | BIGINT | agent_id - Application handle (agent ID) |
| APPL_STATUS | VARCHAR(22) |
appl_status - Application status
. This interface returns a text identifier
based on the defines in sqlmon.h, and is one of:
|
| CODEPAGE_ID | BIGINT | codepage_id - ID of code page used by application |
| NUM_ASSOC_AGENTS | BIGINT | num_assoc_agents - Number of associated agents |
| COORD_NODE_NUM | SMALLINT | coord_node - Coordinating node |
| AUTHORITY_LVL | VARCHAR(512) |
authority_bitmap - User authorization level
. This interface returns a
text identifier based on the database authorities defined in sql.h and their
source, and has the following format:
Possible values for "authority":
Possible values for "source":
|
| CLIENT_PID | BIGINT | client_pid - Client process ID |
| COORD_AGENT_PID | BIGINT | coord_agent_pid - Coordinator agent |
| STATUS_CHANGE_TIME | TIMESTAMP | status_change_time - Application status change time |
| CLIENT_PLATFORM | VARCHAR(12) |
client_platform - Client operating platform
. This interface returns a text
identifier based on the defines in sqlmon.h,
|
| CLIENT_PROTOCOL | VARCHAR(10) |
client_protocol - Client communication protocol
. This interface returns a
text identifier based on the defines in sqlmon.h,
|
| TERRITORY_CODE | SMALLINT | territory_code - Database territory code |
| APPL_NAME | VARCHAR(256) | appl_name - Application name |
| APPL_ID | VARCHAR(128) | appl_id - Application ID |
| SEQUENCE_NO | VARCHAR(4) | sequence_no - Sequence number |
| PRIMARY_AUTH_ID | VARCHAR(128) | auth_id - Authorization ID |
| SESSION_AUTH_ID | VARCHAR(128) | session_auth_id - Session authorization ID |
| CLIENT_NNAME | VARCHAR(128) | client_nname - Client name monitor element |
| CLIENT_PRDID | VARCHAR(128) | client_prdid - Client product/version ID |
| INPUT_DB_ALIAS | VARCHAR(128) | input_db_alias - Input database alias |
| CLIENT_DB_ALIAS | VARCHAR(128) | client_db_alias - Database alias used by application |
| DB_NAME | VARCHAR(128) | db_name - Database name |
| DB_PATH | VARCHAR(1024) | db_path - Database path |
| EXECUTION_ID | VARCHAR(128) | execution_id - User login ID |
| CORR_TOKEN | VARCHAR(128) | corr_token - DRDA correlation token |
| 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 |
| WORKLOAD_ID | INTEGER | workload_id - Workload ID monitor element |
| IS_SYSTEM_APPL | SMALLINT | is_system_appl - Is System Application monitor element |