Mockpreps
🧑💼
🪴
Login now and master your exams with Mockpreps’ mock tests!
Top Interview Questions
IBM DB2 is a family of data management products developed by International Business Machines (IBM). It is a powerful, enterprise-grade Relational Database Management System (RDBMS) designed to store, retrieve, and manage structured data efficiently. DB2 is widely used by large organizations in industries such as banking, finance, insurance, healthcare, telecommunications, and government due to its high performance, scalability, reliability, and security.
DB2 supports the SQL (Structured Query Language) standard and provides advanced features for data warehousing, online transaction processing (OLTP), analytics, and business intelligence. Over the years, DB2 has evolved to support modern workloads such as cloud computing, big data integration, and AI-driven analytics.
IBM introduced DB2 in 1983 for mainframe systems (z/OS). It was one of the earliest commercial implementations of Edgar F. Codd’s relational database model. Initially designed for IBM mainframes, DB2 later expanded to multiple platforms, including UNIX, Linux, and Windows.
Today, DB2 exists in several editions and variants, such as:
DB2 for z/OS (mainframes)
DB2 for Linux, UNIX, and Windows (LUW)
DB2 Warehouse
IBM Db2 on Cloud
IBM has continuously enhanced DB2 with features like in-memory processing, compression, advanced indexing, and integration with Hadoop and Spark.
DB2 follows a client-server architecture, where clients send SQL queries to the DB2 server, and the server processes these requests and returns results.
DB2 Instance
An instance is an environment where DB2 databases run. It contains memory allocation, background processes, and configuration settings.
Database
A database is a collection of data objects such as tables, views, indexes, schemas, and stored procedures.
Table Spaces
Table spaces are logical storage units that contain tables and indexes. They help in organizing and managing physical data storage.
Buffer Pool
The buffer pool is a memory area used to cache data pages from disk, improving performance by reducing disk I/O.
Transaction Logs
DB2 uses logs to ensure data consistency and recovery. All changes are recorded so that transactions can be rolled back or recovered in case of failure.
DB2 Engine
The engine processes SQL statements, optimizes queries, manages locks, and ensures data integrity.
DB2 is optimized for high-speed data processing. Features like query optimization, parallel processing, and in-memory caching enable fast query execution even with large datasets.
DB2 supports vertical and horizontal scalability. It can handle small databases as well as very large enterprise databases with terabytes or petabytes of data.
Security is a major strength of DB2. It supports:
Authentication and authorization
Role-based access control
Data encryption at rest and in transit
Auditing and compliance features
DB2 provides robust backup and recovery mechanisms, including:
Online backups
Point-in-time recovery
Disaster recovery using HADR (High Availability Disaster Recovery)
DB2 offers advanced row and column compression, which reduces storage requirements and improves performance by minimizing disk I/O.
DB2 supports analytical functions, window functions, and integration with machine learning tools, making it suitable for business intelligence and analytics workloads.
DB2 supports standard SQL along with IBM-specific extensions.
DDL (Data Definition Language):
CREATE, ALTER, DROP
DML (Data Manipulation Language):
SELECT, INSERT, UPDATE, DELETE
DCL (Data Control Language):
GRANT, REVOKE
TCL (Transaction Control Language):
COMMIT, ROLLBACK, SAVEPOINT
DB2 also supports:
Stored procedures
Triggers
Views
User-defined functions (UDFs)
DB2 follows the ACID properties of transactions:
Atomicity: All operations in a transaction succeed or fail together.
Consistency: Data remains consistent before and after transactions.
Isolation: Concurrent transactions do not interfere with each other.
Durability: Once committed, data is permanently saved.
DB2 uses locking mechanisms and isolation levels (such as Read Committed, Repeatable Read, and Serializable) to manage concurrent access.
DB2 is heavily used in mission-critical enterprise systems due to its reliability and performance.
Core banking systems
Financial transaction processing
ERP and CRM systems
Data warehousing and reporting
Government record management
DB2 integrates well with enterprise tools such as SAP, IBM Cognos, and other IBM middleware products.
Compared to databases like Oracle, MySQL, and SQL Server, DB2 stands out in:
Mainframe integration
Advanced compression
Enterprise-grade reliability
Large-scale data handling
While open-source databases are popular for smaller applications, DB2 remains a preferred choice for large organizations with complex data and high availability requirements.
Extremely reliable and stable
Excellent performance for large datasets
Strong security and compliance features
Supports multiple platforms
Ideal for enterprise and mission-critical applications
Licensing and maintenance costs can be high
Complex administration compared to some open-source databases
Smaller community compared to MySQL or PostgreSQL
IBM DB2 is a powerful, mature, and enterprise-focused relational database management system. With its strong performance, scalability, security, and reliability, DB2 continues to play a critical role in large-scale business applications. Although it may not be as lightweight or inexpensive as some modern open-source databases, its robustness and enterprise features make it an excellent choice for organizations that require high availability, data integrity, and performance.
In today’s data-driven world, DB2 remains a trusted solution for managing critical business data, especially in environments where stability, security, and scalability are top priorities.
Answer:
DB2 is a relational database management system (RDBMS) developed by IBM. It is used to store, retrieve, and manage data efficiently. DB2 supports SQL (Structured Query Language) and runs on multiple platforms such as Linux, UNIX, Windows, and mainframes (z/OS).
Key features:
High performance and scalability
Strong security
ACID compliance
Supports OLTP and OLAP
Automatic tuning and optimization
Answer:
Some common DB2 versions are:
DB2 for LUW (Linux, UNIX, Windows)
DB2 for z/OS (Mainframe)
DB2 Express / Express-C
DB2 Enterprise Edition
DB2 Advanced Enterprise Server Edition
Answer:
RDBMS stands for Relational Database Management System. It stores data in the form of tables (rows and columns) and maintains relationships between tables using primary keys and foreign keys.
Answer:
A DB2 instance is a logical environment where databases run. It contains:
Memory allocation
Background processes
Configuration parameters
An instance can manage multiple databases, but each database belongs to only one instance.
Answer:
A tablespace is a logical storage unit where DB2 stores tables and indexes.
Types of tablespaces:
System Managed Space (SMS)
Database Managed Space (DMS)
Automatic Storage Tablespace
Answer:
A schema is a logical container that organizes database objects like tables, views, indexes, and procedures.
Example:
CREATE TABLE HR.EMPLOYEE (...);
Here, HR is the schema name.
Answer:
| Table | View |
|---|---|
| Stores actual data | Stores SQL query |
| Takes physical space | No physical storage |
| Faster access | Slower compared to table |
| Can be indexed | Cannot be indexed |
Answer:
A primary key uniquely identifies each row in a table.
Cannot contain NULL values
Must be unique
Example:
CREATE TABLE STUDENT (
ID INT PRIMARY KEY,
NAME VARCHAR(50)
);
Answer:
A foreign key is a column that creates a relationship between two tables by referencing the primary key of another table.
Answer:
An index improves the speed of data retrieval.
Created on one or more columns
Uses B-tree structure
Reduces table scan
Example:
CREATE INDEX idx_emp_name ON employee(name);
Answer:
Normalization is the process of organizing data to reduce redundancy and improve data integrity.
Normal forms:
1NF – Atomic values
2NF – No partial dependency
3NF – No transitive dependency
Answer:
Denormalization is the process of adding redundancy to improve performance, especially in reporting systems.
Answer:
SQL (Structured Query Language) is used to:
Create database objects
Insert, update, delete data
Retrieve data
Answer:
DDL – CREATE, ALTER, DROP
DML – INSERT, UPDATE, DELETE
DQL – SELECT
DCL – GRANT, REVOKE
TCL – COMMIT, ROLLBACK, SAVEPOINT
Answer:
A cursor is used to process multiple rows one at a time.
Used mainly in:
Stored procedures
Application programs (COBOL, Java)
Answer:
A stored procedure is a precompiled set of SQL statements stored in the database.
Advantages:
Faster execution
Reusable
Better security
Answer:
A trigger is automatically executed when a specific event occurs:
INSERT
UPDATE
DELETE
Answer:
COMMIT: Saves all changes permanently
ROLLBACK: Reverts changes made in a transaction
Answer:
A deadlock occurs when two or more transactions wait for each other to release locks, resulting in no progress.
DB2 automatically detects and resolves deadlocks by rolling back one transaction.
Answer:
Locking controls concurrent access to data.
Types of locks:
Row lock
Page lock
Table lock
Answer:
Isolation level defines how transactions interact with each other.
DB2 Isolation Levels:
UR (Uncommitted Read)
CS (Cursor Stability)
RS (Read Stability)
RR (Repeatable Read)
Answer:
A package is a bound form of SQL statements used by DB2 for faster execution.
Answer:
Binding is the process of converting SQL statements into executable access paths.
Answer:
Explain Plan shows how DB2 executes a query, including:
Index usage
Join methods
Cost estimation
Answer:
| DELETE | TRUNCATE |
|---|---|
| Removes selected rows | Removes all rows |
| Can rollback | Cannot rollback |
| Slower | Faster |
| Uses WHERE clause | No WHERE clause |
Answer:
Backup: Copy of database data
Recovery: Restoring data after failure
DB2 supports:
Online backup
Offline backup
Incremental backup
Answer:
The DB2 catalog contains metadata information about:
Tables
Indexes
Users
Privileges
Answer:
A sequence generates unique numeric values.
Example:
CREATE SEQUENCE emp_seq
START WITH 1
INCREMENT BY 1;
Answer:
Constraints enforce rules on data.
Types:
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
Answer:
DB2 is preferred because:
High reliability and security
Excellent performance for large databases
Strong transaction management
Enterprise-level support
Sure 👍
Below are MORE DB2 interview questions and answers in detail for freshers, continuing from the basics and moving slightly toward practical understanding. These are commonly asked in entry-level DB2 interviews.
Answer:
A DB2 database is a collection of data stored in an organized manner along with database objects like tables, views, indexes, and procedures. It is created inside a DB2 instance.
Example:
CREATE DATABASE testdb;
Answer:
A buffer pool is an area of memory where DB2 temporarily stores:
Table data
Index pages
It improves performance by reducing disk I/O.
Answer:
A page is the smallest unit of data storage in DB2.
Default page size is 4 KB, but DB2 also supports 8 KB, 16 KB, and 32 KB pages.
Answer:
A Row ID (RID) uniquely identifies the physical location of a row in a table.
Indexes use RIDs to quickly locate rows.
Answer:
Partitioning divides large tables into smaller, manageable parts to improve performance and maintenance.
Types of partitioning:
Range partitioning
Hash partitioning
Database partitioning (DPF)
Answer:
DB2 DPF allows a database to be spread across multiple nodes, enabling:
Parallel processing
Better performance
Scalability
Answer:
The instance owner is the operating system user who:
Creates the DB2 instance
Starts and stops the instance
Has administrative privileges
Answer:
| Deadlock | Timeout |
|---|---|
| Two transactions wait on each other | Transaction waits too long |
| DB2 automatically resolves | Transaction fails after timeout |
| Occurs instantly | Occurs after defined time |
Answer:
Lock escalation occurs when DB2 converts many row/page locks into a table lock to reduce memory usage.
Answer:
RUNSTATS collects statistics about tables and indexes which the DB2 optimizer uses to choose the best access path.
Example:
RUNSTATS ON TABLE employee WITH DISTRIBUTION AND DETAILED INDEXES ALL;
Answer:
REORG reorganizes data physically to:
Remove fragmentation
Improve performance
Reclaim space
Answer:
REBIND updates access paths for packages when:
Indexes are added
RUNSTATS is executed
DB2 version is upgraded
Answer:
LOAD is used for high-speed bulk data loading into tables.
Features:
Faster than INSERT
Minimal logging
Used in data migration
Answer:
IMPORT: Loads data from external files into tables
EXPORT: Extracts data from tables into files
Answer:
| Tablespace | Database |
|---|---|
| Logical storage unit | Collection of tablespaces |
| Stores tables/indexes | Controls overall storage |
| Inside database | Created first |
Answer:
Rollforward recovery restores the database using backup + transaction logs, allowing recovery up to a specific point in time.
Answer:
DB2 logging records changes made to the database for:
Recovery
Rollback
Crash handling
Types:
Circular logging
Archive logging
Answer:
An MQT stores the result of a query physically to improve query performance.
Answer:
Explain snapshot provides detailed runtime information about query execution.
Answer:
DB2 PureScale is a cluster-based architecture that provides:
High availability
Scalability
Continuous operations
Answer:
| CHAR | VARCHAR |
|---|---|
| Fixed length | Variable length |
| Faster | Saves space |
| Wastes space | Efficient storage |
Answer:
NULL represents unknown or missing data, not zero or blank.
Answer:
SQLCODE indicates the result of SQL execution.
0 → Success
Positive → Warning
Negative → Error
Answer:
SQLSTATE is a 5-character code that provides detailed execution status.
Answer:
Package cache stores compiled SQL statements for reuse, improving performance.
Answer:
Dynamic SQL is prepared and executed at runtime.
Example:
PREPARE stmt FROM :sql_string;
Answer:
Static SQL is compiled at bind time and stored in packages.
Answer:
A join combines rows from multiple tables.
Types:
Inner join
Left join
Right join
Full outer join
Answer:
A subquery is a query inside another query.
Answer:
| UNION | UNION ALL |
|---|---|
| Removes duplicates | Keeps duplicates |
| Slower | Faster |
Answer:
DB2 architecture consists of the following major components:
DB2 Instance – The main environment where databases run
Database – Collection of tablespaces
Tablespaces – Logical storage containers
Buffer Pools – Memory area for caching data and index pages
Log Buffer – Stores transaction logs
Package Cache – Stores compiled SQL statements
DB2 uses a cost-based optimizer to determine the most efficient access path.
Answer:
Buffer pools temporarily hold data and index pages in memory.
Tuning steps:
Monitor hit ratio
Increase buffer pool size for frequently accessed tables
Separate data and index buffer pools
Match buffer pool page size with tablespace page size
Answer:
RUNSTATS collects statistics about:
Table cardinality
Index distribution
Data clustering
DB2 optimizer depends heavily on RUNSTATS to choose the best access path. Without updated statistics, queries may perform poorly.
Answer:
| REORG | REBIND |
|---|---|
| Physically reorganizes data | Recreates access paths |
| Removes fragmentation | Uses new statistics |
| Improves I/O | Improves query plans |
Both are often used together after heavy DML activity.
Answer:
Explain Plan shows:
Index usage
Join methods
Sort operations
Estimated cost
Used to identify:
Table scans
Missing indexes
Inefficient joins
Answer:
Locking prevents data inconsistency.
Handling lock issues:
Reduce transaction duration
Use appropriate isolation level
Ensure proper indexing
Monitor lock escalation
Answer:
| Isolation Level | Use Case |
|---|---|
| UR | Reporting |
| CS | OLTP |
| RS | Read consistency |
| RR | Financial systems |
Answer:
Deadlock occurs when transactions wait on each other’s locks.
DB2 detects deadlocks automatically and rolls back one transaction.
Answer:
Package cache stores:
Dynamic SQL statements
Access paths
Benefits:
Reduces compilation overhead
Improves performance
Answer:
| Static SQL | Dynamic SQL |
|---|---|
| Compiled at bind time | Compiled at runtime |
| Faster | Flexible |
| Used in COBOL | Used in Java, scripts |
Answer:
| LOAD | INSERT |
|---|---|
| High-speed bulk load | Row-by-row |
| Minimal logging | Fully logged |
| Faster | Slower |
Answer:
Rollforward recovery uses archived logs to restore the database up to a point in time.
Answer:
Circular logging – Limited recovery
Archive logging – Point-in-time recovery
Answer:
DB2 converts many row/page locks into a table lock to reduce memory usage.
Answer:
Check long-running queries
Analyze Explain Plan
Monitor buffer pool hit ratio
Review locking and waits
Ensure RUNSTATS are updated
Answer:
Database Partitioning Feature allows data to be distributed across nodes for parallel processing.
Answer:
PureScale provides:
High availability
Scalability
Continuous operations
Answer:
Materialized Query Table stores precomputed results for faster reporting queries.
Answer:
Add proper indexes
Rewrite SQL
Avoid SELECT *
Use appropriate joins
Check statistics
Answer:
System tables that store metadata about database objects.
Answer:
Unique index
Non-unique index
Composite index
Clustered index
Answer:
Determines physical row order in a table.
Answer:
Monitoring utilities like:
LOAD
REORG
RUNSTATS
To ensure they complete successfully.
Answer:
High Availability Disaster Recovery provides real-time data replication between primary and standby databases.
Answer:
SQLCODE: DB2-specific
SQLSTATE: ANSI standard
Answer:
Grant minimal privileges
Use roles
Enable auditing
Encrypt data
Answer:
Federation allows DB2 to access data from heterogeneous databases.
Answer:
TRUNCATE is faster, removes all rows, and cannot be rolled back.
Answer:
Generates unique numbers, often used for primary keys.
Answer:
Run RUNSTATS
REORG affected tables
REBIND packages
Check buffer pools
Review indexes
Answer:
Steps followed in real-time:
Identify the session using monitoring tools
Check Explain Plan for table scans or bad joins
Verify index availability
Ensure RUNSTATS are up to date
Check lock waits and blocking sessions
Review buffer pool hit ratios
Rewrite SQL if required
Answer:
Access path defines how DB2 retrieves data (index scan, table scan, join order).
It is important because:
Wrong access path → poor performance
Influenced by statistics, indexes, and query structure
Answer:
Use Explain Plan
Check full table scans
Analyze predicates in WHERE clause
Review frequently executed queries
Answer:
DB2 uses outdated statistics
Optimizer chooses inefficient access paths
Query performance degrades
Unnecessary table scans occur
Answer:
Synchronous – Index updated immediately during DML
Asynchronous – Index updated later, improves performance for bulk operations
Answer:
Page split occurs when a page becomes full and DB2 must split it, causing overhead.
Avoidance:
Use appropriate PCTFREE
REORG tables
Proper clustering index
Answer:
PCTFREE: Percentage of space left free in each page
FREEPAGE: Number of pages between free pages
Used to reduce page splits.
Answer:
Increasing log buffer size:
Reduces disk I/O
Improves commit performance
Helps high transaction systems
Answer:
Archive logging stores logs externally, enabling:
Point-in-time recovery
Rollforward recovery
Disaster recovery
Answer:
DB2 automatically uses logs to restore database consistency after a crash.
Answer:
HADR replicates log data from primary to standby database.
Modes:
SYNC
NEARSYNC
ASYNC
Answer:
Utility heap provides memory to DB2 utilities like LOAD and REORG.
Answer:
Online backup allows users to access database during backup, requiring archive logging.
Answer:
Index compression reduces index size, improving I/O and memory usage.
Answer:
Monitor CPU and memory usage
Check lock waits
Review buffer pool hit ratio
Analyze long-running SQL
Answer:
Federation allows querying multiple databases as one.
Use case:
Generating reports from DB2 and Oracle together.
Answer:
STMM automatically manages memory components such as:
Buffer pools
Sort memory
Lock lists
Answer:
Increase memory parameters
Tune buffer pools
Enable STMM
Reduce concurrent sessions
Answer:
Locks are applied only during commit, reducing contention.
Answer:
Snapshot monitoring captures real-time performance metrics for analysis.
Answer:
Increase log buffer
Use group commit
Reduce commit frequency
Optimize disk I/O
Answer:
Stores temporary objects such as:
Sort data
Hash joins
Intermediate results
Answer:
Reduces storage space and improves performance by minimizing I/O.
Answer:
Occurs when cursors hold locks longer than expected, impacting concurrency.
Answer:
Removing unused objects improves metadata management and performance.
Answer:
Automatically reorganizes tables based on fragmentation thresholds.
Answer:
Use ALTER instead of DROP
Test in lower environments
Schedule downtime if needed
Take backup before changes
Answer:
Explain Plan – Estimated execution
Explain Snapshot – Actual runtime statistics
Answer:
Identify expensive queries
Optimize SQL
Add indexes
Tune memory parameters
Answer:
Identify blocking transactions
Kill long-running sessions if required
Tune isolation levels
Improve indexing
Reduce transaction scope
