Appendix C.4 Formulas for determining size of table definition information buffer (pd_table_def_cache_size)

Organization of this subsection
(1) Variables used in the formulas
(2) Table definition information buffer size per table (in kilobytes)
(3) Determining the table definition cache size

(1) Variables used in the formulas

a: Value of DEFINITION_CACHE_SIZE (in bytes) of a dictionary table (SQL_TABLES table)
If you do not know the value of DEFINITION_CACHE_SIZE, see (3) Determining the table definition cache size.
b: Number of columns in the table
Substitute 8 for a reflection status management table.
c: Number of indexes in the table
Substitute 1 for a reflection status management table.
d: Number of table-partitioning conditions
For a reflection status management table, substitute the number of servers to which the RDAREAs specified in the -r and -o options of the pdorcreate command belong.
e: Number of RDAREAs for table
For a reflection status management table, substitute the number of RDAREAs specified in the -r option of the pdorcreate command + 1.
f: Number of RDAREAs for index
For a reflection status management table, substitute the number of RDAREAs specified in the -r option of the pdorcreate command + 1.
g: Number of BLOB columns
h: Number of abstract data types that include BLOB attributes
i: Total number of BLOB attributes in abstract data types
j: Number of abstract data types
k: Number of plug-in options
m: Total number of BLOB attributes in abstract data types
n: Number of index exceptional key values
p: Value of STATISTICS_CACHE_SIZE (in bytes) of a dictionary table (SQL_TABLES table)
Add this value when you use the pdgetcst command to obtain table optimization information. Because the result is in bytes, it must be converted into kilobytes before being entered in the formulas. If you do not know the value of STATISTICS_CACHE_SIZE, use the following formula to determine it:
(2.6 [Figure] q1 + 3.0 [Figure] q2 + 0.04 [Figure] c + 0.02) [Figure] 1,024 (bytes)
q1: Total of the number of column optimization information items whose column data type is not DECIMAL and the number of column optimization information items that are DECIMAL type with a precision up to 31 digits.
q2: Number of column optimization information items whose column data is DECIMAL type with a precision of 32 digits or more
r: Number of columns specified in the DEFAULT operand of CREATE TABLE
s: Total default size specified in the DEFAULT operand of CREATE TABLE (bytes)
Add the sizes of all columns for which default values are specified. If the default values might be increased, take the increases into consideration.
t: Number of authorization identifiers that have the same table identifier as the table name of the public view table
u: Number of triggers defined in the table
v: Number of columns used that are qualified by old or new value correlation names
w: Number of trigger action conditions
x: Total size of trigger action condition analysis tree (bytes)
For the formulas used to estimate the size of the trigger action condition analysis tree, see Determining the size of a normal data dictionary RDAREA in the HiRDB Version 9 Installation and Design Guide.
y: Number of trigger columns of the UPDATE trigger
z: Number of foreign keys defined in the table
aa: Number of foreign keys that reference the table's primary key
ab: Number of check constraints defined in the table
ac: Total size of analysis tree for check constraints (bytes)
For the formulas used to estimate the size of the analysis tree for check constraints, see Determining the size of a normal data dictionary RDAREA in the HiRDB Version 9 Installation and Design Guide.
ad: Number of index member substructure paths
ae: Total size of the substructure paths of that index
af: Total size of the analysis tree for the substructure paths of that index (bytes)
For the formulas used to estimate the size of the analysis tree for substructure paths, see Determining the size of a normal data dictionary RDAREA in the HiRDB Version 9 Installation and Design Guide.
ag: Number of compressed columns defined for the table

(2) Table definition information buffer size per table (in kilobytes)

To determine the table definition information buffer size per table, use the following approximation formulas. Note that for a view table, also determine the size of the base table or foreign table that becomes the base for the view table.

Table typeFormula for determining the table definition information buffer size per table (in kilobytes)
Table definition information buffer size per base table[Figure](4 + [Figure]a[Figure] 1,024[Figure] + 0.01 [Figure] b + [Figure]p[Figure] 1,024[Figure] +7) [Figure] 8[Figure][Figure] 8
Table definition information buffer size per view table[Figure](4 + [Figure]a[Figure] 1,024[Figure] + 0.01 [Figure] b + 7) [Figure] 8[Figure][Figure] 8
Table definition information buffer size for reflection status management table (HiRDB Staticizer Option)[Figure](4 + [Figure]a[Figure] 1,024[Figure] + 0.01 [Figure] 8 + 7) [Figure] 8[Figure][Figure] 8

(3) Determining the table definition cache size

(a) When you do not know the value of DEFINITION_CACHE_SIZE

If any of the following conditions is applicable, the value of DEFINITION_CACHE_SIZE cannot be determined, even if a dictionary table is retrieved:

#1: In this case, the value in DEFINITION_CACHE_SIZE is not the correct value.

#2: Whether a table has been converted from 32-bit-mode HiRDB to 64-bit-mode HiRDB can be determined by looking at the time of execution of the pdvrup command and the time of table creation. If the table's creation time is earlier, the table has been converted from the 32-bit mode to the 64-bit mode. The pdvrup command's execution time can be determined from CHANGE_TIME of MASTER.SQL_TABLES in the SQL_TABLES dictionary table. A table's creation time can be determined from CREATE_TIME for the created table in the SQL_TABLES dictionary table. These columns can be retrieved with the following SQL statements:

  select CHANGE_TIME from MASTER.SQL_TABLES
    where TABLE_SCHEMA='MASTER'
    and TABLE_NAME='SQL_TABLES'

  select CREATE_TIME from MASTER.SQL_TABLES
    where TABLE_SCHEMA='authorization-identifier'
    and TABLE_NAME='table-name'

(b) Formula for determining the table definition cache size

Use the following formulas to determine the table definition cache size.