ALTER MODEL statement

The ALTER MODEL statement modifies existing models.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

Single table cardinality estimation model objects do not have privileges directly defined on them. Instead, their privileges associated with the base table will be used. As such, the privileges held by the authorization ID of the statement must include at least one of the following privileges:

  • DBADM
  • SQLADM
  • CONTROL privilege on the table
  • SCHEMAADM on the schema of the table
  • ALTERIN on the schema of the table
  • TENANTADM on the tenant the table is within
  • ALTERIN on the tenant the table is within

Syntax

Read syntax diagramSkip visual syntax diagramALTER MODEL model-nameONtable-name ENABLEDISABLEREVERT

Description

model-name
Identifies the model to be altered. The model-name must identify a model that exists at the current server (SQLSTATE 42704).
ON table-name

The ON table-name clause allows specification of the base table as an alternative to the model-name clause if the model is associated with a table (for example for a single table cardinality model). This clause eliminates the need to find the model name of the model that needs altering if the underlying table name is already known. The table-name must identify a table that exists at the current server (SQLSTATE 42704). The table-name must also identify a table that has an associated model (SQLSTATE 4274O).

ENABLE
If ENABLE is specified, the model will be used for inference and automatic runstats will be allowed to discover and (re)train the model. When a new model version is created, the new model is enabled by default.
DISABLE
If DISABLE is specified, the model will not be used for inference. For example, for a single table cardinality model, the plan optimizer will ignore disabled models for cardinality estimation during query compilation. Also, automatic runstats will not be allowed to discover or (re)train the model.
REVERT
The REVERT clause swaps the current model version with the model's previous version. If a previous version does not exist, no change to the model is made, and an error is returned (SQLSTATE 5507B)

Examples

  1. Disable the model on table tpcds.store_sales. After this statement is executed, the model will no longer be used and will not be automatically discovered or retrained.
    ALTER MODEL ON tpcds.store_sales DISABLE
  2. Enable the model on table tpcds.store_sales. If the model was previously disabled, the model can be used after executing the statement. It can also be discovered or retrained automatically.
    ALTER MODEL ON tpcds.store_sales ENABLE
  3. Revert the model on tpcds.store_sales to the previous version.
    ALTER MODEL ON tpcds.store_sales REVERT