2.2.4 Operands related to SQL : Hitachi Advanced Database Application Development Guide

Specify whether reserved words are to be unregistered if specified as such in the adb_sql_prep_delrsvd_words operand in the server definition.

Check the adb_sql_prep_delrsvd_words operand in the server definition for the reserved words that are unregistered:

Y: Enables the adb_sql_prep_delrsvd_words operand in the server definition (reserved words specified in the adb_sql_prep_delrsvd_words operand are to be unregistered).
N: Disables the adb_sql_prep_delrsvd_words operand in the server definition (reserved words specified in the adb_sql_prep_delrsvd_words operand are not to be unregistered).

If specification of this operand is omitted, Y is assumed.

Specify the transaction isolation level that is to be applied. For details about the transaction isolation levels, see Transaction isolation levels supported by HADB in the HADB Setup and Operation Guide.

READ_COMMITTED: Applies READ COMMITTED as the transaction isolation level.
REPEATABLE_READ: Applies REPEATABLE READ as the transaction isolation level.

If this operand is omitted, the transaction isolation level specified in the adb_sys_trn_iso_lv operand in the server definition is applied.

Multi-node function

When all of the following conditions are met, transaction execution processing is also allocated to secondary nodes and worker nodes:

• The transaction access mode is read-only mode.

• The transaction isolation level is READ COMMITTED.

Therefore, we recommend that you specify READ_COMMITTED in this operand if you want to utilize the resources of secondary nodes and worker nodes.
While transactions that use read/write mode and certain commands are being executed on the primary node, all transactions will be executed on the primary node. Transactions will not be executed on secondary nodes or worker nodes. For details, see Nodes on which transactions and commands are executed in the HADB Setup and Operation Guide.

For details about the commands to which this restriction applies, see Restrictions on simultaneously executing commands with transactions in Nodes on which transactions and commands are executed in the HADB Setup and Operation Guide.

Specify the transaction access mode. For details about the transaction access mode, see the topic Transaction access modes in the HADB Setup and Operation Guide.

READ_WRITE: Applies read/write as the transaction access mode. In this case, a transaction becomes a read/write transaction and can execute all SQL statements.
READ_ONLY: Applies read-only as the transaction access mode. In this case, a transaction becomes a read-only transaction and cannot execute update SQL statements or definition SQL statements.

You can change the specified transaction access mode by setting the following connection attributes:

ODBC functions: SQLSetConnectAttr or SQLSetConnectAttrW
CLI functions: a_rdb_SQLSetConnectAttr()

The transaction access mode is set to READ_WRITE when it is not specified in this operand or with the connection attribute.

Multi-node function:

When all of the following conditions are met, transaction execution processing is also allocated to secondary nodes and worker nodes:

• The transaction access mode is read-only mode.

• The transaction isolation level is READ COMMITTED.

Therefore, we recommend that you specify READ_ONLY in this operand if you want to utilize the resources of secondary nodes and worker nodes.
While transactions that use read/write mode and certain commands are being executed on the primary node, all transactions will be executed on the primary node. Transactions will not be executed on secondary nodes or worker nodes. For details, see Nodes on which transactions and commands are executed in the HADB Setup and Operation Guide.

For details about the commands to which this restriction applies, see Restrictions on simultaneously executing commands with transactions in Nodes on which transactions and commands are executed in the HADB Setup and Operation Guide.

Specify whether the SQL statements issued by the HADB client are to be output to the client message log files and the server message log files.

The maximum length of each SQL statement that is output is 2,048 bytes.

Y: Outputs the SQL statements to the client message log files and the server message log files.
N: Does not output the SQL statements to either the client message log files or the server message log files.

The following table shows the relationship between this operand and the adb_sql_text_out operand in the server definition.

Table 2‒2: Relationship between this operand and the adb_sql_text_out operand in the server definition
adb_sql_text_out operand in the server definition	adb_clt_sql_text_out operand in the client definition
adb_sql_text_out operand in the server definition	Y	N
Y	B	S
N	B	N

Legend:

B: Outputs the SQL statements to both the client message log files and server message log files.

S: Outputs the SQL statements to the server message log files only.

N: Does not output the SQL statements to either the client message log files or the server message log files.

When this operand is specified, the KFAA81002-I message indicating normal termination of a transaction is output to the server message log files. This message is not output to the client message log files.

If specification of this operand is omitted, N is assumed.

Specifies the sort order of character string data when the following ORDER BY clauses are specified:

ORDER BY clauses specified in the outermost query expression of the SELECT statement
ORDER BY clauses specified in the outermost query expression body of the COPY statement

BYTE

Sort character string data by bytecode.

ISO

Sort character string data by sort code (ISO/IEC 14651:2011 compliance).

ISO cannot be specified in this operand when SJIS is specified in the ADBCLTLANG environment variable.

You can also use the adb_sql_order_mode server definition operand and the connection attributes to specify the sort order for character string data. If this sort order is specified by more than one of these methods, the specification to be used is determined in the priority shown below (the smaller the numeric value, the higher the priority).

Table 2‒3: Priority for the sort order of character string data
Priority	Location of specification of the character string data sort order
1	Connection attributes
2	adb_clt_sql_order_mode client definition operand
3	adb_sql_order_mode server definition operand

Explanation:

For example, if BYTE is specified in the adb_clt_sql_order_mode client definition operand and ISO is specified in the adb_sql_order_mode server definition operand, BYTE is applied to the following ORDER BY clauses:

ORDER BY clauses specified in the outermost query expression of the SELECT statement
ORDER BY clauses specified in the outermost query expression body of the COPY statement

BYTE is assumed when the specification is omitted at all locations shown in the table.

Additionally, regardless of the value specified for this operand, the character string data in the ORDER BY clause specified in subqueries or WITH list elements is sorted in bytecode order.

Specify the minimum scaling value of the result of a division operation (arithmetic operation) specified in an SQL statement when the data type of the result is DECIMAL.

INTEGRAL_PART: The minimum scaling value of the result of he division operation is 0. If you specify INTEGRAL_PART, the number of digits in the integer part has priority. Specify INTEGRAL_PART when the division result might be a large value and you want to avoid overflow errors where possible.
FRACTIONAL_PART: The scaling of the first operand (dividend) of the division operation is used as the minimum scaling value of the division result. When you specify FRACTIONAL_PART, the number of decimal places in the first operand is the minimum number of decimal places in the division result.

When the first operand is DECIMAL(p1,s1) and the second operand is DECIMAL(p2,s2), the scaling of the division result is DECIMAL(38,s).

When INTEGRAL_PART is specified

s = MAX{0,38 - (p1 - s1 + s2)}
When FRACTIONAL_PART is specified

s = MAX{s1,38 - (p1 - s1 + s2)}

The following example shows how value specified for this operand affects the division result.

Example:

[Figure]

Suppose that the following SELECT statement is executed:

SELECT "C1"/"C2" AS "division result" FROM "T1"

Division result when INTEGRAL_PART is specified

584.
Division result when FRACTIONAL_PART is specified

584.4457

For an example of the scaling of the division result when retrieving data from a viewed table, see Notes applying when the data type of the division result is DECIMAL in the manual HADB SQL Reference.

If you omit this operand, the value specified for the adb_sql_prep_dec_div_rs_prior operand in the server definition applies.

Specify the sort position of null values when the null-value sort order specification is omitted from SQL statements.

HIGH:: Null values are sorted to a higher position.
LOW:: Null values are sorted to a lower position.

The sort position of null values can be specified in the following locations: 1. has the highest priority.

The null-value sort order specification in the SQL statement
The adb_sql_prep_null_collation operand in the client definition

If you omit this operand, the value specified for the adb_sql_prep_null_collation operand in the server definition is assumed.
The adb_sql_prep_null_collation operand in the server definition

The following table shows the sorting order of null values when the null-value sort order specification in 1. above is omitted.

Table 2‒4: Sort order of null values when the null-value sort order specification is omitted
Specified value of the adb_sql_prep_null_collation operand	Specified value of the order specification	Sort order of null values
HIGH or omitted	ASC or omitted	Null values are sorted to the end.
HIGH or omitted	DESC	Null values are sorted to the beginning.
LOW	ASC or omitted	Null values are sorted to the beginning.
LOW	DESC	Null values are sorted to the end.

Note: The null-value sort order specification and order specification are specified in the sort specification list in the SQL statement. For details about the null-value sort order specification and the order specification, see Specification format for sort specification lists in the manual HADB SQL Reference.