Dmitry67 wrote:zVlad wrote:И знаете как это делалось в SQL/DS? Нет не знаете. Делалось это путем перекодировки символов перед их сохранением и обратной перекодировки перед возвращением. Делается это естественно на сервере. Сравнения и сортировки осуществляются до обратной перекодировки.
Ага, то есть таки то что Вы писали неверно:
zVlad wrote:То есть в случае CHAR не должно быть никаких акцентов - это коды, произвольные коды, которые могут сравниваться только как бинарные коды
А именно сравниваются таки не бинарные коды, а коды после перекодировки.
Что касается того что для буквы Й понадобилось писать программу на ассемблере то Вы пожалуйста никому этого не говорите
а то как про ассемблер услышат то и не купят DB2.
И все таки, я хочу распечатать телефонную книгу
select Names from PhoneBook order by Name
теперь мне тоже самое надо сделать для French accent_insensitive sort order. Что мне надо изменить в операторе ?
DB2 compares codes without having to decode them. Decoding and encoding is an optional for now. Now, if you wish to run say Cyrillic (or French) you have to set up code page in DB2 properly that's all.
You are still able to write Assembler for aka FEILDPROC if you need special handling, for example, when you deal with phone book (see cut from DB2 docs below).
Below you can find few cuts from DB2 docs (sorry for formats) related to discussed problems. Please, read and give your opinion.
==========================================================
│ │
│ │
│ >───┬─UCASE─┬─(expression)───────────────────────────────────────────> │
│ └─UPPER─┘ │
│ │
│ │
└────────────────────────────────────────────────────────────────────────┘
The schema is SYSIBM.
The UCASE or UPPER function returns a string in which all the characters have been
converted to uppercase characters.
expression
An expression that specifies the string to be converted. The string must be a
character or graphic string. A character string argument must not be a CLOB and must
have an actual length that is not greater than 255. A graphic string argument must not
be a DBCLOB and must have an actual length that is not greater than 127.
The alphabetic characters of the argument are translated to uppercase characters
based on the value of the LC_CTYPE locale in effect for the statement. For example,
characters a-z are translated to A-Z, and characters with diacritical marks are
translated to their uppercase equivalent, if any. The locale is determined by special
register CURRENT LOCALE LC_CTYPE. For information about the special register, see
"CURRENT LOCALE LC_CTYPE" in topic 3.14.4.
If the LC_CTYPE locale is blank when the function is executed, the result of the function
depends on the data type of expression. For a character string expression, characters
a-z are translated to A-Z and characters with diacritical marks are not translated. For a
graphic string expression, an error occurs.
The length attribute, data type, subtype, and CCSID of the result are the same as the
expression. If the argument can be null, the result can be null; if the argument is null, the
result is the null value.
============================================================
3.14.4 CURRENT LOCALE LC_CTYPE
CURRENT LOCALE LC_CTYPE specifies the LC_CTYPE locale that will be used to
execute SQL statements that use a built-in function that references a locale. Functions
LCASE, UCASE, and TRANSLATE (with a single argument) refer to the locale when they
are executed. The data type is CHAR(50). If necessary, the value is padded on the right
with blanks so that its length is 50 bytes.
The initial value of CURRENT LOCALE LC_CTYPE is determined by the value of field
LOCALE LC_CTYPE on installation panel DSNTIPF. The default for the initial value of that
field is blank unless your installation has changed the value of that field. The initial value
of CURRENT LOCALE LC_CTYPE in a stored procedure or user-defined function is
inherited from the invoker.
You can change the value of the register by executing the statement SET CURRENT
LOCALE LC_CTYPE. For details about this statement, see "SET CURRENT LOCALE
LC_CTYPE" in topic 6.91.
Example: Save the value of current register CURRENT LOCALE LC_CTYPE in host
variable HV1, which is defined as VARCHAR(50).
EXEC SQL VALUES(CURRENT LOCALE LC_CTYPE) INTO :HV1;
============================================================
APPENDIX1.1.3 Specifying locales
Rules for uppercase and lowercase usage vary according to language and country. A locale defines the subset of a user's environment that depends on language and cultural conventions.
DB2 uses the information associated with a locale to execute UPPER, LOWER, and TRANSLATE functions in a culturally correct manner. A locale consists of two components: the first component represents a specific language and country, and the second component is a CCSID.
Example: In the locale, Fr_CA.IBM-1047, Fr_CA represents the language and country (French Canadian), and IBM-1047 is the associated CCSID.
The symbol for Euro currency is supported through the modifier @EURO.
Example: To display results in Euro dollars instead of French Francs, specify Fr_FR@EURO.
Table 124 shows a partial list of locales supplied with OS/390 C/C++. For a more complete list of
locales, see OS/390 C/C++ Programming Guide.
=======================================================
APPENDIX1.1.4.1 How an entry in SYSIBM.SYSSTRINGS works
The catalog table SYSIBM.SYSSTRINGS contains the following columns:
INCCSID The source CCSID of a character conversion.
OUTCCSID The target CCSID of a character conversion.
TRANSTYPE The type of conversion:
SS SBCS data to SBCS data
SM SBCS data to EBCDIC MIXED data
MS EBCDIC MIXED data to SBCS (EBCDIC and ASCII) data
PS ASCII MIXED data to SBCS (EBCDIC and ASCII) data
GG GRAPHIC data to GRAPHIC data
PM ASCII MIXED data to EBCDIC MIXED data
MM EBCDIC MIXED data to EBCDIC MIXED data.
MP EBCDIC MIXED to ASCII MIXED data.
PP ASCII MIXED to ASCII MIXED data.
SP SBCS (ASCII and EBCDIC) to ASCII MIXED data.
ERRORBYTE Specifies the byte used in the conversion table (TRANSTAB) as an error indicator.
For example, if ERRORBYTE is X'3E', that byte is used in the conversion table to indicate
that no conversion is defined for code points that map to X'3E'. Null indicates the absence
of an error indicator.
SUBBYTE Specifies the byte used in the conversion table (TRANSTAB) as a substitution
character. For example, if SUBBYTE is X'3F', that byte is used in the conversion table as a
substitute for code points that map to X'3F'. A warning occurs when a code point maps to
the value of SUBBYTE. Null indicates the absence of a substitution character.
TRANSPROC The name of a module or a blank string. If IBMREQD is N, a non-blank value of
TRANSPROC is the name of a user-provided conversion procedure. If IBMREQD is Y, a
non-blank value of TRANSPROC is the name of a DB2 module that contains DBCS
conversion tables.
IBMREQD Y indicates that the row is provided by IBM. N indicates that the row has been
inserted by the user.
TRANSTAB A 256-byte conversion table or an empty string.
Each row of SYSSTRINGS contains information about the conversion of character strings from the
coded character set identified by INCCSID to the coded character set identified by OUTCCSID. The
conversion function is automatically invoked when a conversion from the coded character set
identified by the INCCSID column to the coded character set identified by the OUTCCSID column is
required.
For example, the row of SYSSTRINGS in which the value of INCCSID is 500 and the value of
OUTCCSID is 37 describes the conversion from CCSID 500 to CCSID 37. The row in which the
value of INCCSID is 37 and the value of OUTCCSID is 500 describes the conversion from CCSID 37
to CCSID 500.
=========================================================
APPENDIX1.2.7 Field procedures
Field procedures are assigned to a table by the FIELDPROC clause of CREATE TABLE and
ALTER TABLE. A field procedure is a user-written exit routine to transform values in a
single short-string column. When values in the column are changed, or new values
inserted, the field procedure is invoked for each value, and can transform that value
(encode it) in any way. The encoded value is then stored. When values are retrieved
from the column, the field procedure is invoked for each value, which is encoded, and
must decode it back to the original string value.
Any indexes, including partitioned indexes, defined on a column that uses a field
procedure are built with encoded values. For a partitioned index, the encoded value of the
limit key is put into the LIMITKEY column of the SYSINDEXPART table. Hence, a field
procedure might be used to alter the sorting sequence of values entered in a column. For
example, telephone directories sometimes require that names like "McCabe" and
"MacCabe" appear next to each other, an effect that the standard EBCDIC sorting
sequence does not provide. And languages that do not use the Roman alphabet have
similar requirements. However, if a column is provided with a suitable field procedure, it
can be correctly ordered by ORDER BY.
........................
==========================================================
