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

{ (2539 + Si70 + Pi20 + Ti980 + Ci68 + Wi818 + Ki416 + Li5 + Di116 + Ari108 + Gi44 + Ori10 + Sli40 + Upi96 + Fi90 + TiCwi48 + MAX(Pi, Wpi) 52 + MAX(Ti, Sli - 1) 96 + MAX(Ti2, Wi) 24 + MAX(Ti3, Wi) 24 + MAX{MAX(Ti, Ori + Gi + Si + Fi), Sli - 1}24 }1.2 1024 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 4 + c + d + e 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.

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.