Or may be top 10. Or 3. Whatever the number, we are often looking for the worst offenders kicking up the server’s CPU utilisation or I/O wait time to the skies. DB2 built-in snapshot functions are a great help. Run “
select * from table (snapshot_dyn_sql('YOURDB', -1)) t order by rows_read desc fetch first 5 rows only” and you will get a list of the queries retrieving the most data. The only thing is, snapshot functions provide, well, snapshots of the DB2 monitor data and are not by themselves suitable for the collection of historical data. Monitor counters can be reset without your knowledge, for example when the database is deactivated or when the SQL statement expires from the package cache.
To ensure continuity of the data collected by snapshot functions we can quickly create a table where we will store historical snapshot information:
create table DYNSQLDATA as (select * from table (snapshot_dyn_sql('YOURDB', -1)) t) with no data
We will then insert output of the snapshot function into this table at regular intervals:
insert into DYNSQLDATA select t.* from table (snapshot_dyn_sql('YOURDB', -1)) t
Now we can easily determine which statements demand most resources and analyze their historical performance. Something like this to see what were the most resource-intensive queries in the past hour:
select sql_text, rows_read, num_executions , total_exec_time from dynsqldata where stmt_text in ( select distinct stmt_text from dynsqldata where snapshot_timestamp > current_timestamp - 1 hour order by rows_read desc fetch first 5 rows only)
Only… we cannot do that, really: DB2 stores the SQL statement text as a CLOB, which is subject to certain limitations when it comes to using this datatype in queries: CLOB’s cannot be used with the DISTINCT clause, in the ORDER BY or GROUP BY clauses, with the IN predicate, etc. In other words, we can keep the statement history but we cannot analyze it, comparing entries with one another, without jumping through some hoops.
We could, for example, try using the
SUBSTR() function to extract the first 1012 bytes of each CLOB, which would allow us to sort through the statements, but it is obvious that this approach is unacceptable if we are dealing with longer SQL statements. (DB2 9 increases the limit on the length of the sort key to 32 KB, but it still may not be enough in some cases.)
Luckily, there is another way of sorting through long SQL statements – without actually comparing their texts. Instead, we will be comparing their hash values.
Hash function is an algorithm that transforms an arbitrary value, an SQL statement in our case, into a shorter fixed-length value, sort of a fingerprint. The same input will always produce the same output, and while there is a probability that two different inputs will result in the same output, it is small enough that we can ignore it for our purposes.
Our first step will be to implement a user-defined function for the computation of an MD5 hash for a CLOB argument. The computation itself is performed by a Java class MessageDigest, which is a part of the core Java runtime environment since version 1.4.2 and is practically guaranteed to be present wherever DB2 is installed. You can download the Java source from here; after compiling it (“
javac Md5.java” should do it) and putting it into
sqllib/function you are ready to create the UDF:
CREATE FUNCTION md5( clob ) RETURNS char(32) LANGUAGE java PARAMETER STYLE db2general NO SQL FENCED THREADSAFE DETERMINISTIC RETURNS NULL ON NULL INPUT NO EXTERNAL ACTION EXTERNAL NAME 'Md5!md5' ;
Now all we need to is add a column for the hash value to the snapshot table that we created earlier:
alter table DYNSQLDATA add column stmt_md5 char (32)
Snapshots will be added to the table along with the statement hash values:
insert into DYNSQLDATA select t.*, md5(t.stmt_text) from table (snapshot_dyn_sql('YOURDB', -1)) t
Once we have collected enough snapshots we can proceed with analyzing historical performance data, discovering trends, and so on. As an example, we could come up with something like the crazy query below, which is trying to find 5 queries that read most rows each time they are executed within the past 8 hours.
with stats (stmt_md5, snapshot_timestamp, rows_per_exec, maxrows_exec, avgrows_stmt, avgnum_stmt, avgnum_all, avgrows_all, rownum) as ( select stmt_md5, snapshot_timestamp, case num_executions when 0 then 0 else rows_read/num_executions end rows_per_exec, case num_executions when 0 then 0 else max(rows_read/num_executions) over (partition by stmt_md5) end maxrows_exec, case num_executions when 0 then 0 else avg(rows_read/num_executions) over (partition by stmt_md5 order by snapshot_timestamp desc rows 30 preceding) end avgrows_stmt, avg(num_executions) over (partition by stmt_md5 order by snapshot_timestamp desc rows 30 preceding) avgnum_stmt, avg (num_executions) over () avgnum_all, case num_executions when 0 then 0 else avg(rows_read/num_executions) over () end avgrows_all, rownumber() over (partition by stmt_md5 order by snapshot_timestamp desc) rownum from DYNSQLDATA where snapshot_timestamp > (select max(snapshot_timestamp) from DYNSQLDATA) - 8 hours and num_executions > 0 ) select num_executions, rows_read, pool_data_p_reads, stmt_text, n from ( select d.num_executions, d.rows_read, d.pool_data_p_reads, d.stmt_text, rownumber() over (order by stats.avgnum_stmt desc) n from DYNSQLDATA d, stats where d.stmt_md5 = stats.stmt_md5 and d.snapshot_timestamp = stats.snapshot_timestamp and stats.rownum = 1 and stats.avgrows_stmt > 3*stats.avgrows_all and avgnum_stmt > 100 ) tt where tt.n <=5 order by tt.n desc