-1

After reading many of articles from the internet, I am still not sure what is the actual purpose of DB2 Runstats.

As my understanding, DB2 Runstats will "register" the table index to the DB2 catalog, so that next time when the related query run, it will use the index to increase the performance. (Please correct me if I am wrong)

Meaning, if for a long period of time the DB2 Runstats is not run, the index will be removed from the DB2 catalog?

I am creating a new index for a table. Originally that table already contained another index.

After creating the new index, I ran DB2 Runstats on the table for the old index, but I hit the following error:

SQL2314W Some statistics are in an inconsistent state. The newly collected "INDEX" statistics are inconsistent with the existing "TABLE" statistics. SQLSTATE=01650

At first I was thinking it's cause by the activity to create the new index, and the table was still in the "processing" stage. I ran the DB2 Runstats command again the next day but still got the same error.

SovietFrontier
  • 2,047
  • 1
  • 15
  • 33
Panadol Chong
  • 1,793
  • 13
  • 54
  • 119

1 Answers1

3

Your understanding about db2 runstats is not correct. This command collects statistics on the given table and its indexes and placed it to views in the SYSSTAT schema like SYSSTAT.TABLES, SYSSTAT.INDEXES, etc. This information is used by the DB2 optimizer to produce better access plans of your queries. It doesn't "register" indexes itself. Indexes are not removed automatically if you don't collect statistics on them.

As for the warning message SQL2314W.

It's just a warning that table and index statistics is not logically compatible (for example, number of index keys is more than number of rows in the table). Sometimes it happens when you collect statistics on actively updated table at the same time even you run such a collection on a table and its indexes using a single RUNSTATS command. You can either ignore this message or make the RUNSTATS utility lock the table during the statistics collection on table and its indexes using a single command (ALLOW READ ACCESS clause).

Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16
  • Thanks for the explain. Would like to ask about the "produce better access plans of your queries". Is this also applicable when application query to db as well? If I not run the runstats command, will the old index still take effect? If yes, may I know what is the different with runstats command and without runstats command. I apologize if I am asking stupid question. – Panadol Chong Dec 20 '18 at 09:35
  • 1
    db2 uses so called 'cost-based' optimizer. This means, that not only presence of database objects (like indexes) influences the optimizer's decision, but data and index statistics collected earlier as well. So, generally, it's better to have the most current statistics collected to help the optimizer choose the most efficient access plan from all possible ones. – Mark Barinstein Dec 20 '18 at 10:19
  • If I ignore the warning message, will it impact my queries to the table? – Panadol Chong Dec 21 '18 at 01:59
  • It depends on "size of statistics inconsistency". – Mark Barinstein Dec 21 '18 at 10:12
  • Hi @Mark Barinstein, may I know what is means by statistics inconsistency ? what should I do if I want to avoid to hit this warning message. If I ignore it, means the statistics collection is fail right? If fail, will it cause any issue to the table? – Panadol Chong Oct 03 '19 at 02:35
  • @PanadolChong Please, read my post carefully again. There are answers to all your questions already. I don’t know how to answer your questions in other words. – Mark Barinstein Oct 03 '19 at 05:02