SQL-J Language Reference
Page 5 of 121

ALTER STATEMENT statement

The ALTER STATEMENT statement allows you to recompile a stored prepared statement or statements created with a CREATE STATEMENT statement.

Cloudscape allows you to recompile statements explicitly with the ALTER STATEMENT RECOMPILE statement for:

  • a particular statement

    by specifying the statement name

  • all statements in all schemas

    by specifying RECOMPILE ALL

  • all invalid statements in all schemas

    by specifying RECOMPILE INVALID

The USING clause allows you to provide new sample parameters to the ALTER STATEMENT statement for use in optimization (see CREATE STATEMENT statement). The statement is recompiled when the ALTER STATEMENT statement is issued.

You cannot change the text of the stored prepared statement with this statement. Instead, you must drop the statement and re-create it.

Syntax

{
    ALTER STATEMENT StatementName RECOMPILE
        [ USING SingleRowResultSet ] |

    ALTER STATEMENT RECOMPILE { ALL | INVALID }
}

Typically, a stored prepared statement is recompiled as needed when a dependency changes. For example, a prepared statement that depends on an index that is dropped is marked invalid and thus automatically recompiled the next time the statement is executed. Force recompilation for all invalid statements instead of waiting for them to be executed before deploying an application onto read-only media.

In addition, there are some times when you may want to explicitly force a statement to be recompiled, even when it is not invalid, such as when a statement may have gone stale or when a new index is created that will help the performance of an existing prepared statement, and the statement is a SELECT. (For information about stale statements, see Stale Stored Prepared Statements Tuning Cloudscape).

The USING clause serves the same purpose that it serves in the CREATE STATEMENT statement; it gives default values to the optimizer for best optimization. See About the USING Clause.

ALTER STATEMENT Examples

-- Recompile a statement. The recompilation is done the next
-- time the statement is executed
ALTER STATEMENT getFullFlightInfo RECOMPILE

-- Recompile the getFullFlightInfo statement with
-- some new sample values. Do the recompilation now.
ALTER STATEMENT getFullFlightInfo
RECOMPILE USING VALUES ('US1473', 2)

-- recompile all invalid statements
ALTER STATEMENT RECOMPILE INVALID