DB2 Basics

Difficulty: Beginner · ~7 min read

Overview

IBM DB2 (often written Db2) is a family of relational database products developed by IBM. It was one of the first commercial implementations of Edgar F. Codd's relational model (1983) and powers some of the largest mission-critical workloads in banking, insurance, and government — including IBM's own backbone systems on the mainframe.

DB2 comes in three core editions, each tuned for a different platform:

Edition	Runs on	Typical use
Db2 LUW (Linux, UNIX, Windows)	x86 / Linux servers, containers	Most modern enterprise deployments
Db2 for z/OS	IBM Z mainframes	High-volume OLTP, financial systems
Db2 for i	IBM Power (AS/400)	ERP and business apps on IBM i

There is also a free Db2 Community Edition (LUW) for development and learning — that's what we use throughout this tutorial.

DB2 speaks standard SQL (it's heavily standards-compliant) and adds its own procedural language called SQL PL for stored procedures, triggers, and functions.

Syntax

A minimal DB2 statement looks just like standard SQL:

SELECT first_name, last_name
FROM   employees
WHERE  department_id = 10
ORDER  BY last_name;

DB2 statements end with a semicolon when run from the db2 CLP (command-line processor) or a script.

Examples

Example 1: Connecting and running a query

-- Connect to a database from the DB2 CLP
db2 connect to SAMPLE user db2admin using <password>

-- Run a simple query
db2 "SELECT current_date FROM sysibm.sysdummy1"

Output:

1
----------
2026-05-14

  1 record(s) selected.

SYSIBM.SYSDUMMY1 is DB2's equivalent of Oracle's DUAL — a one-row pseudo-table for scalar expressions.

Example 2: Listing your tables

SELECT tabname, tabschema
FROM   syscat.tables
WHERE  tabschema = CURRENT_USER
ORDER  BY tabname;

SYSCAT.TABLES is the system catalog view that describes all tables — every DB2 database exposes the same SYSCAT.* family for metadata.

Example 3: A tiny end-to-end CRUD flow

CREATE TABLE products (
  product_id  INTEGER     NOT NULL PRIMARY KEY,
  name        VARCHAR(80) NOT NULL,
  price       DECIMAL(9,2)
);

INSERT INTO products VALUES (1, 'Widget', 9.99);

SELECT * FROM products;

Output:

PRODUCT_ID   NAME    PRICE
-----------  ------  -------
          1  Widget    9.99

Notes & Tips

DB2 is case-insensitive for identifiers by default — Employees, EMPLOYEES, and employees all refer to the same object. If you wrap a name in double quotes ("Employees"), it becomes case-sensitive.
The default schema is your username unless you change it with SET CURRENT SCHEMA = ….
DB2 is one of the few mainstream databases that natively understands XML as a first-class type (pureXML), not just as text.
SYSCAT.* views are the standard, portable way to inspect metadata across all Db2 LUW versions.
For ad-hoc work, prefer the db2 CLP on Linux/Windows or IBM Data Studio / Db2 Console for a graphical experience.

Practice Exercises

Use SYSCAT.SCHEMATA to list every schema in your database. (hint: SELECT schemaname FROM syscat.schemata;)
Find out which version of DB2 you're connected to. (hint: SELECT service_level FROM TABLE(sysproc.env_get_inst_info()))
Create a one-column table called greetings, insert the string Hello, DB2, and read it back.

Quick Quiz

Q1. What is the DB2 equivalent of Oracle's DUAL table?

Show answer

SYSIBM.SYSDUMMY1 — a system-provided one-row, one-column table you can SELECT from when you need to evaluate a scalar expression with no real source table.

Q2. By default, are unquoted identifiers in DB2 case-sensitive?

Show answer

No. DB2 stores unquoted identifiers in uppercase internally. employees, Employees, and EMPLOYEES all resolve to EMPLOYEES. Double-quoted identifiers like "emp" are case-sensitive.

Q3. Which DB2 edition runs on IBM mainframes?

Show answer

Db2 for z/OS. It's a separate codebase from Db2 LUW and is tuned for very high-volume OLTP workloads on IBM Z hardware.

Next Up

Next we install Db2 Community Edition locally and run our first connection — pick Windows, Linux, or Docker.