Character conversion
A string is a sequence of bytes that can represent characters. Within a string, all the characters are represented by a common encoding representation. In some cases, it might be necessary to convert these characters to a different encoding representation. The process of conversion is known as character conversion.
Character conversion, when required, is automatic, and when successful, it is transparent to the application.
In client/server environments, character conversion can occur when an SQL statement is executed remotely. Consider, for example, the following two cases. In either case, the data could have a different representation at the sending and receiving systems.
- The values of data sent from the requester to the current server
- The values of data sent from the current server to the requester
Conversion can also occur during string operations on the same system, as in the following examples:
- An overriding CCSID is specified.
For example, an SQL statement with a descriptor, which requires an SQLDA. In the SQLDA, the CCSID is in the SQLNAME field for languages other than REXX, and in the SQLCCSID field for REXX. (For more information, see SQL descriptor area (SQLDA)). A DECLARE VARIABLE statement can also be issued to associate a CCSID with the host variables into which data is retrieved from a table.
- The value of the ENCODING bind option or the APPLICATION ENCODING SCHEMA option of the CREATE PROCEDURE or ALTER PROCEDURE statement for a native SQL procedure (static SQL statements) or the CURRENT APPLICATION ENCODING SCHEME special register (for dynamic SQL) is different than encoding scheme of the data being retrieved.
- A mixed character string is assigned to an SBCS column or host variable.
- An SQL statement refers to data that is defined with different CCSIDs.
The text of an SQL statement is also subject to character conversion because it is a character string.
- ASCII
- Acronym for American Standard Code for Information Interchange, an encoding scheme used to represent characters. The term ASCII is used throughout this information to refer to IBM®-PC Data or ISO 8-bit data.
- character set
- A defined set of characters, a character being the smallest component
of written language that has semantic value. For example, the following
character set appears in several code pages:
- 26 nonaccented letters A through Z
- 26 nonaccented letters a through z
- digits 0 through 9
- . , : ; ? ( ) ' " ⁄ - _ & + % * = < >
- code page
- A set of assignments of characters to code points. For example, in EBCDIC, "A" is assigned code point X'C1', and "B" is assigned code point X'C2'. In Unicode UTF-8, "A" is assigned code point X'41', and "B" is assigned code point X'42'. Within a code page, each code point has only one specific meaning.
- code point
- A unique bit pattern that represents a character. It is a numerical index or position in an encoding table used for encoding characters.
- coded character set
- A set of unambiguous rules that establishes a character set and the one-to-one relationships between the characters of the set and their coded representations. It is the assignment of each character in a character set to a unique numeric code value.
- coded character set identifier (CCSID)
- A two-byte, unsigned binary integer that uniquely identifies an encoding scheme and one or more pairs of character sets and code pages.
- EBCDIC
- Acronym for Extended Binary-Coded Decimal Interchange Code, an encoding scheme used to represent character data, a group of coded character sets that consist of 8 bit coded characters. EBCDIC coded character sets use the first 64 code positions (X'00' to X'3F') for control codes. The range X'41' to X'FE' is used for single-byte characters. For double-byte characters, the first byte is in the range X'41' to X'FE' and the second byte is also in the range X'41' to X'FE', while X'4040' represents a double-byte space.
- encoding scheme
- A set of rules used to represent character data. All string data
stored in a table must use the same encoding scheme and all tables
within a table space must use the same encoding scheme, except for
global temporary tables, declared temporary tables, and work file
table spaces. DB2® supports these
encoding schemes:
- ASCII
- EBCDIC
- Unicode
- substitution character
- A unique character that is substituted during character conversion for any characters in the source encoding representation that do not have a match in the target encoding representation.
- Unicode
- A universal encoding scheme for written characters and text that enables the exchange of data
internationally. It provides a character set standard that can be used all over the world. It
provides the ability to encode all characters used for the written languages of the world and treats
alphabetic characters, ideographic characters, and symbols equivalently because it specifies a
numeric value and a name for each of its characters. It includes punctuation marks, mathematical
symbols, technical symbols, geometric shapes, and dingbats. DB2 supports these two encoding forms:
- UTF-8: Unicode Transformation Format, a 8 bit encoding form designed for ease of use with
existing ASCII-based systems. UTF-8 can encode any of the Unicode characters. A UTF-8 character is
1,2,3, or 4 bytes in length. A UTF-8 data string can contain any combination of SBCS and MBCS data,
including supplementary characters. The CCSID value for data in UTF-8 format is 1208.
UTF-8 has multiple code points for spaces, including the '20'X
single-byte space that DB2 uses for padding UTF-8
data.
- UTF-16: Unicode Transformation Format, a 16 bit encoding form designed to provide code values
for over a million characters and a superset of UCS-2. UTF-16 can encode any of the Unicode
characters. In UTF-16 encoding, characters are 2 bytes in length, except for supplementary
characters, which take two 2 byte string units per character. The CCSID value
for data in UTF-16 format is 1200. UTF-16 has multiple code points for spaces,
including the '0020'X single-byte space that DB2 uses
for padding UTF-16 data.
Character data (CHAR, VARCHAR, and CLOB) is encoded in Unicode UTF-8. Character strings are also used for mixed data (that is a mixture of single-byte characters and multi-byte characters) and for data that is not associated with any character set (called bit data). Graphic data (GRAPHIC, VARGRAPHIC, and DBCLOB) is encoded in Unicode UTF-16. For a comparison of some UTF-8 and UTF-16 code points for some sample characters, see Character sets and code pages. This table shows how a UTF-8 character can be 1 to 4 bytes in length, a non-supplementary UTF-16 character is 2 bytes in length, and how a supplementary character in either UTF-8 or UTF-16 takes two 2 byte code points.
- UTF-8: Unicode Transformation Format, a 8 bit encoding form designed for ease of use with
existing ASCII-based systems. UTF-8 can encode any of the Unicode characters. A UTF-8 character is
1,2,3, or 4 bytes in length. A UTF-8 data string can contain any combination of SBCS and MBCS data,
including supplementary characters. The CCSID value for data in UTF-8 format is 1208.
Character conversion can affect the results of several SQL operations. In this information, the effects are described in: