13.22.1 Overview of space conversion facility

When data is compared, one double-byte space and two single-byte spaces are not recognized as the same data. Therefore, when blanks are represented in table data by both one double-byte space and two single-byte spaces, the retrieval results may be inaccurate.

Example
The following are not recognized as being the same data items:
[Figure]

The double-byte space character being discussed here is coded as shown below. Two single-byte space characters are coded as X'2020'.

#: NCHAR and NVARCHAR cannot be used if the character codes are Unicode (UTF-8).
Organization of this subsection
(1) Space conversion levels
(2) Notes

(1) Space conversion levels

As shown in Table 13-32, three levels of space character conversion are provided by the space conversion facility.

Table 13-32 Space conversion levels

LevelExplanation
Level 0No space conversion.
Level 1Converts as follows data spaces that occur in literals, embedded variables, and ? parameters in the data manipulation SQL and data spaces stored by utilities:
  • When a character string literal is being handled as a national character string literal, two single-byte spaces in succession are converted into one double-byte space; a single occurrence of a single-byte space is not converted.
  • When a character string literal is being handled as a mixed character string literal, one double-byte space is converted into two single-byte spaces.
  • When data is being stored in a column of the national character type and when data is being compared with a value expression of the national character type, two single-byte spaces in succession in an embedded variable or ? parameter are converted into one double-byte space; a single occurrence of a single-byte space is not converted.
  • When data is being stored in a column of the mixed character type and when data is being compared with a value expression of the mixed character type, one double-byte space in an embedded variable or ? parameter is converted into two single-byte spaces.
Level 3Adds the following processing to the processing of space conversion level 1:
  • During retrieval of data of a value expression of the national character type, a single double-byte space is converted into two single-byte spaces.

Figure 13-85 illustrates Level 1 processing; Figure 13-86 illustrates Level 3 processing.

Figure 13-85 Level 1 processing

[Figure]

Figure 13-86 Level 3 processing

[Figure]

(2) Notes

  1. When the space conversion level is changed, a UAP's retrieval results may not be the same after the change as they were before the change. For this reason, it is not advisable to change the space conversion level if a change in UAP retrieval results is not acceptable.
  2. If sorting is performed when Level 3 is specified, HiRDB will perform space conversion on the sort results, and the expected results may not be obtained in all cases.
  3. If national character data is retrieved when Level 3 is specified, performance may be poorer than with Level 0 or 1. For example, performance is degraded when large-sized data is retrieved, such as data of the type NVARCHAR (2000). This is because of the overhead involved in converting double-byte spaces into single-byte spaces.
  4. When data is being stored in a column of a cluster key, space conversion may cause a uniqueness error. In such a case, you must either store the data without performing space conversion or standardize the space characters in the existing data (For details on standardizing the space characters in existing data, see 13.22.3 Standardizing space characters in a table).
  5. Spaces in a national character string are converted in units of two characters, beginning at the beginning of the string.
  6. If you are using HiRDB External Data Access, the character codes must be compatible with the character codes on the DBMS of the foreign server. If they are not compatible, unexpected results may be returned.
  7. The following must be noted when Level 1 or 3 is specified as the space conversion level:
    • When determining the storage target RDAREA using a UAP that uses a hash function for table partitioning on a hash partitioned table, specify a space conversion level in the argument of the hash function for table partitioning. If no space conversion level is specified, the result of the hash function for table partitioning may be corrupt.
    • When a UAP is used to apply key range partitioning to a table (and the partitioning key is national character data or mixed character data), convert the partitioning key values with the space conversion function. Otherwise, the key range partitioning results may be invalid.
    For details on the hash function for table partitioning and the space conversion function, see the manual HiRDB Version 8 UAP Development Guide.