Hitachi

Hitachi Advanced Database Setup and Operation Guide

5.8.1 Determining the total number of pages in the data DB area

Use the following formula to determine the total number of pages in the data DB area (DATA_PAGE_NUM).

Organization of this subsection

(1) Formula

The following shows the formula for determining the total number of pages in the data DB area (DATA_PAGE_NUM).

Formula

[Figure]

To Page Top

(2) Explanation of variables

The following explains the variables that are used to determine the total number of pages in the data DB area (DATA_PAGE_NUM).

Explanation of variables

dbarea_file_num

Number of files in the data DB area (number of files)

page_size

Page size (bytes)

SEGBF

Segment blocking factor

This value differs for each data DB area page size. Determine it from the following table.

Table 5‒5: Segment blocking factor for each data DB area page size

No.

Page size (kilobytes)

Segment blocking factor

1

4

12

2

8

42

3

16

127

4

32

341
SEGSIZE

Segment size (pages)

Use the following formula to determine this value.

SEGSIZE = 4,194,304 ÷ page_size
SGDATA

Number of segments in the data DB area

Use the following formula to determine this value.

[Figure]

Note that the methods for determining the variables SGROWTBL, SGCOLUMNTBL, SGIDX, SGRIX, and SGTIX differ for single-chunk tables and multi-chunk tables. If you store both single-chunk tables and multi-chunk tables in the same data DB area, you must determine the variables SGROWTBL, SGCOLUMNTBL, SGIDX, SGRIX, and SGTIX for both types of tables and add them up. The following shows the methods of determining the values of the variables SGROWTBL, SGCOLUMNTBL, SGIDX, SGRIX, and SGTIX:

■ For a single-chunk table

SGROWTBL (for single-chunk tables)

Number of segments for managing the data pages for row store tables in the data DB area

For details, see (a) Determining the variable SGROWTBL (for a single-chunk table).

SGCOLUMNTBL (for single-chunk tables)

Number of segments for managing the data pages for column store tables in the data DB area

For details, see (b) Determining the variable SGCOLUMNTBL (for a single-chunk table).

SGIDX (for single-chunk tables)

Number of segments for managing the B-tree index pages in the data DB area

For details, see (c) Determining the variable SGIDX (for a single-chunk table).

SGRIX (for single-chunk tables)

Number of segments for managing the range index pages in the data DB area

For details, see (d) Determining the variable SGRIX (for a single-chunk table).

SGTIX (for single-chunk tables)

Number of segments for managing the text index pages in the data DB area

For details, see (e) Determining the variable SGTIX (for a single-chunk table).

■ For a multi-chunk table

SGROWTBL (for multi-chunk tables)

Number of segments for managing the data pages for row store tables in the data DB area

For details, see (f) Determining the variable SGROWTBL (for a multi-chunk table).

SGCOLUMNTBL (for multi-chunk tables)

Number of segments for managing the data pages for column store tables in the data DB area

For details, see (g) Determining the variable SGCOLUMNTBL (for a multi-chunk table).

SGIDX (for multi-chunk tables)

Number of segments for managing the B-tree index pages in the data DB area

For details, see (h) Determining the variable SGIDX (for a multi-chunk table).

SGRIX (for multi-chunk tables)

Number of segments for managing the range index pages in the data DB area

For details, see (i) Determining the variable SGRIX (for a multi-chunk table).

SGTIX (for multi-chunk tables)

Number of segments for managing the text index pages in the data DB area

For details, see (j) Determining the variable SGTIX (for a multi-chunk table).

(a) Determining the variable SGROWTBL (for a single-chunk table)

Use the following formula to determine this value.

[Figure]
row_tbl_num_in_dbarea

Total number of tables in the data DB area (tables)

BP(i)

Number of pages for base rows required for storing tables

See (1) Determining the number of pages for base rows (variable BP(i)) in 5.8.2 Determining the number of pages for storing each type of row.

VP(i)

Number of pages for branch rows required for storing tables

See (2) Determining the number of pages for branch rows (variable VP(i)) in 5.8.2 Determining the number of pages for storing each type of row.

(b) Determining the variable SGCOLUMNTBL (for a single-chunk table)

Because data is compressed when it is stored in a column store table, the number of segments cannot be calculated from the record length in the same way as for row store tables. Instead, the process involves storing a small amount of data and estimating the number of segments based on the resulting compression rate.

Use the following formula to determine this value.

[Figure]
column_tbl_num_in_dbarea

Total number of column store tables in the data DB area (tables)

SEGSIZE

Segment size (pages)

Use the following formula to determine this value.

SEGSIZE = 4,194,304 ÷ page_size
COLUMNTBLSIZE(i)

Data size of the i-th column store table (megabytes)

Use the following formula to determine this value.

COLUMNTBLSIZE(i) = IMPORTDATASIZE(i) × COMPRESSION_RATE + COLUMNIZESIZE(i)
IMPORTDATASIZE(i)

Size of the source data (CSV file) stored in the i-th column store table (megabytes)

COMPRESSION_RATE

Compression rate of source data

Use the adbimport command to store part of the source data to be stored in the i-th column store table. Then, use the following formula to determine the compression rate:

[Figure]
testdbsize

The amount of space the partial source data imported by the adbimport command occupies in the database (megabytes)

As the partial source data imported to complete the preceding formula, we recommend that you prepare approximately 100 to 500 megabytes of data whose composition is representative of the actual data. When executing the adbimport command, specify 2 for the adb_import_rthd_num import option.

After executing the adbimport command, use the adbdbstatus command to output the Used_segments (number of segments used by the table) value in the table summary information in megabytes. Use this information to check the number of segments used in the database.

For details about the adbimport command and the adbdbstatus command, see the manual HADB Command Reference.

testcsvsize

The size of the CSV file containing the partial source data imported by the adbimport command (megabytes)

COLUMNIZESIZE(i)

Size of data that is stored in the i-th column store table in column store format by the maintenance processing of the updated-row columnizing facility (megabytes)

Add the COLUMNIZESIZE(i) variable only if the updated-row columnizing facility is enabled. If a B-tree index is defined for the i-th column store table, assume that this value is 0 during estimation.

The formula is as follows:

[Figure]
IMPORTDATASIZE(i)

Size of the source data (CSV file) stored in the i-th column store table (megabytes)

COMPRESSION_RATE

Compression rate of source data

Determine the value as in the explanation of the COMPRESSION_RATE variable shown earlier.

insert_row_num(i)

Number of rows that are added or updated by the INSERT or UPDATE statement in the i-th column store table

import_row_num(i)

Number of rows in the source data (CSV file) stored in the i-th column store table

ADDTBLPGNUM(i)

The number of pages of data to be added to the i-th column store table by using the INSERT or UPDATE statement

Use the following formula to determine this value.

ADDTBLPGNUM(i) = BP(i) + VP(i)
BP(i)

The number of basic row pages of data to be added to the i-th column store table by using the INSERT or UPDATE statement

Determine this value by referring to (1) Determining the number of pages for base rows (variable BP(i)) in 5.8.2 Determining the number of pages for storing each type of row based on the number of rows to be added to the i-th column store table by using the INSERT or UPDATE statement.

VP(i)

The number of branch row pages of data to be added to the i-th column store table by using the INSERT or UPDATE statement

Determine this value by referring to (2) Determining the number of pages for branch rows (variable VP(i)) in 5.8.2 Determining the number of pages for storing each type of row based on the number of rows to be added to the i-th column store table by using the INSERT or UPDATE statement.

DELTBLPGNUMI(i)

The number of pages of data to be added to the invalid row information pages for the i-th column store table by using the UPDATE or DELETE statement

Use the following formula to determine this value.

DELTBLPGNUM(i) = DELLEAFPGNUM(i) + DELUPPERPGNUM(i)
DELLEAFPGNUM(i)

Use the following formula to determine this value.

[Figure]
update_row_num

The number of rows to be updated in the i-th column store table by using the UPDATE statement

delete_row_num

The number of rows to be deleted from the i-th column store table by using the DELETE statement

page_size

The page size of the DB area in which the i-th column store table is defined (bytes)

DELUPPERPGNUM(i)

Use the following formula to determine this value.

[Figure]

The value of the variable SGCOLUMNTBL can also be estimated by assuming that non-compression (NONE) is specified as the compression type for all columns and determining the size accordingly. However, depending on the selected compression type and the compression rate that is achieved, the estimated value might be larger than the actual size of the stored data.

Use the following formula to estimate the value based on the assumption that non-compression (NONE) is specified as the compression type for all columns:

[Figure]
COLUMNDATASEGNUM(i)

Number of column-data segments for i-th column store table

Use the following formula to determine this value.

[Figure]
row_num

Number of rows stored in i-th column store table

column_num

Number of columns in i-th column store table

page_size

Page size of data DB area (bytes)

COL_PAGE_NUM

The value to substitute for the COL_PAGE_NUM variable differs depending on the page size of the data DB area. Substitute the value in the following table that corresponds to the page size of the data DB area:

Table 5‒6: Value to substitute for COL_PAGE_NUM variable

No.

Page size of data DB area (kilobytes)

Value of COL_PAGE_NUM

1

4

1,021

2

8

510

3

16

255

4

32

127
COL_MAX_SIZE

Maximum data length of column data in i-th column store table

Use the following formula to determine this value.

[Figure]
COLUMNDATASIZE(k)

Data length of column data in k-th column (bytes)

The value to substitute for the COLUMNDATASIZE variable differs depending on the data type of the column data. See the following table, and substitute the applicable value.

Table 5‒7: Data length of each type of column data

No.

Classification

Data type

Data length (bytes)

1

Numeric data

INTEGER

10

2

SMALLINT

6

3

DECIMAL(m, n)#

m ≤ 4

4

4

5 ≤ m ≤ 8

6

5

9 ≤ m ≤ 16

10

6

17 ≤ m

18

7

DOUBLE PRECISION

10

8

Character string data

CHARACTER(n)

n ≤ 127

[Figure]

9

128 ≤ n

10

10

VARCHAR(n)

n ≤ 127

[Figure]

11

128 ≤ n ≤ 255

128

12

256 ≤ n

130

13

Datetime data

DATE

6

14

TIME(p)

[Figure]

15

TIMESTAMP(p)

[Figure]

16

Binary data

BINARY(n)

n ≤ 127

[Figure]

17

128 ≤ n

10

18

VARBINARY(n)

n ≤ 127

[Figure]

19

128 ≤ n ≤ 255

128

20

256 ≤ n

130
Legend:

m, n: Positive integers

p: 0, 3, 6, 9 or 12

#

Indicates a fixed-point number that has a total of m digits, with n digits following the decimal point. If m is omitted, 38 is assumed.

ROWDATASEGNUM(i)

Number of row-data segments for i-th column store table

Use the following formula to determine this value.

[Figure]
row_num

Number of rows stored in i-th column store table

page_size

Page size of data DB area (bytes)

var_num

Number of columns managed as branch rows (columns)

When the data type is CHAR or BINARY, determine the number of columns whose definition length is 128 bytes or longer.

When the data type is VARCHAR or VARBINARY, determine the number of columns that include data whose actual length is 128 bytes or longer.

var_size

Data length of columns in the branch rows (bytes)

The value to substitute for the var_size variable differs depending on the data type. See the following table, and substitute the applicable value.

Table 5‒8: Value to be substituted for the variable var_size

No.

Classification

Data type

Data length (bytes)

1

Character string data

CHAR

Definition length

2

VARCHAR

d

3

Binary data

BINARY

Definition length

4

VARBINARY

d
Legend:

d: Actual data length

COLUMNIZESEGNUM(i)

Number of column-data segments that are stored in the i-th column store table by the maintenance processing of the updated-row columnizing facility

Add the COLUMNIZESEGNUM(i) variable only if the updated-row columnizing facility is enabled. If a B-tree index is defined for the i-th column store table, assume that this value is 0 during estimation.

The formula is as follows:

COLUMNIZESEGNUM(i) = COLUMNDATASEGNUM(i)

For details about how to determine the value of the COLUMNDATASEGNUM(i) variable, see the explanation of the COLUMNDATASEGNUM(i) variable shown earlier. When doing so, for the row_num variable, substitute the number of rows added or updated by the INSERT or UPDATE statement in the i-th column store table.

(c) Determining the variable SGIDX (for a single-chunk table)

Use the following formula to determine this value.

[Figure]
idx_num_in_dbarea

Total number of B-tree indexes in the data DB area (indexes)

IP_LOWER(i)

Number of pages used in the lower page segment of each B-tree index

See (1) Determining the number of storage pages used in the lower page segment (variable IP_LOWER(i)) in 5.8.3 Determining the number of storage pages for each B-tree index segment.

IP_UPPER(i)

Number of pages used in the upper page segment of each B-tree index

See (2) Determining the number of storage pages used in the upper page segment (variable IP_UPPER(i)) in 5.8.3 Determining the number of storage pages for each B-tree index segment.

(d) Determining the variable SGRIX (for a single-chunk table)

Use the following formula to determine this value.

[Figure]
rng_num_in_dbarea

Number of range indexes in the data DB area (indexes)

RS(i)

Number of segments required for storing each range index

For details, see 5.8.6 Determining the number of segments for storing each range index.

(e) Determining the variable SGTIX (for a single-chunk table)

Use the following formula to determine its value.

[Figure]
idx_num_in_dbarea

Total number of text indexes in the data DB area (indexes)

TIP_STRSEG(i)

Number of pages used in the string control segment of each text index

See (1) Determining the number of storage pages used in the string control segment (variable TIP_STRSEG(i)) in 5.8.5 Determining the number of storage pages for each text index segment.

TIP_APPSEG(i)

Number of pages used in the position control segment of each text index

See (2) Determining the number of storage pages used in the position control segment (variable TIP_APPSEG(i)) in 5.8.5 Determining the number of storage pages for each text index segment.

(f) Determining the variable SGROWTBL (for a multi-chunk table)

Use the following formula to determine this value.

[Figure]
row_tbl_num_in_dbarea

Total number of row store tables in the data DB area (tables)

chunk_num

Number of non-archived chunks to be created (chunks)

CHBP(i,k)

Number of pages for base rows required for storing tables of each chunk

Substitute the value estimated from the amount of data to be stored by the adbimport command with the -b option specified for the target chunk, the INSERT statement, and the UPDATE statement.

CHVP(i,k)

Number of pages for branch rows required for storing tables of each chunk

Substitute the value estimated from the amount of data to be stored by the adbimport command with the -b option specified for the target chunk, the INSERT statement, and the UPDATE statement.

SGLTBL(i)

Number of segments required to store the location table for the i-th archivable multi-chunk table

You must determine this variable for only archivable multi-chunk tables.

Use the following formula to determine its value.

Formula

[Figure]
SEGSIZE

See SEGSIZE in (2) Explanation of variables under 5.8.1 Determining the total number of pages in the data DB area.

LBP(i)

Use the following formula to determine its value.

Formula

[Figure]
page_size

Page size of the data DB area in which archivable multi-chunk tables are defined (bytes)

row_size

Row size (bytes)

The value to be substituted for the variable row_size differs depending on the data type of the archive range column. The following table shows the relationship between the variable row_size and the data type of the archive range column.

Table 5‒9: Relationship between the variable row_size and the data type of the archive range column

No.

Data type of the archive range column

Value to be substituted for the variable row_size

1

INTEGER

102

2

SMALLINT

94

3

DECIMAL(m,n)

1 ≤ m ≤ 4

90

4

5 ≤ m ≤ 8

94

5

9 ≤ m ≤ 16

102

6

17 ≤ m ≤ 38

118

7

DOUBLE PRECISION

102

8

CHARCTER(p)

88 + 2 x p

9

DATE

94

10

TIME

104

11

TIMESTAMP

112
Legend:

m: Precision

n: Scaling

p: String length (bytes)

LROWNUM

Number of rows to be stored in the location table (rows)

Use the following formula to determine this value.

Formula

[Figure]
archive_chunk_num

Number of executed adbarchivechunk commands

merge_chunk_num

Number of executed adbmergechunk commands

FILENUM(i)

Total number of archive files created by the i-th adbarchivechunk command

Use the following formula to determine this value.

Formula

[Figure]
FILENUM(j)

Total number of archive files corresponding to the archived chunks to be merged by the j-th adbmergechunk command

Determine this value by using the same formula that is used for determining the variable FILENUM(i). During determination, replace i by j.

chunk_num

Number of chunks to be processed by the command (chunks)

thd_num

Substitute the following value.

  • To determine the number of archive files created by the adbarchivechunk command

    Use the following formula to determine this value.

    ↓(value-specified-for-archive-chunk-option-adb_arcv_rthd_num-when-adbarchivechunk-command-is-executed - 1) ÷ 2↓

  • To determine the number of archive files for the archived chunks to be merged by the adbmergechunk command

    Specify the following value:

    value-specified-for-archive-chunk-option-adb_arcv_rthd_num-when-adbarchivechunk-command-is-executed-for-chunks-processed-by-adbmergechunk-command
SGMTGRPNUM

The value of the variable SGMTGRPNUM changes according to the value of the variable sgmtnum_in_chunk. The following table shows the relationship between the variables sgmtnum_in_chunk and SGMTGRPNUM.

Table 5‒10: Relationship between the variables sgmtnum_in_chunk and SGMTGRPNUM

No.

Value of the variable sgmtnum_in_chunk

Value of the variable SGMTGRPNUM

1

sgmtnum_in_chunk < 16

For SGMTGRPNUM, substitute 1.

2

16 ≤ sgmtnum_in_chunk

and

sgmtnum_in_chunk < 32

For SGMTGRPNUM, substitute 4.

3

32 ≤ sgmtnum_in_chunk

and

sgmtnum_in_chunk < 64

For SGMTGRPNUM, substitute 8.

4

64 ≤ sgmtnum_in_chunk

and

sgmtnum_in_chunk < 256

For SGMTGRPNUM, substitute 16.

5

256 ≤ sgmtnum_in_chunk

Use the following formula to determine the value of SGMTGRPNUM:

[Figure]
sgmtnum_in_chunk

Specify the number of segments in the largest table among the chunks to be archived.

Use the following formula to determine this value.

Formula

[Figure]
CHBP(j)

For details, see CHBP(i,k).

CHVP(j)

For details, see CHVP(i,k).

SEGSIZE

See SEGSIZE in (2) Explanation of variables under 5.8.1 Determining the total number of pages in the data DB area.

page_size

See page_size in (2) Explanation of variables under 5.8.1 Determining the total number of pages in the data DB area.

LVP(i)

Use the following formula to determine this value.

Formula

[Figure]
SGLIDX(i)

Number of segments required to store the indexes defined for the location table corresponding to the i-th archivable multi-chunk table

You must determine this variable for only archivable multi-chunk tables.

Use the following formula to determine this value.

Formula

[Figure]
LICHKIDP_LOWER(i)

Number of pages that are used in the lower page segments of the indexes defined for the chunk ID of the chunk for the archive file (pages)

LICHKIDP_UPPER(i)

Number of pages that are used in the upper page segments of the indexes defined for the chunk ID of the chunk for the archive file (pages)

LIRNG01P_LOWER(i)

Number of pages that are used in the lower page segment of the indexes defined for the value range of the archive range column (upper and lower limit values) for the data stored in the archive file (pages)

LIRNG01P_UPPER(i)

Number of pages that are used in the upper page segment of the indexes defined for the value range of the archive range column (upper and lower limit values) for the data stored in the archive file (pages)

LIRNG02P_LOWER(i)

Number of pages that are used in the lower page segment of the indexes defined for the lower limit value of the archive range column for the data stored in the archive file (pages)

LIRNG02P_UPPER(i)

Number of pages that are used in the upper page segment of the indexes defined for the lower limit value of the archive range column for the data stored in the archive file (pages)

For the variables LICHKIDP_LOWER(i), LIRNG01P_LOWER(i), and LIRNG02P_LOWER(i), determine the values by using the formula for determining the variable IP_LOWER(i) in (1) Determining the number of storage pages used in the lower page segment (variable IP_LOWER(i)) under 5.8.3 Determining the number of storage pages for each B-tree index segment. For the variables LICHKIDP_UPPER(i), LIRNG01P_UPPER(i), and LIRNG02P_UPPER(i), determine the values by using the formula for determining the variable IP_UPPER(i) in (2) Determining the number of storage pages used in the upper page segment (variable IP_UPPER(i)) under 5.8.3 Determining the number of storage pages for each B-tree index segment. At this time, substitute values for variables as follows:

R

Substitute 1.5.

key_num

Substitute 0.

dup_key_num

Substitute the value determined from the following formula.

Formula

[Figure]
page_size

Substitute in bytes the page size of the data DB area that stores the archivable multi-chunk table.

pctfree

Substitute 30.

key_dup

Substitute 0.

KEYSZDB

See the following table, and substitute the applicable value.

Table 5‒11: Value to be substituted for the variable KEYSZDB

No.

Variable to be estimated

Data type of the archive range column

Value to be substituted for the variable KEYSZDB

1

The variables to be estimated are as follows:

  • LICHKIDP_LOWER(i)

  • LICHKIDP_UPPER(i)

--

8

2

The variables to be estimated are as follows:

  • LIRNG01P_LOWER(i)

  • LIRNG01P_UPPER(i)

INTEGER

16

3

SMALLINT

8

4

DECIMAL(m,n)

1 ≤ m ≤ 4

4

5

5 ≤ m ≤ 8

8

6

9 ≤ m ≤ 16

16

7

17 ≤ m ≤ 38

32

8

DOUBLE PRECISION

16

9

CHARACTER(p)

2 x p

10

DATE

8

11

TIME

18

12

TIMESTAMP

26

13

The variables to be estimated are as follows:

  • LIRNG02P_LOWER(i)

  • LIRNG02P_UPPER(i)

INTEGER

8

14

SMALLINT

4

15

DECIMAL(m,n)

1 ≤ m ≤ 4

2

16

5 ≤ m ≤ 8

4

17

9 ≤ m ≤ 16

8

18

17 ≤ m ≤ 38

16

19

DOUBLE PRECISION

8

20

CHARACTER(p)

p

21

DATE

4

22

TIME

9

23

TIMESTAMP

13
Legend:

--: Not applicable.

m: Precision

n: Scaling

p: String length (bytes)

(g) Determining the variable SGCOLUMNTBL (for a multi-chunk table)

Because data is compressed when it is stored in a column store table, the number of segments cannot be calculated from the record length in the same way as for row store tables. Instead, the process involves storing a small amount of data and estimating the number of segments based on the resulting compression rate.

Use the following formula to determine this value.

[Figure]
column_tbl_num_in_dbarea

Total number of column store tables in the data DB area (tables)

COLUMNTBLSIZE(i)

Data size of the i-th column store table (megabytes)

Use the following formula to determine this value.

[Figure]
chunk_num

Number of chunks to be created (chunks)

IMPORTDATASIZE(i,k)

Size of the source data (CSV file) stored in the k-th chunk of the i-th column store table (megabytes)

COMPRESSION_RATE

Compression rate of source data

Use the adbimport command to store part of the source data to be stored in the i-th column store table. Then, use the following formula to determine the compression rate:

[Figure]
testdbsize

The amount of space the partial source data imported by the adbimport command occupies in the database (megabytes)

As the partial source data imported to complete the preceding formula, we recommend that you prepare approximately 100 to 500 megabytes of data whose composition is representative of the actual data. When executing the adbimport command, specify 2 for the adb_import_rthd_num import option.

After executing the adbimport command, use the adbdbstatus command to output the Used_segments (number of segments used by the table) value in the table summary information in megabytes. Use this information to check the number of segments used in the database.

For details about the adbimport command and the adbdbstatus command, see the manual HADB Command Reference.

testcsvsize

The size of the CSV file containing the partial source data imported by the adbimport command (megabytes)

COLUMNIZESIZE(i,k)

Size of data that is stored in the k-th chunk of the i-th column store table in column store format by the maintenance processing of the updated-row columnizing facility (megabytes)

Add COLUMNIZESIZE(i,k) only if the updated-row columnizing facility is enabled. If a B-tree index is defined for the i-th column store table, assume that this value is 0 during estimation.

The formula is as follows:

[Figure]
IMPORTDATASIZE(i,k)

Size of the source data (CSV file) stored in the k-th chunk of the i-th column store table (megabytes)

COMPRESSION_RATE

Compression rate of source data

Determine the value as in the explanation of the COMPRESSION_RATE variable shown earlier.

insert_row_num(i,k)

Number of rows that are added or updated by the INSERT or UPDATE statement in the k-th chunk of the i-th column store table

import_row_num(i,k)

Number of rows in the source data (CSV file) stored in the k-th chunk of the i-th column store table

UPDATESEGNUM(i)

The number of segments that will be added when an update SQL statement is executed for the i-th column store table

Use the following formula to determine this value.

[Figure]
SEGSIZE

Segment size (pages)

Use the following formula to determine this value.

SEGSIZE = 4,194,304 ÷ page_size
page_size

The page size of the DB area in which the i-th column store table is defined (bytes)

ADDTBLPGNUM(i,k)

The number of pages of data to be added to the k-th chunk in the i-th column store table by using the INSERT or UPDATE statement

Use the following formula to determine this value.

ADDTBLPGNUM(i,k) = BP(i,k) + VP(i,k)
BP(i,k)

The number of basic row pages of data to be added to the k-th chunk in the i-th column store table by using the INSERT or UPDATE statement

Determine this value by referring to (1) Determining the number of pages for base rows (variable BP(i)) in 5.8.2 Determining the number of pages for storing each type of row based on the number of rows to be added to the k-th chunk in the i-th column store table by using the INSERT or UPDATE statement.

VP(i,k)

The number of branch row pages of data to be added to the k-th chunk in the i-th column store table by using the INSERT or UPDATE statement

Determine this value by referring to (2) Determining the number of pages for branch rows (variable VP(i)) in 5.8.2 Determining the number of pages for storing each type of row based on the number of rows to be added to the k-th chunk in the i-th column store table by using the INSERT or UPDATE statement.

DELTBLPGNUM(i,k)

The number of pages of data to be added to the invalid row information pages for the k-th chunk in the i-th column store table by using the UPDATE or DELETE statement

Use the following formula to determine this value.

DELTBLPGNUM(i,k) = DELLEAFPGNUM(i,k) + DELUPPERPGNUM(i,k)
DELLEAFPGNUM(i,k)

Use the following formula to determine this value.

[Figure]
update_row_num

The number of rows to be updated for the k-th chunk in the i-th column store table by using the UPDATE statement

delete_row_num

The number of rows to be deleted from the k-th chunk in the i-th column store table by using the DELETE statement

page_size

The page size of the DB area in which the i-th column store table is defined (bytes)

DELUPPERPGNUM(i,k)

Use the following formula to determine this value.

[Figure]

The value of the variable SGCOLUMNTBL can also be estimated by assuming that non-compression (NONE) is specified as the compression type for all columns and determining the size accordingly. However, depending on the selected compression type and the compression rate that is achieved, the estimated value might be larger than the actual size of the stored data.

Use the following formula to estimate the value based on the assumption that non-compression (NONE) is specified as the compression type for all columns:

[Figure]
COLUMNTBLSEGNUM(i)

Number of segments for i-th column store table

Use the following formula to determine this value.

[Figure]
COLUMNDATASEGNUM(i,k)

Number of column-data segments for k-th chunk of i-th column store table

Use the following formula to determine this value.

[Figure]
row_num

Number of rows stored in k-th chunk of i-th column store table

column_num

Number of columns in i-th column store table

page_size

Page size of data DB area (bytes)

COL_PAGE_NUM

The value to substitute for the COL_PAGE_NUM variable differs depending on the page size of the data DB area. Substitute the value in the following table that corresponds to the page size of the data DB area:

Table 5‒12: Value to substitute for COL_PAGE_NUM variable

No.

Page size of data DB area (kilobytes)

Value of COL_PAGE_NUM

1

4

1,021

2

8

510

3

16

255

4

32

127
COL_MAX_SIZE

Maximum data length of column data in i-th column store table

Use the following formula to determine this value.

[Figure]
COLUMNDATASIZE(s)

Data length of column data in s-th column

The value to substitute for the COLUMNDATASIZE variable differs depending on the data type of the column data. See the following table, and substitute the applicable value.

Table 5‒13: Data length of each type of column data

No.

Classification

Data type

Data length (bytes)

1

Numeric data

INTEGER

10

2

SMALLINT

6

3

DECIMAL(m, n)#

m ≤ 4

4

4

5 ≤ m ≤ 8

6

5

9 ≤ m ≤ 16

10

6

17 ≤ m

18

7

DOUBLE PRECISION

10

8

Character string data

CHARACTER(n)

n ≤ 127

[Figure]

9

128 ≤ n

10

10

VARCHAR(n)

n ≤ 127

[Figure]

11

128 ≤ n ≤ 255

128

12

256 ≤ n

130

13

Datetime data

DATE

6

14

TIME(p)

[Figure]

15

TIMESTAMP(p)

[Figure]

16

Binary data

BINARY(n)

n ≤ 127

[Figure]

17

128 ≤ n

10

18

VARBINARY(n)

n ≤ 127

[Figure]

19

128 ≤ n ≤ 255

128

20

256 ≤ n

130
Legend:

m, n: Positive integers

p: 0, 3, 6, 9 or 12

#

Indicates a fixed-point number that has a total of m digits, with n digits following the decimal point. If m is omitted, 38 is assumed.

ROWDATASEGNUM(i,k)

Number of row-data segments for k-th chunk of i-th column store table

Use the following formula to determine this value.

[Figure]
row_num

Number of rows stored in k-th chunk of i-th column store table

page_size

Page size of data DB area (bytes)

var_num

Number of columns managed as branch rows (columns)

When the data type is CHAR or BINARY, determine the number of columns whose definition length is 128 bytes or longer.

When the data type is VARCHAR or VARBINARY, determine the number of columns that include data whose actual length is 128 bytes or longer.

var_size

Data length of columns in the branch rows (bytes)

The value to substitute for the var_size variable differs depending on the data type. See the following table, and substitute the applicable value.

Table 5‒14: Value to be substituted for the variable var_size

No.

Classification

Data type

Data length (bytes)

1

Character string data

CHAR

Definition length

2

VARCHAR

d

3

Binary data

BINARY

Definition length

4

VARBINARY

d
Legend:

d: Actual data length

COLUMNIZESEGNUM(i,k)

Number of column-data segments that are stored in the k-th chunk of the i-th column store table by the maintenance processing of the updated-row columnizing facility

Add COLUMNIZESEGNUM(i,k) only if the updated-row columnizing facility is enabled. If a B-tree index is defined for the i-th column store table, assume that this value is 0 during estimation.

The formula is as follows:

COLUMNIZESEGNUM(i,k) = COLUMNDATASEGNUM(i,k)

For details about how to determine the value of COLUMNDATASEGNUM(i,k), see the explanation of the COLUMNDATASEGNUM(i,k) variable shown earlier. When doing so, for the row_num variable, assign the number of rows added or updated by the INSERT or UPDATE statement in the k-th chunk of the i-th column store table.

(h) Determining the variable SGIDX (for a multi-chunk table)

Use the following formula to determine this value.

[Figure]
idx_num_in_dbarea

Total number of B-tree indexes in the data DB area (indexes)

chunk_num

Number of non-archived chunks to be created (chunks)

CHIP_LOWER(i,k)

Number of pages used in the lower page segment of the B-tree index for each chunk

See (1) Determining the number of storage pages used in the lower page segment (variable IP_LOWER(i)) in 5.8.3 Determining the number of storage pages for each B-tree index segment.

At this time, substitute the value estimated from the amount of data to be stored by the adbimport command with the -b option specified for the target chunk, the INSERT statement, and the UPDATE statement.

CHIP_UPPER(i,k)

Number of pages used in the upper page segment of the B-tree index for each chunk

See (2) Determining the number of storage pages used in the upper page segment (variable IP_UPPER(i)) in 5.8.3 Determining the number of storage pages for each B-tree index segment.

At this time, substitute the value estimated from the amount of data to be stored by the adbimport command with the -b option specified for the target chunk, the INSERT statement, and the UPDATE statement.

(i) Determining the variable SGRIX (for a multi-chunk table)

Use the following formula to determine this value.

[Figure]
rng_num_in_dbarea

Number of range indexes in the data DB area (indexes)

chunk_num

Number of non-archived chunks to be created (chunks)

CHRS(i,k)

Number of segments required for storing the range indexes of each chunk

Substitute the value estimated from the amount of data to be stored by the adbimport command with the -b option specified for the target chunk, the INSERT statement, and the UPDATE statement.

(j) Determining the variable SGTIX (for a multi-chunk table)

Use the following formula to determine this value.

[Figure]
idx_num_in_dbarea

Total number of text indexes in the data DB area (indexes)

chunk_num

Number of non-archived chunks to be created (chunks)

CHTIP_STRSEG(i,k)

Number of pages used in the string control segment of the text index for each chunk

See (1) Determining the number of storage pages used in the string control segment (variable TIP_STRSEG(i)) in 5.8.5 Determining the number of storage pages for each text index segment.

At this time, substitute the value estimated from the amount of data to be stored by the adbimport command with the -b option specified for the target chunk, the INSERT statement, and the UPDATE statement.

CHTIP_APPSEG(i,k)

Number of pages used in the position control segment of the text index for each chunk

See (2) Determining the number of storage pages used in the position control segment (variable TIP_APPSEG(i)) in 5.8.5 Determining the number of storage pages for each text index segment.

At this time, substitute the value estimated from the amount of data to be stored by the adbimport command with the -b option specified for the target chunk, the INSERT statement, and the UPDATE statement.

To Page Top

All Rights Reserved. Copyright (C) 2012, 2020, Hitachi, Ltd.