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

The following formula is used to determine one routine's definition information size:

Formula

(1.3 + 0.2 [Figure]

b (kilobytes)

a: Total number of parameters for routines that are used frequently

b: Number of definitions for routines that are used frequently

(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 (kilobytes)

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, use the total for all of the installed plug-ins.

#: Use the following SQL code 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

The table below shows the definition information size for each system definition scalar function:

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

Functions	Definition information size (kilobytes)
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 C.6 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