For information on the variables used in the formulas, see (3) following.
The following formula is used to determine the size of the SQL object in an SQL statement:
Formula
|
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
|
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
|
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):
|
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 |
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
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:
|
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:
|
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 | ![]() ![]() ![]() |
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 ![]() |
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 | ![]() ![]() 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. |
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:
|
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 |
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:
| |
INSERT (format 2) | 1 | |
UPDATE (format 2) |
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![]() ![]() ![]() |
In the following cases, recalculate the size of the SQL object buffer: