6.12.3 Determining the size of the user logs that are output during execution of a definition SQL statement (variable max_user_log)
This subsection explains how to determine the size of the user logs that are output during execution of any of the following definition SQL statements:
-
CREATE TABLE statement (base table definition)
-
CREATE INDEX statement (index definition)
-
CHANGE OPTION CHUNK of the ALTER TABLE statement (change to the maximum number of chunks)
-
ALTER TABLE statement (when executed to change an archivable multi-chunk table to a regular multi-chunk table)
-
DROP USER statement (HADB user deletion)
-
DROP SCHEMA statement (schema deletion)
-
DROP TABLE statement (base table deletion)
-
DROP INDEX statement (index deletion)
-
SCHEMA of the REVOKE statement (revoking schema operation privileges)
For the definition SQL statements other than the preceding ones, there is no need to estimate the size of user log files because user log data larger than the user log file capacity is not output.
- Organization of this subsection
(1) Determining the size of the user logs that are output during execution of the CREATE TABLE statement
Use the following formula to determine the size of the user logs that are output during execution of the CREATE TABLE statement (variable CRTTBLLOG).
- Formula (bytes)
-
![[Figure]](GRAPHICS/F0000590.GIF)
- Explanation of variables
-
dbarea_file_num: Number of DB area files in the data DB area for storing the table to be defined
page_size: Page size in the data DB area for storing the table to be defined (bytes)
(2) Determining the size of the user logs that are output during execution of the CREATE INDEX statement
Use the following formula to determine the size of the user logs that are output during execution of the CREATE INDEX statement (variable CRTIDXLOG).
- Formula (bytes)
-
![[Figure]](GRAPHICS/F0000591.GIF)
- Explanation of variables
-
dbarea_file_num: Number of DB area files in the data DB area for storing the indexes to be defined
page_size: Page size in the data DB area for storing the indexes to be defined (bytes)
(3) Determining the size of the user logs that are output during execution of CHANGE OPTION CHUNK of the ALTER TABLE statement
Determine the size of the user logs output during execution of CHANGE OPTION CHUNK of the ALTER TABLE statement based on the explanation in (7) Determining the size of the user logs that are output during the execution of the DROP TABLE statement. When doing so, assume 0 for the variable SGDATA.
If indexes are defined for the table to be processed, determine the size of the user logs for each defined index based on the explanation in (8) Determining the size of the user logs that are output during the execution of the DROP INDEX statement. When doing so, assume 0 for the variable SGIDX.
The sum total of these determined values becomes the size of the user logs that are output during execution of CHANGE OPTION CHUNK of the ALTER TABLE statement.
(4) Determining the size of user log data output when the ALTER TABLE statement is executed (when an archivable multi-chunk table is changed to a regular multi-chunk table)
When the ALTER TABLE statement is used to change an archivable multi-chunk table to a regular multi-chunk table, the location table and the index defined for the location table are deleted. Therefore, you must determine the size of user log data that is output when the location table and the index defined for the location table are deleted.
Note that the location table and the index defined for the location table are stored in the data DB area that stores the target archivable multi-chunk table.
Use the following formula to determine the value of the variable ALTTBLLOG as the size of user log data that is output when the ALTER TABLE statement is executed to change an archivable multi-chunk table to a regular multi-chunk table.
Formula (bytes)
|
ALTTBLLOG = DRPLTBLLOG + DRPLIDXLOG |
Explanation of variables
- DRPLTBLLOG
-
Location table deletion log
Use the following formula to determine this value.
Formula (bytes)
![[Figure]](GRAPHICS/F0000592.GIF)
- dbarea_file_num
-
Number of data DB area files for the data DB area that stores the location table to be deleted
- SGDATA
-
Number of segments of the data DB area that stores the location table to be deleted
- SEGBF
-
Segment block factor of the data DB area that stores the location table to be deleted
- page_size
-
Page size of the data DB area that stores the location table to be deleted (bytes)
- usrlog_file_num
-
Use the following formula to determine this value.
![[Figure]](GRAPHICS/F0000593.GIF)
- DRPLIDXLOG
-
Location-table index deletion log
Use the following formula to determine this value.
Formula (bytes)
![[Figure]](GRAPHICS/F0000594.GIF)
- dbarea_file_num
-
Number of data DB area files for the data DB area that stores the index defined for the location table to be deleted
- SGIDX
-
Number of segments of the data DB area that stores the index defined for the location table to be deleted
- SEGBF
-
Segment block factor of the data DB area that stores the index defined for the location table to be deleted
- page_size
-
Page size of the data DB area that stores the index defined for the location table to be deleted (bytes)
- usrlog_file_num
-
Use the following formula to determine this value.
![[Figure]](GRAPHICS/F0000595.GIF)
For details about the number of segments in a data DB area and the segment block factors, see (2) Explanation of variables in 5.8.1 Determining the total number of pages in the data DB area.
For details about the number of user log files required when the ALTER TABLE statement is executed to change an archivable multi-chunk table to a regular multi-chunk table, see (1) SQL statements for which the number of user log files needs to be estimated in 6.12.15 Determining the number of user log files.
(5) Determining the size of the user logs that are output during the execution of the DROP USER statement
If there are schemas owned by HADB users who are subject to deletion, determine the size of the user logs for the schemas to be deleted based on the explanation in (6) Determining the size of the user logs that are output during the execution of the DROP SCHEMA statement.
(6) Determining the size of the user logs that are output during the execution of the DROP SCHEMA statement
If tables are defined in the schema to be deleted, determine the user log size for each of the tables to be deleted based on the explanation in (7) Determining the size of the user logs that are output during the execution of the DROP TABLE statement. Then, determine their sum total value.
If no table is defined in the schema to be deleted, there is no need to estimate the size of user log files because user logs are not output if they exceed the user log file capacity.
(7) Determining the size of the user logs that are output during the execution of the DROP TABLE statement
Use the following formula to determine the size of the user logs that are output during execution of the DROP TABLE statement (variable DRPTBLLOG).
Formula (bytes)
|
|
![[Figure]](GRAPHICS/F0000596.GIF)
Explanation of variables
- dbarea_file_num
-
Number of DB area files in data DB area that stores the table to be deleted
- SGDATA
-
Number of segments in the data DB area that stores the table to be deleted
- SEGBF
-
Segment block factor of the data DB area that stores the table to be deleted
- page_size
-
Page size of the data DB area that stores the table to be deleted (bytes)
- usrlog_file_num
-
Use the following formula to determine its value.
![[Figure]](GRAPHICS/F0000597.GIF)
For details about the number of segments in a data DB area and the segment block factors, see (2) Explanation of variables in 5.8.1 Determining the total number of pages in the data DB area.
For details about the number of user log files required during execution of the DROP TABLE statement, see (1) SQL statements for which the number of user log files needs to be estimated in 6.12.15 Determining the number of user log files.
- Important
-
Add the value determined in (8) Determining the size of the user logs that are output during the execution of the DROP INDEX statement if an index is defined for the table to be deleted.
Add the value determined in (4) Determining the size of user log data output when the ALTER TABLE statement is executed (when an archivable multi-chunk table is changed to a regular multi-chunk table) if the table to be deleted is an archivable multi-chunk table.
(8) Determining the size of the user logs that are output during the execution of the DROP INDEX statement
Use the following formula to determine the size of the user logs that are output during execution of the DROP INDEX statement (variable DRPIDXLOG).
Formula (bytes)
|
|
![[Figure]](GRAPHICS/F0000598.GIF)
Explanation of variables
- dbarea_file_num
-
Number of DB area files in data DB area that stores the index to be deleted
- SGIDX
-
Number of segments in the data DB area that stores the index to be deleted
- SEGBF
-
Segment block factor of the data DB area that stores the index to be deleted
- page_size
-
Page size of the data DB area that stores the index to be deleted (bytes)
- usrlog_file_num
-
Use the following formula to determine its value.
![[Figure]](GRAPHICS/F0000599.GIF)
For details about the number of segments in a data DB area and the segment block factors, see (2) Explanation of variables in 5.8.1 Determining the total number of pages in the data DB area.
For details about the number of user log files required during execution of the DROP INDEX statement, see (1) SQL statements for which the number of user log files needs to be estimated in 6.12.15 Determining the number of user log files.
(9) Determining the size of the user logs that are output during execution of SCHEMA in the REVOKE statement
Determine the size of the user logs output during execution of SCHEMA of the REVOKE statement based on the explanation in (6) Determining the size of the user logs that are output during the execution of the DROP SCHEMA statement.