Should I update the corresponding class when run tunetable?
Online document says: TUNE TABLE updates the SQL table definition (and therefore requires privileges to alter the table definition). Commonly, TUNE TABLE also updates the corresponding persistent class definition. This allows the gathered statistics to be used by the query optimizer without requiring a class compilation.
I don't get the point. If I run tune table once with class update and next time no class update, does this cause inconsistce of sql definition and class definition? What happen if I recompile the class ? will table stats in class definition will overwrite which in the sql definition?
then, what this the usage of class update with tune table, for exported the class definition with table statistics in this instance ?
thank you for your time
Comments
From the available material, the main point is this:
TUNE TABLEalways updates the SQL table definition.- It also commonly updates the corresponding persistent class definition.
- The reason given is that this lets the query optimizer use the gathered statistics without requiring a class compilation.
- If the class is deployed,
TUNE TABLEupdates only the SQL table definition, and the optimizer uses the gathered statistics indirectly from the table definition. [1]
So, to your first concern: the documents do not describe this as a harmful inconsistency if one run updates the class and a later run does not. They only state that the SQL table definition is updated, and that class update is common but not always done; for deployed classes, only the SQL table definition is updated and the optimizer still uses the statistics. That indicates the optimizer can work from the SQL table definition even when the class is not updated. [1]
On recompiling the class, the documents only explicitly say:
- when
TUNE TABLEchanges values, it may flag the table’s class definition for recompile; - updating the class definition allows the optimizer to use the statistics without requiring compilation;
- if the class is deployed, the optimizer can use the table definition instead. [1]
They do not explicitly say whether a later class compile will overwrite SQL table-definition statistics, or which side “wins” after recompilation. So I can’t confirm that part from the provided sources. [1]
About the purpose of “class update”, one relevant explanation from the retrieved material is that table statistics are stored as part of the class definition, based on the idea that they are often relatively static and belong with the application. One comment also notes that this means stats can be lost if a system library is overwritten during upgrade, which supports your understanding that class update is related to keeping/exporting those statistics with the class/application definition. [2]
Also relevant:
%CLEAR_VALUESclears existingSELECTIVITY,EXTENTSIZE, and similar values from both the class and table definition. [1]- Running
TUNE TABLErecompiles existing cached queries for the specified table to use the new Tune Table values, unless no values changed. [1]
So the safest summary is:
- Updating the class is useful because it makes the gathered stats available through the class metadata and avoids needing class compilation for optimizer use. [1]
- Not updating the class does not mean the optimizer cannot use the stats, because the SQL table definition is still updated, and this is explicitly how deployed classes work. [1]
- The sources do not explicitly answer whether recompiling the class later overwrites SQL-definition stats, so that specific overwrite behavior is not confirmed here. [1]
- Yes, one documented use of class update is effectively to keep the statistics with the class/application metadata, which aligns with export/deployment scenarios. [2]
If you want, I can help restate this as a practical recommendation for when to use class update in dev, test, and production.
Sources: