Db2 character encodings and CCSID

Why encoding shows up in every shop

Mainframe applications grew up on EBCDIC. Distributed clients often speak ASCII or Unicode. Db2 sits in the middle: it stores strings under a table/column encoding, converts when necessary, and refuses to “guess” for bit data. If you ignore CCSID, you eventually see mojibake, failed conversions, or silent wrong comparisons.

A code point is a bit pattern that means a character inside a coded character set. A CCSID is the integer label for a particular encoding package Db2 understands. Character conversion changes bytes from one CCSID to another using Db2’s conversion tables (including rows in SYSIBM.SYSSTRINGS).

Character subtypes

SBCS (single-byte character set) data uses one byte per character—typical for classic English EBCDIC or ASCII SBCS CCSIDs. Mixed data may contain both single-byte and MBCS (multi-byte) characters under a mixed CCSID. When mixed data is enabled or Unicode is in play, Db2 recognizes multi-byte sequences for parsing, LIKE pattern matching, and conversion. DBCS (double-byte) is the classic graphic world—GRAPHIC/VARGRAPHIC/DBCLOB—often UTF-16 under Unicode.

Installation options such as MIXED DATA on panel DSNTIPF influence which default CCSIDs the subsystem assigns for SBCS, mixed, and graphic data. Application programmers rarely set those panels, but they inherit the consequences in every CHAR/VARCHAR column they create.

EBCDIC, ASCII, Unicode, and UTF-8

All string data in a table generally shares one encoding scheme (with documented exceptions such as certain temporary and work-file cases). Within Unicode tables, IBM associates CHAR/VARCHAR/CLOB with UTF-8 (CCSID 1208) and GRAPHIC/VARGRAPHIC/DBCLOB with UTF-16 (CCSID 1200). Subtype clauses can refine SBCS versus mixed versus bit data even inside Unicode tables—for example CHAR FOR SBCS DATA may use CCSID 367 (7-bit ASCII) in IBM’s Unicode table summary.

1
2
3
4
5
6
7
-- Unicode-oriented character vs graphic (illustrative)
CREATE TABLE APP.CUSTOMER (
    CUST_ID    CHAR(10) NOT NULL,
    CUST_NAME  VARCHAR(100),           -- UTF-8 character data in a Unicode table
    CUST_NOTE  VARGRAPHIC(50),         -- UTF-16 graphic data
    PRIMARY KEY (CUST_ID)
);

Conversion happens when CCSIDs differ

Assignments, comparisons, and distributed access often require conversion. Valid paths are defined in the system; invalid or lossy conversions raise errors or substitute characters per conversion definitions. Bit data skips this machinery on purpose—which is helpful for true binary payloads and disastrous if you stored text as bit data “to avoid conversion” and then mixed platforms.

FOR BIT DATA

FOR BIT DATA tells Db2: these bytes are not associated with a coded character set. The CCSID is 65535 (X'FFFF'). Db2 will not character-convert them. That is correct for opaque tokens, encrypted blobs staged in character columns historically, or binary payloads modeled before BINARY/VARBINARY were widely used.

1
2
3
4
5
6
CREATE TABLE APP.DEVICE (
    DEVICE_ID   CHAR(8) NOT NULL,
    MAC_ADDR    CHAR(6) FOR BIT DATA NOT NULL,
    PAYLOAD     VARCHAR(200) FOR BIT DATA,
    PRIMARY KEY (DEVICE_ID)
);

For new designs that are truly binary, prefer BINARY, VARBINARY, or BLOB when they fit. You will still meet FOR BIT DATA constantly in existing DDL and ODBC discussions (drivers expose how bit/binary types are reported). Never use FOR BIT DATA for human language text you expect to display correctly across EBCDIC and Unicode clients.

CLOB FOR BIT DATA

Large bit-oriented character large objects exist as a historical bridge. Today, a BLOB is usually the clearer large-binary choice. If you inherit CLOB FOR BIT DATA, treat it as binary logistics with LOB storage rules—not as a document CCSID problem.

Practical habits

• Know the table encoding before blaming the application for “weird characters”
• Match host CCSID / encoding to column expectations (COBOL, JDBC, ODBC settings)
• Use bit data only for non-characters
• Plan VARCHAR lengths in bytes for UTF-8, not “number of letters”
• Prefer Unicode for new global text when shop standards allow

1
2
3
4
5
6
Quick map
---------
Text people read     -> CHAR/VARCHAR (+ correct CCSID)
East-Asian graphic   -> GRAPHIC/VARGRAPHIC (often UTF-16)
Opaque bytes         -> BINARY/VARBINARY/BLOB or FOR BIT DATA
Huge text documents  -> CLOB (with real character CCSID)

Explain It Like I'm Five

Letters are secret codes. In one secret club (EBCDIC), the code for “A” is different from another club (ASCII/Unicode). A CCSID is the nametag that says which secret club a box of letters uses. Db2 can translate between clubs when both sides are real writing. If you stamp the box FOR BIT DATA, you are saying “these are not letters—do not translate,” like a sealed bag of random stickers.

Exercises

1. Define CCSID, SBCS, mixed data, and FOR BIT DATA in one sentence each.
2. Why is VARCHAR length planning harder under UTF-8 than under EBCDIC SBCS?
3. Give one good use and one bad use of VARCHAR FOR BIT DATA.
4. What CCSID numbers are commonly associated with UTF-8 character data and UTF-16 graphic data?
5. Explain why skipping conversion “to go faster” can permanently corrupt displayed names.