2.2.3 Operands related to performance : Hitachi Advanced Database Application Development Guide

~<integer>((1 to 65,535))<<1,024>>

Specify the maximum number of rows that are to be sent as retrieval results from the HADB server to the HADB client by a single FETCH process.

If you specify a large value in this operand, an improvement in performance can be expected because more result rows are sent per FETCH process, but more memory is required.

~<integer>((5 to 100,000,000))<<value of adb_dbbuff_wrktbl_clt_blk_num in the server definition>>

Specify the number of local work table buffer pages.

Normally, you do not need to specify this operand. Specify this operand to reduce the execution time of SQL statements that create local work tables. For details, see Tuning to shorten SQL statement execution time by re-examining the buffers in Tuning in the HADB Setup and Operation Guide.

For details about how to estimate an appropriate value for this operand, see Estimating the number of pages in the buffer for local work tables in the HADB Setup and Operation Guide.

The following notes apply to this operand:

If this operand and the adb_dbbuff_wrktbl_clt_blk_num operand in the server definition are both specified, this operand value takes effect.
The buffer specified in this operand is used for an SQL processing real thread only when SQL statements for creating local work tables are executed. Local work tables are specific to real threads, and one local work table is created for each real thread. Therefore, the HADB server allocates for each real thread the amount of local work table buffer space as matches the number of pages specified in this operand. For details about the SQL statements for creating local work tables, see 5.10.2 Work tables created when SQL statements are executed.
The adbmodbuff command cannot be used to change the number of local work table buffer pages for a connection to which this operand is applied.
You can use the adbls -d lbuf command to check the number of local work table buffer pages that are applied for each connection.

~<integer>((0 to 4,096)) <<value of adb_sql_exe_max_rthd_num in the server definition or maximum number of processing real threads usable by the group>>

Specify the maximum number of processing real threads that are to be used during SQL statement execution.

Specify this operand if you are changing the maximum number of processing real threads for SQL statement execution that was specified in the server definition's adb_sql_exe_max_rthd_num operand.

When you specify this operand, see the explanation of the adb_sql_exe_max_rthd_num operand under the topic Operands related to performance (set format) in Detailed descriptions of the server definition operands in Designing the Server Definition in the HADB Setup and Operation Guide.

■ When not using the client-group facility

If you omit this operand, the value specified for the adb_sql_exe_max_rthd_num operand in the server definition is assumed.
If the value you specify in this operand is greater than the value specified in the adb_sql_exe_max_rthd_num operand in the server definition, the value specified in this operand is ignored. In this case, the value specified for the adb_sql_exe_max_rthd_num operand in the server definition is assumed.

■ When using the client-group facility

If you omit this operand, the maximum number of processing real threads usable by the group is assumed.
If the value you specify in this operand is greater than the maximum number of processing real threads usable by the group, the value specified in this operand is ignored. In this case, the maximum number of processing real threads usable by the group is assumed.

For details about the client-group facility, see Client-group facility in the HADB Setup and Operation Guide.

■ Relationship between this operand and the setHADBSQLMaxRthdNum method of the JDBC driver

The setHADBSQLMaxRthdNum method can be used to specify the maximum number of SQL processing real threads.

The following table shows which of the values is applied to this operand according to the relationships among them and depending on whether the setHADBSQLMaxRthdNum method is specified.

For details about the setHADBSQLMaxRthdNum method, see 8.2.37 setHADBSQLMaxRthdNum(int rthdNum).

Whether the `setHADBSQLMaxRthdNum` method is specified	Relationship between SV and CGV	Relationships among V, SV, and CGV	Value applied to this operand
Specified	`CGV` < `SV`	`SV` < `V`	`CGV`^#1
		`CGV` < `V` ≤ `SV`	`CGV`^#1
		`V` ≤ `CGV`	`V`
	`SV` ≤ `CGV`	`V` ≤ `SV`	`V`
		`SV` < `V` ≤ `CGV`	`SV`^#1
		`CGV` < `V`	`SV`^#1
	`CGV` not specified^#2	`V` ≤ `SV`	`V`
	`CGV` not specified^#2	`SV` < `V`	`SV`^#1
Not specified	--	--	`CNV`

Legend:

V: Value specified for the setHADBSQLMaxRthdNum method

SV: Value in the server definition^#3

CGV: Maximum number of processing real threads usable by a group if the client-group facility is used

CNV: Maximum number of SQL processing real threads determined when a connection to the HADB server is established^#4

--: No condition

#1:

Disables the value specified for the setHADBSQLMaxRthdNum method. At this time, the KFAA41106-W message is output to indicate that the specified value was disabled.

#2:

This is the case where no client-group facility is used.

#3:

The maximum number of SQL processing real threads determined when the HADB server starts. This value is determined by conditions, such as whether the adb_sql_exe_max_rthd_num operand is specified in the server definition, and the magnitude relationship with the number of processing real threads. For details, see the explanation of the adb_sql_exe_max_rthd_num operand in Operands related to performance (set format) in the HADB Setup and Operation Guide.

#4:

This value is determined by the following conditions: 1) whether the adb_sql_exe_max_rthd_num operand is specified in the client definition, 2) the value specified for the adb_sql_exe_max_rthd_num operand in the server definition, and 3) the magnitude relationship with the maximum number of processing real threads usable by a group of the client-group facility.

Important: The HADB server references the value of this operand when performing the preprocessing of an SQL statement. Therefore, if you use the setHADBSQLMaxRthdNum method to specify the maximum number of SQL processing real threads, make sure that the setHADBSQLMaxRthdNum method is run before the Statement or PreparedStatement object that executes the SQL statement is generated.

~<integer>((0, 4 to 1,000,000))<<value of adb_sql_exe_hashgrp_area_size in the server definition>> (kilobytes)

Specify a size (in kilobytes) for the hash grouping area.

Specify this operand if you are changing the hash grouping area size that was specified in the server definition's adb_sql_exe_hashgrp_area_size operand.

When you specify this operand, see the explanation of the adb_sql_exe_hashgrp_area_size operand under the topic Operands related to performance (set format) in Detailed descriptions of the server definition operands in Designing the Server Definition in the HADB Setup and Operation Guide.

~<integer>((0 to 4,194,304))<<value of adb_sql_exe_hashtbl_area_size in the server definition>>(megabytes)

Specify the size (in megabytes) of the hash table area.

Use this operand to change the size of the hash table area specified in the adb_sql_exe_hashtbl_area_size operand in the server definition.

When you specify this operand, see the explanation of the adb_sql_exe_hashtbl_area_size operand under the topic Operands related to performance (set format) in Detailed descriptions of the server definition operands in Designing the Server Definition in the HADB Setup and Operation Guide.

If this operand's value is greater than the value of the adb_sql_exe_hashtbl_area_size operand in the server definition, the specification in the server definition is assumed. The specification in the server definition is assumed also when this operand is omitted.

■ Relationship between this operand and the setHADBSQLHashTblSize method of the JDBC driver

The setHADBSQLHashTblSize method can be used to specify the size of the hash table area.

The following table shows which of the values is applied to this operand according to the relationships among them and depending on whether the setHADBSQLHashTblSize method is specified.

For details about the setHADBSQLHashTblSize method, see 8.2.36 setHADBSQLHashTblSize(int areaSize).

Whether the `setHADBSQLHashTblSize` method is specified	Relationship between V and SV	Value applied to this operand (in megabytes)
Specified	`SV` < `V`	`SV`^#1
Specified	`V` ≤ `SV`	`V`
Not specified	--	`CNV`

Legend:

V: Value specified for the setHADBSQLHashTblSize method

SV: Value in the server definition^#2

CNV: Size of the hash table area determined when a connection to the HADB server is established^#3

--: No condition

#1:

Disables the value specified for the setHADBSQLHashTblSize method. At this time, the KFAA41106-W message is output to indicate that the specified value was disabled.

#2:

The size of the hash table area determined when a connection to the HADB server is established. This is the value specified for the adb_sql_exe_hashtbl_area_size operand in the server definition. If the adb_sql_exe_hashtbl_area_size operand in the server definition is omitted, the default value is applied.

#3:

This value is determined by conditions such as: whether the adb_sql_exe_hashtbl_area_size operand is specified in the client definition, and the magnitude relationship between the values specified for the adb_sql_exe_hashtbl_area_size operand in the server definition and the adb_sql_exe_hashtbl_area_size operand in the client definition.

Important: The HADB server references the value of this operand when performing the preprocessing of an SQL statement. Therefore, if you use the setHADBSQLHashTblSize method to specify the size of the hash table area, make sure that the setHADBSQLHashTblSize method is run before the Statement or PreparedStatement object that executes the SQL statement is generated.

~<integer>((0 to 419,430)) (megabytes)

Specify the size (in megabytes) of the hash filter area.

Normally, you will omit this operand. Specify this operand to reduce the execution time of SQL statements to which a hash filter is applied.

When you specify this operand, see the explanation of the adb_sql_exe_hashflt_area_size operand in Operands related to performance (set format) in Detailed descriptions of the server definition operands in Designing the Server Definition in the HADB Setup and Operation Guide.

For details about how to tune the value to be specified for this operand, see Tuning to shorten SQL statement execution time by re-examining the hash filter area size in the HADB Setup and Operation Guide.

The following rules apply to this operand:

The value that is assumed when this operand is omitted varies depending on whether the adb_sql_exe_hashtbl_area_size operand is specified in the client definition.
- If the adb_sql_exe_hashtbl_area_size operand is specified:
  
  ↑ Value of the adb_sql_exe_hashtbl_area_size operand in the client definition ÷ 10 ↑
- If the adb_sql_exe_hashtbl_area_size operand is not specified:
  
  Value of the adb_sql_exe_hashflt_area_size operand in the server definition
Note that if the value of the adb_sql_exe_hashtbl_area_size operand in the client definition is greater than the value of this operand in the server definition, the value of the server definition is assumed.
If the following condition is satisfied, the value of the adb_sql_exe_hashflt_area_size operand in the server definition (not in the client definition) is applied:
```
A < B or C
```
A: Value specified for the adb_sql_exe_hashflt_area_size operand in the server definition

B: Value specified for the adb_sql_exe_hashflt_area_size operand in the client definition

C: Value that is assumed if the adb_sql_exe_hashflt_area_size operand is omitted in the client definition

■ Relationship between this operand and the setHADBSQLHashFltSize method of the JDBC driver

The setHADBSQLHashFltSize method can be used to specify the size of the hash filter area. The following table shows which of the values is applied to this operand according to the relationships among them and depending on whether the setHADBSQLHashFltSize method is specified.

For details about the setHADBSQLHashFltSize method, see 8.2.35 setHADBSQLHashFltSize(int areaSize).

Whether the `setHADBSQLHashFltSize` method is specified	Relationship between V and SV	Relationship between HTV and SV	Value applied to this operand (in megabytes)
Specified	`SV` < `V`	--	`SV`^#1
Specified	`V` ≤ `SV`	--	`V`
Not specified	--	`SV` < ↑`HTV` ÷ 10↑	`SV`
		↑`HTV` ÷ 10↑ ≤ `SV`	↑`HTV` ÷ 10↑
		`HTV` not specified^#2	`CNV`

Legend:

V: Value specified for the setHADBSQLHashFltSize method

SV: Value in the server definition^#3

HTV: Size of the hash table area^#4

CNV: Size of the hash filter area determined when a connection to the HADB server is established^#5

--: No condition

#1:

Disables the value specified for the setHADBSQLHashFltSize method. At this time, the KFAA41106-W message is output to indicate that the specified value was disabled.

#2:

This is the case where the setHADBSQLHashTblSize method is not specified.

#3:

The size of the hash filter area determined when a connection to the HADB server is established. This value is determined by conditions, such as whether the adb_sql_exe_hashflt_area_size operand is specified in the server definition, and the magnitude relationship with the number of processing real threads. For details, see the explanation of the adb_sql_exe_hashflt_area_size operand in Operands related to performance (set format) in the HADB Setup and Operation Guide.

#4:

The size of the hash table area that was last applied if the setHADBSQLHashTblSize method is specified.

#5:

This value is determined by conditions such as: whether the adb_sql_exe_hashflt_area_size operand is specified in the client definition, and the magnitude relationship between the values specified for the adb_sql_exe_hashflt_area_size operand in the server definition and the adb_sql_exe_hashflt_area_size operand in the client definition.

Important: The HADB server references the value of this operand when performing the preprocessing of an SQL statement. Therefore, if you use the setHADBSQLHashFltSize method to specify the size of the hash filter area, make sure that the setHADBSQLHashFltSize method is run before the Statement or PreparedStatement object that executes the SQL statement is generated.