The TOP_DYNAMIC_SQL administrative view returns the top dynamic SQL statements sortable by
number of executions, average execution time, number of sorts, or sorts per statement.
These are the queries that should get focus to ensure they are well tuned.

db2 "select num_executions as Num_Execs, average_execution_time_s as \
Avg_Time_sec, stmt_sorts as Num_Sorts, sorts_per_execution as Sorts_Per_Stmt,
substr(stmt_text,1,35) as SQL_Stmt from sysibmadm.top_dynamic_sql \
where num_executions > 0 order by 1 desc fetch first 5 rows only"

NUM_EXECS            AVG_TIME_SEC         NUM_SORTS            SORTS_PER_STMT       SQL_STMT
-------------------- -------------------- -------------------- -------------------- -----------------------------------
                9281                   0                    0                    0 INSERT INTO OPB_DTL_SWIDG_LOG (WORK
                 515                    0                    0                    0 SELECT OPB_GROUPS.GROUP_ID, OPB_GRO
                 515                    0                    0                    0 SELECT USER_ID, USER_NAME, USER_PAS
                 514                    0                    0                    0 SELECT OPB_GROUPS.GROUP_ID, OPB_GRO
                 514                    0                    0                    0 SELECT USER_ID, USER_NAME, USER_PAS

  5 record(s) selected.


These examples show how the WHERE clause and ORDER BY clause on a SELECT statement can be
effectively used to find the dynamic SQL statement statistics that will be of the highest interest for
performance reviews. The administrative view SYSIBMADM.TOP_DYNAMIC_SQL is used.

The example query also includes the number of sorts in the result, so that you can see if a query
is executed frequently and performs a lot of sorts, as these may be a good candidate for adding a new index.