15.1.7 Formula for size of memory required during SQL preprocessing

Organization of this subsection
(1) Size of memory required when no stored procedure is used
(2) Procedure for obtaining the size of the memory required when using stored procedures

(1) Size of memory required when no stored procedure is used

If no stored procedure is used, the following formula can be used to obtain the size of the memory that is allocated during SQL preprocessing (KB).

Formula
[Figure]{
2,586 + Si x 60 + Pi x 20 + Ti x 1,424 + Ci x Ti x 72 + Wi x 776 + Ti x Wi x 72
+ Ki x 276 + Ki x Ti x 72 + Li x 3 + Li x Ti + Di x Ti x 134 + Ari x 108
+ Gi x 44 + Sli x 40 + Upi x 110
+ Fi x 90 + Ti x Cwi x 48
+ MAX(Pi, Wpi) x 60
} x 1.2 [Figure] 1,024[Figure] (KB)
Si: Number of items to be retrieved in SQL statements
Pi: Number of embedded variables, ? parameters, or SQL parameters in SQL statements
Ti: Number of table names in SQL statements
Ci: Number of column names in SQL statements
Wi: Number of predicates used in Boolean operators (AND and OR) in SQL statements
Ki: Number of literals in SQL statements
Li: Total length of literals in SQL statements (bytes)
Di: Total number of storage conditions defined in SQL statements
Ari: Number of arithmetic operations and concatenation operations in SQL statements
Gi: Number of columns specified in GROUP BY clause of SQL statements
Ori: Number of column specification or sort item specification numbers in ORDER BY clause of SQL statements
Fi: Total number of set functions and scalar functions in SQL statements
Sli: Number of queries specified in SQL statements
Upi: Number of columns to be updated in SQL statements
Cwi: Number of WHENs in CASE expression of SQL statements
Wpi: Number of variables corresponding to WITH clause of SQL statements
Note
If SELECT_APSL is applied, this value is 3; otherwise, it is 1. The access path display utility (pdvwopt) can be used to determine whether SELECT_APSL is applied. For details about the access path display utility (pdvwopt), see the manual HiRDB Version 9 Command Reference.

(2) Procedure for obtaining the size of the memory required when using stored procedures

If stored procedures are used, the size of the memory (in KB) to be allocated during SQL preprocessing is the value obtained from the formula shown in (1) above plus the length of the procedure control object for each stored procedure. For details about the formula for obtaining the length of a procedure control object, see the section on the pd_sql_object_cache_size operand of the system common definition. For details about the length of the procedure control object per stored procedure, see Formula for determining the size of the routine control object of a routine in the manual HiRDB Version 9 System Definition.