DB2 Interview Questions

These are the DB2 questions most often asked in mainframe developer interviews, with short answers you can speak in your own words.

Database basics

• Q: What is DB2? A: IBM's relational database management system for mainframes (z/OS) and distributed platforms. Data is stored in tables made of rows and columns.
• Q: What is a tablespace? A: The physical storage container where table data lives. Tables are created inside tablespaces, which sit in databases and storage groups.
• Q: Primary key vs unique key? A: A primary key uniquely identifies each row and cannot be NULL; a table has only one. A unique key also enforces uniqueness but allows one NULL and a table can have many.
• Q: What is normalization? A: Organizing tables to remove duplicate data, usually up to third normal form, so each fact is stored once.

Program and SQL concepts

• Q: What is a plan and a package? A: A package holds the bound access path for one DBRM (one program). A plan is a collection of packages that a program executes; the plan is what you bind to run.
• Q: What is a cursor? A: A pointer used in COBOL programs to fetch multiple rows one by one from a result set, using DECLARE, OPEN, FETCH, and CLOSE.
• Q: What is a null indicator? A: A small integer host variable paired with a column host variable; -1 means the column value is NULL.
• Q: SPUFI vs QMF? A: SPUFI runs SQL interactively from TSO/ISPF and is used by developers for testing. QMF is a query and reporting tool used more by business users.
• Q: What is a bind? A: The process that converts SQL in a program into an optimized access path stored as a plan or package. It checks authorization and syntax.

Errors and performance

• Q: What does SQLCODE -911 mean? A: Deadlock or timeout. Your transaction could not get a lock in time. The usual fix is to retry, commit more often, and check for lock contention.
• Q: What does SQLCODE -803 mean? A: Duplicate key on INSERT or UPDATE. A unique index or primary key was violated.
• Q: What does SQLCODE -805 mean? A: The DBRM or package was not found in the plan. Rebind the plan with the missing DBRM.
• Q: What is RUNSTATS? A: A utility that collects table and index statistics so the DB2 optimizer can choose the best access path.
• Q: Clustered vs non-clustered index? A: A clustering index keeps table rows in the index key order physically; only one per table. Non-clustering indexes are logical pointers only.