50 DB2 Interview Questions and Answers (2026)

Whether you are preparing for a mainframe developer role, a DB2 DBA position, or a data engineer interview, this comprehensive question bank covers the full range of DB2 topics that appear in technical interviews. Questions are organised from foundational to advanced.

DB2 Architecture and Fundamentals

Q1. What is DB2?

DB2 is IBM's relational database management system (RDBMS), available in two main variants: DB2 for z/OS (running on IBM Z mainframe hardware under z/OS) and DB2 LUW (Linux, Unix, Windows — running on distributed servers). Both use SQL and share many concepts but differ in architecture, administration, and deployment.

Q2. What is the difference between DB2 for z/OS and DB2 LUW?

DB2 z/OS runs on IBM Z mainframes with z/OS as the operating system, uses VSAM ESDS datasets for storage, and is administered through JCL utilities and DSN operator commands. DB2 LUW runs on commodity servers, uses file-based storage, and is administered through the db2 CLI and SQL. z/OS provides tighter integration with CICS, IMS, and Parallel Sysplex for extreme high availability.

Q3. What is a DB2 subsystem?

A DB2 subsystem is a running instance of DB2 on z/OS, identified by a four-character SSID (e.g., DSN1). Each subsystem has its own catalog, logs, and buffer pools. A single z/OS LPAR can run multiple subsystems for development, test, and production.

Q4. What is the DB2 catalog?

The DB2 catalog is a set of system tables storing metadata about all database objects — tables, indexes, views, stored procedures, packages, and more. On z/OS it lives in DSNDB06 and uses the SYSIBM schema. On DB2 LUW it uses the SYSCAT schema. Developers query the catalog with standard SQL.

Q5. What are buffer pools in DB2?

Buffer pools are memory caches where DB2 holds recently accessed data pages. Reading from memory is thousands of times faster than disk I/O. DB2 for z/OS uses named buffer pools (BP0, BP8K0, BP16K0, BP32K) for different page sizes. On DB2 LUW, the buffer pool size is configured in the database configuration.

Q6. What is the difference between a tablespace and a database in DB2?

A database in DB2 is a named container for tablespaces and indexes — primarily an administrative grouping. A tablespace is the physical storage unit within a database — it maps to actual storage (VSAM datasets on z/OS, files on LUW) and contains table data. One database contains multiple tablespaces; one tablespace typically contains one table (best practice).

SQL Questions

Q7. What is the FETCH FIRST clause in DB2?

FETCH FIRST n ROWS ONLY limits the number of rows returned by a query. DB2 does not use LIMIT (MySQL) or TOP (SQL Server). Example: SELECT * FROM EMPLOYEE ORDER BY SALARY DESC FETCH FIRST 10 ROWS ONLY.

Q8. What is the difference between DELETE and TRUNCATE in DB2?

DELETE removes rows one at a time, logs each deletion, respects triggers and referential integrity, and can be rolled back. TRUNCATE removes all rows in a single operation, is minimally logged (faster), does not fire row-level DELETE triggers, and cannot be rolled back after commit. Use TRUNCATE for large tables when you want to remove all data quickly.

Q9. What is the MERGE statement in DB2?

MERGE (also called UPSERT) combines INSERT and UPDATE in a single statement. If a row with the specified key exists, it is updated; if not, a new row is inserted. Example:

MERGE INTO EMPLOYEE AS T
USING (VALUES ('999001', 'JAMES', 'WILSON', 65000.00)) AS S (EMPNO, FIRSTNME, LASTNAME, SALARY)
ON T.EMPNO = S.EMPNO
WHEN MATCHED     THEN UPDATE SET SALARY = S.SALARY
WHEN NOT MATCHED THEN INSERT (EMPNO, FIRSTNME, LASTNAME, SALARY)
                      VALUES (S.EMPNO, S.FIRSTNME, S.LASTNAME, S.SALARY);

Q10. What is COALESCE in DB2?

COALESCE(expr1, expr2, ...) returns the first non-NULL value in the list. Used to replace NULLs with defaults: SELECT COALESCE(BONUS, 0) FROM EMPLOYEE returns 0 for employees with NULL bonus.

Q11. What is the difference between INNER JOIN and LEFT OUTER JOIN?

INNER JOIN returns only rows with matching values in both tables. LEFT OUTER JOIN returns all rows from the left table plus matching rows from the right — unmatched right-side rows appear as NULLs.

Q12. What is a WITH clause (CTE) in DB2?

A Common Table Expression is a named temporary result set defined before the main query using the WITH keyword. It improves readability and allows multiple references to the same subquery. Recursive CTEs can navigate hierarchical data like org charts.

Q13. How do window functions differ from GROUP BY aggregations?

GROUP BY reduces multiple rows to one summary row per group. Window functions (using OVER clause) compute aggregate values while keeping all individual rows in the result. SUM(SALARY) OVER (PARTITION BY WORKDEPT) returns the department total on every row, not just one row per department.

Q14. What does SQLCODE +100 mean?

SQLCODE +100 means no row was found — either a singleton SELECT found no matching row, or a FETCH reached the end of a cursor result set. It is not an error; it is the normal signal to stop fetching.

Q15. What is SQLCODE -803 and how do you handle it?

SQLCODE -803 means a duplicate key violation — an INSERT or UPDATE attempted to create a duplicate value in a unique index or primary key. Handle it by checking SQLCODE after the INSERT and either reporting the duplicate to the user or choosing an alternative key.

Performance and Optimization

Q16. What is EXPLAIN in DB2?

EXPLAIN is a DB2 command that populates the PLAN_TABLE (z/OS) or EXPLAIN tables (LUW) with the query access plan — which indexes are used, join order, whether sorting is needed, and estimated costs. Use it to diagnose slow queries without actually executing them.

Q17. What is ACCESSTYPE 'R' in DB2 PLAN_TABLE?

ACCESSTYPE = 'R' in the PLAN_TABLE means DB2 will perform a tablespace scan — reading every page in the tablespace. For large tables this is slow. The fix is usually to create an appropriate index and run RUNSTATS.

Q18. When would DB2 choose a tablespace scan over an index?

DB2 may choose a tablespace scan when: the query is not selective enough (returns 20-40%+ of rows), statistics are outdated, a function on the indexed column prevents index use, the data type of the predicate doesn't match the column type, or the table is very small (a full scan is cheaper than an index lookup).

Q19. What is a clustering index?

A clustering index defines the physical order in which rows are stored in a tablespace. Queries accessing data in clustering key order benefit from sequential prefetch — DB2 reads consecutive pages in large I/O operations. Each tablespace can have only one clustering index.

Q20. What is RUNSTATS and why is it important?

RUNSTATS collects statistical information about tables and indexes (row counts, cardinalities, column distributions) and stores it in the DB2 catalog. The optimizer uses these statistics to choose access plans. Without current statistics, the optimizer makes poor decisions — choosing tablespace scans instead of indexes or vice versa.

Q21. What is lock escalation in DB2?

Lock escalation occurs when a transaction acquires too many row or page locks (exceeding the LOCKMAX threshold) and DB2 converts them to a single tablespace-level lock. This prevents other transactions from accessing the entire tablespace. Prevent it by committing frequently and using the least restrictive isolation level required.

Q22. What is PREFETCH in DB2 EXPLAIN?

PREFETCH in the PLAN_TABLE indicates that DB2 will read data pages ahead of time to reduce I/O wait time. Sequential prefetch (S) reads pages in large consecutive blocks. List prefetch (L) collects a list of required RIDs first, then fetches them in order. Both improve performance for queries accessing many pages.

Stored Procedures, Triggers, and Cursors

Q23. What is the difference between a stored procedure and a trigger in DB2?

A stored procedure is called explicitly with the CALL statement and can accept parameters and return results. A trigger fires automatically when a specific DML event (INSERT, UPDATE, DELETE) occurs on a table — it cannot be called directly and does not accept explicit parameters.

Q24. What are the parameter modes in a DB2 stored procedure?

IN (input only — caller passes value, procedure reads it), OUT (output only — procedure sets the value, caller reads it), and INOUT (bidirectional — caller passes an initial value, procedure can read and overwrite it).

Q25. How do you handle errors in a DB2 stored procedure?

Use DECLARE HANDLER statements: DECLARE CONTINUE HANDLER FOR SQLSTATE '23505' SET p_status = 'Duplicate'. CONTINUE handlers run the handler code and continue execution; EXIT handlers run the handler code and exit the compound statement. Use GET DIAGNOSTICS CONDITION 1 v_state = RETURNED_SQLSTATE, v_msg = MESSAGE_TEXT to retrieve error details.

Q26. What is a WITH RETURN cursor in DB2?

DECLARE c CURSOR WITH RETURN FOR SELECT... defines a cursor that, when left OPEN, returns its result set to the calling application. Used in stored procedures to return multiple rows to the caller without explicit FETCH logic in the procedure.

Q27. What is WHEN in a DB2 trigger?

The WHEN clause in a trigger is an optional condition filter. The trigger body only executes if the WHEN condition evaluates to TRUE. Example: WHEN (old_emp.SALARY <> new_emp.SALARY) — the audit INSERT only fires when the salary actually changed, not on every UPDATE.

DB2 for z/OS and COBOL

Q28. What is SQLCA in COBOL DB2 programs?

The SQL Communication Area (SQLCA) is a set of fields DB2 populates after every SQL statement. The most important is SQLCODE (0=success, +100=not found, negative=error). Include it with EXEC SQL INCLUDE SQLCA END-EXEC in WORKING-STORAGE. Always test SQLCODE after every EXEC SQL block.

Q29. What is a null indicator variable in COBOL DB2?

A null indicator is a PIC S9(4) COMP variable accompanying a host variable in a SELECT INTO or FETCH. DB2 sets it to -1 if the column is NULL, 0 if not NULL. Without a null indicator, selecting a NULL column into a host variable raises SQLCODE -305.

Q30. What is the difference between a singleton SELECT and a cursor?

A singleton SELECT (SELECT INTO) retrieves exactly one row. SQLCODE -811 if multiple rows returned, +100 if no rows. A cursor is used when a query can return multiple rows — DECLARE, OPEN, FETCH in a loop, CLOSE.

Q31. Why use EXEC CICS SYNCPOINT instead of EXEC SQL COMMIT in a CICS program?

EXEC CICS SYNCPOINT performs a two-phase commit across all resource managers (DB2 + CICS file control + transient data queues). EXEC SQL COMMIT commits only DB2 changes without coordinating with CICS, risking inconsistency. Always use SYNCPOINT in CICS programs.

Q32. What is the BIND process in DB2?

BIND converts a DBRM (produced by the DB2 precompiler) into a DB2 package or plan by validating SQL, checking authorization, and producing an optimised access plan stored in the DB2 catalog. After RUNSTATS or schema changes, REBIND updates the access plan to use new statistics.

Q33. What JCL utility do you run to collect DB2 statistics on z/OS?

RUNSTATS, executed via EXEC PGM=DSNUTILB,PARM='ssid,RUNSTATS' with SYSIN control statements specifying the tablespace, tables, and indexes.

Q34. What is SHRLEVEL CHANGE in DB2 utilities?

SHRLEVEL CHANGE is the online mode for DB2 utilities — the tablespace remains available for reads and writes while the utility runs. It is the standard mode for production RUNSTATS, REORG, and COPY operations.

Locking and Concurrency

Q35. What are the four isolation levels in DB2?

Uncommitted Read (UR), Cursor Stability (CS — default), Read Stability (RS), and Repeatable Read (RR). UR is least restrictive (no read locks); RR is most restrictive (holds all locks until end of unit of work).

Q36. What is SQLCODE -911?

SQLCODE -911 indicates a deadlock — DB2 selected this transaction as the victim of a deadlock situation and rolled it back completely. The application must catch -911 and retry the transaction.

Q37. How do you prevent deadlocks in DB2 applications?

Access resources in a consistent order across all transactions. Keep transactions short. Commit frequently. Avoid holding DB2 locks while waiting for user input (pseudo-conversational CICS design). Use SELECT FOR UPDATE to acquire locks early when update is inevitable.

Q38. What is WITH UR in a DB2 SELECT?

WITH UR (Uncommitted Read) is an isolation level specification appended to a SELECT. The query reads data without acquiring any locks and may see uncommitted (dirty) data from other transactions. Used for dashboard counts and monitoring queries where approximate data is acceptable.

Administration and Recovery

Q39. What is the difference between COPY and BACKUP in DB2?

On DB2 z/OS, COPY is the JCL utility that creates image copies of tablespaces — the backup mechanism for z/OS. On DB2 LUW, BACKUP DATABASE is the CLI command for backup. Both capture a snapshot that can be used with log files to recover to any point in time.

Q40. What is HADR in DB2 LUW?

High Availability Disaster Recovery (HADR) maintains a hot standby database that receives continuous log records from the primary server. If the primary fails, the standby can be promoted to primary with minimal data loss. HADR modes include SYNC (zero data loss) and ASYNC (minimal data loss, better performance).

Q41. What is the difference between SQLCODE -904 and -911?

-904: Unavailable resource — the transaction waited too long for a lock held by another transaction (lock timeout). The failing statement fails but the transaction is not rolled back. -911: Deadlock victim — the transaction is completely rolled back automatically. Both indicate lock contention but require different recovery actions.

Q42. What is REORG and when should it be run?

REORG (Reorganise) physically rebuilds a tablespace or index to reclaim fragmented space, restore clustering order, and improve performance. Run it when REORGCHK reports it is needed (LUW), or when CLUSTERRATIO drops below ~80% or PERCACTIVE drops below ~75% (z/OS). Always take an image copy before running REORG.

Advanced Questions

Q43. What is a Materialized Query Table (MQT) in DB2?

An MQT is a table that physically stores the pre-computed result of a query. Unlike a view (which executes dynamically), an MQT is pre-computed and can be refreshed manually (REFRESH DEFERRED) or automatically (REFRESH IMMEDIATE). With ENABLE QUERY OPTIMIZATION, DB2 can transparently route queries on the base tables to the faster MQT.

Q44. What is Row and Column Access Control (RCAC)?

RCAC is a fine-grained security feature that controls which rows (row permissions) and what column values (column masks) a user can see, regardless of their table-level privileges. Useful for masking sensitive data like salaries, credit card numbers, or health information.

Q45. What is DB2 Data Sharing?

Data Sharing is a Parallel Sysplex feature on z/OS that allows multiple DB2 subsystems (on different LPARs) to access the same data simultaneously. It provides both horizontal scalability and continuous availability — if one LPAR fails, the others continue serving requests.

Q46. What is a DBRM?

A Database Request Module (DBRM) is the output of the DB2 precompiler for a COBOL, PL/I, or C program containing embedded SQL. The DBRM contains the SQL statements extracted from the source code. It is then BINDed to create a DB2 package with an optimised access plan.

Q47. How does DB2 handle implicit type conversion?

DB2 performs implicit type conversion for compatible types (e.g., SMALLINT to INTEGER). However, implicit conversion for predicates on indexed columns can prevent index use — for example, comparing a CHAR(6) EMPNO column with an integer literal forces conversion of every index key before comparison, making index scan impossible. Always match literal types to column types.

Q48. What is OPTIMIZE FOR n ROWS in DB2?

OPTIMIZE FOR n ROWS tells the DB2 optimizer to choose an access plan that returns the first n rows as quickly as possible, even if this makes retrieving all rows slower. Used in CICS programs displaying the first page of results where most users never scroll further.

Q49. What is EXPLAIN STMTCACHE in DB2 for z/OS?

EXPLAIN STMTCACHE shows the access plans currently stored in the Dynamic Statement Cache for recently executed dynamic SQL statements. It lets you analyse the access plans being used for production dynamic SQL without requiring EXPLAIN to be run separately.

Q50. What questions should you ask at the end of a DB2 interview?

Strong candidates ask: What DB2 version is the production environment running? What monitoring tools are in use (Omegamon, OMPE, Query Monitor)? What is the current maintenance strategy (scheduled RUNSTATS frequency, REORG triggers)? What are the biggest performance challenges in the current DB2 environment? Asking these signals genuine interest and practical experience.

Quick Reference: Common SQLCODEs

Continue Your DB2 Journey

This question bank covers the most common topics in DB2 interviews. For deep dives on individual topics, explore the full DB2 Mastery course hub which covers all 27 modules from foundations to advanced performance tuning. Keep the DB2 Cheat Sheet bookmarked as your day-to-day SQL reference.