Routine Statistics

Before executing a new SPL routine for the first time, the database server optimizes the DML statements in the SPL routine. Immediately before you invoke that SPL routine, however, you can reduce the risk of error if you use the Routine Statistics syntax of UPDATE STATISTICS to update its query execution plans, some of which might reference tables whose schemas have been modified by DDL operations of concurrent sessions.

Optimization makes the code depend on the structure of tables referenced by the routine. If a DDL operation modifies the schema of a referenced table after the routine is optimized, but before it is executed, the routine can fail with an error.

This failure typically does not occur, however, if an index is added or dropped while automatic recompilation is enabled for routines referencing tables that ALTER TABLE, CREATE INDEX, or DROP INDEX operations have modified. This is the default behavior of HCL OneDB™. For more information about enabling or disabling automatic reoptimization after changes to the schema of a table, see the description of the IFX_AUTO_REPREPARE option to the SET ENVIRONMENT statement.

When the AUTO_REPREPARE configuration parameter and the IFX_AUTO_REPREPARE session environment variable are set to disable recompilation of SPL routines that reference tables whose schema has been modified, however, adding or dropping an index to a table that an SPL routine references indirectly can cause the routine to return error -710. To avoid this error after DDL operations, or to reoptimize SPL routines after table distributions might have been modified by DML operations, use the Routine Statistics segment of UPDATE STATISTICS to update the execution plans of any SPL routines that reference the table.

Routine Statistics

Notes:

¹ See Routine Parameter List
² See Specific Name


Element	Description	Restrictions	Syntax
owner	Owner of the UDR	No more than 32 bytes. Must be the authorization identifier of the owner of routine.	Owner name
routine	Name that a CREATE FUNCTION or CREATE PROCEDURE statement declared for an SPL routine	Must exist in the database. In ANSI-compliant databases, qualify routine with owner if you are not the owner.	Identifier

Usage

If you change the structure of a table that an SPL routine references, you can run UPDATE STATISTICS FOR ROUTINE, FOR FUNCTION, or FOR PROCEDURE statements to reoptimize the routines that reference the table, rather than waiting until the next time an SPL routine that uses the table executes. (If a table that an SPL routine references is dropped, however, running UPDATE STATISTICS cannot prevent the SPL routine from failing with an error.)

The keywords of the Routine Statistics segment identify one or more SPL routine whose execution plan will be reoptimized.

Keyword: Which Execution Plans are Reoptimized
FUNCTION: The plan for any SPL function with the specified name (and with parameter types that match routine parameter list, if parameters are supplied). If you specify the FUNCTION keyword, the UPDATE STATISTICS statement fails with an error unless the specified routine returns a value or values, with or without the WITH RESUME option.
PROCEDURE: The plan for any SPL procedure with the specified name (and parameter types that match routine parameter list, if supplied)
ROUTINE: The plan for SPL functions and procedures with the specified name (and parameter types that match routine parameter list, if supplied)
SPECIFIC: The plan for the SPL routine called specific name. If you include the SPECIFIC keyword, the immediately following keyword must be either FUNCTION, PROCEDURE, or ROUTINE.

The parentheses symbols are optional if you omit the SPECIFIC keyword and include no argument list.

If you specify no routine name immediately after the FOR FUNCTION, FOR PROCEDURE, or FOR ROUTINE keywords, the execution plans are reoptimized for all SPL routines in the current database.

The database server keeps a list of tables that the SPL routine references explicitly. Whenever an explicitly referenced table is modified, the database server reoptimizes the procedure the next time the procedure is executed.

The sysprocplan system catalog table stores execution plans for SPL routines. Two actions can update the sysprocplan system catalog table:

Execution of an SPL routine that uses a modified table
The UPDATE STATISTICS FOR ROUTINE, FUNCTION, or PROCEDURE statement.

If you change a table that an SPL routine references, you can run UPDATE STATISTICS to reoptimize the procedures that reference the table, rather than waiting until the next time an SPL routine that uses the table executes. If a table that an SPL routine references is dropped, however, running UPDATE STATISTICS cannot prevent the SPL routine from failing with an error.

Examples of updating statistics for a specific routine

The following UPDATE STATISTICS FOR SPECIFIC statement instructs the database server to update statistics for an existing function named Perform_work that returns one or more values:

UPDATE STATISTICS FOR SPECIFIC FUNCTION Perform_work;

For the same Perform_work function, the effect of the following example is identical to that of the previous example:

UPDATE STATISTICS FOR SPECIFIC ROUTINE Perform_work;

Similarly, use the keywords SPECIFIC PROCEDURE or SPECIFIC ROUTINE to update statistics for SPECIFIC procedures that return no value.

Do not include parentheses or a parameter list after the name of the SPECIFIC routine. Because of the parentheses that follow the name of the Perform_work function, the following statement fails with an error:

UPDATE STATISTICS FOR SPECIFIC ROUTINE Perform_work();

The database server also issues an error if parentheses enclose arguments to the SPECIFIC routine, function, or procedure.