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
Data type	Denotes a column of...
CHARACTER(`n`)	Fixed-length character strings with a length of `n` bytes. `n` must be greater than 0 and not greater than 255. The default length is 1.
VARCHAR(`n`)	Varying-length character strings with a maximum length of `n` bytes. `n` must be greater than 0 and less than a number that depends on the page size of the table space. The maximum length is 32704.
CLOB(`n`)	Varying-length character strings with a maximum of `n` characters. `n` cannot exceed 2 147 483 647. The default length is 1M.
GRAPHIC(`n`)	Fixed-length graphic strings that contain `n` double-byte characters. `n` must be greater than 0 and less than 128. The default length is 1.
VARGRAPHIC(`n`)	Varying-length graphic strings. The maximum length, `n`, must be greater than 0 and less than a number that depends on the page size of the table space. The maximum length is 16352.
DBCLOB(`n`)	Varying-length strings of double-byte characters with a maximum of `n` double-byte characters. `n` cannot exceed 1 073 741 824. The default length is 1M.
BINARY(`n`)	Fixed-length or varying-length binary strings with a length of `n` bytes. `n` must be greater than 0 and not greater than 255. The default length is 1.
VARBINARY(`n`)	Varying-length binary strings with a length of `n` bytes. The length of `n` must be greater than 0 and less than a number that depends on the page size of the table space. The maximum length is 32704.
BLOB(`n`)	Varying-length binary strings with a length of `n` bytes. `n` cannot exceed 2 147 483 647. The default length is 1M.

In most cases, the content of the data that a column is to store dictates the data type that you choose.

Example: 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).

Fixed-length and variable-length character strings

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.

String subtypes

If an application that accesses your table uses a different encoding scheme than your DBMS uses, the following string subtypes can be important:

BIT: Does not represent characters.
SBCS: Represents single-byte characters.
MIXED: Represents single-byte characters and multibyte characters.

String subtypes apply only to CHAR, VARCHAR, and CLOB data types. However, the BIT string subtype is not allowed for the CLOB data type.

Graphic and mixed data

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.

Encoding schemes for string data

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.