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;