6.12.7 Determining the size of the user logs that are output during database updating (variable max_user_log)

(1) Determining the base row user log size

The following table shows the base row user log size (variable BRWLOG).

To Page Top

(2) Determining the branch row user log size

The following table shows the formulas for the branch row user log size (variable VRWLOG).

Explanation of variables

var_num

Number of columns managed as branch rows (columns)

Determine the number of columns as described in the following.

If the BRANCH ALL table option is specified

• Number of VARCHAR and VARBINARY columns

If the BRANCH ALL table option is not specified

Sum total of VARCHAR and VARBINARY columns that satisfy any of the following conditions:

• Column for which YES is specified in the BRANCH column definition

• Column whose definition length is 256 bytes or longer and for which AUTO is specified in the BRANCH column definition

• Column whose definition length is 256 bytes or longer and for which the BRANCH column definition is omitted

var_num_upd

Number of columns updated out of those columns that are managed as branch rows (columns)

var_size

Average size of the data in the columns that are managed as branch rows (bytes)

Determine the value according to Table 5‒17: Data length of columns that become branch rows in (2) Determining the number of pages for branch rows (variable VP(i)) under 5.8.2 Determining the number of pages for storing each type of row.

page_size

Page size of data DB area (bytes)

To Page Top

(3) Determining the size of the B-tree index user log

Use the following formula to determine the size of the B-tree index user log (variable IDXSLOG).

Formula

Explanation of variables

idx_num

Number of B-tree indexes defined for the column to be updated

IDXLOG₍i₎

Size of the index user log related to the i-th B-tree index defined for the table to be updated (bytes)

Determine IDXLOG₍i₎ from the following table:

No.	Update type	Number of duplicate update-target keys before updating	IDXLOG(i) value
1	INSERT statement	0 (new key)
2		1 to 254	Same as for row 1
3		255
4		256 or more	Same as for row 1
5	DELETE statement
6	UPDATE statement

#

Same log size as if the updated data is being inserted into the row that is the target of the update.

AFTERLOG₍i₎

For each update target row, updated logical log size for the updated data (bytes)

BEFORELOG₍i₎

For each update target row, updated logical log size for the original data (bytes)

Use the following formula to determine the sizes of AFTERLOG₍i₎ and BEFORELOG₍i₎.

Formula

idx_col_num

Number of columns indexed by the targeted index (columns)

KEYSZDB

Database storage length of the targeted index key (bytes)

The updated index key is used for AFTERLOG₍i₎ and original index key is used for BEFORELOG₍i₎.

SPLITLOG₍i₎

User log size when an index page split occurs (bytes)

Use the following formula to determine the user log size when an index page split occurs in the target index.

Formula (when the number of duplicate index keys in the update-target rows is 254 or fewer)

Formula (when the number of duplicate index keys in the update-target rows is 256 or more)

page_size

Page size of the data DB area in which the target B-tree index is defined (bytes)

idx_level

Number of levels in the target B-tree index (steps)

To Page Top

(4) Determining the size of the text index user log

Use the following formula to determine the size of the text index user log (variable TIXSLOG).

Formula

Explanation of variables

tix_num

Number of text indexes defined for the column to be updated

TIXLOG₍i₎

Size of the text index user log related to the i-th text index defined for the table to be updated (bytes)

Determine TIXLOG₍i₎ from the following table:

Table 6‒24: TIXLOG(i) value

No.	Update type	TIXLOG(i) value
1	INSERT statement	SPLITLOG(i)
2	UPDATE statement	SPLITLOG(i)

SPLITLOG₍i₎

Log size in the event of a text index page split

Use the following formula to determine its value.

Formula (bytes)

page_size

Page size of the data DB area in which the target text index is defined (bytes)

To Page Top

(5) Determining the log size of the invalid row information pages for column store tables

Use the following formula to determine the value of variable COLUMN_DELLOG.

Note that if the processing-target table is a row store table, assume that this value is 0 during estimation.

Formula (bytes)

COLUMN_DELLOG = 332 + 2 × page_size

Explanation of the variables

page_size

Page size of the DB area in which the column store table is defined (bytes)

To Page Top

(6) Determining the user log size for allocating segments

If a row or index entry cannot be stored in the active page when an INSERT or UPDATE statement is executed to add a row or index entry, it is allocated to an unused page. Also, when rows that have been stored in column store format are invalidated by executing the UPDATE or DELETE statement, the information indicating that the rows became invalid sometimes cannot be stored on the invalid row information page. In such a case, the information is allocated to an unused page.

If all pages within a segment are being used, a new segment is allocated.

The table below shows the user log size for allocating new segments (variable SGMTLOG).

Based on the row data to be added by the INSERT statement and the row data to be updated by the UPDATE statement, determine the number of segments to be allocated as described in 5.8.1 Determining the total number of pages in the data DB area, 5.8.2 Determining the number of pages for storing each type of row, 5.8.3 Determining the number of storage pages for each B-tree index segment, and 5.8.5 Determining the number of storage pages for each text index segment.

If the processing-target table is a column store table, take into consideration the number of rows to be deleted by using the DELETE statement during estimation.

#

Note that if the deletion-target table is a row store table, assume that this value is 0 during estimation.

To Page Top