DB2 Version 9.7 for Linux, UNIX, and Windows

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

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

SNAPDBM administrative view

Used with the SNAPDBM_MEMORY_POOL, SNAPFCM, SNAPFCM_PART and SNAPSWITCHES administrative views, 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:

SELECT privilege on the SNAPDBM administrative view
CONTROL privilege on the SNAPDBM administrative view
DATAACCESS authority

In addition, one of the following privileges or authorities is also required:

EXECUTE privilege on the SNAP_GET_DBM_V95 table function
DATAACCESS authority

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

SYSMON
SYSCTRL
SYSMAINT
SYSADM

Example

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

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 example is a sample 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_V95 table function

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

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_V95 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


>>-SNAP_GET_DBM_V95--(--+----------------+--)------------------><
                        '-dbpartitionnum-'

The schema is SYSPROC.

Table function parameter

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 this input option is not used, data will be returned from all active database partitions. An active database partition is a partition where the database is available for connection and use by applications.

If dbpartitionnum 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_V95 table function calls the snapshot from memory.

Authorization

One of the following authorizations is required:

EXECUTE privilege on the SNAP_GET_DBM_V95 table function
DATAACCESS authority

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

SYSMON
SYSCTRL
SYSMAINT
SYSADM

Example

Retrieve the start time and current status of database partition number 2.

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

The following example is a sample 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_V95 table function
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	snapshot_timestamp - Snapshot timestamp monitor element
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 DB2 instance This interface returns a text identifier based on defines 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	The database partition from which the data was retrieved for this row.
POST_THRESHOLD_OLAP_FUNCS	BIGINT	The number of OLAP functions which have requested a sort heap after the sort heap threshold has been exceeded. Sorts, hash joins, and OLAP functions are examples of operations which use a sort heap. Under normal conditions, the database manager will allocate sort heap using the value specified by the `sortheap` configuration parameter. If the amount of memory allocated to sort heaps exceeds the sort heap threshold (`sheapthres` configuration parameter), the database manager will allocate subsequent sort heaps using a value less than that specified by the `sortheap` configuration parameter. OLAP functions which start after the sort heap threshold has been reached may not receive an optimum amount of memory to execute.