Formulas for determining size of routine definition information buffer (pd_routine_def_cache

The following formula is used to determine the definition information size for routines:

total size of definition information for routines that are used frequently
+ total size of definition information for plug-in functions of plug-ins used
+ total size of definition information for system definition scalar functions that are used frequently

Organization of this subsection: (1) Determination of routine definition information size per routine; (2) Determination of definition information size of a plug-in function; (3) Determination of each definition scalar function's definition information size for each function

(1) Determination of routine definition information size per routine

(2) Determination of definition information size of a plug-in function

The formula provided below is used to determine the definition information size of a plug-in function:

Formula

0.6 + c + 0.2 [Figure]

d (KB)

c:: Total number of plug-in functions used in DML by a single plug-in^*

d:: Total number of parameters of plug-in functions used in DML by a single plug-in^*

Note: This formula is for one plug-in. If multiple plug-ins are installed, the total for all of the installed plug-ins should be used.

^* The SQL shown below should be used to determine the total number of plug-in functions used in DML and the parameters of the plug-in functions used in DML:

SELECT COUNT(*),SUM(N_PARAM) FROM MASTER.SQL_PLUGIN_ROUTINES
  WHERE PLUGIN_NAME = 'plug-in-name'
  AND (TIMING_DESCRIPTOR = 'ADT_FUNCTION'
    OR TIMING_DESCRIPTOR IS NULL
    OR TIMING_DESCRIPTOR = 'BEFORE_INSERT'
    OR TIMING_DESCRIPTOR = 'AFTER_INSERT'
    OR TIMING_DESCRIPTOR = 'BEFORE_UPDATE'
    OR TIMING_DESCRIPTOR = 'AFTER_UPDATE'
    OR TIMING_DESCRIPTOR = 'BEFORE_DELETE'
    OR TIMING_DESCRIPTOR = 'AFTER_DELETE'
    OR TIMING_DESCRIPTOR = 'BEFORE_PURGE_TABLE'
    OR TIMING_DESCRIPTOR = 'AFTER_PURGE_TABLE'
    OR TIMING_DESCRIPTOR = 'INDEX_SEARCH'
    OR TIMING_DESCRIPTOR = 'INDEX_COUNT'
    OR TIMING_DESCRIPTOR = 'INDEX_INSERT'
    OR TIMING_DESCRIPTOR = 'INDEX_BEFORE_UPDATE'
    OR TIMING_DESCRIPTOR = 'INDEX_AFTER_UPDATE'
    OR TIMING_DESCRIPTOR = 'INDEX_DELETE'
    OR TIMING_DESCRIPTOR = 'PURGE_INDEX'
    OR TIMING_DESCRIPTOR = 'INDEX_MAINTENANCE_DEFERRED'
    OR TIMING_DESCRIPTOR = 'BEFORE_INSERT_DC'
    OR TIMING_DESCRIPTOR = 'BEFORE_UPDATE_DC'
    OR TIMING_DESCRIPTOR = 'BEFORE_DATA_CHECK'
    OR TIMING_DESCRIPTOR = 'AFTER_DATA_CHECK')

(3) Determination of each definition scalar function's definition information size for each function

Table D-1 is used to determine the definition information size for each system definition scalar function:

Table D-1 Definition information size for each system definition scalar function

Functions	Definition information size (KB)
ACOS, ADD_INTERVAL, ASCII, ASIN, ATAN, ATAN2, CENTURY, COS, COSH, CHR, DATE_TIME, DAYNAME, DAYOFWEEK, DAYOFYEAR, DEGREES, EXP, INTERVAL_DATETIMES, LAST_DAY, LN, LOG10, MIDNIGHTSECONDS, MONTHNAME, MONTHS_BETWEEN, NEXT_DAY, PI, RADIANS, SIN, SINH, SQRT, TAN, TANH, WEEK, WEEKOFMONTH, YEARS_BETWEEN	2
POWER, IS_DBLBYTES, IS_SNGLBYTES, ISDIGITS, ROUNDMONTH, TRANSL_LONG	4
CEIL, FLOOR, HALF, INSERTSTR, INSERTSTR_LONG, LEFTSTR, LTRIMSTR, NUMEDIT, QUARTER, REPLACE_LONG, REVERSESTR, RIGHTSTR, RTRIMSTR, SIGN, STRTONUM, TRUNCYEAR	6
LTRIM, REPLACE, RTRIM, TRANSL, TRUNC	12
POSSTR, ROUND	18
GREATEST, LEAST	32

Appendix D.5 Formulas for determining size of routine definition information buffer (pd_routine_def_cache_size)

(1) Determination of routine definition information size per routine

(2) Determination of definition information size of a plug-in function

(3) Determination of each definition scalar function's definition information size for each function