20 January 2017
The IDUG presentation “RUNSTATS profiles and SYSSTATFEEDBACK are Happily Married “ by Steen Rasmussen, give us a clearly and complete view of all the components linked with RUNSTATS. We decide to implement it in CLICK4DB scenarios. Thanks to him.
RUNSTATS profile have been introduced in DB2 V10 to centralize by table all the data required for a table and by the way, simplify your RUNSTATS statement. The profile management is made via special RUNSTATS statement, you can create, update, delete or display the profile information of each table.
In V11, the optimizer can externalize all the missing statistics to make the best access path determination. The data are stored in SYSIBM.SYSSTATFEEDBACK in case of bind, and in DSN_STAT_FEEDBACK in case of explain.
With Click4DB, you can manage automatically all the information’s from the feedback tables to populate the RUNSTATS profile. You can also use our “clever RUNSTATS: it use tables’ profiles when they exist or the standard RUNSTATS for the other database objects.
We also show the content of the SYSIBM.SYSSTATFEEDBACK before and after the RUNSTATS to validate the process. We delete obsolete rows from DSN_STAT_FEEDBACK to reflect the RUNSTATS as an automatic DB2 clean-up is not natively foreseen.
To complete this topic, we have also the ability to propagate the RUNSTATS information from one DB2 subsystem to all other’s selected DB2’s. You can propagate the basic information (like cardf, firstkeycardf, …) or all the information including cardinality, frequency, and histogram statistics for a single column or a column group.