If you manipulate rows in a table, the system outputs the system log information listed in Table 18-1.
Table 18-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:
|
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 18-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. |
Table 18-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) ![]() |
Amount of system log information that is output to update (UPDATE) n rows in a table | (a1 + d2 + e3) ![]() |
Table 18-3 shows the formulas for determining the amount of base row log information per data item.
Table 18-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![]() | f![]() i=1 f ![]() ![]() j=1 |
Non-FIX table or f > 12 | k1 + k2 + 160![]() 314 ![]() | |
Row interface used | 2 ![]() |
Table 18-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 ![]() + (148 + lt) ![]() | |
Not specified | 300 | |
Data deletion (DELETE statement) | Not specified or partial specified | 1052 |
All specified | ||
Not specified | 468 | |
Data updating (UPDATE statement) | Not specified or partial specified | 1324 |
All specified | 2496 + p1 + 8334 ![]() + (148 + lt) ![]() | |
Not specified | 312 | |
Data concatenation operation (UPDATE statement) Bb ![]() | Not specified or partial specified | 2344 |
All specified | 8340![]() ![]() ![]() | |
Not specified | 428 | |
Data concatenation operation (UPDATE statement) Bb>7168 | Not specified or partial specified | 2772 |
All specified | 2772 + Ba + 8340![]() ![]() | |
Not specified | 428 |
Calculate the amount of branch row log information that will be generated. Table 18-5 shows the formulas for determining the amount of log information per branch row.
Table 18-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 processing | k2 + 160 |
Updating a branch row | k1 + k2 + 160 | |
Deleting a branch row by update processing | k1 + 160 |
Calculate the amount of branch row log information that will be generated. Table 18-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 18-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![]() |
Data deletion (DELETE statement) | |
Data updating (UPDATE statement) | SPN![]() |
Data concatenation operation (UPDATE statement) * | (b + 160) + (SPN - 1)![]() ![]() 160 |
SPN = SPN1 + SPN2 + SPN3 + SPN4![]() SPN1 = f ![]() ![]() ![]() ![]() ![]() i=1 SPN1 = ![]() ![]() SPN2 = f { ![]() ![]() ![]() ![]() ![]() i=1 ![]() When value of branch di > (b - 2853) ![]() SPN3 = f ![]() ![]() ![]() ![]() ![]() i=1 When value of branch di ![]() ![]() SPN4 = SF |
Estimation of the log information output during UAP access to an RDAREA subject to updatable online reorganization (online reorganization shutdown status) must consider not only the normal log information, but in addition the event log information for the target online reorganization process. This section explains the additional log information.
Table 18-7 System log information added per item of data with updatable online reorganization
Type of data manipulation (SQL statement) | Additional log information output (bytes) |
---|---|
INSERT | 320 |
DELETE | |
UPDATE | 320 + c# |
INSERT rollback | 320 |
DELETE rollback | |
UPDATE rollback | 496 + pre-update row data length + c* |
Table 18-8 System log information added per item of BLOB data with updatable online reorganization
Type of data manipulation (SQL statement) | Additional log information output (bytes) |
---|---|
INSERT | 0 |
DELETE | 224 |
UPDATE | |
INSERT rollback | 0 |
DELETE rollback | 148 + p1 + 8340![]() ![]() |
UPDATE rollback |
Table 18-9 System log information added per item of data that includes a repetition column with updatable online reorganization
Type of data manipulation (SQL statement) | Additional log information output (bytes) |
---|---|
UPDATE-SET (column specification) | 164 |
UPDATE-ADD (element specification) (when new elements are added by updating) | n![]() i=1 |
UPDATE-DELETE (element specification) (when new elements are deleted by updating) | m![]() i=1 |
UPDATE-SET (element specification) (when only the specified elements are updated) | n![]() ![]() i=1 |
DELETE | 156 |
UPDATE-SET (column specification) rollback | 156 |
Rollback of UPDATE-ADD (element specification) (when new elements are deleted by updating) | n![]() i=1 |
Rollback of UPDATE-DELETE (element specification) (when new elements are added by updating) | m![]() i=1 |
Rollback of UPDATE-SET (element specification) (when only the specified elements are updated) | n![]() i=1 |
DELETE rollback | 156 |
Table 18-10 shows the formulas for determining the amount of index log information that is output per index when manipulating a row.
Table 18-10 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 | k1 + 156 or (k1 + 156) ![]() | |
Adding the same key as for existing row | d![]() | k1 + 156 | |
d > 200 | k1 + 292 | ||
Key deletion (DELETE statement) | Deleting a key value | k2 + 156 | |
Deleting the same key value as for existing row | d![]() | k2 + 156 | |
d > 200 | k2 + 292 | ||
Key updating (UPDATE statement) | Amount of log information for key deletion + amount of log information for key addition |
Figure 18-1 shows the concept of index page splitting.
Figure 18-1 Concept of index page splitting
When a page that stores an index is split, HiRDB uses one of the two methods explained below to store the key value.
Table 18-11 shows the formulas for determining by split type the amount of index log information that is output at any one time.
Table 18-11 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 ![]() ![]() ![]() | 2 ![]() ![]() |
2 ![]() ![]() | 2 ![]() ![]() | |||
There is not enough unused area to add the key in upper-level page | 2 ![]() ![]() ![]() | n-1![]() i=2 + 2 ![]() 8 ![]() | ||
2 ![]() ![]() | n-1![]() i=2 + 2 ![]() 8 ![]() | |||
Adding the same key value as one already in the index | d1![]() | There is enough unused area to add the key in upper-level page | 2 ![]() ![]() | |
There is not enough unused area to add the key in upper-level page | n-1![]() i=2 + 2 ![]() 8 ![]() | |||
d1 > 200 | There is enough unused area to add the key in lower-level page | k1 + 472 + a | ||
There is not enough unused area to add the key in lower-level page | k1 + 462 + 2 ![]() | |||
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 ![]() ![]() ![]() | 2 ![]() ![]() |
2 ![]() ![]() | 2 ![]() ![]() | |||
There is not enough unused area to add the key in upper-level page | 2 ![]() ![]() ![]() | n-1![]() i=2 + 2 ![]() 8 ![]() | ||
2 ![]() ![]() | n-1![]() i=2 + 2 ![]() 8 ![]() |
Table 18-12 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 ![]() |
Segment allocation resulting from the above page allocation (each time as many pages are allocated as the segment size) | 1940 |
The event log information is output when row data containing repetition columns is added, deleted, or updated using HiRDB Datareplicator. Table 18-13 shows the amount of event log information that is output when a single row is manipulated.
Table 18-13 Amount of event log information that is output when a single row is manipulated
Type of data manipulation | Amount of event log information (bytes) | |
---|---|---|
Data addition (INSERT statement) | 156 ![]() | |
Data updating (UPDATE statement) | New elements added by updating (UPDATE ADD) | 164 ![]() |
Elements deleted by updating (UPDATE DELETE) | n![]() i = 1 | |
Only specified elements updated (UPDATE SET) | n![]() i = 1 | |
Specified repetition columns updated (UPDATE SET) | 164 ![]() |