DB2 Version 10.1 for Linux, UNIX, and Windows
DB2 Version 10.1 for Linux, UNIX, and Windows
The SNAPSTMT administrative view and the SNAP_GET_STMT table function return information about SQL or XQuery statements from an application snapshot.
This administrative view allows you to retrieve statement snapshot information for the currently connected database.
Used with the SNAPAGENT, SNAPAGENT_MEMORY_POOL, SNAPAPPL, SNAPAPPL_INFO and SNAPSUBSECTION administrative views, the SNAPSTMT administrative view provides information equivalent to the GET SNAPSHOT FOR APPLICATIONS on database-alias CLP command, but retrieves data from all database members.
The schema is SYSIBMADM.
Refer to Table 1 for a complete list of information that can be returned.
In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.
SELECT SUBSTR(STMT_TEXT,1,30) AS STMT_TEXT, ROWS_READ, ROWS_WRITTEN,
STMT_OPERATION FROM SYSIBMADM.SNAPSTMTSTMT_TEXT ROWS_READ ROWS_WRITTEN STMT_OPERATION
---------...- ---------...- ------------...- --------------------
- 0 0 FETCH
- 0 0 STATIC_COMMIT
2 record(s) selected.The SNAP_GET_STMT table function returns the same information as the SNAPSTMT administrative view, but allows you to retrieve the information for a specific database on a specific database member, aggregate of all database members or all database members.
Used with the SNAP_GET_AGENT, SNAP_GET_AGENT_MEMORY_POOL, SNAP_GET_APPL, SNAP_GET_APPL_INFO and SNAP_GET_SUBSECTION table functions, the SNAP_GET_STMT table function provides information equivalent to the GET SNAPSHOT FOR ALL APPLICATIONS CLP command, but retrieves data from all database partitions.
Refer to Table 1 for a complete list of information that can be returned.
>>-SNAP_GET_STMT--(--dbname--+----------+--)------------------->< '-, member-'
The schema is SYSPROC.
If both dbname and member are 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_STMT table function takes a snapshot for the currently connected database and database member number.
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.
SELECT SUBSTR(STMT_TEXT,1,30) AS STMT_TEXT, ROWS_READ,
ROWS_WRITTEN, STMT_OPERATION FROM TABLE(SNAP_GET_STMT('',-1)) AS TSTMT_TEXT ROWS_READ ...
------------------------------ ---------...- ...
update t set a=3 0 ...
SELECT SUBSTR(STMT_TEXT,1,30) 0 ...
- 0 ...
- 0 ...
update t set a=2 9 ...
...
5 record(s) selected. ...... ROWS_WRITTEN STMT_OPERATION
... ------------...- --------------------
... 0 EXECUTE_IMMEDIATE
... 0 FETCH
... 0 NONE
... 0 NONE
... 1 EXECUTE_IMMEDIATE
... | Column name | Data type | Description or corresponding monitor element |
|---|---|---|
| SNAPSHOT_TIMESTAMP | TIMESTAMP | The date and time that the snapshot was taken. |
| DB_NAME | VARCHAR(128) | db_name - Database name |
| AGENT_ID | BIGINT | agent_id - Application handle (agent ID) |
| ROWS_READ | BIGINT | rows_read - Rows read |
| ROWS_WRITTEN | BIGINT | rows_written - Rows written |
| NUM_AGENTS | BIGINT | num_agents - Number of agents working on a statement |
| AGENTS_TOP | BIGINT | agents_top - Number of agents created |
| STMT_TYPE | VARCHAR(20) | stmt_type - Statement type . This interface returns
a text identifier based on defines in sqlmon.h and is one of:
|
| STMT_OPERATION | VARCHAR(20) | stmt_operation/operation - Statement
operation . This interface returns a text identifier based on
defines in sqlmon.h and is one of:
|
| SECTION_NUMBER | BIGINT | section_number - Section number |
| QUERY_COST_ESTIMATE | BIGINT | query_cost_estimate - Query cost estimate |
| QUERY_CARD_ESTIMATE | BIGINT | query_card_estimate - Query number of rows estimate |
| DEGREE_PARALLELISM | BIGINT | degree_parallelism - Degree of parallelism |
| STMT_SORTS | BIGINT | stmt_sorts - Statement sorts |
| TOTAL_SORT_TIME | BIGINT | total_sort_time - Total sort time |
| SORT_OVERFLOWS | BIGINT | sort_overflows - Sort overflows |
| INT_ROWS_DELETED | BIGINT | int_rows_deleted - Internal rows deleted |
| INT_ROWS_UPDATED | BIGINT | int_rows_updated - Internal rows updated |
| INT_ROWS_INSERTED | BIGINT | int_rows_inserted - Internal rows inserted |
| FETCH_COUNT | BIGINT | fetch_count - Number of successful fetches |
| STMT_START | TIMESTAMP | stmt_start - Statement operation start timestamp |
| STMT_STOP | TIMESTAMP | stmt_stop - Statement operation stop timestamp |
| STMT_USR_CPU_TIME_S | BIGINT | stmt_usr_cpu_time - User CPU time used by statement (in seconds)* |
| STMT_USR_CPU_TIME_MS | BIGINT | stmt_usr_cpu_time - User CPU time used by statement (fractional, in microseconds)* |
| STMT_SYS_CPU_TIME_S | BIGINT | stmt_sys_cpu_time - System CPU time used by statement (in seconds)* |
| STMT_SYS_CPU_TIME_MS | BIGINT | stmt_sys_cpu_time - System CPU time used by statement (fractional, in microseconds)* |
| STMT_ELAPSED_TIME_S | BIGINT | stmt_elapsed_time - Most recent statement elapsed time (in seconds)* |
| STMT_ELAPSED_TIME_MS | BIGINT | stmt_elapsed_time - Most recent statement elapsed time (fractional, in microseconds)* |
| BLOCKING_CURSOR | SMALLINT | blocking_cursor - Blocking cursor |
| STMT_NODE_NUMBER | SMALLINT | stmt_node_number - Statement node |
| CURSOR_NAME | VARCHAR(128) | cursor_name - Cursor name |
| CREATOR | VARCHAR(128) | creator - Application creator |
| PACKAGE_NAME | VARCHAR(128) | package_name - Package name |
| STMT_TEXT | CLOB(16 M) | stmt_text - SQL statement text |
| CONSISTENCY_TOKEN | VARCHAR(128) | consistency_token - Package consistency token |
| PACKAGE_VERSION_ID | VARCHAR(128) | package_version_id - Package version |
| POOL_DATA_L_READS | BIGINT | pool_data_l_reads - Buffer pool data logical reads |
| POOL_DATA_P_READS | BIGINT | pool_data_p_reads - Buffer pool data physical reads |
| POOL_INDEX_L_READS | BIGINT | pool_index_l_reads - Buffer pool index logical reads |
| POOL_INDEX_P_READS | BIGINT | pool_index_p_reads - Buffer pool index physical reads |
| POOL_XDA_L_READS | BIGINT | pool_xda_l_reads - Buffer Pool XDA Data Logical Reads monitor element |
| POOL_XDA_P_READS | BIGINT | pool_xda_p_reads - Buffer Pool XDA Data Physical Reads monitor element |
| POOL_TEMP_DATA_L_READS | BIGINT | pool_temp_data_l_reads - Buffer pool temporary data logical reads |
| POOL_TEMP_DATA_P_READS | BIGINT | pool_temp_data_p_reads - Buffer pool temporary data physical reads |
| POOL_TEMP_INDEX_L_READS | BIGINT | pool_temp_index_l_reads - Buffer pool temporary index logical reads |
| POOL_TEMP_INDEX_P_READS | BIGINT | pool_temp_index_p_reads - Buffer pool temporary index physical reads |
| POOL_TEMP_XDA_L_READS | BIGINT | pool_temp_xda_l_reads - Buffer Pool Temporary XDA Data Logical Reads |
| POOL_TEMP_XDA_P_READS | BIGINT | pool_temp_xda_p_reads - Buffer Pool Temporary XDA Data Physical Reads monitor element |
| DBPARTITIONNUM | SMALLINT | dbpartitionnum - Database partition number monitor element |
| MEMBER | SMALLINT | member - Database member monitor element |
| * To calculate the total time spent for the monitor element that this column is based on, you must add the full seconds reported in the column for this monitor element that ends with _S to the fractional seconds reported in the column for this monitor element that ends with _MS, using the following formula: (monitor-element-name_S × 1,000,000 + monitor-element-name_MS) ÷ 1,000,000. For example, (ELAPSED_EXEC_TIME_S × 1,000,000 + ELAPSED_EXEC_TIME_MS) ÷ 1,000,000. | ||