The SNAPDYN_SQL administrative view and the SNAP_GET_DYN_SQL_V95 table function return snapshot information from the dynsql logical data group.
This administrative view allows you to retrieve dynsql logical group snapshot information for the currently connected database.
This view returns information equivalent to the GET SNAPSHOT FOR DYNAMIC SQL ON database-alias CLP command.
The schema is SYSIBMADM.
Refer to Table 1 for a complete list of information that can be returned.
SELECT PREP_TIME_WORST, NUM_COMPILATIONS, SUBSTR(STMT_TEXT, 1, 60)
AS STMT_TEXT, DBPARTITIONNUM
FROM SYSIBMADM.SNAPDYN_SQL ORDER BY ROWS_READ
PREP_TIME_WORST NUM_COMPILATIONS ...
-------------------- -------------------- ...
98 1 ...
9 1 ...
0 0 ...
0 1 ...
0 1 ...
0 1 ...
0 1 ...
0 1 ...
40 1 ...
9 record(s) selected.
... STMT_TEXT ...
... ------------------------------------------------------------ ...
... select prep_time_worst, num_compilations, substr(stmt_text, ...
... select * from dbuser.employee ...
... SET CURRENT LOCALE LC_CTYPE = 'en_US' ...
... select prep_time_worst, num_compilations, substr(stmt_text, ...
... select prep_time_worst, num_compilations, substr(stmt_text, ...
... select * from dbuser.employee ...
... insert into dbuser.employee values(1) ...
... select * from dbuser.employee ...
... insert into dbuser.employee values(1) ...
... DBPARTITIONNUM
... --------------
... 0
... 0
... 0
... 2
... 1
... 2
... 2
... 1
... 0
The SNAP_GET_DYN_SQL_V95 table function returns the same information as the SNAPDYN_SQL administrative view, but allows you to retrieve the information for a specific database on a specific database partition, aggregate of all database partitions or all database partitions.
This table function returns information equivalent to the GET SNAPSHOT FOR DYNAMIC SQL ON database-alias CLP command.
Refer to Table 1 for a complete list of information that can be returned.
>>-SNAP_GET_DYN_SQL_V95--(--dbname--+------------------+--)---->< '-, dbpartitionnum-'
The schema is SYSPROC.
If both dbname and dbpartitionnum 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_DYN_SQL_V95 table function takes a snapshot for the currently connected database and database partition number.
SELECT PREP_TIME_WORST, NUM_COMPILATIONS, SUBSTR(STMT_TEXT, 1, 60)
AS STMT_TEXT FROM TABLE(SNAP_GET_DYN_SQL_V95('',-1)) as T
ORDER BY ROWS_READ
PREP_TIME_WORST ...
-------------------- ...
0 ...
3 ...
...
4 ...
...
4 ...
...
4 ...
...
3 ...
...
4 ...
...
... NUM_COMPILATIONS STMT_TEXT
... -------------------- ---------------------------------------...-
... 0 SET CURRENT LOCALE LC_CTYPE = 'en_US'
... 1 select rows_read, rows_written,
... substr(stmt_text, 1, 40) as
... 1 select * from table
... (snap_get_dyn_sqlv9('',-1)) as t
... 1 select * from table
... (snap_getdetaillog9('',-1)) as t
... 1 select * from table
... (snap_get_hadr('',-1)) as t
... 1 select prep_time_worst, num_compilations,
... substr(stmt_text,
... 1 select prep_time_worst, num_compilations,
... substr(stmt_text,
SELECT STATS_FABRICATE_TIME,SYNC_RUNSTATS_TIME
FROM TABLE (SNAP_GET_DYN_SQL_V95('mytestdb', -1))
AS SNAPDB
STATS_FABRICATE_TIME SYNC_RUNSTATS_TIME
---------------------- ------------------
2 12
1 30
SELECT STATS_FABRICATE_TIME,SYNC_RUNSTATS_TIME
FROM SYSIBMADM.SNAPDYN_SQL
STATS_FABRICATE_TIME SYNC_RUNSTATS_TIME
---------------------- ------------------
5 10
3 20
2 record(s) selected.
Column name | Data type | Description or corresponding monitor element |
---|---|---|
SNAPSHOT_TIMESTAMP | TIMESTAMP | snapshot_timestamp - Snapshot timestamp monitor element |
NUM_EXECUTIONS | BIGINT | num_executions - Statement executions |
NUM_COMPILATIONS | BIGINT | num_compilations - Statement compilations |
PREP_TIME_WORST | BIGINT | prep_time_worst - Statement worst preparation time |
PREP_TIME_BEST | BIGINT | prep_time_best - Statement best preparation time |
INT_ROWS_DELETED | BIGINT | int_rows_deleted - Internal rows deleted |
INT_ROWS_INSERTED | BIGINT | int_rows_inserted - Internal rows inserted |
INT_ROWS_UPDATED | BIGINT | int_rows_updated - Internal rows updated |
ROWS_READ | BIGINT | rows_read - Rows read |
ROWS_WRITTEN | BIGINT | rows_written - Rows written |
STMT_SORTS | BIGINT | stmt_sorts - Statement sorts |
SORT_OVERFLOWS | BIGINT | sort_overflows - Sort overflows |
TOTAL_SORT_TIME | BIGINT | total_sort_time - Total sort time |
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_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_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_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_XDA_L_READS | BIGINT | pool_xda_l_reads - Buffer Pool XDA Data Logical Reads |
POOL_XDA_P_READS | BIGINT | pool_xda_p_reads - Buffer Pool XDA Data 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 |
TOTAL_EXEC_TIME | BIGINT | total_exec_time - Elapsed statement execution time (in seconds)* |
TOTAL_EXEC_TIME_MS | BIGINT | total_exec_time - Elapsed statement execution time (fractional, in microseconds)* |
TOTAL_USR_CPU_TIME | BIGINT | total_usr_cpu_time - Total user CPU for a statement (in seconds)* |
TOTAL_USR_CPU_TIME_MS | BIGINT | total_usr_cpu_time - Total user CPU for a statement (fractional, in microseconds)* |
TOTAL_SYS_CPU_TIME | BIGINT | total_sys_cpu_time - Total system CPU for a statement (in seconds)* |
TOTAL_SYS_CPU_TIME_MS | BIGINT | total_sys_cpu_time - Total system CPU for a statement (fractional, in microseconds)* |
STMT_TEXT | CLOB(2 M) | stmt_text - SQL statement text |
DBPARTITIONNUM | SMALLINT | The database partition from which the data was retrieved for this row. |
STATS_FABRICATE_TIME | BIGINT | The total time (in milliseconds) spent by system to create needed statistics without table or index scan during query compilation for a dynamic statement. |
SYNC_RUNSTATS_TIME | BIGINT | The total time (in milliseconds) spent on synchronous statistics-collect activities during query compilation for a dynamic statement. |
* 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. |