SNAPDBM administrative view and SNAP_GET_DBM table function - Retrieve the dbm logical grouping snapshot information

The SNAPDBM administrative view and the SNAP_GET_DBM table function return the snapshot monitor database manager (dbm) logical grouping information.

Important: The SNAPDBM administrative view and SNAP_GET_DBM table function are deprecated and have been replaced by the MON_GET_INSTANCE table function - Get instance level information.

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

SNAPDBM administrative view
SNAP_GET_DBM table function

SNAPDBM administrative view

Used with the SNAPSWITCHES administrative view and the MON_GET_FCM, MON_GET_MEMORY_SET, MON_GET_MEMORY_POOL table functions, the SNAPDBM administrative view provides the data equivalent to the GET SNAPSHOT FOR DBM command.

The schema is SYSIBMADM.

Refer to Table 1 for a complete list of information that can be returned.

Authorization

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

SELECT privilege on the SNAPDBM administrative view
CONTROL privilege on the SNAPDBM administrative view
DATAACCESS authority
DBADM authority
SQLADM authority
ACCESSCTRL authority
SECADM authority

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

EXECUTE privilege on the SNAP_GET_DBM table function
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

Retrieve database manager status and connection information for all database members.

SELECT DB2_STATUS, DB2START_TIME, LAST_RESET, LOCAL_CONS, REM_CONS_IN, 
   (AGENTS_CREATED_EMPTY_POOL/AGENTS_FROM_POOL) AS AGENT_USAGE, 
   DBPARTITIONNUM FROM SYSIBMADM.SNAPDBM ORDER BY DBPARTITIONNUM

The following is an example of output from this query.

DB2_STATUS   DB2START_TIME              LAST_RESET     ...
------------ -------------------------- ----------...- ...
ACTIVE       2006-01-06-14.59.59.059879              - ...
ACTIVE       2006-01-06-14.59.59.097605              - ...
ACTIVE       2006-01-06-14.59.59.062798              - ...

  3 record(s) selected.                                ...

Output from this query (continued).

... LOCAL_CONS     REM_CONS_IN     AGENT_USAGE     DBPARTITIONNUM 
... ----------...- -----------...- -----------...- -------------- 
...              1               1               0              0 
...              0               0               0              1 
...              0               0               0              2

SNAP_GET_DBM table function

The SNAP_GET_DBM table function returns the same information as the SNAPDBM administrative view, but allows you to retrieve the information for a specific database member, aggregate of all database members or all database members.

Used with the SNAP_GET_DBM_MEMORY_POOL, SNAP_GET_FCM, SNAP_GET_FCM_PART and SNAP_GET_SWITCHES table functions, the SNAP_GET_DBM table function provides the data equivalent to the GET SNAPSHOT FOR DBM command.

Refer to Table 1 for a complete list of information that can be returned.

Syntax

The schema is SYSPROC.

Table function parameter

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 this input option is not used, data will be 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 member is 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_DBM table function calls the snapshot from memory.

Authorization

One of the following authorizations is required:

EXECUTE privilege on the SNAP_GET_DBM 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 the start time and current status of database member number 2.

SELECT DB2START_TIME, DB2_STATUS FROM TABLE(SNAP_GET_DBM(2)) AS T

The following is an example of output from this query.

DB2START_TIME              DB2_STATUS 
-------------------------- ------------
2006-01-06-14.59.59.062798 ACTIVE

Information returned

Table 1. Information returned by the SNAPDBM administrative view and the SNAP_GET_DBM table function
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	The date and time that the snapshot was taken.
SORT_HEAP_ALLOCATED	BIGINT	sort_heap_allocated - Total sort heap allocated
POST_THRESHOLD_SORTS	BIGINT	post_threshold_sorts - Post threshold sorts
PIPED_SORTS_REQUESTED	BIGINT	piped_sorts_requested - Piped sorts requested
PIPED_SORTS_ACCEPTED	BIGINT	piped_sorts_accepted - Piped sorts accepted
REM_CONS_IN	BIGINT	rem_cons_in - Remote connections to database manager
REM_CONS_IN_EXEC	BIGINT	rem_cons_in_exec - Remote Connections Executing in the Database Manager monitor element
LOCAL_CONS	BIGINT	local_cons - Local connections
LOCAL_CONS_IN_EXEC	BIGINT	local_cons_in_exec - Local Connections Executing in the Database Manager monitor element
CON_LOCAL_DBASES	BIGINT	con_local_dbases - Local databases with current connects
AGENTS_REGISTERED	BIGINT	agents_registered - Agents registered
AGENTS_WAITING_ON_TOKEN	BIGINT	agents_waiting_on_token - Agents waiting for a token
DB2_STATUS	VARCHAR(12)	db2_status - Status of the database instance This interface returns a text identifier based on definitions in sqlmon.h, and is one of: ACTIVE QUIESCE_PEND QUIESCED
AGENTS_REGISTERED_TOP	BIGINT	agents_registered_top - Maximum number of agents registered
AGENTS_WAITING_TOP	BIGINT	agents_waiting_top - Maximum number of agents waiting
COMM_PRIVATE_MEM	BIGINT	comm_private_mem - Committed private memory
IDLE_AGENTS	BIGINT	idle_agents - Number of idle agents
AGENTS_FROM_POOL	BIGINT	agents_from_pool - Agents assigned from pool
AGENTS_CREATED_EMPTY_POOL	BIGINT	agents_created_empty_pool - Agents created due to empty agent pool
COORD_AGENTS_TOP	BIGINT	coord_agents_top - Maximum number of coordinating agents
MAX_AGENT_OVERFLOWS	BIGINT	max_agent_overflows - Maximum agent overflows
AGENTS_STOLEN	BIGINT	agents_stolen - Stolen agents
GW_TOTAL_CONS	BIGINT	gw_total_cons - Total number of attempted connections for Db2® Connect
GW_CUR_CONS	BIGINT	gw_cur_cons - Current number of connections for Db2 Connect
GW_CONS_WAIT_HOST	BIGINT	gw_cons_wait_host - Number of connections waiting for the host to reply
GW_CONS_WAIT_CLIENT	BIGINT	gw_cons_wait_client - Number of connections waiting for the client to send request
POST_THRESHOLD_ HASH_JOINS	BIGINT	post_threshold_hash_joins - Hash join threshold
NUM_GW_CONN_SWITCHES	BIGINT	num_gw_conn_switches - Connection switches
DB2START_TIME	TIMESTAMP	db2start_time - Start database manager timestamp
LAST_RESET	TIMESTAMP	last_reset - Last reset timestamp
NUM_NODES_IN_ DB2_INSTANCE	INTEGER	num_nodes_in_db2_instance - Number of nodes in database partition
PRODUCT_NAME	VARCHAR(32)	product_name - Product name
SERVICE_LEVEL	VARCHAR(18)	service_level - Service level
SORT_HEAP_TOP	BIGINT	sort_heap_top - Sort private heap high water mark
DBPARTITIONNUM	SMALLINT	dbpartitionnum - Database partition number monitor element
POST_THRESHOLD_OLAP_FUNCS	BIGINT	post_threshold_olap_funcs - OLAP function threshold
MEMBER	SMALLINT	member - Database member monitor element