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.


  1. 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))
  2. 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
  3. Load the statistics log records into the table.
       load from c1 of cursor replace into db2user.stats_log
  4. 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;