If the SPL routine depends on a table that is referenced only indirectly, 3however, the database server cannot detect the need to reoptimize the procedure 3after that table is modified. For example, a table can be referenced indirectly 3if the SPL routine invokes a trigger. If the schema of a table that is referenced 3by the trigger (but not directly by the SPL routine) is changed, the database 3server does not know that it should reoptimize the SPL routine before running 3it. When the procedure is run after the table has been changed, error -710 3can occur.
3Each SPL routine is optimized the first time that it is run (not when it 3is created). This behavior means that an SPL routine might succeed the first 3time it is run but fail later under virtually identical circumstances, if 3the schema of an indirectly referenced table has been changed. The failure 3of an SPL routine can also be intermittent, because failure during one execution 3forces an internal warning to reoptimize the procedure before the next execution.
3You can use either of two methods to recover from this error:
3To prevent this error, you can force reoptimization of the SPL routine. 3To force reoptimization, execute the following statement:
3UPDATE STATISTICS FOR PROCEDURE routine
You can add this statement to your program 3in either of the following ways:
3For efficiency, you can put the UPDATE STATISTICS statement with the 3action that occurs less frequently in the program (change of object mode or 3execution of the procedure). In most cases, the action that occurs less frequently 3in the program is the change of object mode.
3When you follow this method of recovering from this error, you must execute 3UPDATE STATISTICS for each procedure that indirectly references the altered 3tables unless the procedure also references the tables explicitly.
3You can also recover from error -710 after an indirectly referenced table 3is altered simply by re-executing the SPL routine. The first time that the 3stored procedure fails, the database server marks the procedure as in need 3of reoptimization. The next time that you run the procedure, the database 3server reoptimizes the procedure before running it. Running the SPL routine 3twice, however, might be neither practical nor safe. A safer choice is to 3use the UPDATE STATISTICS statement to force reoptimization of the procedure.
When you upgrade a database to use with a newer database server, you can use the UPDATE STATISTICS statement to convert the indexes to the form that the newer database server uses. You can choose to convert the indexes one table at a time or for the entire database at one time. Follow the conversion guidelines that are outlined in the IBM Informix Migration Guide.
When you use the UPDATE STATISTICS statement to convert the indexes to use with a newer database server, the indexes are implicitly dropped and re-created. The only time that an UPDATE STATISTICS statement causes table indexes to be implicitly dropped and re-created is when you upgrade a database for use with a newer database server.
The more specific you make the list of objects that UPDATE STATISTICS examines, the faster it completes execution. Limiting the number of columns distributed speeds the update. Similarly, precision affects the speed of the update. If all other keywords are the same, LOW works fastest, but HIGH examines the most data.
Related statements: SET EXPLAIN and SET OPTIMIZATION
For a discussion of the performance implications of UPDATE STATISTICS, see your IBM Informix Performance Guide.
For a discussion of how to use the dbschema utility to view distributions created with UPDATE STATISTICS, see the IBM Informix Migration Guide.