Appendix C.3 Formulas for determining size of view analysis information buffers (pd_view_def_cache_size)

Organization of this subsection
(1) Formulas for determining the size of the view analysis information buffer for a single view table
(2) LPTREE formulas
(3) Variables used in the formulas

(1) Formulas for determining the size of the view analysis information buffer for a single view table

The size of the view analysis information buffer for a single view table can be found from the following formulas. See (2) and (3) for details about the variables used in the formulas.

Formula for HiRDB in 32-bit mode

Size of the view analysis information buffer per view table (in kilobytes) =
[Figure]view-analysis-information-size-per-view-table[Figure] 1,024[Figure]
view-analysis-information-size-per-view-table (bytes) =
[Figure](1,024 + LCNST + 15) [Figure] 16[Figure][Figure] 16
+ [Figure](LPTREE + 15) [Figure] 16[Figure][Figure] 16
+ [Figure](28 [Figure] NINCC + 15) [Figure] 16[Figure][Figure] 16
+ [Figure](28 [Figure] NINCP + 15) [Figure] 16[Figure][Figure] 16
+ [Figure](24 + 512 [Figure] NTBL + 40 [Figure] (NTBL + NDTBL)
+ 128 [Figure] NCLM + 15) [Figure] 16[Figure][Figure] 16
+ [Figure](16 [Figure] MAX([Figure]NADTL[Figure] 50[Figure], 1)
+ 200 [Figure] NADTL) [Figure] 16[Figure][Figure] 16
+ [Figure](16 [Figure] MAX([Figure]NATTL[Figure] 50[Figure], 1)
+ 144 [Figure] NATTL) [Figure] 16[Figure][Figure] 16
+ 16 + 16 [Figure] (3 + NINCC + NINCP)

Formula for HiRDB in 64-bit mode

Size of view analysis information buffer per view table (in kilobytes) =
[Figure]view-analysis-information-size-per-view-table[Figure] 1,024[Figure]
view-analysis-information-size-per-view-table (bytes) =
[Figure](1,600 + LCNST + 15) [Figure] 16[Figure][Figure] 16
+ [Figure](LPTREE + 15) [Figure] 16[Figure][Figure] 16
+ [Figure](40 [Figure] NINCC + 15) [Figure] 16[Figure][Figure] 16
+ [Figure](40 [Figure] NINCP + 15) [Figure] 16[Figure][Figure] 16
+ [Figure](32 + 768 [Figure] NTBL + 48 [Figure] (NTBL + NDTBL)
+ 184 [Figure] NCLM + 15) [Figure] 16[Figure][Figure] 16
+ [Figure](20 [Figure] MAX([Figure]NADTL[Figure] 50[Figure], 1)
+ 224 [Figure] NADTL) [Figure] 16[Figure][Figure] 16
+ [Figure](20 [Figure] MAX([Figure]NATTL[Figure] 50[Figure], 1)
+ 160 [Figure] NATTL) [Figure] 16[Figure][Figure] 16
+ 16 + 24 [Figure] (3 + NINCC + NINCP)
Note
When a view definition includes a view table that generates an internal derived table, add the formula for the size of the view analysis information buffer for that table.
If the view table does not generate an internal derived table, add the value calculated for the SQL statement that is specified directly without going through its view table.

(2) LPTREE formulas

Determine LPTREE from the following formulas. See (3) for details about the variables used in the formulas.

Formula for HiRDB in 32-bit mode

LPTREE (bytes) =
276 [Figure] NQRY
+ 12 [Figure] (2 [Figure] NQRY + NSBQ + NSTOP)
+ 12 [Figure] (2 [Figure] NQRY + NSLST + NSLAS)
+ 12 [Figure] (4 [Figure] NTBL + 4 [Figure] NJTBL + 5 [Figure] NDTBL + NDCLM)
+ 12 [Figure] (NWHRC + NGPHV)
+ 12 [Figure] (NVLCM + NEXCM + NNLCM
+ 3 [Figure] NRANG + 3 [Figure] NLKCM + 3 [Figure] NSMCM + NETCM)
+ 12 [Figure] NRVCL
+ 12 [Figure] NTVCL
+ 12 [Figure] NLGEX
+ 12 [Figure] (NCNST + NSREG + NPRCS)
+ 12 [Figure] 4 [Figure] NCSCV
+ 12 [Figure] (NARTH + NCNCT)
+ 12 [Figure] NSFNC
+ 12 [Figure] NLBLD
+ 12 [Figure] (3 [Figure] NCLM)
+ 12 [Figure] (NCASE + NWHEN)
+ 12 [Figure] 2 [Figure] (NSCLF + 2 [Figure] NCSSP)
+ 12 [Figure] (2 [Figure] NPOS + 3 [Figure] NDTV)
+ 12 [Figure] (NEXTR + 2 [Figure] NSBST + 3 [Figure] NVALU + NBTEX + NCAST)
+ 12 [Figure] (4 [Figure] NFCSP + NFPRM)
+ 12 [Figure] (3 [Figure] NATTL + NATNM)
+ 28 [Figure] NOPTL + 20 [Figure] NOPTJ + 32 [Figure] NOPTT + 20 [Figure] NOPTIX
+ 8 [Figure] NSLAS
+ 120 [Figure] NJTBL
+ 480 [Figure] NCSET
+ 84 [Figure] NSPDT
+ 72 [Figure] (NSCLF + NCASE + NCSSP[Figure] 2)
+ (144 + 20 + 32) [Figure] NFCSP + (300 + 136) [Figure] NRTNL
+ 260 [Figure] (NEXRTN + NCLASS + NJAR) + 32 [Figure] NPVOW
+ 140 [Figure] NPLGL + 172 [Figure] NPPRL
+ (260 + 257 + 257) [Figure] NPLGL
+ 32 [Figure] NCUD
+ 512 [Figure] NDTBL
+ 40 [Figure] NDTBL
+ 128 [Figure] NDCLM
+ 182
+ 376
+ 1,384
+ 356 [Figure] NVCLM
+ 24 [Figure] NVCSC

Formula for HiRDB in 64-bit mode

LPTREE (bytes) =
496 [Figure] NQRY
+ 24 [Figure] (2 [Figure] NQRY + NSBQ + NSTOP)
+ 24 [Figure] (2 [Figure] NQRY + NSLST + NSLAS)
+ 24 [Figure] (4 [Figure] NTBL + 4 [Figure] NJTBL + 5 [Figure] NDTBL + NDCLM)
+ 24 [Figure] (NWHRC + NGPHV)
+ 24 [Figure] (NVLCM + NEXCM + NNLCM
+ 3 [Figure] NRANG + 3 [Figure] NLKCM + 3 [Figure] NSMCM + NETCM)
+ 24 [Figure] NRVCL
+ 24 [Figure] NTVCL
+ 24 [Figure] NLGEX
+ 24 [Figure] (NCNST + NSREG + NPRCS)
+ 24 [Figure] 4 [Figure] NCSCV
+ 24 [Figure] (NARTH + NCNCT)
+ 24 [Figure] NSFNC
+ 24 [Figure] NLBLD
+ 24 [Figure] (3 [Figure] NCLM)
+ 24 [Figure] (NCASE + NWHEN)
+ 24 [Figure] 2 [Figure] (NSCLF + 2 [Figure] NCSSP)
+ 24 [Figure] (2 [Figure] NPOS + 3 [Figure] NDTV)
+ 24 [Figure] (NEXTR + 2 [Figure] NSBST + 3 [Figure] NVALU + NBTEX + NCAST)
+ 24 [Figure] (4 [Figure] NFCSP + NFPRM)
+ 24 [Figure] (3 [Figure] NATTL + NATNM)
+ 56 [Figure] NOPTL + 40 [Figure] NOPTJ + 64 [Figure] NOPTT + 40 [Figure] NOPTIX
+ 16 [Figure] NSLAS
+ 208 [Figure] NJTBL
+ 480 [Figure] NCSET
+ 96 [Figure] NSPDT
+ 96 [Figure] (NSCLF + NCASE + NCSSP[Figure] 2)
+ (176 + 24 + 40) [Figure] NFCSP + (328 + 144) [Figure] NRTNL
+ 260 [Figure] (NEXRTN + NCLASS + NJAR) + 32 [Figure] NPVOW
+ 168 [Figure] NPLGL + 184 [Figure] NPPRL
+ (260 + 264 + 257) [Figure] NPLGL
+ 40 [Figure] NCUD
+ 768 [Figure] NDTBL
+ 48 [Figure] NDTBL
+ 184 [Figure] NDCLM
+ 226
+ 568
+ 1,496
+ 480 [Figure] NVCLM
+ 32 [Figure] NVCSC

(3) Variables used in the formulas

Variable nameExplanation
LCNSTTotal size of the constants resulting from adding the following expressions:#1, #2, #3
  • Total size of constants in the view definition + number of character set conversions in the view definition
  • Total size after conversion of the target constants + number of numeric character conversions in the view definition
  • Total size after conversion of the target constants + number of scalar operations in the view definition
NINCC
  • When LCNST > 11,516 bytes:
    32-bit mode: [Figure](LCNST - 11,516) [Figure] 4,020[Figure]
    64-bit mode: [Figure](LCNST - 11,516) [Figure] 4,024[Figure]
  • When LCNST[Figure] 11,516 bytes:
    0
NINCP
  • When LTREE > 11,516 bytes:
    32-bit mode: [Figure](LTREE - 11,516) [Figure] 4,020[Figure]
    64-bit mode: [Figure](LTREE - 11,516) [Figure] 4,024[Figure]
  • When LCNST[Figure] 11,516 bytes:
    0
NQRYNumber of queries in the view definition
NSBQNumber of subqueries in the view definition
NSTOPNumber of set operations in the view definition
NSLSTTotal selection items for all query specifications in the view definition#4
NWHRCNumber of WHERE clauses in the view definition
NGPHVNumber of GROUP BY clauses in the view definition + Number of HAVING clauses in the view definition
NVLCMNumber of comparison predicates in the view definition + Number of quantified predicates in the view definition
NEXCMNumber of IN predicates in the view definition
NNLCMNumber of NULL predicates in the view definition
NRANGNumber of BETWEEN predicates in the view definition
NLKCMNumber of LIKE and XLIKE predicates in the view definition
NSMCMNumber of SIMILAR predicates in the view definition
NETCMNumber of EXISTS predicates in the view definition
NRVCLNumber of row value constructor elements in the view definition
NTVCLNumber of row value constructor elements on the right side of IN predicates in the view definition
NLGEXNumber of logical operators in the view definition
NCNSTTotal number of constants in the view definition + Total number of pattern characters in the view definition
NSREGTotal number of USER value functions, CURRENT DATE value functions, CURRENT TIME value functions, and CURRENT TIMESTAMP value functions in the view definition
NPRCSNumber of CURRENT TIMESTAMP decimal seconds precision specifications in the view definition
NCSCVNumber of constants subject to character set conversion in the view definition#2
NARTHTotal number of arithmetic, date, and time operations in the view definition
NCNCTNumber of concatenation operations in the view definition
NLBLDNumber of labeled durations in the view definition
NSFNCNumber of set functions in the view definition
NSCLFNumber of system built-in scalar functions in the view definition
NFCSPNumber of function invocations or scalar functions in the view definition
NCLMTotal number of columns in the view definition
NCASENumber of CASE expressions in the view definition + Number of NULLIF CASE abbreviations in the view definition
NWHENNumber of WHEN CASE expressions in the view definition + 1
NCSSPNumber of CAST specifications in the view definition
NPOSNumber of system built-in POSITION scalar functions in the view definition
NDTVNumber of system built-in scalar functions (DATE, TIME, TIMESTAMP, or VARCHAR_FORMAT) in the view definition
NEXTRNumber of system built-in scalar functions (YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND) in the view definition
NSBSTNumber of system built-in SUBSTR scalar functions in the view definition
NVALUNumber of system built-in VALUE scalar functions in the view definition + Number of COALESCE CASE abbreviations in the view definition
NBTEXNumber of system built-in BIT_AND_TEST scalar functions in the view definition
NCASTNumber of system built-in scalar functions (INTEGER or DECIMAL) in the view definition
NOPTLNumber of SQL optimization specifications in the view definition
NOPTJNumber of joined format SQL optimization specifications in the view definition
NOPTTNumber of table SQL optimization specifications in the view definition
NOPTIXNumber of index SQL optimization specifications in the view definition
NSLASNumber of select expression AS clauses in the view definition
NJTBLNumber of joined tables in the view definition
NCSETNumber of types of character sets that can be specified by HiRDB + 1#5
NSPDTNumber of AS data-type specifications in the view definition
NFPRMTotal number of function invocation arguments and system-defined scalar function arguments in the view definition
NRTNLTotal number of functions potentially invoked by function invocations and functions potentially invoked by system-defined scalar functions in the view definition
NEXRTNTotal number of external routines among functions that could potentially be invoked in the view definition
NCLASSTotal number of JAVA function classes in the view definition
NJARTotal number of JAVA function JAVA archives in the view definition
NPVOWTotal number of public view owner names in the view definition
NDTBLNumber of FROM clause derived tables in the view definition + Number of view tables that generate internal derived tables in the view definition
NTBL
  • When the total number of tables in the view definition is 15 or fewer and the total of the number of correlation names in the view definition and the NDTBL value is 15 or less:
    15
  • When the total number of tables in the view definition is 16 or more and the total of the number of correlation names in the view definition and the NDTBL value is 16 or more:
    Max(Total number of tables in the view definition, Number of correlation names in the view definition + NDTBL value)
NDCLMTotal number of columns of FROM clause derived tables in the view definition + Total number of columns of view tables that generate internal derived tables in the view definition
NVCLMNumber of columns derived by the view definition
NVCSCNumber of columns derived by the view definition that specify a character set other than the default character set
NADTLNumber of all abstract data types in the view definition, including those in inheritance relationships#6
NATTLNumber of component specifications in the view definition
NATNMNumber of attribute names of all component specifications in the view definition
NPLGLNumber of functions provided by plug-ins in the view definition
NPPRLNumber of parameters of functions provided by plug-ins in the view definition
NCUDNumber of abstract data type columns in the view definition
#1
Use the following to calculate the sizes of the various types of constants:
Constant typeSize (bytes)Maximum size required by boundary adjustment (bytes)
Integer constant47
Decimal constant[Figure](decimal constant precision + 1) [Figure] 2[Figure][Figure](decimal constant precision + 1) [Figure] 2[Figure] + 7
Floating-point numeric literal815
Character string constant2 + character string constant character count2 + character string constant character count + 3
Hexadecimal constant4 + (hexadecimal constant character string length) [Figure] 24 + (hexadecimal constant character string length) [Figure] 2 + 3
National character string literal2 + national character string literal character count[Figure] 22 + national character string literal character count[Figure] 2 + 3
Mixed character string literal2 + mixed character string literal single-byte character count + mixed character string literal double-byte character count[Figure] 22 + mixed character string literal single-byte character count + mixed character string literal double-byte character count[Figure] 2 + 3
#2
The following constants are subject to character set conversion:
  • Character string constants that are compared to character set items.
  • Character string constants for which a character set was specified by a CAST specification in the post-conversion data type.
#3
The following constants are subject to numeric character conversion:
  • Character string constants that are compared to numeric attribute items.
  • Numeric constants specified in an operand of a concatenation operation.
  • Character string constants specified in the operand of an arithmetic operation.
#4
When * is specified in a selection item, add the total column count for all tables specified in FROM clauses. When table-name.* is specified in a select expression, add the column count of that table.
#5
The only character set that can be specified in HiRDB is EBCDIK.
#6
For details about the inheritance relationships of abstract data types provided by plug-ins, see the applicable manuals for the specific plug-ins.