Db2

From the Db2 Digest for Monday November 27, 2023

Questions and Answers:

IBM TechXchange Community

Hi, my turn - I am with Bruce and Roy.  However, RUNSTATS plays a key role in several performance monitoring products in order to reflect performance for the current  database. It is also one of the main mechanisms for updating the Db2 Catalog, which the optimizer uses to choose access paths. 

Let's look at a practical, but notable use case:

An INDEX is added to a "large" table in a database to improve the performance of an SQL statement that reads it and RUNSTATS is not used nor any other Catalog update utility (for example, CATMAINT) for production.  You knew this was a problem access path because you ran an APM tool that captured and reported on the execution of the resource consuming SQL statement.  The table is used in a batch job for production during the nightly batch process window.  Prior to the addition of the INDEX, the batch window was starting to jeopardize the startup of the on-line subsystems (CICS/Db2/IMS, etc.) because its SQL statement had extremely poor performance. The addition of the INDEX (done first in the TEST environment, of course) proved to significantly reduce the processing time for this table in the batch JOB.  

If the Catalog is not updated, the optimizer will not choose the new INDEX (in our case) and the batch JOB will run as before, with an old access path, thus quasi nullifying the benefit of the new INDEX.  The APM tool is run with the batch JOB's execution, and also shows that the new INDEX was not used.  In the meantime, the DBA is feeling very comfortable because he/she "fixed" the issue. That changes at 3:00 AM (any time zone) when the DbA receives a call about the same old resource consumptive SQL statement on that table.

Now most production systems are astute enough to "always" run RUNSTATS (or equivalent) when INDEXES are added to a table when being migrated into Production.  So this means, regardless of the size of the table, updating the Catalog is a requirement – thus, for this Matthias' question, RUNSTATS needs to be run (or an equivalent). 

But for the shops that are not as careful as they should be, omitting the RUNSTATS because there is this HUGE table, would cause confusion at the very least (why isn't the new INDEX working - and the APM indicates such) and cause another long batch run cycle, which may or may not overrun the batch cycle window.

So, if you can avoid running RUNSTATS but still update the Db2 Catalog, then take that route.  If not, then run RUNSTATS.