Appendix D.3 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 (KB)
(3) Determining the table definition cache size

(1) Variables used in the formulas

a: Value of DEFINITION_CACHE_SIZE (units: 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
c: Number of indexes in the table
d: Number of table-partitioning conditions
e: Number of RDAREAs for table
f: Number of RDAREAs for index
g: Number of BLOB columns
h: Number of abstract data types
i: Number of plug-in options
j: Number of plug-in indexes
k: Total number of applicable functions
m: Total number of BLOB attributes in abstract data types
n: Number of index exceptional key values
p: Value of STATISTICS_CACHE_SIZE (units: 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] q + 0.04 [Figure] c + 0.02 (units: KB)
q: Number of column optimization information items
r: Number of abstract data types containing BLOB attributes
s: Number of columns specified by the DEFAULT operand of CREATE TABLE
t: Total size of the default values to be specified by the DEFAULT operand of CREATE TABLE
Add the sizes of all columns for which default values are specified. If the default values may be increased, take the increases into consideration.
u: Number of authorization identifiers that have the same table identifier as the table name of the public view table

(2) Table definition information buffer size per table (KB)

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 (units: KB)
Table definition information buffer size per base table or foreign table[Figure]{(4 + [Figure] a[Figure] 1024 [Figure] + 0.01 [Figure] b + p + 7) [Figure] 8}[Figure][Figure] 8
Table definition information buffer size per view table[Figure]{(4 + [Figure] a[Figure] 1024 [Figure] + 0.01 [Figure] b + 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 or not 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.