SNAPAPPL_INFO administrative view and SNAP_GET_APPL_INFO table function - Retrieve appl_info logical data group snapshot information

Depending on if you are using the administrative view or the table function, refer to one of the following sections:

• SNAPAPPL_INFO administrative view
• SNAP_GET_APPL_INFO table function

One of the following authorizations is required to use the view:

• SELECT privilege on the SNAPAPPL_INFO administrative view
• CONTROL privilege on the SNAPAPPL_INFO administrative view
• DATAACCESS authority
• DBADM authority
• SQLADM authority

One of the following is required to use the table function:

• EXECUTE privilege on the SNAP_GET_APPL_INFO table function
• DATAACCESS authority
• DBADM authority
• SQLADM authority

Also, one of the following authorities is required:

• SYSMON
• SYSMAINT
• SYSCTRL
• SYSADM

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.

One of the following authorizations is required:

• EXECUTE privilege on the SNAP_GET_APPL_INFO table function
• DATAACCESS authority

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

• SYSMON
• SYSCTRL
• SYSMAINT
• SYSADM

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(CAST(NULL AS VARCHAR(128)),-1)) AS T

The 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(CAST(NULL AS VARCHAR(128)),-1)) AS T

The 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.