5.12.1 Estimating the sizes of system tables

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

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

To perform operation under the preceding conditions, substitute 186 for the variable GETCOST_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.

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

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

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.

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

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

Specify the number of rows in the STATUS_CHUNKS table.

Use the following formula to determine the value.

Formula

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

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

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.

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

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

Specify the number of synonym dictionaries that will be created.

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.

#

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.