DB2 12 - Data types - String data types
Db2® supports several types of string data: character strings, graphic strings, and binary strings.
Character strings contain text and can be either a fixed-length or a varying-length.
Graphic strings contain graphic data, which can also be either a fixed-length or a varying-length.
Binary strings contain strings of binary bytes and can be either a fixed-length or a varying-length.
All of these types of string data can be represented as large objects.
The following table describes the different string data types and indicates the range for the length of each string data type.
Table 1. String data types
In most cases, the content of the data that a column is to store dictates the data type that you choose.
The DEPT table has a column, DEPTNAME.
The data type of the DEPTNAME column is VARCHAR(36).
Because department names normally vary considerably in length, the choice of a varying-length data type seems appropriate.
If you choose a data type of CHAR(36), for example, the result is a lot of wasted, unused space.
In this case, Db2 assigns all department names, regardless of length, the same amount of space (36 bytes).
A data type of CHAR(6) for the employee number (EMPNO) is a reasonable choice because all values are fixed-length values (6 bytes).
Using VARCHAR saves disk space, but it incurs a 2-byte overhead cost for each value.
Using VARCHAR also requires additional processing for varying-length rows.
Therefore, using CHAR is preferable to VARCHAR, unless the space that you save by using VARCHAR is significant.
The savings are not significant if the maximum column length is small or if the lengths of the values do not have a significant variation.
Recommendation: Generally, do not define a column as VARCHAR(n) or CLOB(n) unless n is at least 18 characters.
If an application that accesses your table uses a different encoding scheme than your DBMS uses, the following string subtypes can be important:
String subtypes apply only to CHAR, VARCHAR, and CLOB data types.
However, the BIT string subtype is not allowed for the CLOB data type.
When columns contain double-byte character set (DBCS) characters, you can define them as either graphic data or mixed data.
Graphic data can be either GRAPHIC, VARGRAPHIC, or DBCLOB. Using VARGRAPHIC saves disk space, but it incurs a 2-byte overhead cost for each value.
Using VARGRAPHIC also requires additional processing for varying-length rows.
Therefore, using GRAPHIC data is preferable to using VARGRAPHIC unless the space that you save by using VARGRAPHIC is significant.
The savings are not significant if the maximum column length is small or if the lengths of the values do not vary significantly.
Recommendation: Generally, do not define a column as VARGRAPHIC(n) unless n is at least 18 double-byte characters (which is a length of 36 bytes).
Mixed-data character string columns can contain both single-byte character set (SBCS) and DBCS characters.
You can specify the mixed-data character string columns as CHAR, VARCHAR, or CLOB with MIXED DATA.
Recommendation: If all of the characters are DBCS characters, use the graphic data types. (Kanji is an example of a language that requires DBCS characters.)
For SBCS characters, use mixed data to save 1 byte for every single-byte character in the column.
For string data, all characters are represented by a common encoding representation (Unicode, ASCII, or EBCDIC).
Encoding schemes apply to string data types and to distinct types that are based on string types.
Multinational companies that engage in international trade often store data from more than one country in the same table.
Some countries use different coded character set identifiers.
Db2 for z/OS® supports the Unicode encoding scheme, which represents many different geographies and languages.
If you need to perform character conversion on Unicode data, the conversion is more likely to preserve all of your information.
In some cases, you might need to convert characters to a different encoding representation.
The process of conversion is known as character conversion.
Most users do not need a knowledge of character conversion.
When character conversion does occur, it does so automatically and a successful conversion is invisible to the application and users.