IBM DB2 Interview Questions

DB2 is IBM's family of relational database products. The three main editions are:

• Db2 LUW — Linux, UNIX, Windows. Most modern enterprise deployments.
• Db2 for z/OS — IBM mainframes. Different codebase tuned for OLTP at extreme scale.
• Db2 for i — IBM Power (formerly AS/400). Integrated with the IBM i OS.

A free Db2 Community Edition of LUW exists for development (up to 4 cores, 16 GB RAM).

A one-row, one-column system table DB2 provides for evaluating scalar expressions that don't need a real source table — the equivalent of Oracle's DUAL. Example: SELECT CURRENT_DATE FROM SYSIBM.SYSDUMMY1.

Use the SQL-standard FETCH FIRST n ROWS ONLY clause. For pagination, combine with OFFSET: ... ORDER BY id OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY (DB2 11.1+).

CHAR(n) always uses n characters of storage, padding shorter values with trailing spaces. VARCHAR(n) stores only the bytes you write plus a tiny length prefix. Use VARCHAR for variable-length text; reserve CHAR for genuinely fixed-length codes (ISO country codes, etc.).

Atomicity (all-or-nothing), Consistency (constraints hold after every transaction), Isolation (concurrent transactions don't see each other's partial state), Durability (committed changes survive crashes).

A PRIMARY KEY is NOT NULL and UNIQUE, and there can only be one per table — it's the row's identity. UNIQUE columns allow NULLs (multiple NULLs are not considered duplicates in DB2), and a table can have many unique constraints. Both create unique indexes under the hood.

WHERE filters rows before grouping; HAVING filters groups after aggregation. Example: WHERE region = 'EU' filters individual rows; HAVING COUNT(*) > 10 filters groups with more than ten members. WHERE cannot reference aggregate functions; HAVING can.

MERGE INTO target USING source ON match_condition performs an upsert — runs WHEN MATCHED THEN UPDATE ... or WHEN NOT MATCHED THEN INSERT ... in one atomic statement. Use it for nightly sync jobs, replication staging tables, and any pattern where you'd otherwise write "if exists update else insert" in application code.

• DELETE FROM t — removes rows, fully logged, can be rolled back, table remains
• TRUNCATE TABLE t IMMEDIATE — removes all rows fast, minimally logged, table remains
• DROP TABLE t — removes table definition and data entirely

• UR (Uncommitted Read) — dirty reads allowed; cheapest
• CS (Cursor Stability) — default; only sees committed; locks current row
• RS (Read Stability) — rows you read stay stable for the whole transaction
• RR (Repeatable Read) — full serializable

An index that contains every column the query needs — both filter columns and projection columns. DB2 satisfies the query entirely from index leaf pages without touching the table. Use CREATE INDEX … (filter_cols) INCLUDE (other_cols) to add non-key columns to the index without bloating its sort order.

A logical storage container that maps tables to physical disk (or memory). DB2 lets you have multiple tablespaces per database, each with its own page size, buffer pool, and containers. This separation enables I/O distribution and per-tablespace backup/recovery.

A Materialized Query Table — DB2's equivalent of a materialized view. It stores the result of a query physically and can be automatically used by the query optimizer for query rewrite when the user issues a similar query. Common for aggregation-heavy reporting workloads.

When you INSERT or UPDATE through the view, DB2 verifies the new row still satisfies the view's WHERE clause. Without it, you can write rows that "fall outside" the view — they get stored but are invisible through the view.

INNER JOIN returns only rows where the join condition matches in both tables. LEFT JOIN returns every row from the left table plus matching rows from the right — when there's no match, the right-side columns are NULL. Use LEFT JOIN when "missing on the right side" is information you care about (e.g. departments with no employees).

Because the WHERE runs after the join. For unmatched rows the right side is NULL, and NULL = X is unknown (filtered out). Move the filter into the ON clause to keep the unmatched left rows.

RUNSTATS updates the statistics DB2's cost-based optimizer uses to choose query plans — row counts, value distributions, index cardinalities. Out-of-date stats are the #1 cause of bad plans. Run it after large data changes, bulk loads, or any time the optimizer's choices look surprising.

Run EXPLAIN PLAN FOR <query>, then format the plan with db2exfmt. Look for IXSCAN (good — used an index) vs. TBSCAN (full table scan). The combination IXSCAN + FETCH means the index found the row IDs, then DB2 fetched the rows. An IXSCAN with no FETCH means index-only access — the index covered everything the query needed.

A special index that controls the physical order of rows on disk. After REORG, rows are stored in clustering-key order. Range scans on the clustering key are then very fast because adjacent rows are on adjacent pages. A table can have only one clustering index.

DB2 runs a background deadlock detector at a configurable interval (default 10 seconds). When a deadlock is found, DB2 picks a victim (usually the transaction with the smaller log), rolls it back, and returns SQLCODE -911 reason code 2 to that transaction. Your application code should retry on -911 RC2.

BEFORE triggers run before the row change is applied — they can modify the NEW row or reject the operation with SIGNAL. AFTER triggers run after the change is applied — they can read the final state but can't change it. Use BEFORE to validate and rewrite; use AFTER to log and react.

A savepoint is a named marker inside a transaction. SAVEPOINT sp1 ON ROLLBACK RETAIN CURSORS creates one; ROLLBACK TO SAVEPOINT sp1 undoes only the work done after sp1 (the transaction stays open and earlier work is preserved). Useful for "try-this-then-retry" patterns inside a longer transaction.

When you genuinely need per-row procedural logic that can't be expressed in a single SQL statement: branching to different external systems, calling stored procedures per row, complex conditional rewriting, or interactive paging. For most batch transformations, a single UPDATE, MERGE, or INSERT ... SELECT is dramatically faster.

The default DB2 CLP terminator is ;. SQL PL procedure / trigger bodies contain ; internally — between statements, after END IF, etc. With the default terminator, the CLP would cut the body off at the first internal ;. Use db2 -td@ -vf script.sql to set a different terminator (@) just for that script.

CS is the default and right for most OLTP. Use WITH UR for dashboards and reports where speed > strict consistency. Use RR only when business logic truly needs serialisability.

DB2's columnar storage option (introduced in DB2 10.5). Tables can be created ORGANIZE BY COLUMN, storing each column compressed in its own structure. For analytic / scan-heavy workloads this can be 10–100× faster than row-organized storage. Trade-off: slower point-row inserts, so it's best for data warehouses, not OLTP.

In order:

1. EXPLAIN PLAN FOR <query> — has the access path changed?
2. Check SYSCAT.TABLES.STATS_TIME — when were stats last collected?
3. If stats are stale or the table has grown significantly, RUNSTATS WITH DISTRIBUTION AND DETAILED INDEXES ALL.
4. Re-EXPLAIN. Most of the time the plan switches back.
5. If not, check MON_GET_PKG_CACHE_STMT for the actual rows-fetched, look for skew, and consider an index or query rewrite.

The most common causes, in order of likelihood:

1. Too many indexes — every insert maintains every index. Audit SYSCAT.INDEXES; drop unused ones.
2. Foreign-key cascade overhead — verify referenced columns have indexes on the child side.
3. Triggers running per-row — review SYSCAT.TRIGGERS; consider statement-level alternatives.
4. Lock contention — db2pd -locks to inspect.
5. Log device saturated — measure I/O.

A common pattern:

1. Create the new table with the new schema.
2. Backfill in chunks (commit per chunk) from the old table.
3. Add a trigger on the old table to mirror new writes into the new table — or use CDC / Replication.
4. Once caught up and verified, switch app reads to the new table.
5. Once writes are also using the new table, drop the trigger/CDC and the old table.

Lock timeout — the session waited for a lock longer than CURRENT LOCK TIMEOUT allowed. Different from RC2 (deadlock). Causes: long-running transactions holding locks, missing COMMITs, isolation level too high (RR on hot tables), or genuine contention. Fix: shorten transactions, commit more frequently, drop to CS/UR where appropriate, or add the right index so updaters touch fewer rows.

Out-of-date optimizer statistics. Run RUNSTATS after large data changes. Without fresh stats, DB2 underestimates or overestimates cardinalities and picks the wrong access path.

MERGE is atomic and race-free at the database level. The app-side check-then-act pattern has a race window where another session can insert the row between your check and your insert, causing a duplicate-key error. MERGE evaluates the match condition and runs UPDATE or INSERT in a single, locked operation.

db2exfmt -d <database> -1 -o <outfile>. The -1 means "the most recent explain". This writes a human-readable plan to <outfile> you can inspect for access methods, costs, and join order.