Formulas for determining size of SQL object buffer (pd_sql_object_cache

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 =: {; 1,600 + 46 RCN + 394 Si + 24 Pi + 2,058 Ti + 76 Ti Di + 80 Ci + 40 Ii + 586 Wi; + 24 Ki + Li + 8 TCi + 656 Di + 116 Ti QX + 28 QX + 200 Ai + 48 nFF; + 100 nFP + 148 nFC + 712 nPFF + 32 (nAT + nPAT) + 20 nCAT; + 28 (nAF + nCAF) + 20 (nAA + nPAA + nCAA) + 1,057 nSPA + 120 nSPP; + 287 nSFF + 8 nSFP + 813 nJFC + 20 nJFP; Add the result of this formula when using a trigger:; + 1,057 nTR + 120 (nTSN + nTSO) + 20 (nTCN + nTCO); Add the result of this formula when using a referential constraint:; + 760 + 376 RCC + 1,880 RCT; Add the result of this formula when using a set operation:; + 64 Ui; Add the result of this formula when using matrix partitioning:; + 16 Tmi + 16 Tmi Di; Add the result of this formula when specifying a row value constructor:; + 384 Wri + 32 QXs; Add the result of this formula when using a check constraint:; + 72 CDi + 88; Add the result of this formula when obtaining SQL runtime interim results:; + 24 PIX + 192 Ti + 68 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 ALP; Add the result of this formula when using the XML type:; + 36 Ti + 4,240 XQX + 8 XQX nURI; Add the result of this formula when using a character set:; + 80 TCCi + 88 sRi; Add the result of this formula when using a sequence generator:; + 168 nSQ; Add the result of this formula when using the data compression facility:; + 8 Ci + 32 nUOC + 28; Add the result of this formula when using temporary tables:; + 200 nTT; This is a computation formula for column name description area size. Add the result of this formula when using dynamic SQL:; + 32 Si + 16; This is a computation formula for type name description area size. Add the result of this formula when using dynamic SQL:; + (42 SiT) + {52 + 152 (SiTA + SiSA + SiNA) (SiT + SiS + SiN)}; } 1,024

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

(3) Variables used in the formulas

Variable name	Explanation
RCN	Total number of tables and indexes used by the SQL object
Si	Number of retrieval items inside the SQL statement (number of columns if the columns specified by the SQL statement are index columns)
Pi	Number of embedded variables or parameters inside the SQL statement
Ti	Number of table names inside the SQL statement
Ci	Number 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
TCi	Number of table columns inside the SQL statement
Wi	Number of boolean operators inside the SQL statement^#
Ki	Number of constants inside the SQL statement^#
Li	Total size of the constants inside the SQL statement^# (in bytes)
Ii	Number of indexes used during SQL statement execution (number of indexes specified as retrieval conditions in the table specified by the SQL statement)
Di	Total number of storage conditions defined in the table inside the SQL statement (times 2 for a matrix-partitioned table)
QX	Number of query specifications
Ai	Total number of scalar operations in the SQL statement
SiT	Number of abstract data types in the selection expression inside the SQL statement
SiS	Number of supertypes for the abstract data types in the selection expression inside the SQL statement
SiN	Total number of supertypes for the abstract data type, which is a subtype of the selection expression inside the SQL statement
SiTA	Number of attributes for the abstract data types in the selection expression inside the SQL statement
SiSA	Number of attributes of the supertypes for the abstract data types in the selection expression inside the SQL statement
SiNA	Total number of components specified for the abstract data type, which is a subtype of the selection expression inside the SQL statement
nSPA	Number of times the procedure statements inside the SQL statement are invoked
nSPP	Total number of arguments for the procedure statements inside the SQL statement
nFF	Number of times the functions inside the SQL statement are invoked^#
nFP	Total number of function arguments inside the SQL statement^#
nFC	Total 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)
nPFF	Number 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)
nSFF	Number of times the system definition scalar functions inside the SQL statement are invoked^#
nSFP	Total number of arguments for the system definition scalar functions inside the SQL statement^#
nJFC	Number of times external Java functions are invoked inside the SQL statement
nJFP	Total number of arguments of external Java functions inside the SQL statement
nAT	Number 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)
nAA	Number 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)
nAF	Total number of attributes used by component specification inside the SQL statement
nPAT	Number 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)
nPAA	Number of abstract data types used by the arguments of the plug-in functions used by the SQL object (including supertypes and subtypes)
nCAT	Number of times the constructor functions inside the SQL statement are invoked
nCAA	Number of abstract data types of the constructor functions inside the SQL statement (including supertypes)
nCAF	Total number of attributes of the abstract data types of the constructor functions inside the SQL statement
nTR	Number of triggers that are activated by SQL statement execution
nTSN	Total number of columns modified by the new values correlation name inside the trigger SQL statement of each trigger activated by SQL statement execution
nTSO	Total number of columns modified by the old correlation name inside the trigger SQL statement of each trigger activated by SQL statement execution
nTCN	Total number of columns modified by the new values correlation name inside the trigger action condition of each trigger activated by SQL statement execution
nTCO	Total number of columns modified by the old correlation name inside the trigger action condition of each trigger activated by SQL statement execution
RCC	Total 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
RCT	Total number of tables that reference the update-target table and the number of tables that the update-target table references inside the SQL statement
RCP	Total number of cascades specified for referencing actions during referencing table definition
RIi	Total number of indexes defined for referenced tables that are specified for referencing during referencing table definition
RDi	Total 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)
Ui	Number of set operations + 1
Tmi	Number of tables with matrix partitioning
Wri	Number of conditions using a row value constructor with two or more row value constructor elements specified
QXs	Number of table subqueries with multiple selection expressions specified
CDi	Number of check constraint conditions
PIX	Number of indexes used as multiple indexes
ALP	Number of receive functions for passing inter-function values from a list
XQX	Number of XQuery specifications
nURI	Number of XML name space URIs specified
TCCi	Number of columns with character set specification
nSQ	Number of sequence generators
nUOC	Number of UOCs used for data compression
nTT	Number of temporary tables
sRi	Number of SQL parameters inside procedures and functions (multiply the number of SQL parameters with the INOUT specification by 2).
sRUi	Total 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)
sDi	Number of SQL variables (declare) inside procedures, functions, and trigger SQL statements
sSXi	Total number of SQLCODE and SQLCOUNT variables inside procedures, functions, and trigger SQL statements
sCUi	Total number of CURRENT_TIME and CURRENT_DATE constants inside procedures, functions, and trigger SQL statements
sSi	Number 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)
sPi	Number of routine control SQL statements inside procedures, functions, and trigger SQL statements (BEGIN, SET, IF, ELSE IF, and WHILE, for example)
sLA	Number of labels inside procedures, functions, and trigger SQL statements
sKi	Number of constants inside procedures, functions, and trigger SQL statements (excluding the constants of data manipulation SQL statements described inside procedures and trigger SQL statements)
sL	Combined 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)
sWi	Number of condition predicates inside procedures, functions, and trigger SQL statements
sCM	Number of compound statements inside procedures, functions, and trigger SQL statements
sCCR	Number of compound statements that describe cursor declarations for procedures and trigger SQL statements
sDCR	Number of cursor declarations for procedures and trigger SQL statements
sCHD	Number of compound statements that describe handler declarations for procedures, functions, and trigger SQL statements
sDHD	Number of handler declarations for procedures, functions, and trigger SQL statements
sHCN	Number of condition values described inside handler declarations for procedures, functions, and trigger SQL statements
nRFF	Number of function invocations inside the routine
nRFP	Total number of function arguments inside the routine
nRFC	Total 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)
nPRFF	Number of times the plug-in functions used by SQL objects of the routine are invoked
nPRFP	Total number of plug-in parameters of the plug-in functions used by the SQL objects of the routine
nPA	Number of procedure invocations inside the routine
nPP	Total number of parameters for the procedures inside the routine
nPPI	Total number of input parameters for the procedures inside the routine (including input/output parameters)
nPPO	Total number of output parameters for the procedures inside the routine (including input/output parameters)
nPPA	Number of times the plug-in procedure used by the SQL objects of the routine is invoked
nPPP	Total number of plug-in parameters of the plug-in procedure used by the SQL objects of the routine
nRSFF	Number of times the system definition scalar functions inside the routine are invoked
nRSFP	Total number of arguments for the system definition scalar functions inside the routine
nPJA	Number of times external Java procedures are invoked inside the routine
nPJP	Total number of arguments for external Java procedures inside the routine
nRJFC	Number of times external Java functions are invoked inside the routine
nRJFP	Total number of arguments for external Java functions inside the routine
nAR	Number 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)
nARA	Total number of attributes used in component specification inside the routine
nRPAT	Number 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)
nRPAA	Number of abstract data types used by the parameters of the plug-in routines used by the SQL objects of the routine (including supertypes)
nRPAF	Total 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
nRCAT	Number of times the constructor functions inside the routine are invoked
nRCAA	Number of abstract data types of the constructor functions inside the routine (including supertypes)
nRCAF	Total 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 name	Explanation
RCN	2 When an SQL statement is issued, there is one table and one index, for a total of 2.
Si	0 UAP searches (SELECT) are not subject to reflection, so this value is 0.
Pi	Depends 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: column-name[suffix] 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.
Ti	1 For SQL that is executed by reflection processing, there is only one table name, so this value is 1.
Ci	Depends 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.
TCi	Number of member columns in the table that the UAP will update
Wi	0
Ki	0
Li	0
Ii	1 For updatable online reorganization, one unique index is determined and specified in the search conditions, so this value is 1.
Di	Number of table storage conditions For matrix partitioning, double the value.
SiT	0
SiS	0
SiN	0
SiTA	0
SiSA	0
SiNA	0
nSPA	0
nSPP	0
nFF	Number 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	Abstract data type columns defined in the table^#1 + {number of SUBSTR operation updates by BLOB or BINARY columns^#1 defined in the table 3}
nFC	0
nPFF	Number 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.
nSFF	Number of BLOB or BINARY columns updated by SUBSTR operation
nSFP	Number of BLOB or BINARY columns updated by SUBSTR operation 3
nJFC	0
nJFP	0
nAT	0
nAA	0
nAF	0
nPAT	0
nPAA	0
nCAT	Number of abstract data type columns defined in the table^#1
nCAA	Number of types of abstract data types defined in the table^#1
nCAF	(number of attributes of abstract data types defined in the table^#1 n) n: For SGMLTEXT and XML types, 1; for FREEWORD type, 2.
nTR	0
nTSN	0
nTSO	0
nTCN	0
nTCO	0
RCC	Total 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.
RCT	Total 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 substitution	After substitution
Number of abstract data type columns defined in the table	Number 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 table	Those 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 table	Number 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 table	Number 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 substitution	SQL type	After substitution
Number of member columns in the table that the UAP will update	INSERT (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)	1

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]

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:

When variable values described in (a) Formula for determining the size of the SQL object in an SQL statement become large
An example is when a table with more columns than the tables currently being used is added to the tables that are subject to updatable online reorganization.
When variable values described in (b) Formula for determining the minimum required SQL object buffer size become large
This applies when a new SQL type will be executed; for example, if up to this point only INSERT was executed, but now UPDATE will also be executed.

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

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