Databases

DB2 Top 10 SQL for number of executions

SQL sentence to obtain the SQL TOP TEN with more executions of my BD2 database:

db2 "SELECT NUM_EXECUTIONS as EXECUTIONS, AVERAGE_EXECUTION_TIME_S as TIME_SECONDS, STMT_TEXT as TEXT 
     FROM SYSIBMADM.TOP_DYNAMIC_SQL 
     WHERE upper(STMT_TEXT) like 'SELECT%' 
     ORDER BY NUM_EXECUTIONS DESC 
     FETCH FIRST 10 ROWS ONLY"

DB2TOP register and replay

Actually DB2TOP is a real-time tool, however many DBA’s don’t know that it can be run in REPLAY mode with captured session information. So, next time you have a big crisis with your database, you can capture all the data from db2top and do the analysis afterwards.

How to capture the data? Option -C to capture. Press N to create a file. The option “-m” to gather data during n minutes.