The SNAPDBM administrative view and the SNAP_GET_DBM_V95 table function return the snapshot monitor DB2® database manager (dbm) logical grouping information.
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.
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
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. ...
... LOCAL_CONS REM_CONS_IN AGENT_USAGE DBPARTITIONNUM
... ----------...- -----------...- -----------...- --------------
... 1 1 0 0
... 0 0 0 1
... 0 0 0 2
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.
>>-SNAP_GET_DBM_V95--(--+----------------+--)------------------>< '-dbpartitionnum-'
The schema is SYSPROC.
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.
SELECT DB2START_TIME, DB2_STATUS FROM TABLE(SNAP_GET_DBM_V95(2)) AS T
DB2START_TIME DB2_STATUS
-------------------------- ------------
2006-01-06-14.59.59.062798 ACTIVE
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:
|
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. |