Db2 graphic and binary types

Graphic data: GRAPHIC and VARGRAPHIC

When columns contain double-byte character set (DBCS) characters, you can define them as graphic data or as mixed character data. Graphic types are GRAPHIC, VARGRAPHIC, and DBCLOB.

GRAPHIC(n) — fixed-length graphic string of n double-byte characters. n must be greater than 0 and less than 128; default length is 1.

VARGRAPHIC(n) — varying-length graphic string. Maximum n depends on table space page size and can be up to 16352 double-byte characters. Like VARCHAR, VARGRAPHIC saves space when lengths vary but costs a length prefix and variable-row processing. IBM’s guidance: prefer GRAPHIC unless savings are significant; generally avoid VARGRAPHIC(n) unless n is at least about 18 double-byte characters (36 bytes).

1
2
3
4
5
CREATE TABLE PRODUCT_JA (
    SKU          CHAR(10)        NOT NULL,
    NAME_DBCS    VARGRAPHIC(40)  NOT NULL,
    PRIMARY KEY (SKU)
);

Graphic vs mixed character

If all characters are DBCS, use graphic types. Mixed-data CHAR/VARCHAR/CLOB with MIXED can hold SBCS and DBCS together; for pure SBCS runs inside mixed data you may save space versus treating everything as graphic—but pure DBCS workloads belong on graphic columns.

Unicode graphic data

Multinational applications often use Unicode. Graphic Unicode data is commonly associated with UTF-16 (for example CCSID 1200). SQL provides Unicode graphic hex literals (such as UX\'....\') and application variables aligned to graphic Unicode encodings.

Character conversion between encodings can be automatic and invisible when it succeeds—but designing columns and CCSID from the start avoids silent substitution characters. Prefer Unicode when one table must serve many geographies.

1
2
3
-- Conceptual: Unicode graphic hex literal (UTF-16 code units)
SELECT UX'004100420043' FROM SYSIBM.SYSDUMMY1;
-- Represents graphic Unicode characters (example pattern for ABC)

Exact literal forms and host-variable declarations depend on language and CCSID setup—treat this as vocabulary, then follow your shop’s Unicode standards.

Binary: BINARY and VARBINARY

Binary strings contain bytes that do not represent characters. Types include BINARY, VARBINARY, and BLOB.

BINARY(n) — binary string of length n bytes (1–255; default 1). Think of it as the binary analogue of short fixed CHAR.

VARBINARY(n) — varying-length binary string; maximum n depends on page size and can be up to 32704. Use for moderate binary values (keys, hashes, small opaque payloads). Use BLOB when sizes grow into megabytes or more.

1
2
3
4
5
6
CREATE TABLE DEVICE (
    DEVICE_ID   INTEGER       NOT NULL,
    MAC_ADDR    BINARY(6)     NOT NULL,
    FINGERPRINT VARBINARY(64) NOT NULL,
    PRIMARY KEY (DEVICE_ID)
);

Binary vs character bit data

Historically, CHAR/VARCHAR FOR BIT DATA stored non-character bytes in character-typed columns. BINARY/VARBINARY express the intent more clearly. New designs should prefer binary types unless compatibility with old tables requires FOR BIT DATA.

Literals

Binary-string constants use forms such as BX\'hex\'. Do not confuse them with X\'hex\' character hex constants—they are different types and not interchangeable.

Choosing among the families

• SBCS / UTF-8 text — CHAR / VARCHAR / CLOB
• DBCS / graphic Unicode text — GRAPHIC / VARGRAPHIC / DBCLOB
• Raw bytes — BINARY / VARBINARY / BLOB
• Mixed SBCS+DBCS in one character column — MIXED character types (advanced CCSID design)

Mixing families in comparisons requires casts and careful CCSID planning. Keep keys and join columns in compatible types.

Explain It Like I'm Five

Regular letters fit in a small pencil case (CHAR). Some alphabets need wider pencils—that case is graphic data. A sealed bag of beads that are not letters at all is binary data: count beads (bytes), don’t read them as words. Big bags of beads are BLOBs; big wide-pencil books are DBCLOBs.

Exercises

1. Choose GRAPHIC or VARGRAPHIC for a 2-character DBCS code vs a free-form 80-character DBCS name.
2. Why is BINARY(6) reasonable for a MAC address?
3. When would you promote VARBINARY to BLOB?
4. Explain one risk of storing binary bytes in a VARCHAR without FOR BIT DATA / binary types.
5. In one sentence, distinguish mixed character data from graphic data.