Skip to main content
Previous sectionNext section


Tunes a table based on representative data.


TUNE TABLE tablename [ tune_options ]


Argument Description
tablename The name of an existing table to be tuned. The table name can be qualified (schema.table), or unqualified (table). An unqualified table name takes the default schema name.
tune_options Optional — If specified, one or more TUNE TABLE options, specified in any order, separated by spaces. These tune_options are not case sensitive.


The TUNE TABLE command tunes an existing table based on the data currently in the table. This data should be representative of the data expected when the table is fully populated.

To execute a TUNE TABLE statement, the user must have the %ALTER_TABLE SQL system privilege. To execute TUNE TABLE the user must have %ALTER privilege on the table being tuned.

If TUNE TABLE is successful, it sets SQLCODE = 0. If the specified tablename does not exist, TUNE TABLE issues an SQLCODE -30 error. If the user does not have %ALTER privileges for the specified table, TUNE TABLE issues an SQLCODE -99 error.

TUNE TABLE calculates and sets the blocksize, selectivity, and extent size of the table, based on representative data. Normally, TUNE TABLE sets one or more of these values, and purges all cached queries that use this persistent class (table) so that queries will use these new values. However, if TUNE TABLE does not change any of these values (for example, if the data has not changed since the last time TUNE TABLE was run against this table) cached queries are not purged and the table’s class definition is not flagged for recompile.


  • %KEEP_UP_TO_DATE: if not specified (the default), the up-to-date flag on the modified class definition is not set. This indicates that the class definition is out of date and is flagged for recompile. If specified, the class definition remains flagged as up-to-date. This is the preferred option when making changes to statistics on a live system, because it makes it less likely that a table class definition will be recompiled.

  • %CLEAR_VALUES: if specified, calls TuneTable with ClearValues=1. When ClearValues=1, the existing SELECTIVITY, EXTENTSIZE, etc. values are cleared from the class and table definition. The default is ClearValues=0, so not specifying this option provides the default TuneTable behavior.

  • %SAMPLE_PERCENT percentage: if specified, calls TuneTable with a percentage value passed to the SamplePercent argument of TuneTable. This specifies the percentage of rows of the table to be used for sampling the data for the TuneTable utility. This percentage can be specified as .## or ##%; for example, either .12 or 12% will cause TuneTable to use 12% of the rows in the table when sampling the data. Specify percentage with a value greater than 0 and less than or equal to 100%; a value out of this range issues an SQLCODE -1 error. This value does not usually need to be specified when calling TuneTable. Only specify this value when potential outlier values for a field are not evenly distributed among rows throughout the table. Note, for any table with an extentsize < 1000 rows, the entire extent will be used by TuneTable regardless of the %SAMPLE_PERCENT value.

  • %RECOMPILE_CQ: if specified, calls TuneTable with RecompileCQ=1. When RecompileCQ=1, instead of just purging cached queries for the table that was tuned, TuneTable will instead recompile the cached query classes using the new Tune Table statistics. The default is RecompileCQ=0, so not specifying this option would provide the default TuneTable behavior.

If the specified tune_options value does not exist, TUNE TABLE issues an SQLCODE -25 error. If the same tune_options value is specified twice, TUNE TABLE issues an SQLCODE -326 error.

For further details on these options refer to the $SYSTEM.SQL.TuneTable() method.

Cached Queries

Executing TUNE TABLE creates a cached query. The Show Plan display indicates that no Query Plan is created. No SQL Statement is created. The cached query is general to the namespace; it is not listed for the specific table. You can re-run the same TUNE TABLE statement using the cached query.

Executing TUNE TABLE purges all existing cached queries for the specified table, including the cached query for the previous execution of TUNE TABLE. You can optionally have TUNE TABLE recompile all of these cached queries with the new Tune Table values.

If running TUNE TABLE does not change any Tune Table values, cached queries are not purged.

Other Ways to Run Tune Table

There are two other interfaces for running Tune Table:

For further details, refer to Tune Table in the “Optimizing Tables” chapter of the SQL Optimization Guide.


The following Dynamic SQL example tunes a table:

  TRY {
  SET mysql = "TUNE TABLE Sample.MyTest %KEEP_UP_TO_DATE"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(mysql)
     IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
     IF rset.%SQLCODE=0 { WRITE !,"Executed Tune Table",! }
     ELSE { SET badSQL=##class(%Exception.SQL).%New(,rset.%SQLCODE,,rset.%Message)
            THROW badSQL }
  CATCH exp { WRITE "In the CATCH block",!
              IF 1=exp.%IsA("%Exception.SQL") {
                WRITE "SQLCODE: ",exp.Code,!
                WRITE "Message: ",exp.Data,! }
              ELSE { WRITE "Not an SQL exception",! }
Copy code to clipboard

See Also