Hitachi

Hitachi Advanced Database Setup and Operation Guide


5.12.1 Estimating the sizes of system tables

The following describes how to estimate the sizes of the following system tables:

Organization of this subsection

(1) Estimating the size of the STATUS_TABLES table

The following shows the formula to calculate the size of the STATUS_TABLES table (the value of STBLTABLESSIZE).

If you do not execute the adbgetcst command, substitute 0.

Formula (kilobytes)

[Figure]

Explanation of the variables

COSTTBL_NUM

Specify the number of tables in which to collect cost information.

GETCOST_NUM

Specify the number of times the adbgetcst command is executed per table.

Estimate how many times the adbgetcst command will be executed per execution of the adbreorgsystemdata command.

For details about the interval for executing the adbreorgsystemdata command, see 11.17.2 Timing for reorganizing a system table.

The following shows an estimation example, which assumes the following operational conditions.

▪ Operational conditions

  • Cost information is collected once a day.

  • The system tables (base tables) are reorganized once every six months.

▪ How to obtain GETCOST_NUM based on the conditions

[Figure]

To perform operation under the preceding conditions, substitute 186 for the variable GETCOST_NUM.

(2) Estimating the size of the STATUS_COLUMNS table

The following shows the formula to calculate the size of the STATUS_COLUMNS table (the value of STBLCOLUMNSSIZE).

If you do not execute the adbgetcst command, substitute 0.

Formula (kilobytes)

[Figure]

Explanation of the variables

COSTCOL_NUM

Specify the total number of table columns in which to collect cost information.

For example, if you collect cost information in 10 columns of table A and in 20 columns of table B, the value of this variable is 30.

GETCOST_NUM

For details, see the description of the variable GETCOST_NUM in (1) Estimating the size of the STATUS_TABLES table.

ALTERCOL_NUM

Specify the number of times a column is renamed by using the ALTER TABLE statement.

(3) Estimating the size of the STATUS_INDEXES table

The following shows the formula to calculate the size of the STATUS_INDEXES table (the value of STBLINDEXESSIZE).

If you do not execute the adbgetcst command, substitute 0.

Formula (kilobytes)

[Figure]

Explanation of the variables

COSTIDX_NUM

Specify the total number of indexes that are defined for the tables in which to collect cost information.

For example, if you collect cost information in table A with two indexes defined and in table B with one index defined, the value of this variable is 3.

GETCOST_NUM

For details, see the description of the variable GETCOST_NUM in (1) Estimating the size of the STATUS_TABLES table.

(4) Estimating the size of the STATUS_CHUNKS table

The following shows the formula to calculate the size of the STATUS_CHUNKS table (the value of STBLCHUNKSSIZE).

If you do not define multi-chunk tables, substitute 0.

Formula (kilobytes)

[Figure]

Explanation of the variables

CHUNK_COMMENT_SIZE

Specify the average data length of each chunk comment (kilobytes).

STBLCHUNKS_ROWNUM

Specify the number of rows in the STATUS_CHUNKS table.

Use the following formula to determine the value.

Formula

[Figure]

TOTAL_CHUNKNUM

Specify the total number of chunks in the multi-chunk tables that are used for operation.

For example, if you are using multi-chunk table A that has 700 chunks and multi-chunk table B that has 600 chunks, the value of this variable is 1,300.

STBLCHUNK_INVALID_ROWNUM

Specify the number of invalid-data rows that arise in the STATUS_CHUNKS table.

Estimate how many invalid-data rows will arise in the STATUS_CHUNKS table in the time between the execution of the adbreorgsystemdata command and the re-execution of the command. The following shows the formula:

Formula

[Figure]

reorg_period

Specify the interval for executing the adbreorgsystemdata command (months).

For example, if the adbreorgsystemdata command is executed once every six months, substitute 6 for this variable.

For details about the interval for executing the adbreorgsystemdata command, see 11.17.2 Timing for reorganizing a system table.

backgroundimport_table_num

Specify the number of multi-chunk tables subject to background import.

For example, if background import is performed for five multi-chunk tables every day, substitute 5.

INVALID_ROW_NUM

Specify the number of invalid-data rows that will arise per month for one multi-chunk table.

First, estimate the total number of SQL statements and commands that will be executed for a multi-chunk table. Then, obtain the number of invalid-data rows that will arise from the following table.

Table 5‒21: Number of invalid-data rows that arise in the STATUS_CHUNKS table when an SQL statement or command is executed

No.

SQL statement or command executed for a multi-chunk table

Number of invalid-data rows that arise in the STATUS_CHUNKS table#1

1

Execution of a definition SQL statement

(when executed to delete multi-chunk tables)

Total number of chunks in the deletion-target tables

2

Execution of a PURGE CHUNK statement

Number of deleted chunks

3

Execution of a TRUNCATE TABLE statement

Number of chunks in the processing-target tables

4

Execution of a adbimport command

(when executed in creation mode)

Number of chunks in the processing-target tables

5

Execution of a adbimport command

(when executed for background import)

1

6

Execution of a adbidxrebuild command

(when background import started by the adbimport command is interrupted)

1

7

Execution of a adbmergechunk command

Number of chunks to be merged × 2#2

8

Execution of a adbchgchunkcomment command

1

9

Execution of a adbchgchunkstatus command

Number of chunks whose status is changed

10

Execution of a adbarchivechunk command

Number of archived chunks

11

Execution of a adbunarchivechunk command

Number of unarchived chunks

#1

The value in this column indicates the number of invalid-data rows that will arise when the SQL statement or command executed for a multi-chunk table in the left column is executed once. If the statement or command is executed multiple times, multiply the value by the number of times the statement or command is executed.

#2

Invalid row data does not arise if merge-source chunks cannot be deleted for the following reasons:

• The adbmergechunk command is interrupted after the merge-target chunk is ready but before the merge-source chunks are deleted

• A merge-chunk target table is already being referenced when the adbmergechunk command is executed with NOWAIT specified in the --purge-chunk option

The following shows the operational conditions of an example operation, and describes how to obtain the value of the variable STBLCHUNK_INVALID_ROWNUM when the operation is executed.

▪ Operational conditions

  • The system tables (base tables) are reorganized once every six months.

  • Background import is performed for 10 multi-chunk tables.

  • Background import is performed once every five minutes (288 times per day).

  • The 288 chunks created by background import are merged as a daily chunk.

  • 31 daily chunks are merged as a monthly chunk at each month end (assuming 31 days per month).

  • Each monthly chunk is deleted at the end of a month after two years since creation.

▪ To obtain STBLCHUNK_INVALID_ROWNUM under the indicated conditions

For the variable reorg_period, which indicates the interval for executing the adbreorgsystemdata command, substitute 6.

For the variable backgroundimport_table_num, which indicates the number of multi-chunk tables subject to background import, substitute 10.

For the variable INVALID_ROW_NUM, which indicates the number of invalid-data rows that arise per month for one multi-chunk table, substitute the value obtained from the following formula:

Formula to obtain INVALID_ROW_NUM

[Figure]

For the variable INVALID_ROW_NUM, substitute 26,847, which is obtained from the preceding formula.

Obtain the value of STBLCHUNK_INVALID_ROWNUM by substituting the obtained values for the variables on the right side. In the preceding example, the number of invalid-data rows that will arise in the STATUS_CHUNKS table over a six-month period is estimated to be 1,610,820.

(5) Estimating the size of the STATUS_SYNONYM_DICTIONARIES table

The following shows the formula to calculate the size of the STATUS_SYNONYM_DICTIONARIES table (the value of STBLSYNONYMDICSIZE).

If you do not define any synonym dictionaries, substitute 0.

Formula (kilobytes)

[Figure]

Explanation of the variables

BINARY_PATH_SIZE

Specify the data length of the path name of the directory that stores synonym dictionary files (kilobytes).

SYNONYMDIC_COMMENT_SIZE

Specify the average data length of each synonym dictionary comment (kilobytes).

SYNONYMDIC_NUM

Specify the number of synonym dictionaries that will be created.

SYNONYMDIC_INVALID_ROWNUM

Specify the number of invalid-data rows that arise in the STATUS_SYNONYM_DICTIONARIES table.

Estimate how many invalid-data rows will arise in the STATUS_SYNONYM_DICTIONARIES table per execution of the adbreorgsystemdata command. For details about the interval for executing the adbreorgsystemdata command, see 11.17.2 Timing for reorganizing a system table.

Obtain the number of invalid-data rows that will arise in the STATUS_SYNONYM_DICTIONARIES table from the following table. At that time, also estimate how many times the command is executed.

Table 5‒22: Number of invalid-data rows that arise in the STATUS_SYNONYM_DICTIONARIES table when a command is executed

No.

Command to be executed

Specify the number of invalid-data rows that arise in the STATUS_SYNONYM_DICTIONARIES table#

1

adbsyndict command

(when executed to update synonym dictionaries)

Number of updated synonym dictionaries

2

adbsyndict command

(when executed to delete synonym dictionaries)

Number of deleted synonym dictionaries

#

The value in this column indicates the number of invalid-data rows that will arise when the command in the left column is executed once. If the command is executed multiple times, multiply the value by the number of times the command is executed.