Review information about user-defined routines
The following table shows where the database server stores
information from CREATE FUNCTION and CREATE PROCEDURE statements in
the sysprocedures system catalog table.
| UDR information | CREATE statement syntax | Column of sysprocedures |
|---|---|---|
| Routine type: function or procedure | FUNCTION or PROCEDURE keyword | isproc |
| Owner name (optional) | Precedes the routine name: owner.routine_name Defaults to the creator of the routine |
owner |
| Routine name | After FUNCTION or PROCEDURE keyword | procname |
| Specific name (optional) | SPECIFIC keyword | specificname |
| Routine parameters | Parameter list | numargs, paramstyle, paramtypes |
| Routine modifiers | WITH clause | variant, handlesnulls, iterator, percallcost, negator, selfunc, internal, class, stack, parallelizable, costfunc, selconst, modifiers |
| Location of the routine (if it is external) | EXTERNAL NAME | externalname |
| Routine language | LANGUAGE | langid |
The database server assigns a unique identifying number to each UDR and stores this number in the procid column of sysprocedures table.
For SPL routines, the database server also stores routine information in the sysprocbody and sysprocplan system catalog tables. The sysprocbody table stores both the text and the compiled version (which is not legible) of the SPL routine. The sysprocplan table stores a compiled version of the execution plan, which is not legible.