Improving query performance for large statistics logs
If the statistics log files are large, you can improve query performance by copying the log records into a table, creating indexes, and then gathering statistics.
Procedure
- Create a table with appropriate columns for the log records.
create table db2user.stats_log ( pid bigint, tid bigint, timestamp timestamp, dbname varchar(128), retcode integer, eventtype varchar(24), objtype varchar(30), objschema varchar(20), objname varchar(30), event1_type varchar(20), event1 timestamp, event2_type varchar(20), event2 varchar(40), event3_type varchar(20), event3 varchar(40), eventstate varchar(20))
- Declare a cursor for a query against SYSPROC.PD_GET_DIAG_HIST.
declare c1 cursor for select pid, tid, timestamp, dbname, retcode, eventtype, substr(objtype, 1, 30) as objtype, substr(objname_qualifier, 1, 20) as objschema, substr(objname, 1, 30) as objname, substr(first_eventqualifiertype, 1, 20), substr(first_eventqualifier, 1, 26), substr(second_eventqualifiertype, 1, 20), substr(second_eventqualifier, 1, 40), substr(third_eventqualifiertype, 1, 20), substr(third_eventqualifier, 1, 40), substr(eventstate, 1, 20) from table (sysproc.pd_get_diag_hist ('optstats', 'EX', 'NONE', current_timestamp - 1 year, cast(null as timestamp ))) as sl
- Load the statistics log records into the table.
load from c1 of cursor replace into db2user.stats_log
- Create indexes and then gather statistics on the table.
create index sl_ix1 on db2user.stats_log(eventtype, event1); create index sl_ix2 on db2user.stats_log(objtype, event1); create index sl_ix3 on db2user.stats_log(objname); runstats on table db2user.stats_log with distribution and sampled detailed indexes all;