Weekly Tips from DB2 Experts: How to identify a long running or badly tuned dynamic SQL using DB2 snapshots?

Body

We have many customers wondering how to fine tune their dynamic SQL queries . The following snapshots can be run based on a particular environment:

Single partition:

db2 get snapshot for dynamic sql on <dbname>

DPF:

db2 get snapshot for dynamic sql on <dbname> global

Example:

Number of executions               = 2
 Number of compilations             = 0
 Worst preparation time (ms)        = 338
 Best preparation time (ms)         = 338
 Internal rows deleted              = 0
 Internal rows inserted             = 0
 Rows read                          = 66468018
 Internal rows updated              = 0
 Rows written                       = 4177641
 Statement sorts                    = 1022221
 Statement sort overflows           = 8
 Total sort time                    = 13411
 Buffer pool data logical reads     = 13592056
 Buffer pool data physical reads    = 221958
 Buffer pool temporary data logical reads   = 716112
 Buffer pool temporary data physical reads  = 6714
 Buffer pool index logical reads    = 53460655
 Buffer pool index physical reads   = 108922
 Buffer pool temporary index logical reads  = 0
 Buffer pool temporary index physical reads = 0
 Total execution time (sec.ms)      = 760.660240
 Total user cpu time (sec.ms)       = 130.975914   ---> 130/2 = 65 seconds per execution, which is high.
 Total system cpu time (sec.ms)     = 7.009845
 Statement text                     = select * from ACT

You can run the above snapshots couple of times as its a cumulative .

From the above example, the 'Total user cpu time (sec.ms) / Number of executions' will give us the per execution time of each query.

If the per execution is too high, then we can start analyzing this by using  db2advis(Design Advisor) on the problematic query to see if there are any recommendations.