Execute an SPL routine

Unlike a routine in C or Java™, whose executable code resides in an external file, the executable code for an SPL routine is stored directly in the sysprocbody system catalog table of the database. When you create an SPL routine, the database server parses the SPL routine, compiles it, and stores the executable code in the sysprocbody system catalog table. When a statement invokes an SPL routine, the database server executes the SPL routine from the internally-stored compiled code.

When you execute an SPL routine with the EXECUTE FUNCTION, EXECUTE PROCEDURE, or CALL statement, the database server performs the following tasks:
  • Retrieves the p-code, execution plan, and dependency list from the system catalog and converts them to binary format
  • Parses and evaluates the arguments passed by the EXECUTE FUNCTION, EXECUTE PROCEDURE, or CALL statement
  • Checks the dependency list for each SQL statement that will be executed

    If an item in the dependency list indicates that reoptimization is needed, optimization occurs at this point. If an item needed in the execution of the SQL statement is missing (for example, a column or table has been dropped), an error occurs at this time.

  • Executes the p-code instructions

An SPL routine with the WITH RESUME clause of the RETURN statement causes multiple executions of the same SPL routine in the same routine sequence. However, an SPL routine does not have access to the user state of its routine sequence.