Hitachi

Hitachi Advanced Database Application Development Guide

2.2.4 Operands related to SQL

• adb_sql_prep_delrsvd_use_srvdef ={Y|N}

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.

• adb_clt_trn_iso_lv = {READ_COMMITTED|REPEATABLE_READ}

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 both of the following conditions are met, transaction execution processing is also allocated to the slave node:

    • The transaction access mode is read-only mode.

    • The transaction isolation level is READ COMMITTED.

    We recommend that you specify READ_COMMITTED in this operand if you want to utilize the resources of the slave node.

  • When transactions that use read/write mode and certain commands are running on the master node, all transactions run on the master node for the duration of that transaction or command. No transactions run on the slave node during this time. 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.

• adb_clt_trn_access_mode = {READ_WRITE|READ_ONLY)

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 both of the following conditions are met, transaction execution processing is also allocated to the slave node:

    • The transaction access mode is read-only mode.

    • The transaction isolation level is READ COMMITTED.

    We recommend that you specify READ_ONLY in this operand if you want to utilize the resources of the slave node.

  • When transactions that use read/write mode and certain commands are running on the master node, all transactions run on the master node for the duration of that transaction or command. No transactions run on the slave node during this time. 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.

• adb_clt_sql_text_out ={Y|N}

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‒1: 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

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.

• adb_clt_sql_order_mode= {BYTE|ISO}

Specify the sort order for character string data in a SELECT statement in which the ORDER BY clause is specified.

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‒2: 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 SELECT statements (in which the ORDER BY clause is specified) that are executed from the application program.

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

• adb_sql_prep_dec_div_rs_prior = {INTEGRAL_PART|FRACTIONAL_PART}

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.

To Page Top

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