SQLMentor // learn db2

DB2 Data Types

Difficulty: Beginner · ~9 min read

Overview

DB2 supports a rich set of data types organised into four families:

  1. Numeric — integers, decimals, floats
  2. String — fixed/varying character data
  3. Date & Time — dates, times, timestamps
  4. Special — binary, XML, BLOB, BOOLEAN, generated columns

Picking the right type matters for storage size, query performance, and constraint integrity. DB2 will silently convert between compatible types (implicit casting), but a wrong-type schema is the root cause of many performance issues.

Syntax

All types are declared in CREATE TABLE or ALTER TABLE:

CREATE TABLE example (
  id          INTEGER         NOT NULL,
  short_text  VARCHAR(80),
  long_text   CLOB(1M),
  amount      DECIMAL(12,2),
  created_at  TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  metadata    XML
);

Examples

Example 1: Numeric types

CREATE TABLE numeric_demo (
  small_n   SMALLINT,      -- 2 bytes, -32,768 to 32,767
  int_n     INTEGER,       -- 4 bytes, ±2.1 billion
  big_n     BIGINT,        -- 8 bytes, very large
  money     DECIMAL(15,2), -- exact, 15 digits, 2 after decimal
  ratio     REAL,          -- 4-byte float (~7 digits)
  precise   DOUBLE         -- 8-byte float (~15 digits)
);

Use DECIMAL for money — never REAL or DOUBLE. Floats lose precision; decimals don't.

Example 2: String types

CREATE TABLE string_demo (
  code      CHAR(5),          -- always 5 chars, padded with spaces
  name      VARCHAR(80),      -- up to 80 chars, variable length
  bio       VARCHAR(4000),    -- max in-row VARCHAR
  article   CLOB(1M),         -- Character Large Object (up to 2 GB)
  flag      CHAR(1)
);
Type When to use
CHAR(n) Fixed-length codes ('ACTIVE', ISO country codes) — padded with spaces
VARCHAR(n) Variable text up to 32,672 bytes per row
CLOB(n) Long text (articles, JSON blobs) — stored out-of-row

Example 3: Date and time

CREATE TABLE event_log (
  event_date  DATE,                    -- YYYY-MM-DD
  event_time  TIME,                    -- HH:MM:SS
  event_ts    TIMESTAMP,               -- YYYY-MM-DD HH:MM:SS.ffffff
  event_tsz   TIMESTAMP WITH TIME ZONE -- timezone-aware
);

INSERT INTO event_log VALUES (
  CURRENT_DATE,
  CURRENT_TIME,
  CURRENT_TIMESTAMP,
  CURRENT_TIMESTAMP
);

Output of SELECT * FROM event_log:

EVENT_DATE  EVENT_TIME  EVENT_TS                       EVENT_TSZ
----------  ----------  -----------------------------  ----------------------------------------
2026-05-14  09:42:11    2026-05-14-09.42.11.293045     2026-05-14-09.42.11.293045 +05:30

Example 4: Special types

CREATE TABLE special_demo (
  is_active   BOOLEAN,
  doc_xml     XML,
  photo       BLOB(2M),
  uuid_col    CHAR(36) FOR BIT DATA,    -- raw bytes (UUID style)
  full_name   VARCHAR(120) GENERATED ALWAYS AS (first_name || ' ' || last_name),
  first_name  VARCHAR(60),
  last_name   VARCHAR(60)
);

A generated column (GENERATED ALWAYS AS) is computed automatically — you can't insert into it.

Example 5: Choosing the right type

-- A realistic employee table
CREATE TABLE employees (
  employee_id   INTEGER       NOT NULL PRIMARY KEY,
  email         VARCHAR(254)  NOT NULL UNIQUE,
  hire_date     DATE          NOT NULL DEFAULT CURRENT_DATE,
  salary        DECIMAL(11,2) NOT NULL CHECK (salary > 0),
  manager_id    INTEGER,
  is_active     BOOLEAN       NOT NULL DEFAULT TRUE,
  notes         CLOB(64K)
);

Notes & Tips

  • Always prefer VARCHAR over CHAR unless the column is truly fixed-length (like ISO country codes).
  • DECIMAL(p,s) is exact: p total digits, s digits after the decimal point. DECIMAL(11,2) fits up to 999,999,999.99.
  • TIMESTAMP precision is 6 by default (microseconds). You can change with TIMESTAMP(0) (seconds only) up to TIMESTAMP(12) (picoseconds).
  • A VARCHAR column up to 32,672 bytes is stored inline; longer needs CLOB.
  • BOOLEAN was added in Db2 11. Before that you'd use CHAR(1) with 'Y'/'N' or SMALLINT 0/1.
  • DB2 implicit casting can mask bugs. Use explicit CAST(x AS DECIMAL(10,2)) when mixing types.

Practice Exercises

  1. Create a books table with: integer book_id PK, varchar title (max 200), date published_on, decimal price (8 digits, 2 decimal), boolean in_stock.
  2. Insert a row using CURRENT_DATE for the published_on column.
  3. Add a generated column summary to books that concatenates '[' || title || ']'.

Quick Quiz

Q1. Why should you never use REAL or DOUBLE for currency?

Show answer

They are binary floating-point types — many decimal fractions (like 0.1) can't be represented exactly. Repeated arithmetic accumulates rounding errors. Use DECIMAL(p,s) for money: it's stored as exact decimal digits with no rounding.

Q2. What's the difference between CHAR(10) and VARCHAR(10)?

Show answer

CHAR(10) always uses 10 characters of storage — shorter strings are padded with trailing spaces. VARCHAR(10) uses only the bytes you actually store (plus a small length prefix). For variable-length data, VARCHAR is almost always better.

Q3. What's special about a column declared GENERATED ALWAYS AS (expr)?

Show answer

DB2 computes the value automatically from the expression — you cannot INSERT or UPDATE it directly. It's recomputed whenever the source columns change, useful for derived values like full-name or total-price columns.

Next Up

You've got tables — let's start filling them with data using INSERT, UPDATE, DELETE, and SELECT.