Db2 VARCHAR data type

Variable-length character storage

A character string in Db2 is a sequence of bytes. For VARCHAR(n), n is the maximum length in bytes you allow. Actual values may be shorter. That is the whole point: a last name of 5 characters should not force you to store 95 blank padding characters the way a wide CHAR column would.

IBM requires the VARCHAR length attribute to be in the range 1–32704. For a column in a table, the practical maximum can be lower because of the maximum record size for that table and the other columns in the row. When you need multi-megabyte documents, use a CLOB (a character large object), not an oversized VARCHAR.

1
2
3
4
5
6
7
CREATE TABLE HR.EMPLOYEE (
    EMPNO      CHAR(6) NOT NULL,
    FIRSTNME   VARCHAR(12) NOT NULL,
    LASTNAME   VARCHAR(15) NOT NULL,
    EMAIL      VARCHAR(50),
    PRIMARY KEY (EMPNO)
);

Here EMPNO is a fixed-width code—classic CHAR territory. Names and email vary, so VARCHAR fits. The column still has a CCSID and character subtype (SBCS, mixed, or bit data) based on table encoding and any FOR … DATA clause; those topics live on the character encodings page.

Length prefix and maximum length

Varying-length strings need a place to record “how many bytes follow.” Db2 uses a length prefix. In IBM’s summary table of data types, the byte count for VARCHAR is listed as n+2: up to n bytes of string data plus two bytes for the length. That is why a VARCHAR(10) is not “only 10 bytes of row cost” in planning conversations—you also pay for the prefix (and, separately, null indicators and other row structure).

The length you declare is a byte length for character VARCHAR. Under UTF-8 (Unicode character columns), one visible character may take one to four bytes. Declaring VARCHAR(30) does not always mean “30 letters”—it means up to 30 bytes. Design Unicode columns with that in mind, or you truncate international names unexpectedly.

1
2
3
4
5
6
7
-- Length function returns the length of the string value
SELECT LASTNAME,
       LENGTH(LASTNAME) AS NAME_LEN
  FROM HR.EMPLOYEE;
 
-- Casting / assignment longer than n fails or truncates
-- depending on statement context and options—test carefully.

Empty string versus NULL

If the length is zero, the value is the empty string. IBM is explicit: do not confuse the empty string with the null value. An empty VARCHAR is a known value of length zero. NULL means the value is unknown or not present. Predicates, functions, and host variables treat them differently—especially in COBOL, where null indicators and blank-filled host fields already invite confusion.

Trailing blanks

Trailing blanks are one of the sharp edges between CHAR and VARCHAR. With CHAR(n), Db2 pads shorter strings with blanks up to n. With VARCHAR, the length prefix reflects what you stored. If your application or literal includes trailing blanks, those blanks are part of the VARCHAR value’s length.

1
2
3
4
5
6
7
-- Conceptual contrast (exact comparison rules depend on
-- predicates, cast, and pad options—verify in your shop):
INSERT INTO HR.EMPLOYEE (EMPNO, FIRSTNME, LASTNAME)
VALUES ('000010', 'CHRISTINE', 'HAAS   ');  -- trailing blanks in LASTNAME
 
-- LENGTH may count those blanks for VARCHAR.
-- CHAR columns would pad to fixed width instead.

When blanks should not matter, use TRIM / RTRIM (and related functions) deliberately in predicates and joins. Blind equality checks between CHAR and VARCHAR columns are a frequent source of “it looks the same on the screen” bugs. Prefer consistent types for join keys, or normalize with TRIM and documented cast rules.

CHAR vs VARCHAR choice

IBM guidance for Unicode tables often favors varying length for fields longer than a modest fixed size when values are not always full width—padding waste is painful in UTF-8/UTF-16. For short fixed codes (department A00, status A/I), CHAR remains clear and efficient. Neither type replaces binary types or FOR BIT DATA when bytes are not characters—see the encodings page.

Host variables and DCLGEN

COBOL and other host languages map VARCHAR to a length field plus a character area (often generated by DCLGEN). Always set the length correctly on INSERT/UPDATE, and on FETCH read the length Db2 returns. Treating VARCHAR host areas like fixed CHAR without honoring the length is a classic truncation and blank-padding bug pattern.

1
2
3
4
SELECT EMPNO, LASTNAME
  FROM HR.EMPLOYEE
 WHERE LASTNAME LIKE 'H%'
 ORDER BY LASTNAME;

Explain It Like I'm Five

A CHAR box is always the same size—if your name is short, Db2 stuffs blank paper in the rest of the box. A VARCHAR box comes with a sticky note that says how many letters are inside, so small names use a small box and long names use more space up to the maximum you allowed. An empty box with “0 letters” is not the same as a missing box (NULL).

Exercises

1. Declare a table with one CHAR code column and two VARCHAR name columns; justify each choice.
2. Why is VARCHAR(30) under UTF-8 not always “30 characters”?
3. Explain empty string versus NULL with an example INSERT for each.
4. When would you pick CLOB instead of VARCHAR(32704)?
5. Describe one trailing-blank bug you might see joining CHAR to VARCHAR.