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

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
[Figure](1.3 + 0.2 [Figure] a)[Figure][Figure] b (KB)
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 (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

FunctionsDefinition 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_BETWEEN2
POWER, IS_DBLBYTES, IS_SNGLBYTES, ISDIGITS, ROUNDMONTH, TRANSL_LONG4
CEIL, FLOOR, HALF, INSERTSTR, INSERTSTR_LONG, LEFTSTR, LTRIMSTR, NUMEDIT, QUARTER, REPLACE_LONG, REVERSESTR, RIGHTSTR, RTRIMSTR, SIGN, STRTONUM, TRUNCYEAR6
LTRIM, REPLACE, RTRIM, TRANSL, TRUNC12
POSSTR, ROUND18
GREATEST, LEAST32