>>---SQLCACHE_SNAPSHOT-----------------------------------------><
The schema is SYSFUN.
The SQLCACHE_SNAPSHOT returns the the results of a snapshot of the db2 dynamic sql statement cache, when a snapshot is taken with iStoreResult set to true.
The results of the snapshot are returned to the buffer and written to the file applid.sql in the tmp subdirectory of the instance's sqllib subdirectory on the DB2 server. Where applid is the application ID of the user making the snapshot request. You can then access the snapshot data using the table function SQLCACHE_SNAPSHOT.
You can select specific columns by referencing data elements by their snapshot's name. For example:
1) get snapshot for dynamic sql on foo write to file
2) select table_name.db_name, table_name.num_executions from
table(sysfun.SQLCACHE_SNAPSHOT()) table_name where
table_name.commit_sql_stmts > 100
Where table_name is an arbitrary valid SQL identifier. The table function in this example is called SQLCACHE_SNAPSHOT(). This corresponds to a table function for returning information from a get snapshot for dynamic SQL.
For time and time and timestamp elements, the identifier names are idname_s and idname_ms. For example, for the data element total_exec_time, the identifiers would be total_exec_time_s and total_exec_time_ms.
The file containing the results of a dynamic SQL snapshot will be overwritten by the next dynamic SQL snapshot request. As well, any snapshot data files generated during the database connection are erased when the application disconnects.
The function does not take any arguments. A snapshot of the
statement cache can only be taken over a database connection. If write
to file is attempted over an instance attachement, the request will be
rejected.
Table 4. Column names and data types of the table returned by SQLCACHE_SNAPSHOT table function
| Column name | Data type |
|---|---|
| NUM_EXECUTIONS | INTEGER |
| NUM_COMPILATIONS | INTEGER |
| PREP_TIME_WORST | INTEGER |
| PREP_TIME_BEST | INTEGER |
| INT_ROWS_DELETED | INTEGER |
| INT_ROWS_INSERTED | INTEGER |
| ROWS_READ | INTEGER |
| INT_ROWS_UPDATED | INTEGER |
| ROWS_WRITE | INTEGER |
| STMT_SORTS | INTEGER |
| TOTAL_EXEC_TIME_S | INTEGER |
| TOTAL_EXEC_TIME_MS | INTEGER |
| TOT_U_CPU_TIME_S | INTEGER |
| TOT_U_CPU_TIME_MS | INTEGER |
| TOT_S_CPU_TIME_S | INTEGER |
| TOT_S_CPU_TIME_MS | INTEGER |
| DB_NAME | VARCHAR(8) |
| STMT_TEXT | CLOB(64K) |