DB2 SQL Performance Review and optimization

1. Top10sql "with the most execution times"
DB2 "SELECT * from Sysibmadm.snapdyn_sql ORDER BY num_executions DESC FETCH first ten rows only"
2, the average execution time the longest Top10sql "
DB2 "SELECT * from Sysibmadm.top_dynamic_sql ORDER BY average_execution_time_s DESC FETCH first ten rows only"
3, the most ranked Top10sql "
DB2 "SELECT * from Sysibmadm.snapdyn_sql ORDER BY stmt_sorts DESC FETCH first ten rows only"

Executed using the instance user.

Find SQL with long run time

DB2 Get monitor Switches//get configuration of monitors

DB2 Update monitor switches using SORT on STATEMENT in TIMESTAMP on//Modify monitor settings

DB2 Reset monitor all//restart monitors

DB2 get snapshot for dynamic SQL on Dbname>sql//Get Snapshot

grep "Total execution Time" Sql|sort-rn +4>time//Get Runtime

Head-n 5 time//Get the top five running longer

Then put the time-consuming SQL into the Test.sql

Perform:

db2advis-d dbname-i test.sql-q Dbuser

To get optimization recommendations.

DB2 SQL Performance Review and optimization