16.2.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]{
(2539 + Si[Figure]70 + Pi[Figure]20 + Ti[Figure]980 + Ci[Figure]68 + Wi[Figure]818 + Ki[Figure]416 + Li[Figure]5
+ Di[Figure]116 + Ari[Figure]108 + Gi[Figure]44 + Ori[Figure]10 + Sli[Figure]40 + Upi[Figure]96 + Fi[Figure]90
+ Ti[Figure]Cwi[Figure]48 + MAX(Pi, Wpi) [Figure]52 + MAX(Ti, Sli - 1) [Figure]96
+ MAX(Ti[Figure]2, Wi) [Figure]24 + MAX(Ti[Figure]3, Wi) [Figure]24
+ MAX{MAX(Ti, Ori + Gi + Si + Fi), Sli - 1}[Figure]24
}[Figure]1.2 [Figure]1024[Figure][Figure]CLS
(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
CLS: Number of areas generated per access path in an SQL object*
* The following formula can be used to obtain the number of areas where one access path is generated in an SQL object.
Formula
When SELECT_APSL is applied*
a + b[Figure] 4 + c + d + e[Figure] 2
When SELECT_APSL is not applied*
a + b + c + d + e
a: Number of front-end servers
Specify 1 for the number of front-end servers.
b: Number of tables
Use the following formula to obtain the number of tables:
Number of base tables + number of correlation names
c: Number of set operation servers
If a set function is specified, specify 1; otherwise, specify 0.
d: Number of queries specifying GROUP BY, DISTINCT, or ORDER BY clause
e: Number of join servers
Use the following formula to obtain the number of join servers:
b - number of queries in SQL statement
* The access path display utility (pdvwopt) can be used to determine whether or not SELECT_APSL is applied. For details about the access path display utility (pdvwopt), see the manual HiRDB Version 8 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 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 8 System Definition.