17.1.4 Amount of system log information output during table data updating

When you manipulate rows in tables, the system logs shown below are output.

For HiRDB/Single Server, add the amount of system log information calculated here to the amount of log information output by Single Server. For HiRDB/Parallel Server, add the amount of system log information calculated here to, respectively, the amounts of log information output by the back-end server and dictionary server that manage the RDAREAs that store the tables and indexes being updated.

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

Type of system log informationDescription
Base row log informationThis log information is output when a table's row data is added, deleted, or updated.
Branch row log informationThis log information is output when row data is manipulated in columns with the following data types:
  • VARCHAR#1
  • NVARCHAR#2
  • MVARCHAR#1
  • Repetition columns
  • Abstract data type
  • BINARY type#3
Index log informationThis 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 logThis log information is output when HiRDB Datareplicator is used or when row data containing repetition columns is added, deleted, or updated.
#1: 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.
#2: 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.
#3: 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.

ConditionFormula (bytes)
Amount of system log information that is output to add (INSERT) or delete (DELETE) n rows in a table(a + b + c) x n
Amount of system log information that is output to update (UPDATE) n rows in a table(a#1 + d#2 + e#3) x 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
#1: This value is added when the column value to be updated (UPDATE) is in the base row.
#2: This value is added when the column value to be updated (UPDATE) is in the branch row.
#3: 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

The following table lists 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)
Data updating (UPDATE statement)FIX table with f[Figure] 12f
[Figure] di +
i=1
f
[Figure] dj + 4 x f + 152
j=1
Non-FIX table or f > 12k1 + k2 + 160
[Figure]If LOCK is specified in the pd_nowait_scan_option operand, add:
314 x 2
Row interface used2 x k1 + 160
k: Length of row to be added or deleted
k1: Length of row before updating
k2: Length of row after updating
f: Number of columns to be updated
di: Data length of column before updating
dj: Data length of column after updating
Note 1
The values of k, k1, and k2 depend on the specification of FIX, as shown as follows.
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
When a BLOB column is defined in a table, fix the row length of Table 17-3 at nine bytes for BLOB columns, and add the amounts of log information shown in the following table.

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

Type of data manipulation (SQL statement)Specification of recoveryAmount of log information output (bytes)
Data addition
(INSERT statement)
Not specified or partial specified604 + 180 x p5
All specified2,348 + p1 + 8,340 x p2
+ (148 + lt) x p3
Not specified300
Data deletion
(DELETE statement)
Not specified or partial specified460 + 180 x p6
All specified
Not specified468
Data updating
(UPDATE statement)
Not specified or partial specified604 + 180 x p5 + 460 + 180 x p6
All specified2,496 + p1 + 8,334 x p2
+ (148 + lt) x p3
Not specified312
Data concatenation operation
(UPDATE statement)
Bb[Figure] 7,168
Not specified or partial specified2,344
All specified8,340 x a + 1,600 + d + 8,340 x p4 + (148 + lt2) x p3
Not specified428
Data concatenation operation
(UPDATE statement)
Bb > 7,168
Not specified or partial specified2,772
All specified2,772 + Ba + 8,340 x p4 + (148 + lt2) x p3
Not specified428
Backward deletion/updating of data
(UPDATE statement)
Bb[Figure] 7,168
Not specified or partial specified2,344
All9,512
No428
Backward deletion/updating of data
(UPDATE statement)
Bb > 7,168
Not specified or partial specified2,492 + 180 x[Figure](Bb-Bd) [Figure] 8,192[Figure]
All2,492 + 180 x[Figure](Bb -Bd) [Figure] 8,192[Figure]
No428 + 180 x[Figure](Bb-Bd) [Figure] 8,192[Figure]
Bi: BLOB data length (bytes)
Ba: One of the following values:
  • If Bb > 7,168: 8,192-{(Bb - 7,168)- [Figure](Bb - 7,168) [Figure] 8,192[Figure] x 8,192}
  • If Bb[Figure] 7,168: 0
Bb: BLOB data length before update (bytes)
Bc: BLOB added data length (bytes)
Bd: Data length after update (value specified by value expression 3 of SUBSTR function) (bytes)
lt: One of the following values:
  • If Bi > 7,168: Bi - 7,168- [Figure](Bi - 7,168) [Figure] 8,192 [Figure] x 8,192
  • If Bi[Figure] 7,168: 0
lt2: One of the following values:
  • If Bc + Bb > 7,168: (Bc + Bb - Ba - 7,168)- [Figure](Bc + Bb - Ba - 7,168) [Figure] 8,192 [Figure] x 8,192
  • If Bc + Bb[Figure] 7,168: 0
p1: One of the following values:
  • If Bi > 7,168: 7,168
  • If Bi[Figure] 7,168: Bi
p2: One of the following values:
  • If Bi > 7,168: [Figure](Bi - 7,168) [Figure] 8,192 [Figure]
  • If Bi[Figure] 7,168: 0
p3: One of the following values:
  • If lt = 0 or lt2 = 0: 0
  • If lt > 0 or lt2 > 0: 1
p4: One of the following values:
  • If Bc + Bb > 7,168: [Figure](Bc + Bb - Ba - 7,168) [Figure] 8,192 [Figure]
  • If Bc + Bb[Figure] 7,168: 0
p5: One of the following values
  • If Bi > 0: [Figure](Bi + 1,024) [Figure] 8,192[Figure]
  • If Bi = 0: 0
p6: One of the following values
  • If Bb > 0: [Figure](Bb + 1,024) [Figure] 8,192[Figure]
  • If Bb = 0: 0
a: One of the following values:
  • If Bb > 0: 1
  • If Bb = 0: 0
d: One of the following values:
  • If Bc + Bb[Figure] 7,168: Bc + Bb
  • If Bc + Bb > 7,168: 7,168

(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. The following table lists 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)
Data updating
(UPDATE statement)
Creating a new branch row by update processingk2 + 160
Updating a branch rowk1 + k2 + 160
Deleting a branch row by update processingk1 + 160
k: Length of one branch row to be added or deleted
k1: Length of one branch row before updating
k2: 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. The table below lists the formulas for determining the amount of log information per 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 x (b + 152)
Data deletion (DELETE statement)
Data updating (UPDATE statement)SPN x (b + 160)
Data concatenation operation (UPDATE statement)#1(b + 160) + (SPN - 1) x (b + 152)
[Figure]If the pd_rpl_hdepath operand is specified, add:
160
Backward deletion/updating of data (UPDATE statement)#1, #2(b x 2 + 160) + ([Figure](c - d) [Figure] (b - 57)[Figure]) x (b + 152)
[Figure]If you specify the pd_rpl_hdepath operand, add:
160
#1
For BINARY type columns only
#2
Not applicable to compressed columns. For backward deletion/updating of data in compressed columns (UPDATE statement), use data updating (UPDATE statement).
b: Page length of an RDAREA
c: Data length before update
d: Data length after update (value specified by value expression 3 of SUBSTR function)
SPN: The following shows how to obtain this value:
If there is a branch row (for the branch condition, see #5 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
For details about SPN1 and SPN2, see (3) Variables used in formulas in 16.1.2 Calculating the number of table storage pages. Make sure, however, to use 1 for a.

(3) Determining the amount of index log information

Use the formulas shown in the following table to determine the amount of index log information that is output for each row operated on in an index.

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 keyk1 + 156 or (k1 + 156) x 2#
Adding the same key as for existing rowd[Figure] 200k1 + 156
d > 200k1 + 292
Key deletion
(DELETE statement)
Deleting a key valuek2 + 156
Deleting the same key value as for existing rowd[Figure] 200k2 + 156
d > 200k2 + 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
k1: Length of key to be added (bytes)
k2: Length of key to be deleted (bytes)
Note
Length of key refers to the database storage key length. For details about how to determine the key length, see 16.1.3 Calculating the number of index storage pages.
#: 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

The following figure illustrates the concept of index page splitting.

Figure 17-1 Concept of index page splitting

[Figure]

Explanation:
  1. 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.
  2. 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.

The following table lists the amount of index log information output per instance by split type.

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

Split typeConditionAmount of log information output (bytes)
Page splitting containing the maximum key valueAdding a key value that is different from any key value already in the indexThere is enough unused area to add the key in upper-level page2 x k1 + a + 8 x (m + 1) [Figure] 31,5162 x k1 + 472 + a + 8
x (m + 1)
2 x k1 + a + 8 x (m + 1) > 31,5162 x k1 + 632 + a + 8
x (m + 1)
There is not enough unused area to add the key in upper-level page2 x k1 + a + 8 x (m + 1) [Figure] 31,516n-1
[Figure](288 + a)
i=2
+ 2 x k1 + 472 + a +
8 x (m + 1)
2 x k1 + a + 8 x (m + 1) > 31,516n-1
[Figure](288 + a)
i=2
+ 2 x k1 + 628 + a +
8 x (m + 1)
Adding the same key value as one already in the indexd1[Figure] 200There is enough unused area to add the key in upper-level page2 x k1 + 472 + a + 8
x (m + 1)
There is not enough unused area to add the key in upper-level pagen-1
[Figure](288 + a)
i=2
+ 2 x k1 + 472 + a +
8 x (m + 1)
d1 > 200There is enough unused area to add the key in lower-level pagek1 + 472 + a
There is not enough unused area to add the key in lower-level pagek1 + 462 + 2 x a
Page splitting without the maximum key valueThere is not enough unused area to add the keyThere is enough unused area to add the key in upper-level page2 x k1 + a + 8 x (m + 1) [Figure] 31,5162 x k1 + 332 + a + 8
x (m + 1)
2 x k1 + a + 8 x (m + 1) > 31,5162 x k1 + 492 + a + 8
x (m + 1)
There is not enough unused area to add the key in upper-level page2 x k1 + a + 8 x (m + 1) [Figure] 31,516n-1
[Figure](288 + a)
i=2
+ 2 x k1 + 332 + a +
8 x (m + 1)
2 x k1 + a + 8 x (m + 1) > 31,516n-1
[Figure](288 + a)
i=2
+ 2 x k1 + 492 + a +
8 x (m + 1)
a: Page length of RDAREA storing the index (bytes)
d1: Number of duplicated key values
k1: Length of key value to be added (bytes)
Length of key refers to the database storage key length. For details about how to determine the key length, see 16.1.3 Calculating the number of index storage pages.
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[Figure] 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 you are handling large amounts of data, you must add the amounts of log information shown in the following table to determine the amount.

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

ConditionAmount 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 x n + 272
Segment allocation resulting from the above page allocation (each time as many pages are allocated as the segment size)1,940
n
Number of index levels when page splitting occurred

(4) Determining the size of event log information

The event log information is output when row data containing repetition columns is added, deleted, or updated using HiRDB Datareplicator. The following table shows the amount of event log information that is output when a single row is manipulated.

Table 17-10 Amount of event log information that is output when a single row is manipulated

Type of data manipulationAmount of event log information (bytes)
Data addition (INSERT statement)156 x n
Data updating (UPDATE statement)New elements added by updating (UPDATE ADD)164 x n
Elements deleted by updating
(UPDATE DELETE)
n
[Figure] (p5 +160)i
i = 1
Only specified elements updated
(UPDATE SET)
n
[Figure] (p5 +160)
i = 1
Specified repetition columns updated
(UPDATE SET)
164 x n
n: Number of repetition columns being updated
p5: [Figure]{[Figure](average of the largest subscript number that is specified - average of the smallest subscript number that is specified + 1) [Figure] 8[Figure]} [Figure] 4[Figure] x 4