Appendix D.2 Formulas for determining size of SQL object buffer (pd_sql_object_cache_size)

For information on the variables used in the formulas, see (3) following.

Organization of this subsection
(1) Formula for determining the size of the SQL object in an SQL statement
(2) Formula for determining the size of the routine control object of a routine
(3) Variables used in the formulas

(1) Formula for determining the size of the SQL object in an SQL statement

The following formula is used to determine the size of the SQL object in an SQL statement:

Formula

Size of the SQL object in an SQL statement (KB) =
[Figure]{
1840 + 46 [Figure] RCN + 298 [Figure] Si + 20 [Figure] Pi + 1138 [Figure] Ti + 76 [Figure] Ti[Figure] Di + 80 [Figure] Ci + 40 [Figure] Ii + 534 [Figure] Wi + 20 [Figure] Ki + Li + 8 [Figure] TCi + 656 [Figure] Di + 48 [Figure] nFF + 100 [Figure] nFP + 148 [Figure] nFC + 696 [Figure] nPFF + 16 [Figure] (nAT + nPAT) + 20 [Figure] nCAT + 28 [Figure] (nAF + nCAF) + 20 [Figure] (nAA + nPAA + nCAA) + 1057 [Figure] nSPA + 120 [Figure] nSPP + 287 [Figure] nSFF + 8 [Figure] nSFP + 813 [Figure] nJFC + 20 [Figure] nJFP
[ + 60 [Figure] nFT + nFTS + 32 [Figure] nFTQ]1
[ + 1057 [Figure] nTR + 120 [Figure] (nTSN + nTSO) + 20 [Figure] (nTCN + nTCO)]2
[ + 760 + 376 [Figure] RCC + 1880 [Figure] RCT]3
[ + 32 [Figure] Si + 16]4
[ + [Figure](42 [Figure] SiT) + {52 + 152 [Figure] (SiTA + SiSA + SiNA) [Figure] (SiT + SiS + SiN)} [Figure]]5
} [Figure] 1024 [Figure]

1 Add the result of this formula when specifying a foreign table.

2 Add the result of this formula when using a trigger.

3 Add the result of this formula when using a referential constraint.

4 Computation formula for column name description area size. Add the result of this formula when using dynamic SQL.

5 Computation formula for type name description area size. Add the result of this formula when using dynamic SQL.

Notes:
  • If you use stored procedures or stored functions, also include the SQL statements described in the procedures or functions in the computation.
  • If you use triggers, also include the SQL statements described in the triggers in the computation.
  • If you use stored procedures, stored functions, or triggers, compute the size of the routine control object separately for the stored procedures, stored functions, or triggers, and add the result to the size of the SQL object buffer. The formula for computing the size of a routine control object is shown in (2) following.

(2) Formula for determining the size of the routine control object of a routine

(a) When the user defines a trigger

When the user defines a stored procedure, a stored function, or a trigger, the following formula is used to determine the size of the routine control object of a routine:

Formula

Size of the routine control object of a routine (KB) =
[Figure]{
600 + 28 [Figure] sRi + 32 [Figure] (sRUi + sDi) + 56 [Figure] sSXi + sCUi + sSi + sPi + sLA + sKi + sL + 80 [Figure] sWi + 24 [Figure] sCM + 32 [Figure] sCCR + 2 [Figure] sDCR + 60 [Figure] sCHD + 72 [Figure] sDHD + 64 [Figure] sHCN + 8 [Figure] sCHD[Figure] sHCN + 48 [Figure] nRFF + 100 [Figure] nRFP + 148 [Figure] nRFC + 200 [Figure] nPRFF + 8 [Figure] nPRFP + 196 [Figure] nPA + 64 [Figure] nPP + 36 [Figure] nPPI + 20 [Figure] nPPO + 200 [Figure] nPPA + 8 [Figure] nPPP + 20 [Figure] nAR + 48 [Figure] nARA + 16 [Figure] nRPAT + 20 [Figure] nCAT + 28 [Figure] (nRPAF + nRCAF) + 20 [Figure] (nRPAA + nRCAA) + 287 [Figure] nRSFF + 8 [Figure] nRSFP + 813 [Figure] nPJA + 20 [Figure] nPJP + 813 [Figure] nRJFC + 20 [Figure] nRJFP
[ + 28 [Figure] (nTSN[Figure] 2 + nTSO)]*
} [Figure] 1024 [Figure]

* Add the result of this formula when using a trigger.

(b) When HiRDB automatically creates a trigger

When HiRDB creates a trigger for constraint control when CASCADE is specified for the referential action during table definition, the following formula is used to determine the size of the routine control object of a routine:

Formula

Size of the routine control object of a routine (KB) =
[Figure]{
240 + 608 [Figure] RCC + (5120 + 100 [Figure] RDi + 256 [Figure] RIi) [Figure] RCP[Figure] RCT
} [Figure] 1024 [Figure]

(3) Variables used in the formulas

Variable nameExplanation
RCNTotal number of tables and indexes used by the SQL object
SiNumber of retrieval items inside the SQL statement (number of columns if the columns specified by the SQL statement are index columns)
PiNumber of embedded variables or parameters inside the SQL statement
TiNumber of table names inside the SQL statement
CiNumber of column names inside the SQL statement
TCiNumber of table columns inside the SQL statement
WiNumber of boolean operators inside the SQL statement1
KiNumber of constants inside the SQL statement1
LiTotal size of the constants inside the SQL statement1(units: bytes)
IiNumber of indexes used during SQL statement execution (number of indexes specified as retrieval conditions in the table specified by the SQL statement)
DiTotal number of storage conditions defined in the table inside the SQL statement (times 2 for a matrix-partitioned table)
SiTNumber of abstract data types in the selection expression inside the SQL statement
SiSNumber of supertypes for the abstract data types in the selection expression inside the SQL statement
SiNTotal number of supertypes for the abstract data type, which is a subtype of the selection expression inside the SQL statement
SiTANumber of attributes for the abstract data types in the selection expression inside the SQL statement
SiSANumber of attributes of the supertypes for the abstract data types in the selection expression inside the SQL statement
SiNATotal number of components specified for the abstract data type, which is a subtype of the selection expression inside the SQL statement
nSPANumber of times the procedure statements inside the SQL statement are invoked
nSPPTotal number of arguments for the procedure statements inside the SQL statement
nFFNumber of times the functions inside the SQL statement are invoked1
nFPTotal number of function arguments inside the SQL statement1
nFCTotal number of function definition candidates for the functions inside the SQL statement (number of function invocations nFF + number of function definitions that use as arguments the subtypes to the function invocations whose arguments are abstract data types)
nPFFNumber of times the plug-in functions used by SQL objects are invoked (1 for the number of plug-in function invocations inside the SQL statement + SELECT; 6 for INSERT, UPDATE, or DELETE)
nSFFNumber of times the system definition scalar functions inside the SQL statement are invoked1
nSFPTotal number of arguments for the system definition scalar functions inside the SQL statement1
nJFCNumber of times the Java functions inside the SQL statement are invoked
nJFPTotal number of arguments of the Java functions inside the SQL statement
nATNumber of abstract data types used by component specification inside the SQL statement (excluding the abstract data types that are manifested by supertypes and abstract data type attributes)
nAANumber of abstract data types used by component specification inside the SQL statement (including the abstract data types that are manifested by supertypes and abstract data type attributes)
nAFTotal number of attributes used by component specification inside the SQL statement
nPATNumber of abstract data types used by the arguments of the plug-in functions used by the SQL object (excluding the abstract data types that are manifested by supertypes and abstract data type attributes)
nPAANumber of abstract data types used by the arguments of the plug-in functions used by the SQL object (including supertypes and subtypes)
nCATNumber of times the constructor functions inside the SQL statement are invoked
nCAANumber of abstract data types of the constructor functions inside the SQL statement (including supertypes)
nCAFTotal number of attributes of the abstract data types of the constructor functions inside the SQL statement
nFTNumber of foreign tables inside the SQL statement
nFTSSize of the SQL statement that retrieves foreign tables2
nFTQNumber of embedded variables or parameters inside the SQL statement that retrieves foreign tables2
nTRNumber of triggers that are activated by SQL statement execution
nTSNTotal number of columns modified by the new values correlation name inside the trigger SQL statement of each trigger activated by SQL statement execution
nTSOTotal number of columns modified by the old correlation name inside the trigger SQL statement of each trigger activated by SQL statement execution
nTCNTotal number of columns modified by the new values correlation name inside the trigger action condition of each trigger activated by SQL statement execution
nTCOTotal number of columns modified by the old correlation name inside the trigger action condition of each trigger activated by SQL statement execution
RCCTotal number of member columns of the foreign key and the primary key of the table that references the update-target table inside the SQL statement
RCTTotal number of tables that reference the update-target table and the number of tables that the update-target table references inside the SQL statement
RCPTotal number of cascades specified for referencing actions during referencing table definition
RIiTotal number of indexes defined for referenced tables that are specified for referencing during referencing table definition
RDiTotal number of partitioning storage conditions defined for referenced tables that are specified for referencing during referencing table definition (to be multiplied by 2 for a matrix partitioning table)
sRiNumber of SQL parameters inside procedures and functions (Count for SQL parameter with INOUT specification should be multiplied by 2).
sRUiTotal number of SQL parameters inside procedures and functions (or the total number of columns modified by an old or new values correlation name inside the trigger SQL statement inside trigger definition)
sDiNumber of SQL variables (declare) inside procedures, functions, and trigger SQL statements
sSXiTotal number of SQLCODE and SQLCOUNT variables inside procedures, functions, and trigger SQL statements
sCUiTotal number of CURRENT_TIME and CURRENT_DATE constants inside procedures, functions, and trigger SQL statements
sSiNumber of data manipulation SQL statements inside procedures and trigger SQL statements (SQL statements excluding cursor declaration: OPEN, FETCH, CLOSE, UPDATE, DELETE, and INSERT statements, for example)
sPiNumber of routine control SQL statements inside procedures, functions, and trigger SQL statements (BEGIN, SET, IF, ELSE IF, and WHILE, for example)
sLANumber of labels inside procedures, functions, and trigger SQL statements
sKiNumber of constants inside procedures, functions, and trigger SQL statements (excluding the constants of data manipulation SQL statements described inside procedures and trigger SQL statements)
sLCombined total size of the constants inside procedures, functions, and trigger SQL statements (excluding the constants of data manipulation SQL statements described inside procedures and trigger SQL statements)
sWiNumber of condition predicates inside procedures, functions, and trigger SQL statements
sCMNumber of compound statements inside procedures, functions, and trigger SQL statements
sCCRNumber of compound statements that describe cursor declarations for procedures and trigger SQL statements
sDCRNumber of cursor declarations for procedures and trigger SQL statements
sCHDNumber of compound statements that describe handler declarations for procedures, functions, and trigger SQL statements
sDHDNumber of handler declarations for procedures, functions, and trigger SQL statements
sHCNNumber of condition values described inside handler declarations for procedures, functions, and trigger SQL statements
nRFFNumber of function invocations inside the routine
nRFPTotal number of function arguments inside the routine
nRFCTotal number of function definition candidates for the functions inside the routine (number of function invocations nFF + number of function definitions that use as arguments the subtypes to the function invocations whose arguments are abstract data types)
nPRFFNumber of times the plug-in functions used by SQL objects of the routine are invoked
nPRFPTotal number of plug-in parameters of the plug-in functions used by the SQL objects of the routine
nPANumber of procedure invocations inside the routine
nPPTotal number of parameters for the procedures inside the routine
nPPITotal number of input parameters for the procedures inside the routine (including input/output parameters)
nPPOTotal number of output parameters for the procedures inside the routine (including input/output parameters)
nPPANumber of times the plug-in procedure used by the SQL objects of the routine is invoked
nPPPTotal number of plug-in parameters of the plug-in procedure used by the SQL objects of the routine
nRSFFNumber of times the system definition scalar functions inside the routine are invoked
nRSFPTotal number of arguments for the system definition scalar functions inside the routine
nPJANumber of times the Java procedures inside the routine are invoked
nPJPTotal number of arguments for the Java procedures inside the routine
nRJFCNumber of times the Java functions inside the routine are invoked
nRJFPTotal number of arguments for the Java functions inside the routine
nARNumber of abstract data types used by component specification inside the routine (excluding the abstract data types that are manifested by supertypes and abstract data type attributes)
nARATotal number of attributes used in component specification inside the routine
nRPATNumber of abstract data types used by the parameters of the plug-in routines used by the SQL objects of the routine (excluding the abstract data types of supertypes and abstract data type attributes)
nRPAANumber of abstract data types used by the parameters of the plug-in routines used by the SQL objects of the routine (including supertypes)
nRPAFTotal number of attributes of the abstract data types used by the parameters of the plug-in routines used by the SQL objects of the routine
nRCATNumber of times the constructor functions inside the routine are invoked
nRCAANumber of abstract data types of the constructor functions inside the routine (including supertypes)
nRCAFTotal number of attributes of the abstract data types of the constructor functions inside the routine
1 When triggers are used, the trigger action conditions for the individual triggers that are activated by SQL statement execution must also be counted.
2 You can use the access path display utility (pdvwopt command) to check for the SQL statements that retrieve foreign tables.