Hitachi

Hitachi Advanced Database Setup and Operation Guide

7.2.11 Operands and options related to global buffers (command format)

[51] adbbuff

Defines the content of the global buffers to be allocated to a data DB area. You can specify this operand multiple times.

When you specify this operand, use a value such that the combined total value of the following two variables will be 30 to 40% of the maximum size of the memory used by the HADB server, as determined with reference to 6.3 Estimating the HADB server's memory requirement.

Note that because global buffers are allocated automatically to the master directory DB area, dictionary DB area, system-table DB area, and work table DB area, you do not need to define them in this operand.

You can use the adbls -d gbuf command to check the contents of the global buffers allocated by this operand. For details about the adbls -d gbuf command, see adbls -d gbuf (Display Global Buffer Information) in the manual HADB Command Reference.

Note

A global buffer for global work tables and a buffer for local work tables are allocated to the work table DB area. The following describe their specification methods:

  • How to specify a global buffer for global work tables

    You can specify the number of pages in the global buffer for global work tables in the adb_dbbuff_wrktbl_glb_blk_num server definition operand explained in 7.2.2 Operands related to performance (set format). The name of the global buffer for global work tables is ADBWRK.

  • Specifying a buffer for local work tables

    You can specify the number of pages in the buffer for local work tables in the adb_dbbuff_wrktbl_clt_blk_num server definition operand explained in 7.2.2 Operands related to performance (set format).

    You can also specify the number of pages in the buffer for local work tables in the adb_dbbuff_wrktbl_clt_blk_num operand in the client definition and the export option adb_export_wrktbl_blk_num.

    For details about the adb_dbbuff_wrktbl_clt_blk_num operand in the client definition, see Operands related to performance in the HADB Application Development Guide. For details about the export option adb_export_wrktbl_blk_num, see Format of export options in Specification format for the adbexport command under adbexport (Export Data) in the manual HADB Command Reference.

-g global-buffer-name

~<global buffer name> ((1 to 30 bytes))

Specify a global buffer name that is unique within the HADB server.

The naming rules for global buffer names are as follows:

  • The permitted characters include single-byte numeric characters, single-byte uppercase letters, single-byte lowercase letters, half-width katakana characters, single-byte spaces, single-byte underscores (_), and single-byte hyphens (-). Double-byte characters are also permitted. Double-byte spaces are not permitted.

  • Single-byte characters can be intermixed with double-byte characters.

  • Single-byte uppercase letters are always distinguished from single-byte lowercase letters.

  • The first character must be a single-byte uppercase letter, a single-byte lowercase letter, or a half-width uppercase katakana character ([Figure]). Alternatively, specify a double-byte character.

  • The last character cannot be a single-byte space.

  • To include a single-byte space or a single-byte hyphen (-) in the a global buffer name, enclose the entire global buffer name in \" (backslash followed by a double quotation mark). Then, enclose the entire name in double quotation marks (").

    Example

  adbbuff -g "\"Sample Buffer01\"" -n AREA01 -p 1024
  adbbuff -g "\"Sample-Buffer02\"" -n AREA02 -p 1024

Do not specify in this option a name that includes ##ADBOTHER. Names that include ##ADBOTHER are sometimes used by the HADB server for allocating global buffers automatically. If you specify a name that includes ##ADBOTHER, it might not be possible to differentiate global buffers based on their names.

Tip
• Choice of the global buffer allocation method (-n or -o option)

To specify the DB area to which you want to allocate the global buffer, specify the -n option in normal cases. To allocate the global buffer to all DB areas that are not specified in the -n option, specify the -o option.

-n DB-area-name[,DB-area-name]...

~<character string> ((1 to 30 bytes))

Specify the names of the DB areas to which the global buffer are to be allocated.

For details about the naming rules for DB area names, see Specifying names in the manual HADB SQL Reference.

Note the following when specifying this option:

  • When specifying multiple DB area names

    By specifying multiple DB area names in this option, you can allocate multiple DB areas to a single global buffer. When specifying multiple DB area names, specify only DB areas having the same page size. If you specify DB areas having different page sizes, an error occurs.

  • When specifying a data DB area that stores a column store table

    When specifying a data DB area that stores a column store table, we recommend that you define an adbbuff operand that specifies only the name of this data DB area in this option. That is, specify this option in such a way that a dedicated global buffer is assigned to data DB areas that store column store tables.

    Assigning a data DB area that stores a row store table or index to the same global buffer as a data DB area that stores a column store table might impact retrieval performance. That is, retrieval from the row store table or retrieval using the index might be slower.

  • When specifying a dictionary DB area

    If a message is output asking you to allocate a global buffer to a dictionary DB area, define a new adbbuff operand with only ADBDIC specified for this option. Normally, there is no need to specify a dictionary DB area in this option.

  • When specifying a system-table DB area

    If a message is output asking you to allocate a global buffer to the system-table DB area, define a new adbbuff operand with only ADBSTBL specified for this option. Normally, there is no need to specify a system-table DB area in this option.

If no allocation of global buffers is made to a DB area by means of the -n option, the same global buffer as when the -o option is specified is allocated automatically to that DB area. In this case, ##ADBOTHER is specified for the -g option.

-o

Specifies that global buffers are to be allocated to all DB areas that are not specified in the -n option.

You can only have one adbbuff operand that specifies this option.

When this option is specified, DB areas that have the same page size are grouped together and a global buffer is allocated to each such group. The name of each global buffer is -g global-buffer-name + #nnnnnnnnnn (where nnnnnnnnnn is the page size expressed as a 10-digit decimal number). An example follows.

Example:

  • Specification of adbbuff operand

    adbbuff -g gbuf01 -o

  • DB areas that are not allocated to global buffers in the -n option.

    - DB areas with page sizes of 4 KB: ADBDIC, ADBSTBL, ADBU00011, and ADBU00012

    - DB area with page sizes of 32 KB: ADBU00013

Explanation

In this case, the HADB server creates the following two global buffers:

  • gbuf01#0000004096

    ADBDIC, ADBSTBL, ADBU00011, and ADBU00012 are allocated to this global buffer.

  • gbuf01#0000032768

    ADBU00013 is allocated to this global buffer.

If global buffers are allocated to all DB areas by using the -n option, specification of the -o option is ignored.

-p number-of-pages-in-global-buffer

~<integer> ((0 to 2,147,483,647)) <<0>>

Specify the number of pages in the global buffers to be allocated to DB areas.

If you omit this option or specify 0 for it, the value calculated using the following formula is assumed:

Formula

[Figure]
-a number-of-pages-in-global-buffer-dedicated-to-range-indexes

~<integer> ((0 to 2,147,483,647))

This option is related to range indexes.

Specify the number of pages in the global buffer to be used exclusively for range indexes.

We recommend that you specify the value determined from the following formula. For a multi-chunk table, use the following formula to determine the value for each chunk. Then, specify in this option the sum of the values you determined.

Formula

[Figure]
rngidx_num

Number of range indexes that are stored in the DB area specified in this adbbuff operand

SGRI(i)

See (1) Determining the SGRI variable in 5.8.6 Determining the number of segments for storing each range index.

SEGSIZE(i)

Segment size in the DB area that stores range indexes (pages)

Use the following formula to determine its value:

[Figure]
page_size(i)

Page size of the DB area that stores range indexes (bytes)

SAUSEDPAGENUM(i)

Number of pages used by each range index (pages)

Use the following formula to determine its value:

[Figure]
PTNUM(i)

Number of pointers used by each range index (pointers)

Use the following formula to determine its value:

[Figure]
tbl_dbareafile_num(i)

Number of DB area files that store tables for which range indexes are defined (files)

tbl_dbarea_initsize(i)

Initial allocation size of the DB area that stores tables for which range indexes are defined (gigabytes)

Substitute the value that was specified for the initial allocation size option when one of the following commands was executed for the target DB area.

  • The value specified for the -i option, which is an initialization option of the adbinitdbarea operand of the adbinit command

  • The value specified for the -i option, which is a DB area addition and modification option of the adbaddarea operand of the adbmodarea command

  • The value specified for the -i option, which is a DB area addition and modification option of the adbexpandarea operand of the adbmodarea command

Note

  • For details about the initialization options of the adbinit command, see Format of initialization options in Specification format for the adbinit command under adbinit (Initialize the Database) in the manual HADB Command Reference.

  • For details about the DB area addition and modification options of the adbmodarea command, see Format of DB area addition and modification options in Specification format for the adbmodarea command under adbmodarea (Add and Change DB Areas) in the manual HADB Command Reference.

Note the following points:

  • The global buffer pages specified by the -a option are allocated separately from the global buffer pages specified by the -p option.

  • If you specify the -a option, range indexes do not use the global buffer pages specified by the -p option.

  • If you omit the -a option, range indexes use the global buffer pages specified by the -p option.

-v memory-size-used-for-table-scan-buffer[,maximum-memory-size-used-per-real-thread]

Specifying this option allocates a table scan buffer to improve the processing speed of table scans.

The allocated table scan buffer is applied to the following segments:

  • Basic row segment (for a table in row store format)

  • Column-data segment (for a table in column store format)

Application targets are only the preceding segments.

Important

Specify this option when you want to allocate a table scan buffer in HADB server version 04-01 or later. Do not specify the -k option.

If you omit both this option and the -k, the system does not allocate a table scan buffer.

memory-size-used-for-table-scan-buffer:

~<integer> ((50 to 100,000,000)) (megabytes)

Specify, in megabytes, the amount of memory to assign to the table scan buffer used during execution of a table scan.

The memory to be assigned to the table scan buffer is allocated according to the value specified in this option when the HADB server starts.

The table scan buffer is designed to improve the table scan processing speed. Batch-reading multiple pages from a database into the table scan buffer can reduce the number of I/O operations. The HADB server accesses the pages in the table scan buffer at the page level, in the same manner as those in the global buffer.

When you specify this option, a table scan buffer is allocated. This means that the table scan buffer rather than the global buffer is used when performing a table scan. Use of a table scan buffer might improve the execution speed of the following SQL statements:

  • An SQL statement that becomes a simple table scan

  • An SQL statement in which the outermost query specification becomes a table scan

If the table scan buffer allocated for table scanning is insufficient, global buffers are used.

If multiple table scans take place during execution of an SQL statement, the memory allocated to the real threads used to execute that SQL statement are re-used. For example, this applies to a situation when a table scan is executed for multiple tables. The memory allocated to the real threads that execute each SQL statement are deallocated when the cursor is closed.

When you specify this option, specify a value that is between the minimum memory size and the maximum memory size determined from following two formulas:

Formula (value specified for memory-size-used-for-table-scan-buffer in -v option)

[Figure]
Note

The memory size specified in this option includes the following two types of memory:

  • Global buffer pages used to read data from the database

  • Process common memory used to manage the table scan buffer

For details about the page size of the global buffer pages used to read data from the database, see the description of the variable SCANPAGES in (2) Determining the global buffer page requirement (for starting the HADB server) under 6.3.3 Determining the memory requirement for starting the HADB server.

maximum-memory-size-used-per-real-thread:

~<integer> ((50 to 100,000,000)) (megabytes)

Specify, in megabytes, the maximum memory size of the table scan buffer available to each real thread.

If you do not specify a value, no maximum memory size is set.

When you specify this option, when the memory allocated to the real threads that execute each SQL statement reaches the maximum memory size, the global buffer is used instead of the table scan buffer to fulfill further memory requirements.

If the number of concurrently executed SQL statements that include table scans is unknown, we recommend that you do not specify a value. This is to ensure that the table scan buffer can be used in the most efficient way.

If the number of concurrently executed SQL statements that include table scans is known and you want the table scan buffer to be split evenly among them, specify the value determined by the following formula:

Formula (value to specify for maximum-memory-size-used-per-real-thread in -v option)

Value specified for maximum-size-of-memory-used-per-real-thread in the -v option =
    value specified for size-of-memory-used-for-table-scan-buffer in the -v option ÷
    (number of concurrently executed SQL statements that include table scans
      × value specified for the adb_sql_exe_max_rthd_num operand in the server definition)
-k number-of-sectors-in-table-scan-buffer-for-batch-loading-segments

~<integer> ((1 to 20,000,000))

Important

There is no need to specify this operand in HADB server version 04-01 or later. Specify the -v option when you want to allocate a table scan buffer.

You cannot specify this option and the -v option together. If you specify both options, the HADB server cannot start (the message KFAA51223-E is output).

If you omit both this option and the -v option, the system does not allocate a table scan buffer.

If you specify a value of 12 or lower for this option, the system does not allocate a table scan buffer (the message KFAA51204-W is output).

Specify the number of buffer sectors in the table scan buffer for batch-reading segments when a table scan is performed. The size of a sector in the table scan buffer is the same as the segment size, which is 4 megabytes.

The table scan buffer is designed to improve the table scan processing speed. Batch-reading multiple pages from a database into the table scan buffer at the segment level can reduce the number of I/O operations. This might improve the processing speed.

The HADB server accesses the pages in the table scan buffer, which were read in units of segments, on the same page-by-page basis as it accesses the pages in the global buffer.

Specifying this option allocates the table scan buffer. Therefore, the table scan buffer, rather than the global buffer, is used when a table scan is performed. Using the table scan buffer might improve the execution speed of the following SQL statements:

  • An SQL statement that becomes a simple table scan

  • An SQL statement in which the outermost query specification becomes a table scan

When you specify this option, specify a value that is between the minimum number of buffer sectors and the maximum number of buffer sectors determined from the two formulas shown in the following.

Formula (value specified in the -k option)

[Figure]

When you specify this option, we recommend that you specify the server definition so that the formula shown below is satisfied. Make adjustments so that the values on the left and right sides of the equation are as close to each other as possible.

Formula

[Figure]

If the table scan buffer allocated for table scanning is insufficient, global buffers are used. You can use the adbstat command to check whether a table scan buffer of sufficient size has been allocated. For details, see 13.2.5 Reducing the execution time of SQL statements that perform table scans.

For details about table scanning, see How to retrieve tables in Designs Related to Improvement of Application Program Performance in the HADB Application Development Guide.

To Page Top

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