SQL using lots of CPU

This is the easiest method I've found to use snapshot table functions to help tune SQL. This was a life saver after migrating a client from DB2 on Z/os to Linux.

This method uses snapshot table functions and finds the 10 statements using the most CPU. Then I recommend running the db2 advisor on the biggest users.

----First create this view into the snapshot table function
create view DB2_MON_DYN_SQL as select * from table(snapshot_dyn_sql('',-1)) as sntable;

----You can then run this statement out of a file to view the 10 highest CPU using sql statements.

select int(num_executions) as "Num_Execs",
decimal(total_usr_cpu_time,10,2)as "Usr_CPU_Time",
int(stmt_sorts/num_executions) as "Sorts_Per_Stmt",
total_exec_time/num_executions as "Avg Time (sec)",
rows_read as "Rows_Read",
rows_written as "Rows_Written",
substr(stmt_text,1,25) as "SQL_Stmt"
from db2_mon_dyn_sql
where num_executions > 10
order by 2 desc
fetch first 10 rows only;