Hitachi

Hitachi Advanced Database Setup and Operation Guide

7.2.5 Operands related to SQL statements (set format)

[29] adb_sql_prep_delrsvd_words = reserved-word-to-be-unregistered[,reserved-word-to-be-unregistered]...

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

Specify the words to be unregistered as HADB reserved words. You can specify up to 999 reserved words for unregistration.

To unregister a reserved word, specify it in all uppercase letters enclosed in the backslash and double quotation mark (\"), or specify it in all uppercase or all lowercase letters not enclosed in the backslash and double quotation mark (\"). The following are specification examples in which the specified reserved word will be unregistered correctly.

Example: Specification examples to unregister the reserved word ABS

  • adb_sql_prep_delrsvd_words = \"ABS\"

  • adb_sql_prep_delrsvd_words = ABS

  • adb_sql_prep_delrsvd_words = abs

Note

A character string that is enclosed in the backslash and double quotation mark (\") is treated as being case-sensitive. Therefore, if you specify a character string consisting entirely of lowercase letters enclosed in the backslash and double quotation mark, such as \"abs\", the character string is regarded as lowercase only. However, because there are no lowercase reserved words in HADB, you cannot unregister a reserved word by enclosing a character string consisting of all lowercase letters in the backslash and double quotation mark.

Note that the reserved words that can be unregistered with the adb_sql_prep_delrsvd_words operand are predetermined. If a specified word is not a reserved word or is a reserved word that cannot be unregistered, the KFAA51111-W warning message will be issued.

For details about reserved words that cannot be unregistered, see List of reserved words in the manual HADB SQL Reference.

Multi-node function

If you are using the multi-node function, specify the same value for this operand for all of the HADB servers on all nodes. When you do so, also specify the reserved words in the same order.

[30] adb_sys_trn_iso_lv = {READ_COMMITTED|REPEATABLE_READ}

Specify the transaction isolation level to be applied. For details about transaction isolation levels, see 2.9.2 Transaction isolation levels supported by HADB.

  • READ_COMMITTED

    Applies READ COMMITTED as the transaction isolation level.

  • REPEATABLE_READ

    Applies REPEATABLE READ as the transaction isolation level.

Multi-node function

If you are using the multi-node function, specify the same value for this operand for all of the HADB servers on all nodes.

[31] adb_sql_text_out = {Y|N}

Specify whether to output all SQL statements accepted by the HADB server to the server message log file.

When Y is specified, the first 2,048 bytes of each SQL statement are output.

  • Y

    Output SQL statements to the server message log file.

  • N

    Do not output SQL statements to the server message log file.

Even when N is specified for this operand or this operand is omitted, SQL statements are output to the server message log file if Y is specified for the adb_clt_sql_text_out operand in the client definition.

For details about the adb_clt_sql_text_out operand, see Operands related to SQL in the HADB Application Development Guide.

[32] adb_sql_trc_out = {Y|N}

Specify whether SQL trace information is to be output when SQL statements are executed.

  • Y

    Output SQL trace information to SQL trace files.

  • N

    Do not output SQL trace information to SQL trace files.

When N is specified in this operand, SQL trace information is not output regardless of the values specified in the following operands:

  • adb_sql_trc_param operand in the server definition

  • adb_sql_trc_accesspath operand in the server definition

  • adb_sql_trc_level operand in the server definition

  • adb_sql_trc_txtfile_size operand in the server definition

[33] adb_sql_trc_param = {Y|N}

Specify whether information about dynamic parameters is to be output as part of the SQL trace information.

  • Y

    Output information about dynamic parameters as part of the SQL trace information.

  • N

    Do not output information about dynamic parameters as part of the SQL trace information.

When Y is specified in this operand but N is specified in the adb_sql_trc_out operand in the server definition, information about dynamic parameters is not output.

[34] adb_sql_trc_accesspath = {Y|N}

Specify whether access path information and access path statistical information are to be output as part of the SQL trace information.

  • Y

    Output access path information and access path statistical information as part of the SQL trace information.

  • N

    Do not output access path information and access path statistical information as part of the SQL trace information.

When Y is specified in this operand but N is specified in the server definition's adb_sql_trc_out operand, access path information and access path statistical information are not output.

[35] adb_sql_trc_level = {SQL|CALL}

Specify the unit for output of SQL trace information.

  • SQL

    Output SQL trace information for each SQL statement.

  • CALL

    Output SQL trace information for each call.

[36] adb_sql_trc_txtfile_size = maximum-capacity-of-an-SQL-trace-file

~<integer> ((32 to 1,024)) <<256>> (megabytes)

Specify in megabytes the maximum size of each SQL trace file. Eight SQL trace files are created.

Normally, there is no need to specify this operand. Specify this operand when you want to change the maximum size of the individual SQL trace files.

The maximum size of an SQL trace files specified in this operand takes effect when the HADB server starts. If the value of this operand is reduced, SQL trace files whose size is greater than the specified value might still exist immediately after the HADB server has started. In such a case, the maximum size of SQL trace files specified in this operand takes effect when the corresponding SQL trace files are swapped out to output SQL trace information.

[37] adb_sql_order_mode = {BYTE|ISO}

Specify the sort order mode for character string data when the search results of the SELECT statement with the ORDER BY clause specified are sorted. If this operand is omitted, BYTE is assumed.

  • BYTE

    Character string data is sorted by bytecode.

  • ISO

    Character string data is sorted by sort code (ISO/IEC 14651:2011 compliance).

    However, if SJIS is specified in the environment variable ADBLANG, you cannot specify ISO for this operand.

Multi-node function

If you are using the multi-node function, specify the same value for this operand for all of the HADB servers on all nodes.

You can also specify the sort order mode for character string data in the adb_clt_sql_order_mode operand in the client definition and in the connection attribute. If the sort order mode for character string data is specified in these locations, the sort order is determined according to the priority listed in the following table (the smaller the number, the higher the priority).

Table 7‒4: Priority of the sort order mode for character string data

Priority

Location where the sort order mode for character string data is specified

1

Connection attribute

2

adb_clt_sql_order_mode operand in the client definition

3

adb_sql_order_mode operand in the server definition
Explanation

For example, if ISO is specified in the adb_sql_order_mode operand in the server definition and BYTE is specified in the adb_clt_sql_order_mode operand in the client definition, BYTE is applied to the SELECT statement (with the ORDER BY clause specified) executed from an application program.

If you omit specification in all locations listed in the above table, BYTE is assumed.

[38] adb_sql_prep_dec_div_rs_prior = {INTEGRAL_PART|FRACTIONAL_PART}

If the data type of the result of a division (arithmetic operation) specified in an SQL statement is DECIMAL, specify the minimum value of the scaling value for the division result.

INTEGRAL_PART:

The minimum value of the scaling for a division result is 0. If INTEGRAL_PART is specified, the number of digits in the integer part takes precedence. Therefore, when you execute an SQL statement that performs division whose result might become a large value, you can avoid occurrence of an overflow error wherever possible by specifying INTEGRAL_PART.

FRACTIONAL_PART:

The scaling of the first operand (dividend) is used as the minimum value for a division result. If FRACTIONAL_PART is specified, the number of digits in the fractional part of the first operand is the minimum number of digits in the fractional part of the division result.

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

  • If INTEGRAL_PART is specified

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

  • If FRACTIONAL_PART is specified

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

The following shows an example of a difference in division results depending on the value specified for this operand.

Example:

[Figure]

Suppose that the following SELECT statement is executed.

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

  • The division result if INTEGRAL_PART is specified

    584.

  • The division result if FRACTIONAL_PART is specified

    584.4457

For details about an example of the scaling for division results when a viewed table is searched, see Notes applying when the data type of the division result is DECIMAL in the manual HADB SQL Reference.

Multi-node function

If you are using the multi-node function, specify the same value for this operand for all of the HADB servers on all nodes.

[39] adb_sql_default_dbarea_shared = name-of-default-data-DB-area-that-stores-tables-or-indexes

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

Specify the name of a data DB area. The data DB area you specify in this operand is used as the default DB area for storing tables or indexes when no DB area is specified for storing the table or index in the following SQL statements:

  • CREATE TABLE statement

  • CREATE INDEX statement

  • ALTER TABLE statement

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

If either of the following applies, a warning message is output when the HADB server starts. The value specified for this operand takes effect after you have resolved the cause of the warning.

  • The DB area specified in the operand is not a data DB area

  • The DB area specified in the operand does not exist

Multi-node function

If you are using the multi-node function, specify the same value for this operand for all of the HADB servers on all nodes.

To Page Top

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