Altered tables that are referenced indirectly in SPL routines

After DDL operations that change the schema of a table that an SPL routine references indirectly, but not as the target of a DML operation, you might need to perform UPDATE STATISTICS operations on the modified table and on the SPL routine to avoid exceptions when the SPL routine is invoked.

If the SPL routine depends on a table that is referenced only indirectly, the database server cannot detect the need to reoptimize the procedure after that table is modified. For example, a table can be referenced indirectly if the SPL routine activates a trigger. After schema modifications of a table that is referenced by the trigger but not referenced directly by the SPL routine, the database server does not know that it should reoptimize execution plan of the SPL routine before running it. When the procedure is run after the table has been changed, error -710 can occur.

Each SPL routine is optimized the first time that it is run, not when it is created. This behavior means that an SPL routine might succeed the first time it is run, but might fail later under virtually identical circumstances, if the schema of an indirectly referenced table has been changed. The failure of an SPL routine can also be intermittent, because failure during one execution forces an internal warning to reoptimize the procedure before the next execution.

You can use either of two methods to recover from this error:
  • Issue UPDATE STATISTICS FOR PROCEDURE to force reoptimization of the routine.
  • Rerun the routine.
To prevent this error, you can force reoptimization of the SPL routine. To force reoptimization, execute the following statement: 
UPDATE STATISTICS FOR PROCEDURE routine;
You can add this statement to your program in either of the following ways:
  • Issue UPDATE STATISTICS after each statement that changes the mode of an object.
  • Issue UPDATE STATISTICS FOR PROCEDURE before each invocation of the SPL routine.
For efficiency, you can put the UPDATE STATISTICS statement with the action that occurs less frequently in the program (change of object mode or execution of the procedure). In most cases, the action that occurs less frequently in the program is the change of object mode.

When you follow this method of recovering from this error, you must execute UPDATE STATISTICS FOR PROCEDURE for each SPL procedure that indirectly references the altered tables, unless the procedure also references the tables explicitly.

You can also recover from error -710 after an indirectly referenced table is altered simply by re-executing the SPL routine. The first time that the stored procedure fails, the database server marks the procedure as in need of reoptimization. The next time that you run the procedure, the database server reoptimizes the procedure before running it. Running the SPL routine twice, however, might be neither practical nor safe. A safer choice is to use the UPDATE STATISTICS FOR PROCEDURE statement to force reoptimization of the procedure.

When