Appendix C.1 Formulas for determining size of statistics log file (pd_stj_file_size)

The size of the statistics log that is output differs depending on the statistical information. The size of the statistics log for each type of statistical information is described starting in (1) as follows. For the pd_stj_file_size operand, specify a value that is equal to or greater than the combined total of the statistics log sizes determined in (1) and beyond.

Note
Each of these formulas produces a size in bytes; each result in bytes must be converted subsequently to kilobytes.
Organization of this subsection
(1) Statistical information related to system operation (sys)
(2) Statistical information related to UAPs (uap)
(3) Statistical information related to SQL (sql)
(4) Statistical information related to global buffers (buf)
(5) HiRDB file statistical information related to database manipulation (fil)
(6) Statistical information related to deferred write processing (dfw)
(7) Statistical information related to indexes (idx)
(8) Statistical information related to SQL static optimization (sop)
(9) Statistical information related to SQL dynamic optimization (dop)
(10) Statistical information related to SQL object execution (pcd)
(11) Statistical information related to SQL statement statistics (sqh)
(12) Statistics on SQL object transmission (obj)
(13) Statistics on database input and output operations (dio)

(1) Statistical information related to system operation (sys)

This will depend on the options specified in the pdstbegin operand or pdstbegin command.

When neither -a nor -s option is specified
Statistics log size = 2,412 [Figure] ([Figure]a[Figure] b[Figure] + number-times-pdstjsync-command-is-executed-during-statistical-information-output-time) (bytes)
When -a option is specified
Statistics log size = (1,836 [Figure] c + 2,412) [Figure] ([Figure]a[Figure] b[Figure] + number-times-pdstjsync-command-is-executed-during-statistical-information-output-time) (bytes)
When -s option is specified
Statistics log size = (1,836 [Figure] d + 2,412) [Figure] ([Figure]a[Figure] b[Figure] + number-times-pdstjsync-command-is-executed-during-statistical-information-output-time) (bytes)
a:
Statistical information output period (minutes)
b:
Statistical information output interval (minutes)
Interval specified in the pdstbegin operand or the -m option of the pdstbegin command
c:
Number of servers in unit
System manager is not included.
d:
Number of servers specified in the pdstbegin operand or the -s option of the pdstbegin command

(2) Statistical information related to UAPs (uap)

Formula
Statistics log size = 1,252 [Figure] number-of-UAPS-to-be-executed-during-statistical-information-collection (bytes)

If the total number of UAPs that will actually be executed is known, use the formula above. If the number of UAPs that will be executed during statistical information collection is not known, that number can be determined with the following formula:

For example, if 10 UAPs are executed in 30 minutes and statistical information is collected for a period of 60 minutes, the UAP count will be 10 [Figure] 60 [Figure] 30 = 20.

(3) Statistical information related to SQL (sql)

Formula
Statistics log size = 728 [Figure] number-of-SQLs-to-be-executed-during-statistical-information-collection# (bytes)

If the total number of SQLs that will actually be executed is known, use the formula above. If the number of SQLs that will be executed during statistical information collection is not known, that number can be determined with the following formula:

Number of SQLs# = average number of SQL statements executed by UAP[Figure] (number of UAPs to be executed per unit time[Figure] statistical information collection period [Figure]unit time)

#: If a stored procedure or stored function will be executed, the SQL statements in the procedure or function must also be counted.

(4) Statistical information related to global buffers (buf)

Formula
Statistics log size = 412 [Figure] a[Figure] b (bytes)
a:
Number specified by the pdbuffer operand
b:
Number of times synchronization point dump is collected during statistical information collection
The number of times a synchronization point dump is collected during the statistical information collection period can be determined with the following formula:
[Figure]size-of-system-log-output-during-statistical-information-collection-period
[Figure](total-of-pd_log_max_data_size-operand-values-in-individual-server-definitions[Figure] 3)
[Figure]total-of-pd_log_sdinterval-operand-specification-values-in-individual-server-definitions[Figure]
For details about the system log size, see the HiRDB Version 9 Installation and Design Guide.

(5) HiRDB file statistical information related to database manipulation (fil)

Formula
Statistics log size = 428 [Figure] a[Figure] b (bytes)
a:
Number of HiRDB files specified in the control statement of the database initialization utility
b:
Number of times synchronization point dump is collected during statistical information collection
The number of times a synchronization point dump is collected during the statistical information collection period can be determined with the following formula:
[Figure]size-of-system-log-size-output-during-statistical-information-collection-period
[Figure](total-of-pd_log_max_data_size-operand-specification-values-in-individual-server-definitions[Figure] 3)
[Figure]total-of-pd_log_sdinterval-operand-specification-values-in-individual-server-definitions[Figure]
For details about the system log size, see the HiRDB Version 9 Installation and Design Guide.

(6) Statistical information related to deferred write processing (dfw)

Formula
Statistics log size = 384 [Figure] a[Figure] 2 + (384 [Figure] b[Figure] c) [Figure] (d[Figure] e[Figure] f) (bytes)
a:
Number of times synchronization point dump is collected during statistical information collection
The number of times a synchronization point dump is collected during the statistical information collection period can be determined with the following formula:
[Figure]size-of-system-log-size-output-during-statistical-information-collection-period
[Figure](total-of-pd_log_max_data_size-operand-specification-values-in-individual-server-definitions[Figure] 3)
[Figure]total-of-pd_log_sdinterval-operand-specification-values-in-individual-server-definitions[Figure]
For details about the system log size, see the HiRDB Version 9 Installation and Design Guide.
b:
Average number of pages updated per transaction
c:
Number of transactions to be executed within statistical information collection period
d:
Total buffer sectors count specified by the -n option of the pdbuffer operand
e:
Database-updating transactions as a percentage of all transactions
f:
Updated pages output ratio during deferred write, as specified by the pdbuffer operand

(7) Statistical information related to indexes (idx)

Formula
Statistics log size = 3,768 [Figure][Figure]a[Figure] 128[Figure][Figure] b (bytes)
a:
Total number of partitioned indexes
The total number of partitioned indexes is the sum of the numbers of RDAREA names specified by IN RDAREA of individual CREATE INDEX statements of definition SQLs.
b:
Number of times synchronization point dump is collected during statistical information collection
The number of times a synchronization point dump is collected during the statistical information collection period can be determined with the following formula:
[Figure]size-of-system-log-output-during-statistical-information-collection-period
[Figure](total-of-pd_log_max_data_size-operand-specification-values-in-individual-server-definitions[Figure] 3)
[Figure]total-of-pd_log_sdinterval-operand-specification-values-in-individual-server-definitions[Figure]
For details about the system log size, see the HiRDB Version 9 Installation and Design Guide.

(8) Statistical information related to SQL static optimization (sop)

Formula
Statistics log size = 92 [Figure] a (bytes)
a:
Number of SQL objects to be created in the SQL statements to be executed during statistical information collection. This applies to the following SQL statements: PREPARE statement, EXECUTE IMMEDIATE statement, and static SQL statements.

If it is possible to determine the actual number of SQL objects to be created, this formula can be used to make that determination. If the number of SQL objects to be created during statistical information collection is not known, the number of SQL objects to be created can be determined using the following formula:

Number of SQL objects to be created = average-number-of-SQL-statements-to-be-executed-in-UAP [Figure]SQL-object-cache-miss-rate [Figure](number-of-UAP-executions-per-unit-of-time [Figure]statistical-information-collection-time [Figure]unit-of-time)

The SQL object cache miss rate can be determined from the statistical information related to the system.

(9) Statistical information related to SQL dynamic optimization (dop)

Formula
Statistics log size =
[Figure]
(bytes)
a: Number of SQL statements to be executed
b: Number of tables inside the c-th SQL statement

(10) Statistical information related to SQL object execution (pcd)

[Figure]HiRDB single server configuration

Formula
Statistics log size =388 [Figure] {(a + b + c + d + e + f) + g + h}
(bytes)

[Figure]HiRDB parallel server configuration

Formula
Statistics log size =388 [Figure] {(a + b + c + d + e + f) [Figure] i + g + h}
(bytes)
a: Number of OPEN statements to be executed
b: Number of CLOSE statements to be executed
c: Number of INSERT statements to be executed
d: Number of DELETE statements to be executed
e: Number of ASSIGN LIST statements to be executed
f: Number of UPDATE statements to be executed
g: Number of FETCH statements to be executed
h: Number of DESCRIBE statements to be executed
i: Value obtained from the following formula:
[Figure]
n: Number of SQL statements to be executed
Tm: Number of tables inside the m-th SQL statement

(11) Statistical information related to SQL statement statistics (sqh)

Formula
Statistics log size = (728 + a) [Figure] b (bytes)
a: Average SQL length of SQL statements
b: Number of SQLs to be executed during statistical information collection

If the total number of SQLs that will actually be executed is known, use the computation formula above. If the number of SQLs that will be executed during statistical information collection is not known, that number can be determined with the following formula:

Number of SQLs# = average-number-of-SQL-statements-executed-per-UAP[Figure] (number-of-UAPs-to-be-executed-per-unit-time[Figure] (statistical-information-collection-period[Figure] unit-time)

#: If a stored procedure or stored function will be executed, the SQL statements in the procedure or function must also be counted.

(12) Statistics on SQL object transmission (obj)

Formula
Statistics log size = 300 [Figure] number-of-SQL-statements-to-be-executed-during-statistical-information-collection (bytes)

If it is possible to determine the cumulative number of SQL statements to be executed, this formula can be used to make that determination. If the number of SQL statements to be executed during statistical information collection is not known, the number of SQL statements can be determined using the following formula:

Number of SQL statements# = average-number-of-SQL-statements-to-be-executed-in-UAP [Figure](number-of-UAP-executions-per-unit-of-time [Figure]statistical-information-collection-time [Figure]unit-of-time)

#: If a stored procedure or stored function is to be executed, the SQL statements described in the procedure or function must also be included in the count.

(13) Statistics on database input and output operations (dio)

This differs depending on the option specified for the pdstbegin operand or pdstbegin command.

[Figure]If -D everyfile is not specified
Formula
statistics-log-size = 7464 [Figure] ([Figure]a[Figure] b[Figure] + number-of-times-the-pdstjsync-command-is-executed-during-statistical-information-output-time) (bytes)
a: Statistics log output time (seconds)
time-when-output-of-statistical-information-ends - time-when-output-of-statistical-information-starts
b: Interval at which statistical information is output (seconds)
Interval specified for the pdstbegin operand or the -I option of the pdstbegin command.
[Figure]If -D everyfile is specified
Formula
statistics-log-size =
[Figure]
(bytes)
a: Statistics log output time (seconds)
time-when-output-of-statistical-information-ends - time-when-output-of-statistical-information-starts
b: Interval at which statistical information is output (seconds)
Interval specified for the pdstbegin operand or the -I option of the pdstbegin command.
c: [Figure]a[Figure] b[Figure] + number-of-times-the-pdstjsync-command-is-executed-during-statistical-information-output-time (number of times statistics log is obtained)
d: Number of HiRDB files that constitute an RDAREA#2 where an input or output occurs between the triggers for outputting each statistical information item#1
#1
  • When the interval at which statistical information is output elapses
  • When the pdstjsync command is executed during statistical information output time
#2
RDAREA managed by the server whose statistical information is to be output if you specify the pdstbegin operand or the -s option for the pdstbegin command.