Virtual-Memory Cache for Routines

The database server caches the following items in the virtual portion of the database server shared memory:

• For SPL routines and other UDRs, information in the sysprocedures system catalog table
• For SPL routines only, the executable form of the routine in the UDR cache

The sysprocedures System Catalog Table

When any session requires the use of an SPL routine for the first time, the database server reads the sysprocedures system catalog tables and stores the information in the buffer pool in shared memory. The database server uses this information in shared memory if it is present for subsequent sessions that invoke the UDR.

The database server keeps the sysprocedures system catalog information in the buffer pool on a most recently used basis.

The sysprocedures table includes the following information:

• Name of routine
• Compiled size (in bytes) of return values
• Compiled size (in bytes) of p-code for the routine
• Number of arguments
• Data types of parameters
• Type of routine (function or procedure)
• Location of external routine
• Virtual-processor class in which the routine executes

UDR Cache

When any session requires the use of an SPL routine for the first time, the database server reads the system catalog tables to retrieve the code for the SPL routine. The database server converts the p-code to an executable form. The database server caches this executable form of the SPL routine in the virtual portion of shared memory.

The database server keeps the executable format of an SPL routine in the UDR cache on a most recently used basis.

You can monitor the UDR cache with the -g prc option of the onstat utility. For more information on onstat -g prc and adjusting the size of the UDR cache with the PC_POOLSIZE configuration parameter, refer to the IBM Informix: Performance Guide.