Amount of system log information output during table data updating

If you manipulate rows in a table, the system outputs the system log information listed in Table 17-1.

Table 17-1 Types of system log information that are output when table rows are manipulated

Type of system log information	Description
Base row log information	This log information is output when a table's row data is added, deleted, or updated.
Branch row log information	This log information is output when row data is manipulated in columns with the following data types: VARCHAR¹ NVARCHAR² MVARCHAR¹ Repetition columns Abstract data type BINARY type³
Index log information	This log information is output when index keys are added, deleted, or updated. Determine the amount of index log information on the basis of the type of database manipulation (INSERT, DELETE, or UPDATE statement), as shown in Table 17-2.
Event log	This log information is output when HiRDB Datareplicator is used or when row data containing repetition columns is added, deleted, or updated.

¹Branch row log information is output if either one of the following conditions is satisfied:

The no-split option is not specified and the actual data length is 256 bytes or greater.
The no-split option is specified and the actual total length of data per row exceeds the page length.

² Branch row log information is output if either one of the following conditions is satisfied:

The no-split option is not specified and the actual data length is 128 bytes or greater.
The no-split option is specified and the actual total length of data per row exceeds the page length.

³ If the actual total length of data per row exceeds the page length, branch row log information is output.

Table 17-2 Amount of log information depending on type of database manipulation

Type of data manipulation (SQL statement)	Amount of log information
Key addition (INSERT statement)	Amount of addition log information
Key deletion (DELETE statement)	Amount of deletion log information
Key updating (UPDATE statement)	Amount of deletion log information for the key before updating + amount of addition log information for the key after updating

The amount of system log information that is output when a database is updated (INSERT, DELETE, or UPDATE) can be obtained from Formulas 1 and 2 as follows, depending on the type of operation (INSERT, DELETE, or UPDATE).

The amount of system log information that is output during UAP execution that does collect log is 460 bytes, which is based on the fact that segment allocations occur.

Condition	Formula (bytes)
Amount of system log information that is output to add (INSERT) or delete (DELETE) n rows in a table	(a + b + c) n
Amount of system log information that is output to update (UPDATE) n rows in a table	(a¹ + d² + e³) n

a: Amount of base row log information (bytes)

b: Total amount of all branch row log information (bytes)

c: Total amount of all index log information (bytes)

d: Total amount of all branch row log information subject to update processing (bytes)

e: Total amount of all index log information subject to update processing (bytes)

n: Number of rows manipulated

¹ This value is added when the column value to be updated (UPDATE) is in the base row.

² This value is added when the column value to be updated (UPDATE) is in the branch row.

³ This value is added when an index is defined for the column being updated (UPDATE).

Organization of this subsection: (1) Determining the amount of base row log information; (2) Determining the amount of branch row log information; (3) Determining the amount of index log information; (4) Determining the size of event log information

(1) Determining the amount of base row log information

Table 17-3 shows the formulas for determining the amount of base row log information per data item.

Table 17-3 Formulas for determining the amount of base row log information per data item

Type of data manipulation (SQL statement)		Amount of log information output (bytes)
Data addition (INSERT statement)		k + 152
Data deletion (DELETE statement)		k + 152
Data updating (UPDATE statement)	FIX table with f 12	f d_i + i=1 f d_j + 4f + 152 j=1
	Non-FIX table or f > 12	k₁ + k₂ + 160 If LOCK is specified in the pd_nowait_scan_option operand, add: 314 2
	Row interface used	2 k₁ + 160

k: Length of row to be added or deleted

k₁: Length of row before updating

k₂: Length of row after updating

f: Number of columns to be updated

d_i: Data length of column before updating

d_j: Data length of column after updating

Note 1

The values of k, k₁, and k₂ depend on the specification of FIX, as shown as follows.

Table 16-1 List of data lengths
Table 16-2 Data lengths for the variable-length character string type (except abstract data type and repetition columns)
Table 16-3 Data lengths for the variable-length character string type (abstract data type)
Table 16-4 Data lengths for the variable-length character string type (repetition columns)
FIX specified
Total data length of all columns in table + 4.
FIX not specified
Total data length of all columns in table + 6 + 2 total number of columns in table.

Note 2: If HiRDB Datareplicator is being used, then the same amount will be output to the log file for updating 12 or fewer columns of the FIX table as for updating 13 or more columns.

Note 3

If a BLOB column is defined for the table, 9 bytes must be used as the row length shown in Table 17-3 for a BLOB column, and the amount of log information shown in Table 17-4 must be added.

Table 17-4 Formulas for determining the amount of log information per BLOB column data item

Type of data manipulation (SQL statement)	Specification of recovery	Amount of log information output (bytes)
Data addition (INSERT statement)	Not specified or partial specified	1176
	All specified	2348 + p1 + 8340 p2 + (148 + lt) p3
	Not specified	300
Data deletion (DELETE statement)	Not specified or partial specified	1052
	All specified	1052
	Not specified	468
Data updating (UPDATE statement)	Not specified or partial specified	1324
	All specified	2496 + p1 + 8334 p2 + (148 + lt) p3
	Not specified	312
Data concatenation operation (UPDATE statement) Bb7168	Not specified or partial specified	2344
	All specified	8340a + 1600 + d + 8340p4 + (148 + lt2) p3
	Not specified	428
Data concatenation operation (UPDATE statement) Bb>7168	Not specified or partial specified	2772
	All specified	2772 + Ba + 8340p4 + (148 + lt2) p3
	Not specified	428

Bi: BLOB data length (bytes)

Ba: One of the following values:

Bb > 7168: 8192-{(Bb - 7168)- (Bb - 7168) 8192 8192}
Bb7168: 0

Bb: BLOB data length before update (bytes)

Bc: BLOB added data length (bytes)

lt: One of the following values:

Bi > 7168: Bi - 7168- (Bi - 7168) 8192 8192
Bi7168: 0

lt2: One of the following values:

Bc + Bb > 7168: (Bc + Bb - Ba - 7168)- (Bc + Bb - Ba - 7168) 8192 8192
Bc + Bb7168: 0

p1: One of the following values:

Bi > 7168: 7168
Bi7168: Bi

p2: One of the following values:

Bi > 7168: (Bi - 7168) 8192
Bi7168: 0

p3: One of the following values:

lt = 0 or lt2 = 0: 0
lt > 0 or lt2 > 0: 1

p4: One of the following values:

Bc + Bb > 7168: (Bc + Bb - Ba - 7168) 8192
Bc + Bb7168: 0

a: One of the following values:

Bb > 0: 1
Bb = 0: 0

d: One of the following values:

Bc + Bb 7168: Bc + Bb
Bc + Bb > 7168: 7168

(2) Determining the amount of branch row log information

(a) Branch row log information for VARCHAR, NVARCHAR, and MVARCHAR, when the no-split option is not specified

Calculate the amount of branch row log information that will be generated. Table 17-5 shows the formulas for determining the amount of log information per branch row.

Table 17-5 Formulas for determining the amount of log information per branch row (1)

Type of data manipulation (SQL statement)		Amount of log information output (bytes)
Data addition (INSERT statement)		k + 152
Data deletion (DELETE statement)		k + 152
Data updating (UPDATE statement)	Creating a new branch row by update processing	k₂ + 160
	Updating a branch row	k₁ + k₂ + 160
	Deleting a branch row by update processing	k₁ + 160

k: Length of one branch row to be added or deleted

k₁: Length of one branch row before updating

k₂: Length of one branch row after updating

Note: The following formula is used to obtain the row lengths k, k1, and k2:; 8 + MIN (average length of actual data, page length of RDAREA - 48)

(b) Branch row log information for abstract data columns, repetition columns, BINARY columns, and VARCHAR, NVARCHAR, and MVARCHAR when the no-split option is specified

Calculate the amount of branch row log information that will be generated. Table 17-6 shows the formulas for obtaining the amount of log information for one branch row.

If there is more than one table storage RDAREA and the page length varies from one RDAREA to another, obtain the amount of branch row log information for each RDAREA with the same page length, then use their sum as the amount of branch row log information.

Table 17-6 Formulas for determining the amount of log information per branch row (2)

Type of data manipulation (SQL statement)	Amount of log information output (bytes)
Data addition (INSERT statement)	SPN (b + 152)
Data deletion (DELETE statement)	SPN (b + 152)
Data updating (UPDATE statement)	SPN (b + 160)
Data concatenation operation (UPDATE statement)^*	(b + 160) + (SPN - 1)(b + 152) If the pd_rpl_hdepath operand is specified, add: 160

^* For BINARY type columns only.

b: Page length of an RDAREA

SPN: The following shows how to obtain this value:

If there is a branch row (for the branch condition, see ⁵in Table 16-1 List of data lengths), obtain the value of SPN for all columns that constitute the table for the INSERT and DELETE statements and the value for the columns subject to updating for the UPDATE statement. However, for concatenation operations on BINARY columns, calculate di as the length of data to be added.

SPN = SPN1 + SPN2 + SPN3 + SPN4
[Figure]

Other than BINARY columns
SPN1 =
f
[Figure]

Value of branch d_i [Figure]

(b - 61)

SF
i=1
SPN1 =
[Figure]

BINARY columns
[Figure]

Number of branch pages due to INSERT
SPN2 =
f
{ [Figure]

Value of branch d_i [Figure]

(b - 59)

+ 1}

SF
i=1

Number of branch pages due to pdload or pdrorg
When value of branch d_i > (b - 2853) [Figure]

255
SPN3 =
f
[Figure]

{

(branch d_i + 11) } [Figure]

(b - 48)

SF
i=1
When value of branch d_i [Figure]

(b - 2853) [Figure]

255
SPN4 = SF

f: Number of branch columns

SF: 1.3: Use a value greater than 1.3 in the following cases:

A large number of columns with the abstract data type will be updated.
A large number of repetition columns will be updated in such a manner that the elements' data lengths increase or the number of elements increases.
A large number of columns with VARCHAR, NVARCHAR, MVARCHAR, or BINARY data type will be updated in such a manner that the elements' data lengths increase
A large number of columns with BINARY data type will be updated in such a manner that the elements' data lengths increase
A large number of columns with data suppressed will be updated in such a manner that their data lengths increase.
A large number of columns other than the above will be updated in such a manner that the NULL values are changed to non-NULL values.

(3) Determining the amount of index log information

Table 17-7 shows the formulas for determining the amount of index log information that is output per index when manipulating a row.

Table 17-7 Amount of index log information per index

Type of data manipulation (SQL statement)			Amount of log information output (bytes)
Key addition (INSERT statement)	Adding a new key		k₁ + 156 or (k₁ + 156) 2^*
	Adding the same key as for existing row	d 200	k₁ + 156
	Adding the same key as for existing row	d > 200	k₁ + 292
Key deletion (DELETE statement)	Deleting a key value		k₂ + 156
	Deleting the same key value as for existing row	d 200	k₂ + 156
	Deleting the same key value as for existing row	d > 200	k₂ + 292
Key updating (UPDATE statement)			Amount of log information for key deletion + amount of log information for key addition

d: Number of duplicated key values

k₁: Length of key to be added (bytes)

k₂: Length of key to be deleted (bytes)

^* Use this formula for indexes with UNIQUE specified using the index key no-lock option.

(a) Determining the amount of index log information for index page splitting

Figure 17-1 shows the concept of index page splitting.

Figure 17-1 Concept of index page splitting

[Figure]

Explanation:

Splitting a page in two by adding a key to the rightmost leaf page (containing the maximum key value in the figure) is called page splitting containing the maximum key value.
Splitting a page in two by adding a key to any other leaf page is called page splitting without the maximum key value.

When a page that stores an index is split, HiRDB uses one of the two methods explained below to store the key value.

Page splitting without the maximum key value
When a key value is added or deleted, HiRDB stores the key value by splitting the key and the unused area at a ratio of approximately 50:50. Page splitting without the maximum key value occurs in the following cases:
- The index storage page is too small to store the key value.
- a rows with the same key value are added while there are more than 200 duplicated key values (the value of a can be obtained from the formula shown below; in this case, page splitting occurs at every a rows).
Formula

a = Page length of RDAREA for storing index (bytes) 4
Page splitting containing the maximum key value
If a key value is added or updated in an index storage page containing the maximum key value, HiRDB stores the key value by splitting the key and the unused area at the ratio specified by the PCTFREE operand of CREATE INDEX.
For example, if PCTFREE = 30 is specified, HiRDB stores the key value by splitting the key and unused area at a ratio of approximately 70:30.
Page splitting containing the maximum key value occurs if it is impossible to acquire as much free space as specified in the PCTFREE operand of CREATE INDEX when a key value is to be added. However, this does not apply to an upper-level page.

Table 17-8 shows the formulas for determining by split type the amount of index log information that is output at any one time.

Table 17-8 Amount of index log information by split type

Split type	Condition			Amount of log information output (bytes)
Page splitting containing the maximum key value	Adding a key value that is different from any key value already in the index	There is enough unused area to add the key in upper-level page	2 k₁ + a + 8 (m + 1) 31516	2 k₁ + 472 + a + 8 (m + 1)
			2 k₁ + a + 8 (m + 1) > 31516	2 k₁ + 632 + a + 8 (m + 1)
		There is not enough unused area to add the key in upper-level page	2 k₁ + a + 8 (m + 1) 31516	n-1 (288 + a) i=2 + 2 k₁ + 472 + a + 8 (m + 1)
			2 k₁ + a + 8 (m + 1) > 31516	n-1 (288 + a) i=2 + 2 k₁ + 628 + a + 8 (m + 1)
	Adding the same key value as one already in the index	d₁ 200	There is enough unused area to add the key in upper-level page	2 k₁ + 472 + a + 8 (m + 1)
		d₁ 200	There is not enough unused area to add the key in upper-level page	n-1 (288 + a) i=2 + 2 k₁ + 472 + a + 8 (m + 1)
		d₁ > 200	There is enough unused area to add the key in lower-level page	k₁ + 472 + a
		d₁ > 200	There is not enough unused area to add the key in lower-level page	k₁ + 462 + 2 a
Page splitting without the maximum key value	There is not enough unused area to add the key	There is enough unused area to add the key in upper-level page	2 k₁ + a + 8 (m + 1) 31516	2 k₁ + 332 + a + 8 (m + 1)
			2 k₁ + a + 8 (m + 1) > 31516	2 k₁ + 492 + a + 8 (m + 1)
		There is not enough unused area to add the key in upper-level page	2 k₁ + a + 8 (m + 1) 31516	n-1 (288 + a) i=2 + 2 k₁ + 332 + a + 8 (m + 1)
			2 k₁ + a + 8 (m + 1) > 31516	n-1 (288 + a) i=2 + 2 k₁ + 492 + a + 8 (m + 1)

a: Page length of RDAREA storing the index (bytes)

d₁: Number of duplicated key values

k₁: Length of key value to be added (bytes)

m: Number of index levels where split occurred

n: Number of upper page levels affected by splitting: If an upper-level page affected by leaf page splitting is also split, the value of n is 3 (n 3).

Note

These formulas are used to estimate the amount of update log information for each row and index part. The derived value does not include the amount of log information related to system management that is output when a new page or segment is allocated during addition or update processing. Therefore, if a large amount of data is handled, the amount of log information shown in Table 17-9 must be added.

Table 17-9 Amount of log information for page allocation and segment allocation

Condition	Amount of log information output (bytes)
Allocation of a new page for storing rows resulting from data addition (INSERT) or updating (UPDATE)	440
Index page splitting resulting from data addition (INSERT) or updating (UPDATE)	544 n + 272
Segment allocation resulting from the above page allocation (each time as many pages are allocated as the segment size)	1940

n: Number of index levels when page splitting occurred

Type of data manipulation		Amount of event log information (bytes)
Data addition (INSERT statement)		156 n
Data updating (UPDATE statement)	New elements added by updating (UPDATE ADD)	164 n
	Elements deleted by updating (UPDATE DELETE)	n (p5 +160)_i i = 1
	Only specified elements updated (UPDATE SET)	n (p5 +160) i = 1
	Specified repetition columns updated (UPDATE SET)	164 n