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 12	f di + i=1 f dj + 4 f + 152 j=1
	Non-FIX table or f > 12	k1 + k2 + 160 If LOCK is specified in the pd_nowait_scan_option operand, add: 314 2
	Row interface used	2 k1 + 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, or if updatable online reorganization is executing, 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 Formulas for determining the amount of base row log information per data item 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 recovery	Amount of log information output (bytes)
Data addition (INSERT statement)	Not specified or partial specified	604 + 180 p5
	All specified	2,348 + p1 + 8,340 p2 + (148 + lt) p3
	Not specified	300
Data deletion (DELETE statement)	Not specified or partial specified	460 + 180 p6
	All specified
	Not specified	468
Data updating (UPDATE statement)	Not specified or partial specified	604 + 180 p5 + 460 + 180 p6
	All specified	2,496 + p1 + 8,334 p2 + (148 + lt) p3
	Not specified	312
Data concatenation operation (UPDATE statement) Bb 7,168	Not specified or partial specified	2,344
	All specified	8,340 a + 1,600 + d + 8,340 p4 + (148 + lt2) p3
	Not specified	428
Data concatenation operation (UPDATE statement) Bb > 7,168	Not specified or partial specified	2,772
	All specified	2,772 + Ba + 8,340 p4 + (148 + lt2) p3
	Not specified	428
Backward deletion/updating of data (UPDATE statement) Bb 7,168	Not specified or partial specified	2,344
	All	9,512
	No	428
Backward deletion/updating of data (UPDATE statement) Bb > 7,168	Not specified or partial specified	2,492 + 180 (Bb-Bd) 8,192
	All	2,492 + 180 (Bb -Bd) 8,192
	No	428 + 180 (Bb-Bd) 8,192

Bi: BLOB data length (bytes)

Ba: One of the following values:

• If Bb > 7,168: 8,192-{(Bb - 7,168)- (Bb - 7,168) 8,192 8,192}
• If Bb 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- (Bi - 7,168) 8,192 8,192
• If Bi 7,168: 0

lt2: One of the following values:

• If Bc + Bb > 7,168: (Bc + Bb - Ba - 7,168)- (Bc + Bb - Ba - 7,168) 8,192 8,192
• If Bc + Bb 7,168: 0

p1: One of the following values:

• If Bi > 7,168: 7,168
• If Bi 7,168: Bi

p2: One of the following values:

• If Bi > 7,168: (Bi - 7,168) 8,192
• If Bi 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: (Bc + Bb - Ba - 7,168) 8,192
• If Bc + Bb 7,168: 0

p5: One of the following values

• If Bi > 0: (Bi + 1,024) 8,192
• If Bi = 0: 0

p6: One of the following values

• If Bb > 0: (Bb + 1,024) 8,192
• 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 7,168: Bc + Bb
• If Bc + Bb > 7,168: 7,168

(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 processing	k2 + 160
	Updating a branch row	k1 + k2 + 160
	Deleting a branch row by update processing	k1 + 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. 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 (b + 152)
Data deletion (DELETE statement)
Data updating (UPDATE statement)	SPN (b + 160)
Data concatenation operation (UPDATE statement)#1	(b + 160) + (SPN - 1) (b + 152) If the pd_rpl_hdepath operand is specified, add: 160
Backward deletion/updating of data (UPDATE statement)#1, #2	(b 2 + 160) + ((c - d) (b - 57)) (b + 152) 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.

(c) Additional log information output with updatable online reorganization

To determine the amount of log information that is output when a UAP accesses an RDAREA for the purpose of updatable online reorganization (while accessing subordinate RDAREAs that are in online reorganization hold), you must add the amount of event log information for the pdorend application process to the amount of log information that is ordinarily output. This section explains the additional log information.

• During updatable online reorganization, each time a row is updated, a row update start event and a row update completion event are output to the log. In addition, in the event of rollback, for each row recovered a row recovery start event and a row recovery completion event are output to the log. Therefore, when you determine system log file capacity, add the amounts of log information shown in the following table to the formulas of Table 17-3 Formulas for determining the amount of base row log information per data item.
  

  Table 17-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#1 + 160#2
  INSERT rollback	320
  DELETE rollback
  UPDATE rollback	496 + data length before row update + c#1 + 160#2

  #1

  c is added when the -e option of the commit a database for online reorganization command (pdorbegin) is specified. The formula for c is as follows:

  (((a - b + 1) 8 + 6) 4) 4 2

  a: ID of the largest column of the columns to be updated

  b: ID of the smallest column of the columns to be updated

  #2

  Add this amount of log information when you perform a concatenation operation or a backward deletion/update on a BINARY type column with a defined length of 32,001 or more.

  
  
• If a BLOB column is defined for a table during updatable online reorganization, a BLOB update event log is added. Therefore, when you determine system log file capacity, add the amounts of log information shown in the following table to the formulas of Table 17-4 Formulas for determining the amount of log information per BLOB column data item.
  

  Table 17-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	224 + 160#2
  INSERT rollback	0
  DELETE rollback	148 + p1 + 8,340 p2 + (148 + lt) p3#1
  UPDATE rollback	148 + p1 + 8,340 p2 + (148 + lt) p3#1 + 160#2

  #1

  For details about variables p1, p2, p3, and lt, see Table 17-4 Formulas for determining the amount of log information per BLOB column data item. In these variables, substitute the length of the BLOB data to be deleted.

  #2

  Add this amount of log information when you perform a concatenation operation or a backward deletion/update.

• During updatable online reorganization, a new repetition column update event log is output in the cases listed below. Therefore, when determining system log file capacity, add the amounts of log information shown in the table below to the formulas of Table 17-3 Formulas for determining the amount of base row log information per data item.
  
  • When a row containing repetition columns is deleted
  • When an entire repetition column is specified for update processing
  • When elements in a repetition column are specified for update processing
  • When elements in a repetition column are specified for deletion processing
  • When specified elements in a repetition column are specified for update processing
    

    Table 17-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 164 i=1
    UPDATE-DELETE (element specification) (when new elements are deleted by updating)	M (p4 + 160) i=1
    UPDATE-SET (element specification) (when only the specified elements are updated)	N ((p4 + 160) 2) i=1
    DELETE	156
    UPDATE-SET (column specification) rollback	156
    Rollback of UPDATE-ADD (element specification) (when new elements are deleted by updating)	N (p4 + 180) i=1
    Rollback of UPDATE-DELETE (element specification) (when new elements are added by updating)	M 184 i=1
    Rollback of UPDATE-SET (element specification) (when only the specified elements are updated)	N (p4 + 180) i=1
    DELETE rollback	156

  n: Number of repetition columns to be updated

  m: Number of elements to be deleted

  p4: {(Average of the largest subscript number that is specified - average of the smallest subscript number that is specified + 1) 8} 4 4

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-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) 2#
	Adding the same key as for existing row	d 200	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 200	k2 + 156
		d > 200	k2 + 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)

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

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.

• 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.
  

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

Table 17-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 k1 + a + 8 (m + 1) 31,516	2 k1 + 472 + a + 8 (m + 1)
			2 k1 + a + 8 (m + 1) > 31,516	2 k1 + 632 + a + 8 (m + 1)
		There is not enough unused area to add the key in upper-level page	2 k1 + a + 8 (m + 1) 31,516	n-1 (288 + a) i=2 + 2 k1 + 472 + a + 8 (m + 1)
			2 k1 + a + 8 (m + 1) > 31,516	n-1 (288 + a) i=2 + 2 k1 + 628 + a + 8 (m + 1)
	Adding the same key value as one already in the index	d1 200	There is enough unused area to add the key in upper-level page	2 k1 + 472 + a + 8 (m + 1)
			There is not enough unused area to add the key in upper-level page	n-1 (288 + a) i=2 + 2 k1 + 472 + a + 8 (m + 1)
		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 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 k1 + a + 8 (m + 1) 31,516	2 k1 + 332 + a + 8 (m + 1)
			2 k1 + a + 8 (m + 1) > 31,516	2 k1 + 492 + a + 8 (m + 1)
		There is not enough unused area to add the key in upper-level page	2 k1 + a + 8 (m + 1) 31,516	n-1 (288 + a) i=2 + 2 k1 + 332 + a + 8 (m + 1)
			2 k1 + a + 8 (m + 1) > 31,516	n-1 (288 + a) i=2 + 2 k1 + 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)

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 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-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 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

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-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 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

n: Number of repetition columns being updated

p5: {(average of the largest subscript number that is specified - average of the smallest subscript number that is specified + 1) 8} 4 4