Appendix C.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
(4) Executing updatable online reorganization

(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 =
[Figure]{
1,600 + 46 [Figure] RCN + 394 [Figure] Si + 24 [Figure] Pi + 2,058 [Figure] Ti + 76 [Figure] Ti[Figure][Figure] Di + 80 [Figure] Ci + 40 [Figure] Ii + 586 [Figure] Wi
+ 24 [Figure] Ki + Li + 8 [Figure] TCi + 656 [Figure] Di + 116 [Figure] Ti[Figure] QX + 28 [Figure] QX + 200 [Figure] Ai + 48 [Figure] nFF
+ 100 [Figure] nFP + 148 [Figure] nFC + 712 [Figure] nPFF + 32 [Figure] (nAT + nPAT) + 20 [Figure] nCAT
+ 28 [Figure] (nAF + nCAF) + 20 [Figure] (nAA + nPAA + nCAA) + 1,057 [Figure] nSPA + 120 [Figure] nSPP
+ 287 [Figure] nSFF + 8 [Figure] nSFP + 813 [Figure] nJFC + 20 [Figure] nJFP
Add the result of this formula when using a trigger:
+ 1,057 [Figure] nTR + 120 [Figure] (nTSN + nTSO) + 20 [Figure] (nTCN + nTCO)
Add the result of this formula when using a referential constraint:
+ 760 + 376 [Figure] RCC + 1,880 [Figure] RCT
Add the result of this formula when using a set operation:
+ 64 [Figure] Ui
Add the result of this formula when using matrix partitioning:
+ 16 [Figure] Tmi + 16 [Figure] Tmi[Figure] Di
Add the result of this formula when specifying a row value constructor:
+ 384 [Figure] Wri + 32 [Figure] QXs
Add the result of this formula when using a check constraint:
+ 72 [Figure] CDi + 88
Add the result of this formula when obtaining SQL runtime interim results:
+ 24 [Figure] PIX + 192 [Figure] Ti + 68 [Figure] QX + 96
Add the result of this formula when specifying the LIMIT clause:
+ 160
Add the result of this formula when defining a falsification prevented table:
+ 200
Add the result of this formula when using the SET SESSION AUTHORIZATION statement:
+ 32
Add the result of this formula when obtaining transfer values from a list:
+ 32 [Figure] ALP
Add the result of this formula when using the XML type:
+ 36 [Figure] Ti + 4,240 [Figure] XQX + 8 [Figure] XQX[Figure] nURI
Add the result of this formula when using a character set:
+ 80 [Figure] TCCi + 88 [Figure] sRi
Add the result of this formula when using a sequence generator:
+ 168 [Figure] nSQ
Add the result of this formula when using the data compression facility:
+ 8 [Figure] Ci + 32 [Figure] nUOC + 28
Add the result of this formula when using temporary tables:
+ 200 [Figure] nTT
This is a computation formula for column name description area size. Add the result of this formula when using dynamic SQL:
+ 32 [Figure] Si + 16
This is a computation formula for type name description area size. Add the result of this formula when using dynamic SQL:
+ [Figure](42 [Figure] SiT) + {52 + 152 [Figure] (SiTA + SiSA + SiNA) [Figure] (SiT + SiS + SiN)}[Figure]
} [Figure] 1,024[Figure]
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 (in kilobytes) =
[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
Add the result of this formula when using a trigger:
+ 28 [Figure] (nTSN[Figure] 2 + nTSO)
} [Figure] 1,024[Figure]
(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 (in kilobytes) =
[Figure]{
608 [Figure] RCC + (5,120 + 100 [Figure] RDi + 256 [Figure] RIi) [Figure] RCP[Figure] RCT
} [Figure] 1,024[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
Also add the number of columns for which the following definition was specified during table definition (these columns might not be in the SQL statement):
  • Columns for which DEFAULT is specified in the column definition
  • Columns for which WITH DEFAULT is specified for a NOT NULL constraint specification
TCiNumber of table columns inside the SQL statement
WiNumber of boolean operators inside the SQL statement#
KiNumber of constants inside the SQL statement#
LiTotal size of the constants inside the SQL statement# (in 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)
QXNumber of query specifications
AiTotal number of scalar operations in the SQL statement
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 invoked#
nFPTotal number of function arguments inside the SQL statement#
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 invoked#
nSFPTotal number of arguments for the system definition scalar functions inside the SQL statement#
nJFCNumber of times external Java functions are invoked inside the SQL statement
nJFPTotal number of arguments of external 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
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)
UiNumber of set operations + 1
TmiNumber of tables with matrix partitioning
WriNumber of conditions using a row value constructor with two or more row value constructor elements specified
QXsNumber of table subqueries with multiple selection expressions specified
CDiNumber of check constraint conditions
PIXNumber of indexes used as multiple indexes
ALPNumber of receive functions for passing inter-function values from a list
XQXNumber of XQuery specifications
nURINumber of XML name space URIs specified
TCCiNumber of columns with character set specification
nSQNumber of sequence generators
nUOCNumber of UOCs used for data compression
nTTNumber of temporary tables
sRiNumber of SQL parameters inside procedures and functions (multiply the number of SQL parameters with the INOUT specification 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 external Java procedures are invoked inside the routine
nPJPTotal number of arguments for external Java procedures inside the routine
nRJFCNumber of times external Java functions are invoked inside the routine
nRJFPTotal number of arguments for external 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
#: When triggers are used, the trigger action conditions for the individual triggers that are activated by SQL statement execution must also be counted.

(4) Executing updatable online reorganization

The SQL object buffer size must be recalculated when you execute updatable online reorganization. The following formula is used to determine the SQL object buffer size:

SQL object buffer size = current SQL object buffer size + SQL object buffer size required for updatable online reorganization

Reference note
In reflection processing during updatable online reorganization, the SQL code is assembled from the update log and executed on the table in the original RDAREA. Because the SQL code executed at reflection processing might differ from the SQL code executed by the UAP, the amount of SQL object buffer space that will be needed must be determined.
(a) Formula for determining the size of the SQL object in an SQL statement

The formula that is used for determining the size of the SQL object in an SQL statement for updatable online reorganization is the formula presented in (1) Formula for determining the size of the SQL object in an SQL statement. Refer to the table below for details about the values of the variables used in the formula:

Variable nameExplanation
RCN2
When an SQL statement is issued, there is one table and one index, for a total of 2.
Si0
UAP searches (SELECT) are not subject to reflection, so this value is 0.
PiDepends on the SQL:
  • INSERT: Number of member columns in the table that the UAP will update#2
  • UPDATE table-name SET: Number of member columns in the table that the UAP will update#2 + Number of mapping key member columns
    This formula adds the number of elements of the following updated repetition columns:
    [Figure]column-name[suffix]
    [Figure]column-name[*]
  • UPDATE table-name ADD: Number of repetition columns that the UAP will update + Number of mapping key member columns
  • UPDATE table-name DELETE: Number of mapping key member columns
  • DELETE: Number of mapping key member columns
If the calculation result of Pi is greater 30,000, Pi is assumed to be 30,000.
Ti1
For SQL that is executed by reflection processing, there is only one table name, so this value is 1.
CiDepends on the SQL:
  • INSERT: Number of member columns in the table#2
  • UPDATE table-name SET: Number of member columns in the table that the UAP will update#2 + Number of mapping key member columns
    This formula adds the number of elements of the following updated repetition columns:
  • UPDATE table-name ADD: Number of repetition columns that the UAP will update + Number of mapping key member columns
  • UPDATE table-name DELETE: Number of mapping key member columns
  • DELETE: Number of mapping key member columns
If the calculation result of Ci is greater 30,000, Ci is assumed to be 30,000.
TCiNumber of member columns in the table that the UAP will update
Wi0
Ki0
Li0
Ii1
For updatable online reorganization, one unique index is determined and specified in the search conditions, so this value is 1.
DiNumber of table storage conditions
For matrix partitioning, double the value.
SiT0
SiS0
SiN0
SiTA0
SiSA0
SiNA0
nSPA0
nSPP0
nFFNumber of abstract data type columns defined in the table#1 + Number of columns on which SUBSTR operation is performed in update target BLOB or BINARY columns
nFP[Figure]Abstract data type columns defined in the table#1 + [Figure] {number of SUBSTR operation updates by BLOB or BINARY columns#1 defined in the table [Figure] 3}
nFC0
nPFFNumber of abstract data type columns defined in the table#1 + 6
If no abstract data types are defined in the table, this value is 0.
nSFFNumber of BLOB or BINARY columns updated by SUBSTR operation
nSFPNumber of BLOB or BINARY columns updated by SUBSTR operation [Figure] 3
nJFC0
nJFP0
nAT0
nAA0
nAF0
nPAT0
nPAA0
nCATNumber of abstract data type columns defined in the table #1
nCAANumber of types of abstract data types defined in the table#1
nCAF[Figure](number of attributes of abstract data types defined in the table#1[Figure] n)
n: For SGMLTEXT and XML types, 1; for FREEWORD type, 2.
nTR0
nTSN0
nTSO0
nTCN0
nTCO0
RCCTotal number of member columns of the external key of the table that references the table to be updated and the number of member columns of the primary key inside the SQL statement.
RCTTotal number of tables that reference the table to be updated and tables that the table to be updated references inside the SQL statement.
#1:
For specifying the -e option with the pdorbegin command and reflecting only updated columns, make the following substitutions:
Before substitutionAfter substitution
Number of abstract data type columns defined in the tableNumber of abstract data type columns that are targets for updating among the abstract data type columns defined in the table
BLOB or BINARY column defined in the tableThose BLOB or BINARY columns that are targets for updating among the BLOB or BINARY columns defined in the table.
However, also add the number of columns defined as follows at the time of table definition among those BLOB or BINARY columns that are explicitly not updated by the UAP:
  • Columns for which the DEFAULT clause is specified in their column definition
  • Columns for which the WITH DEFAULT clause is specified in their NOT NULL constraint specification
Number of types of abstract data types defined in the tableNumber of types of abstract data types that are targets for updating among the types of abstract data types defined in the table
Number of attributes of abstract data types defined in the tableNumber of attributes of abstract data types that are targets for updating among the attributes of the abstract data types defined in the table
#2:
If the SQL issued in reflection processing is a format 2 INSERT or UPDATE statement, or if reflection processing is run only on updated columns, make the following substitutions:
Before substitutionSQL typeAfter substitution
Number of member columns in the table that the UAP will updateINSERT
(format 1)
--
UPDATE
(format 1)
Number of columns updated explicitly by the UAP
However, also add from among those columns that are not updated explicitly by the UAP the number of columns defined as follows at the time of table definition:
  • Columns for which the DEFAULT clause is specified in their column definition
  • Columns for which the WITH DEFAULT clause is specified in their NOT NULL constraint specification
INSERT
(format 2)
1
UPDATE
(format 2)
Legend:
--: No substitution required
(b) Formula for determining the minimum required SQL object buffer size

The formula for determining the minimum required SQL object buffer size that will not generate shortages of SQL object buffer space during reflection processing is shown below:

Formula

MAX{ORG_SQL_OBJ_MAX_SIZE,ORG_SQL_OBJ_AVE_SIZE[Figure] ORG_PROC_NUM[Figure]
[Figure]
Explanation of variables
ORG_SQL_OBJ_MAX_SIZE: Size of the largest SQL object among the individual SQL objects found in the SQL being executed during reflection processing
ORG_SQL_OBJ_AVE_SIZE: Average SQL object size in the SQL being executed during reflection processing
ORG_PROC_NUM: Number of reflection processes (value specified in the -m option of the pdorend command; if the -m option is omitted, this value is 2)
ORG_SQL_KIND: Number of SQL types (INSERT, UPDATE, and DELETE only) being executed on the table
n: Number of tables subject to updatable online reorganization
(c) When the SQL object buffer size must be recalculated

In the following cases, recalculate the size of the SQL object buffer: