2.2.8 Operands related to the SQL specifications

35) pd_overflow_suppress = Y | N
Specifies whether error suppression is to be implemented during computations. The following types of errors can be suppressed:
  • Overflow in the middle of a computation
  • Division by 0 errors
Y:
If an applicable error occurs in a computation during SQL execution, convert the computation result to the null value and continue the processing.
N:
If an applicable error occurs in a computation during SQL execution, cancel the process with an error.
36) pd_space_level = 0 | 1 | 3
Specifies the applicable space conversion level when the space configuration facility is used. For details about the space conversion facility, see the HiRDB Version 9 System Operation Guide.
0: Use space conversion level 0.
1: Use space conversion level 1.
3: Use space conversion level 3.
The following table explains the space conversion levels.
LevelExplanation
Level 0No space conversion.
Level 1Converts spaces in literals, embedded variables, and ? parameter data in a data manipulation SQL as follows:
  • If a character string literal is considered a national character string literal, two single-byte spaces are converted into a double-byte space. If a single-byte space appears by itself, it is not converted.
  • If a character string literal is considered a mixed character string literal, a double-byte space is converted into two single-byte spaces.
  • When data is being stored into a national character string-type string or is being compared with a national character string-type value expression, two single-byte spaces in an embedded variable or ? parameter are converted into a double-byte space. If a single-byte space appears by itself, it is not converted.
  • When data is being stored into a mixed character string-type string or is being compared with a mixed character string-type value expression, a double-byte space in an embedded variable or ? parameter is converted into two single-byte spaces.
Level 3The following processing is performed, in addition to the processing in Level 1:
  • When data of a national character string-type value expression is being retrieved, a double-byte space is converted into two single-byte spaces.
Notes
  • When the space conversion level is changed, there might be differences in UAP results before and after the change. To ensure that UAP results remain the same, do not change the space conversion level.
  • When sorting is performed with the space conversion level set to 3, HiRDB performs space conversion on the sorting results, which might produce unexpected results.
  • When data is being stored in a cluster key string, space conversion might cause a uniqueness error. In such a case, either store the data without space conversion or use the database reorganization utility to standardize the spaces in the existing database.
  • Spaces in a national character string are converted in units of two characters from the beginning of the string.
  • Note the following when 1 or 3 is specified for the space conversion level:
    When a storage RDAREA is determined in the case of a UAP that uses a hash function for table partitioning from a hash-partitioned table, it is necessary to specify a space conversion level in the argument of the hash function for table partitioning. If no level is specified, the result of the hash function for table partitioning might be incorrect.
    When a UAP is used to execute key range partitioning on a table that has been key range-partitioned (partitioning key is national character data or mixed character data), the partitioning key must be converted with the space conversion function. Otherwise, the result of key range partitioning might be incorrect.
    For details about the hash functions for table partitioning and the space conversion function, see the HiRDB Version 9 UAP Development Guide.
Relationship to client environment definition
The value of this operand can be different for each client. To change the operand for a client, specify the PDSPACELVL operand in the client environment definition.
For details about the PDSPACELVL operand, see the HiRDB Version 9 UAP Development Guide.
37) pd_dec_sign_normalize = Y | N
Specifies whether the sign of signed packed data transferred from a UAP is to be normalized (in other words, specifies whether the facility for conversion to a DECIMAL signed normalized number is to be used).Normalizing the sign means converting the sign A to F of signed packed data to C or D. For details about the facility for conversion to a DECIMAL signed normalized number, see the HiRDB Version 9 System Operation Guide.
Y:
Use the facility for conversion to a DECIMAL signed normalized number. The sign of signed packed data is normalized. A to F are considered to be DECIMAL data signs.
N:
Do not use the facility for conversion to a DECIMAL signed normalized number. The sign of signed packed data is not normalized. C, D, and F are considered to be DECIMAL data signs.
Notes
The following must be noted when Y is specified (that is, when the facility for conversion to a DECIMAL signed normalized number is to be used):
  • For determining the storage RDAREA for a hash-partitioned table in the case of a UAP that uses the hash function for table partitioning, you must specify to use the facility for conversion to a DECIMAL signed normalized number as an argument of the hash function for table partitioning. If this is not specified, the result of the hash function for table partitioning might be corrupted.
  • When a UAP is used to execute key range partitioning for a key range-partitioned table (for data whose partitioning key is DECIMAL), the partitioning key value must be converted using the facility for conversion to a DECIMAL signed normalized number. Otherwise, the result of the key range partitioning might be corrupted.
For details about the hash functions for table partitioning and the facility for conversion to a DECIMAL signed normalized number, see the HiRDB Version 9 UAP Development Guide.
38) pd_sql_dec_op_maxprec = maximum-precision-for-a-DECIMAL-type-operation-result-not-exceeding-29-digits
~<unsigned integer>((29 | 38))
  • 0904 compatibility mode: <<29>>
Specifies one of the following maximum precision values, as applicable:
  • Maximum precision value when the data type of the operation result is DECIMAL
    [Figure] Arithmetic operations performed exclusively on data that does not exceed 29 digits
    [Figure]Set functions AVG and SUM performed exclusively on data that does not exceed 29 digits
    [Figure]Scalar functions DECIMAL, VALUE, GREATEST, and LEAST performed exclusively on data that does not exceed 29 digits
    [Figure]DECIMAL scalar functions whose argument is FLOAT type and that omit precision
    [Figure]CASE expression when the values of the corresponding THEN and ELSE clauses do not exceed 29 digits
    [Figure]Calls to functions whose only potential functions are user-defined functions that include abstract data type parameters and whose return value data type does not exceed 29 digits
  • When the data type of a set operation's result column is DECIMAL, the maximum precision value when the column corresponding to the set operation's derived table is exclusively data that does not exceed 29 digits
Specification guidelines
  • When HiRDB is upgraded from version 09-04 or earlier:
    If the value specified for this operand before the upgrade is not 38, specify 29 for this operand or apply 0904 compatibility mode.
  • In all other cases:
    This operand depends on the configuration of the applications.
    For data of 29 or fewer digits that is used in an application, specify 29 if you want to handle the calculation results of such data by 29-digit Decimal precision.
    If not, specify 38 or omit this operand.
Notes
If this operand is changed, applications that use the type Decimal will be affected.
You must modify applications to obtain the result data of the value expressions below and to obtain query specification results. In addition, if a viewed table, a trigger, a routine, or a table with a check constraint contains a value expression or query specification below, re-create it because a Decimal type operation might overflow during the execution or the retrieval of the viewed table might fail.
To re-create a trigger, delete it and then re-create it. Do not use ALTER TRIGGER or ALTER ROUTINE.
  • Arithmetic operations performed exclusively on data that does not exceed 29 digits
  • Set functions AVG and SUM performed exclusively on data that does not exceed 29 digits
  • Scalar functions DECIMAL, VALUE, GREATEST, and LEAST performed exclusively on data that does not exceed 29 digits
  • DECIMAL scalar functions whose argument is FLOAT type and that omit precision
  • CASE expression when the values of the corresponding THEN and ELSE clauses do not exceed 29 digits
  • Calls to functions whose only potential functions are user-defined functions that include abstract data type parameters and whose return value data type does not exceed 29 digits
  • Query specification when the data type of a set operation's result column is Decimal and the corresponding column in the set operation's derived table consists only of data that does not exceed 29 digits
Effects on individual estimation formulas
If the value of the pd_sql_dec_op_maxprec operand is changed, the following estimation formulas are affected:
HiRDB Version 9 Installation and Design Guide:
  • Procedure for obtaining the size of the memory required during execution of rapid grouping facility under Estimating the memory size required for a HiRDB single server configuration
  • Procedure for obtaining the size of the memory required during execution of rapid grouping facility under Estimating the memory size required for a HiRDB parallel server configuration
39) pd_sql_mode = 0 | 1
Specifies whether to allow the set operator MINUS in SQL statements.
0: Do not allow the set operator MINUS.
1: Allow the set operator MINUS.
Notes
  • If 1 is specified in this operand, MINUS becomes an SQL reserved word. If names such as table and column names contain MINUS, change MINUS to "MINUS" by enclosing it in double quotation marks ("). For details about how to specify names, see Specification of names in the manual HiRDB Version 9 SQL Reference.
  • The value of this operand determines whether the set operator MINUS is available for use during routine definition and compilation.
  • Make sure that the value of the operand specified in the routine definition matches the operand value used when the routine is recompiled. If the value of this operand is changed, and then the routine is recompiled, a syntax error might result.