5.12.1 Estimating the sizes of system tables
The following describes how to estimate the sizes of the following system tables:
-
STATUS_TABLES table
Determine the value as explained in (1) Estimating the size of the STATUS_TABLES table.
-
STATUS_COLUMNS table
Determine the value as explained in (2) Estimating the size of the STATUS_COLUMNS table.
-
STATUS_INDEXES table
Determine the value as explained in (3) Estimating the size of the STATUS_INDEXES table.
-
STATUS_CHUNKS table
Determine the value as explained in (4) Estimating the size of the STATUS_CHUNKS table.
-
STATUS_SYNONYM_DICTIONARIES table
Determine the value as explained in (5) Estimating the size of the STATUS_SYNONYM_DICTIONARIES table.
- 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)
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
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)
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)
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)
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
- 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.
(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)
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.