SNAP_GET_APPL_INFO table function - Retrieve appl_info logical data group snapshot information

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.

dbpartitionnum

An optional input argument of type INTEGER that specifies a valid database partition number. Specify -1 for the current database partition, or -2 for an aggregate of all active database partitions. If dbname is not set to NULL and dbpartitionnum is set to NULL, -1 is set implicitly for dbpartitionnum. If this input option is not used, that is, only dbname is provided, data is returned from all active database partitions. An active database partition is a partition 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

To access snapshot monitor data, one of the following authorities is also required:

• SYSMON authority
• SYSCTRL authority
• SYSMAINT authority
• SYSADM authority

Retrieve the status of all applications on the connected database partition.

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 example is a sample 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 T

The following example is a sample 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.