STAFF                   35        105            140

SYSTABLES                0         30             30

SYSROUTINES              0         10             10

INTERNAL                 0          5              5

Example 9 20 illustrates a usage of the 

SNAPSHOT_DYN_SQL()

 function, which is 

very useful for finding the SQL statements that are taking the most time in the 

database.

Example 9 20   Sample snapshot table function

SELECT stmt_text, total_exec_time, num_executions 

FROM TABLE( SNAPSHOT_DYN_SQL( DB2_EMP ,  1)) as dynSnapTab 

ORDER BY total_exec_time desc 

FETCH FIRST 5 ROW ONLY

Example 9 21 finds the five SQL statements with the worst average execution 

time.

Example 9 21   Sample snapshot table function

SELECT CASE WHEN num_executions = 0

            THEN 0

            ELSE (total_exec_time / num_executions)

       END avgExecTime,

       num_executions, 

       stmt_text

FROM TABLE( SNAPSHOT_DYN_SQL( DB2_EMP ,  1)) as dynSnapTab

ORDER BY avgExecTime desc

FETCH FIRST 5 ROWS ONLY

Like snapshot commands, snapshot table functions access point in time data 

kept by monitors in memory. To keep the history of the snapshots, create a table 

based on the snapshot query such as presented in Example 9 22; include the 

SNAPSHOT_TIMESTAMP

 column in the snapshot query, and periodically store the 

results of the query in the table.

Example 9 22   Storing snapshot data in a table

db2  create table table_snap_hist as 

(select snapshot_timestamp, table_name, rows_written, rows_read, 

rows_written + rows_read as rows_accessed 

from table (SNAPSHOT_TABLE( DB2_EMP ,  1))as T) definition only

db2 "insert into table_snap_hist 

select snapshot_timestamp, table_name, rows_written, rows_read, 

rows_written + rows_read as rows_accessed 

from table (SNAPSHOT_TABLE( DB2_EMP ,  1))as T 

290 

MySQL to DB2 UDB Conversion Guide