FREEZE PLANS (SQL)

Freezes one or more query plans.

Synopsis

FREEZE PLANS BY ID statement-hash
FREEZE PLANS BY TABLE table-name
FREEZE PLANS BY SCHEMA schema-name
FREEZE PLANS

Description

The FREEZE PLANS command freezes query plans. To unfreeze frozen query plans use the UNFREEZE PLANS command.

FREEZE PLANS can freeze query plans with the Plan State Unfrozen. It cannot freeze query plans with the Plan State Unfrozen/Parallel.

FREEZE PLANS provides four syntax forms for freezing query plans:

A specified query plan: FREEZE PLANS BY ID statement-hash. The statement-hash value must be delimited by double quotation marks.
All query plans for a table: FREEZE PLANS BY TABLE table-name. You can specify a table name or a view name. If a query plan references multiple tables and/or views, specifying any of these tables or views freezes the query plan.
All query plans for all tables in a schema: FREEZE PLANS BY SCHEMA schema-name.
All query plans for all tables in the current namespace: FREEZE PLANS.

This command issues SQLCODE 0 if one or more query plans are frozen; it issues SQLCODE 100 if no query plans are frozen. The Rows Affected (%ROWCOUNT) indicates the number of query plans frozen.

Other Interfaces

You can use the following $SYSTEM.SQL.Statement methods to freeze a single query plan or multiple query plans: FreezeStatement()Opens in a new tab for a single plan; FreezeRelation()Opens in a new tab for all plans for a relation (a table or view referenced in the query plan); FreezeSchema()Opens in a new tab for all plans for a schema; FreezeAll()Opens in a new tab for all plans in the current namespace. There are corresponding Unfreeze methods.

You can use the Management Portal, to freeze a query plan as described in Frozen Plans Interface.

Arguments

statement-hash

The internal hash representation of the SQL Statement definition for a query plan, enclosed in quotation marks. Occasionally, what appear to be identical SQL statements may have different statement hash entries. Any difference in settings/options that require different code generation of the SQL statement result in a different statement hash. This may occur with different client versions or different platforms that support different internal optimizations. Refer to SQL Statement Details.

table-name

The name of an existing table or view. A table-name can be qualified (schema.table), or unqualified (table). An unqualified table name takes the default schema name.

schema-name

The name of an existing schema. This command freezes all query plans for all tables in the specified schema.

Security and Privileges

The FREEZE PLANS command is a privileged operation that required the user to have %Development:USE permission. Such permissions can be granted through the Management Portal. Executing a FREEZE PLANS command without this privileges will result in a SQLCODE -99 error and the command will fail. There are two exceptions:

The command is executed via Embedded SQL, which does not perform privilege checks.
The user explicitly specifies not privilege checking by, for example, calling either %Prepare() with the checkPriv argument set to 0 or %ExecDirectNoPriv() on a %SQL.StatementOpens in a new tab.