17.5 Determining the size of a data dictionary LOB RDAREA

Organization of this section
(1) Estimating the size of the data dictionary LOB RDAREA for storing sources
(2) Estimating the size of the data dictionary LOB RDAREA for storing objects
(3) QOi (SQL object size) formula
(4) PR (routine control object size) formula
(5) Variables used in the calculation of PR and QOi

(1) Estimating the size of the data dictionary LOB RDAREA for storing sources

The following formula is used to determine the size of the data dictionary LOB RDAREA for storing sources.

Formula
Size of data dictionary LOB RDAREA for source storage (bytes)
={
a
[[Figure][Figure] Si[Figure] 64000 [Figure][Figure] 96 + 7 + 3 [Figure](a - 1)]1
i=1
+
b
[[Figure][Figure](Cj + 1024) [Figure] 8192 [Figure]]2
j=1
}3[Figure] 81924
a: Number of HiRDB files that constitute the data dictionary LOB RDAREA for storing sources
b: Total number of procedures (CREATE PROCEDURE), functions and procedures in abstract data types (each FUNCTION (excluding plug-in functions) and PROCEDURE), and user-defined functions (CREATE FUNCTION)
Si: Number of segments for each HiRDB file specified with the create rdarea statement of the database initialization utility or database structure modification utility.
Cj: Length of each procedure (length of each CREATE PROCEDURE), function and procedure in abstract data types (length of each FUNCTION (excluding plug-in functions) and PROCEDURE), and user-defined function (length of CREATE FUNCTION)
1 Total number of pages in the directory pages part.
2 Total number of pages in the data pages part.
3 Indicates the total number of pages in the data dictionary LOB RDAREA for storing sources.
4 Indicates the page length of the data dictionary LOB RDAREA for storing sources.

(2) Estimating the size of the data dictionary LOB RDAREA for storing objects

The following formula is used to estimate the size of the data dictionary LOB RDAREA for storing objects:

Formula
Size of data dictionary LOB RDAREA for object storage (bytes)
={
a
[[Figure][Figure] Si[Figure] 64000 [Figure][Figure]96 + 7 + 3 [Figure](a - 1)]1
i=1
+
b
[[Figure][Figure](Cj + 1024) [Figure] 8192 [Figure]]2
j=1
}3[Figure] 81924 + 5000005
a: Number of HiRDB files that constitute the data dictionary LOB RDAREA for storing objects
b: Total number of procedures (CREATE PROCEDURE), functions and procedures in abstract data types (FUNCTION (excluding plug-in functions), PROCEDURE), user-defined functions (CREATE FUNCTION), and trigger definitions (CREATE TRIGGER).
Si: Number of segments for each HiRDB file specified with the create rdarea statement of the database initialization utility (pdinit) or database structure modification utility (pdmod).
Cj: QOi + PR (The formula for QOi is shown in (3), and the formula for PR is shown in (4). The variables used in these formulas are shown in (5).)
1 Total number of pages in the directory pages part.
2 Total number of pages in the data pages part.
3 Indicates the total number of pages in the data dictionary LOB RDAREA for storing objects.
4 Indicates the page length of the data dictionary LOB RDAREA for storing objects.
5 This is added when an abstract data type or plug-in function is used.

(3) QOi (SQL object size) formula

QOi (bytes) =
a
[Figure]{
i=1

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
}
a: Number of SQL statements in stored procedures
1 Add this formula if you specify foreign tables.
2 Add this formula if you use triggers.
3 Add this formula if you use referential constraints.
4 This is the formula for determining the length of the Column Name Descriptor Area (SQLCNDA). Add it for dynamic SQL statements.
5 This is the formula for determining the length of the Type Name Descriptor Area (SQLTNDA). Add it for dynamic SQL statements.

(4) PR (routine control object size) formula

(a) When defined by the user

If you have defined a stored procedure, stored function, or trigger, use the following formula to determine the size of the routine control object:

PR (bytes) =
a
[Figure]{
i=1

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)]*
}
a: Number of the following SQL statements:
  • Procedures (CREATE PROCEDURE)
  • Functions and procedures in abstract data types (each FUNCTION (excluding plug-in functions), PROCEDURE)
  • User-defined functions (CREATE FUNCTION)
  • Trigger definitions (CREATE TRIGGER)
* Add this formula if you use triggers.
(b) When HiRDB creates automatically

If you specified CASCADE during table definition, use the following formula to determine the size of the routine control object when HiRDB creates triggers for constraint control:

PR (bytes) =
a
[Figure]{240 + 608 [Figure] RCC + (5120 + 100 [Figure] RDi + 256 [Figure] RIi) [Figure] RCP[Figure] RCT}
i=1
a: Number of the following SQL statements:
  • Procedures (CREATE PROCEDURE)
  • Functions and procedures in abstract data types (FUNCTION (excluding plug-in functions), PROCEDURE)
  • User defined functions (CREATE FUNCTION)
  • Trigger definitions (CREATE TRIGGER)

(5) Variables used in the calculation of PR and QOi

Variable nameExplanation
RCNTotal number of tables and indexes used by SQL objects
SiNumber of retrieval items in SQL statements (if the columns specified by SQL statements are index columns, the number of those columns)
PiNumber of embedded variables or parameters in SQL statements
TiNumber of table names in SQL statements
CiNumber of column names in SQL statements
TCiNumber of table composition columns in SQL statements
WiNumber of logical operators in SQL statements1
KiNumber of literals in SQL statements1
LiTotal length of literals in SQL statements1 (bytes)
IiNumber of indexes used during SQL statement execution (of the tables specified by SQL statements, the number of indexes specified in retrieval conditions)
DiTotal number of storage conditions defined by tables used in SQL statements (count matrix partitioning tables twice)
SiTNumber of abstract data types in queries in SQL statements
SiSNumber of supertypes of abstract data types in queries in SQL statements
SiNNumber of subtypes of abstract data types in queries in SQL statements
SiTANumber of attributes of abstract data types in queries in SQL statements
SiSANumber of supertype attributes of abstract data types in queries in SQL statements
SiNANumber of component specifications of abstract data types that are query subtypes in SQL statements
nSPANumber of procedure calls in SQL statements
nSPPTotal number of procedure call parameters in SQL statements
nFFNumber of function calls in SQL statements1
nFPNumber of function call parameters in SQL statements1
nFCTotal number of function definition candidates among the functions in the SQL statements (to the number of function calls nFF, add the number of function definitions that have subtypes as arguments for which the arguments are abstract data types)
nPFFNumber of plug-in function calls used by SQL objects (number of plug-in function calls in SQL statements + 1 for SELECT and 6 for INSERT, UPDATE, or DELETE)
nSFFNumber of system definition scalar function calls in SQL statements1
nSFPTotal number of system definition scalar function arguments in SQL statements1
nJFCNumber of Java function calls in SQL statements
nJFPTotal number of Java function arguments in SQL statements
nATNumber of abstract data types used by component specifications in SQL statements (excluding supertypes and abstract data types that emerge depending on the abstract data type attributes)
nAANumber of abstract data types used by component specifications in SQL statements (including supertypes and abstract data types that emerge depending on the abstract data type attributes)
nAFTotal number of attributes used by component specifications in SQL statements
nPATNumber of abstract data types of plug-in function arguments used by SQL objects (excluding supertypes and abstract data types that emerge depending on the abstract data type attributes)
nPAANumber of abstract data types of plug-in function arguments used by SQL objects (including supertypes and subtypes)
nCATNumber of constructor function calls in SQL statements
nCAANumber of constructor function abstract data types in SQL statements (including supertypes)
nCAFTotal number of constructor function abstract data type attributes in SQL statements
nFTNumber of foreign tables in SQL statements
nFTSLength of SQL statements that retrieve from foreign tables2
nFTQNumber of embedded variables or ? parameters in SQL statements that retrieve from foreign tables2
nTRNumber of triggers activated by the execution of SQL statements
nTSNTotal number of columns modified by new value correlation names in SQL statements that are triggered by the execution of SQL statements
nTSOTotal number of columns modified by old value correlation names in SQL statements that are triggered by the execution of SQL statements
nTCNTotal number of columns modified by new value correlation names in the trigger action conditions of triggers that are activated by the execution of SQL statements
nTCOTotal number of columns modified by old value correlation names in the trigger action conditions of triggers that are activated by the execution of SQL statements
RCCTotal number of foreign key component columns and primary key component columns of the tables that reference update-target tables in SQL statements
RCTSum of the number of tables that reference update-target tables and the number of tables that are referenced by update-target tables in SQL statements
RCPTotal number of CASCADEs specified for referencing action when referencing tables are defined
RIiTotal number of indexes defined for referenced tables with reference specified when referencing tables are defined
RDiTotal number of partition storage conditions defined for referenced tables with reference specified when referencing tables are defined (double the value for matrix partitioning tables)
sRiNumber of SQL parameters in procedures and functions (count SQL parameters specified with INOUT twice)
sRUiTotal number of SQL parameters in procedures and functions (or total number of columns modified by new or old value correlation names in the triggered SQL statements defined by triggers)
sDiTotal number of SQL variables (declare) in procedures, functions, and triggered SQL statements
sSXiTotal number of SQLCODE and SQLCOUNT variables in procedures, functions, and triggered SQL statements
sCUiTotal number of CURRENT_TIME and CURRENT_DATE variables in procedures, functions, and triggered SQL statements
sSiNumber of data manipulation SQLs in procedures and triggered SQL statements (excluding cursor declarations: OPEN, FETCH, CLOSE, UPDATE, DELETE, INSERT, etc.)
sPiNumber of routine control SQL statements in procedures, functions, and triggered SQL statements (BEGIN, SET, IF, ELSEIF, WHILE, etc.)
sLANumber of labels in procedures, functions, and triggered SQL statements
sKiNumber of literals in procedures, functions, and triggered SQL statements (excluding data manipulation SQL literals described in procedures and triggered SQL statements)
sLTotal length of constants in procedures, functions, and triggered SQL statements (excluding data manipulation SQL literals described in procedures and triggered SQL statements)
sWiNumber of conditional predicates in procedures, functions, and triggered SQL statements
sCMNumber of compound statements in procedures, functions, and triggered SQL statements
sCCRNumber of compound statements that describe cursor declarations in procedures and triggered SQL statements
sDCRNumber of cursor declarations in procedures and triggered SQL statements
sCHDNumber of compound statements that specify handler declarations in procedures, functions, and triggered SQL statements
sDHDNumber of handler declarations in procedures, functions, and triggered SQL statements
sHCNNumber of condition values specified in handler declarations in procedures, functions, and triggered SQL statements
nRFFNumber of function calls in routines
nRFPTotal number of function arguments in routines
nRFCTotal number of function definition candidates among the routines in the SQL statements (to the number of function calls nFF, add the number of function definitions that have subtypes as arguments for which the arguments are abstract data types)
nPRFFNumber of plug-in function calls used by routine SQL objects
nPRFPTotal number of plug-in parameters of plug-in function calls used by routine SQL objects
nPANumber of procedure calls in routines
nPPTotal number of procedure parameters in routines
nPPITotal number of input parameters in routine procedures (including input/output parameters)
nPPOTotal number of output parameters in routine procedures (including input/output parameters)
nPPANumber of plug-in procedure calls in routine SQL objects
nPPPTotal number of plug-in parameters of plug-in procedures used by routine SQL objects
nRSFFNumber of system defined scalar function calls in routines
nRSFPTotal number of system defined scalar function call arguments in routines
nPJANumber of Java procedure calls in routines
nPJPTotal number of Java procedure call arguments in routines
nRJFCNumber of Java function calls in routines
nRJFPTotal number of Java function call arguments in routines
nARNumber of abstract data types used by component specifications in routines (excluding supertypes and abstract data types that emerge depending on the abstract data type attributes)
nARATotal number of attributes used by component specifications in routines
nRPATTotal number of abstract data types used as parameters of plug-in routines used by routine SQL objects (excluding abstract data types that are supertypes or abstract data type attributes)
nRPAANumber of abstract data types used as parameters of plug-in routines used by routine SQL objects (including supertypes)
nRPAFTotal number of attributes of abstract data types used as parameters of plug-in routines used by routine SQL objects
nRCATNumber of constructor function calls in routines
nRCAANumber of abstract data types of constructor functions in routines (including supertypes)
nRCAFTotal number of abstract data type attributes of constructor functions in routines
1 When triggers are used, all of the trigger activation conditions of the triggers activated by execution of SQL statements must be counted.
2 SQL statements that retrieve from foreign tables can be checked with the access path display utility (pdvwopt command).