SYSPROCEDURES

The sysprocedures system catalog table lists the characteristics for each function and procedure that is registered in the database. It contains one row for each routine.

Each function in sysprocedures has a unique value, procid, called a routine identifier. Throughout the system catalog, a function is identified by its routine identifier, not by its name.

The sysprocedures table has the following columns.
Table 1. SYSPROCEDURES table column descriptions
Column Type Explanation
procname VARCHAR(128) Name of routine
owner VARCHAR(32) Name of owner
procid SERIAL Unique identifying code for the routine
mode CHAR(1) Mode type:
  • D or d = DBA
  • O or o = Owner
  • P or p = Protected
  • R or r = Restricted
  • T or t = Trigger
retsize INTEGER Compiled size (in bytes) of returned values
symsize INTEGER Compiled size (in bytes) of symbol table
datasize INTEGER Compiled size (in bytes) of constant data
codesize INTEGER Compiled size (in bytes) of routine code
numargs INTEGER Number of arguments to routine
isproc CHAR(1) Specifies if the routine is a procedure or a function:
  • t = procedure
  • f = function
specificname VARCHAR(128) Specific name for the routine
externalname VARCHAR(255) Location of the external routine. This item is language-specific in content and format.
paramstyle CHAR(1) Parameter style: I = HCL OneDB™
langid INTEGER Language code (in sysroutinelangs table)
paramtypes RTNPARAMTYPES Information describing the parameters of the routine
variant BOOLEAN Whether the routine is VARIANT or not:
  • t = is VARIANT
  • f = is not VARIANT
client BOOLEAN Reserved for future use
handlesnulls BOOLEAN NULL handling indicator:
  • t = handles NULLs
  • f = does not handle NULLs
percallcost INTEGER Amount of CPU per call

Integer cost to execute UDR: cost/call - 0 -(2^31-1)

commutator VARCHAR(128) Name of commutator function
negator VARCHAR(128) Name of the negator function
selfunc VARCHAR(128) Name of function to estimate selectivity of the UDR
internal BOOLEAN Specifies if the routine can be called from SQL:
  • t = routine is internal, not callable from SQL
  • f = routine is external, callable from SQL
class CHAR(18) CPU class by which the routine should be executed
stack INTEGER Stack size in bytes required per invocation
parallelizable BOOLEAN Parallelization indicator for UDR:
  • t = parallelizable
  • f = not parallelizable
costfunc VARCHAR(128) Name of the cost function for the UDR
selconst SMALLFLOAT Selectivity constant for UDR
procflags INTEGER For internal use only
collation CHAR(32) Collating order at the time when the routine was created

In the mode column, the R mode is a special case of the O mode. A routine is in restricted (R) mode if it was created with a specified owner who is different from the routine creator. If routine statements involving a remote database are executed, the database server uses the access privileges of the user who executes the routine instead of the privileges of the routine owner. In all other scenarios, R-mode routines behave the same as O-mode routines.

The database server can create protected routines for internal use. The sysprocedures table identifies these protected routines with the letter P or p in the mode column, where p indicates an SPL routine. Protected routines have the following restrictions:
  • You cannot use the ALTER FUNCTION, ALTER PROCEDURE, or ALTER ROUTINE statements to modify protected routines.
  • You cannot use the DROP FUNCTION, DROP PROCEDURE, or DROP ROUTINE statements to unregister protected routines.
  • You cannot use the dbschema utility to display protected routines.
In earlier versions, protected SPL routines were indicated by a lowercase p. Starting with version 9.0, protected SPL routines are treated as DBA routines and cannot be Owner routines. Thus D and O indicate DBA routines and Owner routines, while d and o indicate protected DBA routines and protected Owner routines.
The trigger mode designates user-defined SPL routines that can be invoked only from the FOR EACH ROW section of a triggered action.
Important: After you issue the SET SESSION AUTHORIZATION statement, the database server assigns a restricted mode to all Owner routines that you created while using the new identity.

A unique index is defined on the procid column. A composite index on the procname, isproc, numargs, and owner columns allows duplicate values, as does a composite index on the specificname and owner columns.