SNAPDYN_SQL administrative view and SNAP_GET_DYN_SQL table function - Retrieve dynsql logical group snapshot information
The SNAPDYN_SQL administrative view and the SNAP_GET_DYN_SQL table function return snapshot information from the dynsql logical data group.
The MON_GET_PKG_CACHE_STMT table function - Get package cache statement metrics returns a point-in-time view of both static and dynamic SQL statements in the database package cache. If you use this table function, you can take advantage of the monitoring infrastructure introduced in Version 9.7.
Depending on if you are using the administrative view or
the table function, refer to one of the following sections:
SNAPDYN_SQL administrative view
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.
Authorization
One of the
following authorizations is required to use the view:
- SELECT privilege on the SNAPDYN_SQL administrative view
- CONTROL privilege on the SNAPDYN_SQL administrative view
- DATAACCESS authority
- DBADM authority
- SQLADM authority
- ACCESSCTRL authority
- SECADM authority
One of the following authorizations is required to use
the table function:
- EXECUTE privilege on the SNAP_GET_DYN_SQL 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 a list of dynamic SQL run
on the currently connected database, ordered by the number of rows
read.
SELECT PREP_TIME_WORST, NUM_COMPILATIONS, SUBSTR(STMT_TEXT, 1, 60)
AS STMT_TEXT, DBPARTITIONNUM
FROM SYSIBMADM.SNAPDYN_SQL ORDER BY ROWS_READ
The following is an example of output from this
query.
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.
Output from this query (continued).
... 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) ...
Output from this query (continued).
... DBPARTITIONNUM
... --------------
... 0
... 0
... 0
... 2
... 1
... 2
... 2
... 1
... 0
SNAP_GET_DYN_SQL table function
The SNAP_GET_DYN_SQL 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 member, aggregate of all database members or all database members.
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.
Syntax
The schema is SYSPROC.
Table function parameters
-
dbname
- An input argument of type VARCHAR(128) that specifies a valid database name in the same instance as the currently connected database. Specify a database name that has a directory entry type of either "Indirect" or "Home", as returned by the LIST DATABASE DIRECTORY command. Specify NULL or empty string to take the snapshot from the currently connected database. member
- An optional input argument of type INTEGER that specifies a valid database member number. Specify -1 for the current member, or -2 for an aggregate of all active members. If dbname is not set to NULL and member is set to NULL, -1 is set implicitly for member. If this input option is not used, that is, only dbname is provided, data is returned from members where the database is active.
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_DYN_SQL table function takes a snapshot for the currently connected database and database member number.
Important: The SYSPROC.SNAP_WRITE_FILE procedure is deprecated and
might be removed in a future release. For more information, see SNAP_WRITE_FILE procedure.
Authorization
One of the
following authorizations is required:
- EXECUTE privilege on the SNAP_GET_DYN_SQL 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 a list of dynamic SQL run
on the currently connected database, ordered by the number of rows
read.
SELECT PREP_TIME_WORST, NUM_COMPILATIONS, SUBSTR(STMT_TEXT, 1, 60)
AS STMT_TEXT FROM TABLE(SNAP_GET_DYN_SQL('',-1)) as T
ORDER BY ROWS_READ
The following is an example of output from this
query.
PREP_TIME_WORST NUM_COMPILATIONS ...
-------------------- ---------------------...
0 0 ...
49 1 ...
0 0 ...
46 1 ...
0 0 ...
0 0 ...
0 0 ...
29 1 ...
0 0 ...
0 0 ...
10 1 ...
0 0 ...
4 0 ...
53 0 ...
0 0 ...
6 1 ...
334 0 ...
0 0 ...
5 0 ...
10 0 ...
599 0 ...
15 1 ...
7 0 ...
23 record(s) selected.Output from this query
(continued).
... STMT_TEXT
... -----------------------------------------------------------
... SET :HV00017 :HI00017 = RPAD(VARCHAR(:HV00035 :HI00035 ),
... SELECT COLNAME, TYPENAME FROM SYSCAT.COLUMNS WHERE TABNAME=
... DECLARE RES CURSOR WITH RETURN TO CALLER FOR SELECT R.TEXT F
... SELECT PREP_TIME_WORST, NUM_COMPILATIONS, SUBSTR(STMT_TEXT,
... VALUES (:HV00026 :HI00026 + 1, :HV00024 :HI00024 + 1) IN
... VALUES (:HV00035 :HI00035 + 1, :HV00024 :HI00024 + 1) IN
... VALUES (1) INTO :HV00035 :HI00035
... SELECT TRIGNAME FROM SYSCAT.TRIGGERS WHERE TABNAME='POLICY'
... VALUES (:HV00024 :HI00024 +1, :HV00022 :HI00022 +1) INTO :
... VALUES (1, CARDINALITY(CAST(:HV00040 :HI00040 AS "SYSIBMAD
... CALL SYSPROC.SYSINSTALLOBJECTS('POLICY','V','','')
... SET :HV00017 :HI00017 = RPAD(VARCHAR(:HV00035 :HI00035 ),
... drop event monitor act
... SELECT TABSCHEMA, TABNAME, TYPE, STATUS, TBSPACEID, PROPERTY
... CALL SAVE_EXEC_INFO (CAST(:HV00040 :HI00040 AS "SYSIBMADM"
... SET CURRENT LOCK TIMEOUT 5
... SELECT TABNAME FROM SYSCAT.PERIODS WHERE PERIODNAME = 'SYSTE
... SELECT ARRAY_AGG(P.EXECUTABLE_ID ORDER BY M.IO_WAIT_TIME DES
... SET CURRENT ISOLATION RESET
... CALL monreport.pkgcache()
... SELECT A.SPECIFICNAME FROM SYSCAT.ROUTINES A WHERE (A.FENCED
... SELECT POLICY FROM SYSTOOLS.POLICY WHERE MED='DB2CommonMED'
... VALUES 0
23 record(s) selected.After running a workload,
user can use the following query with the table function.
SELECT STATS_FABRICATE_TIME,SYNC_RUNSTATS_TIME
FROM TABLE (SNAP_GET_DYN_SQL('mytestdb', -1))
AS SNAPDB
STATS_FABRICATE_TIME SYNC_RUNSTATS_TIME
---------------------- ------------------
2 12
1 30
For the view based on this table function:
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.Information returned
| Column name | Data type | Description or corresponding monitor element |
|---|---|---|
| SNAPSHOT_TIMESTAMP | TIMESTAMP | The date and time that the snapshot was taken. |
| 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 |
| TOTAL_EXEC_TIME_MS | BIGINT | total_exec_time - Elapsed statement execution time |
| TOTAL_USR_CPU_TIME | BIGINT | total_usr_cpu_time - Total user CPU for a statement |
| TOTAL_USR_CPU_TIME_MS | BIGINT | total_usr_cpu_time - Total user CPU for a statement |
| TOTAL_SYS_CPU_TIME | BIGINT | total_sys_cpu_time - Total system CPU for a statement |
| TOTAL_SYS_CPU_TIME_MS | BIGINT | total_sys_cpu_time - Total system CPU for a statement |
| STMT_TEXT | CLOB(2 M) | stmt_text - SQL statement text |
| DBPARTITIONNUM | SMALLINT | dbpartitionnum - Database partition number monitor element |
| 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. |
| 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. | ||