Mockpreps
🧑💼
🪴
Login now and master your exams with Mockpreps’ mock tests!
Top Interview Questions
DB2 refers to a family of data management products developed by IBM, most commonly known today as IBM Db2. It is a relational database management system (RDBMS) designed to store, manage, and retrieve structured data efficiently. DB2 has been widely used in enterprise environments for decades, particularly in industries that require high reliability, scalability, and performance such as banking, insurance, healthcare, and government systems.
DB2 is a database system that allows users and applications to interact with data using structured query language (SQL). It supports relational database concepts, where data is organized into tables consisting of rows and columns. Each table represents an entity, and relationships between tables are maintained using keys.
Originally introduced by IBM in the 1980s, DB2 was designed to run on mainframe systems. Over time, it evolved into a multi-platform database system that supports various operating systems including Linux, UNIX, and Windows. Modern versions of DB2 are part of IBM’s broader data platform and include advanced features for analytics, cloud integration, and AI-driven workloads.
DB2 is based on the relational model, where data is structured into tables. Each table has:
Rows (records): Represent individual data entries
Columns (fields): Represent attributes of the data
Relationships between tables are established using primary keys and foreign keys, ensuring data integrity and reducing redundancy.
DB2 uses SQL as its primary language for interacting with data. SQL allows users to:
Insert new records
Query existing data
Update records
Delete records
Create and manage database structures
DB2 supports both standard SQL and extended SQL features, enabling complex queries, joins, subqueries, and aggregations.
DB2 is known for its ability to handle large volumes of data and high transaction loads. It is optimized for:
Parallel processing
Efficient indexing
Query optimization
Memory management
These features make it suitable for enterprise-grade applications that require fast response times and high throughput.
Security is a critical aspect of DB2. It provides:
Authentication and authorization mechanisms
Role-based access control
Encryption of data at rest and in transit
Auditing capabilities
These features help organizations protect sensitive data and comply with regulatory requirements.
DB2 includes mechanisms to ensure that data is always available, even in the event of hardware or software failures. These include:
Replication: Copying data across multiple systems
Clustering: Running multiple database instances for redundancy
Backup and recovery tools: Protecting against data loss
This makes DB2 suitable for mission-critical applications.
While primarily a relational database, DB2 supports various types of data, including:
Structured data (tables)
XML data
JSON data
Large objects (LOBs) such as images, videos, and documents
This flexibility allows developers to store and manage diverse data formats within a single system.
Modern versions of IBM Db2 include built-in capabilities for analytics and integration with machine learning tools. It supports:
In-database analytics
Data warehousing
Integration with AI/ML frameworks
Real-time data processing
These features enable organizations to derive insights directly from their data without needing separate analytics systems.
DB2 follows a multi-layered architecture that typically includes:
An instance is a logical environment where databases operate. It manages configuration settings, memory allocation, and system resources.
A database is a collection of related data stored in tables. Each database is self-contained and managed independently.
Tablespaces are storage structures that group related tables and indexes. They help organize how data is physically stored on disk.
The buffer pool is a memory area where frequently accessed data is cached. This improves performance by reducing disk I/O operations.
DB2 uses transaction logs to ensure data consistency and durability. Changes are recorded in logs before being permanently applied to the database.
DB2 is widely used in enterprise environments for a variety of applications:
DB2 is commonly used in core banking systems, transaction processing, fraud detection, and financial reporting due to its reliability and security features.
Online retail systems use DB2 to manage product catalogs, user accounts, orders, and transactions.
Hospitals and healthcare providers use DB2 to manage patient records, appointments, billing, and medical histories.
Government agencies rely on DB2 for citizen databases, tax systems, and administrative records.
DB2 supports large-scale data warehousing solutions where organizations analyze historical data to generate insights and reports.
Robust performance for large-scale applications
Strong data integrity through ACID compliance (Atomicity, Consistency, Isolation, Durability)
Cross-platform support
Advanced security features
Scalability for growing workloads
Integration with modern technologies such as cloud and AI
Cost: Enterprise editions can be expensive
Complexity: Requires skilled administrators and developers
Learning curve: Not as beginner-friendly as some other database systems
Vendor dependency: Primarily tied to IBM ecosystem
DB2 competes with other relational database systems such as Oracle Database, Microsoft SQL Server, and PostgreSQL. Compared to these:
DB2 is often preferred in IBM-centric enterprise environments
It excels in mainframe and large-scale transactional systems
It provides strong integration with IBM tools and platforms
Each database has its strengths, and the choice depends on factors like cost, ecosystem, scalability needs, and existing infrastructure.
IBM Db2 is a powerful and mature relational database management system designed for enterprise-level data handling. With its strong focus on performance, scalability, security, and reliability, DB2 has remained a trusted solution for organizations managing critical workloads for decades.
It supports a wide range of applications—from transaction processing to analytics—and continues to evolve with modern capabilities such as cloud deployment and AI integration. While it may have a steeper learning curve and higher cost compared to some alternatives, its robustness and enterprise-grade features make it a valuable choice for organizations that require dependable and high-performing database systems.
Answer:
DB2 (now called IBM Db2) is a relational database management system (RDBMS) developed by IBM. It is used to store, manage, and retrieve structured data using SQL.
Key features:
Supports relational data model
Uses SQL for querying
High performance and scalability
ACID compliance (Atomicity, Consistency, Isolation, Durability)
Supports both OLTP and OLAP workloads
Db2 is widely used in enterprise environments such as banking, insurance, and telecom systems.
Answer:
DB2 architecture consists of:
Instance: Logical environment that manages database objects and memory
Database: Collection of tables, indexes, and data
Buffer Pools: Memory areas used to cache data pages
Tablespaces: Logical storage containers for tables and indexes
Tables: Store actual data in rows and columns
Log Files: Record changes for recovery
System Catalog: Metadata repository containing information about database objects
Answer:
A table is a database object that stores data in rows and columns.
Rows represent records
Columns represent attributes
Each column has a data type (INTEGER, VARCHAR, DATE, etc.)
Example:
CREATE TABLE EMPLOYEE (
ID INT,
NAME VARCHAR(50),
SALARY DECIMAL(10,2)
);
Answer:
A primary key is a column (or combination of columns) that uniquely identifies each row in a table.
Characteristics:
Must be unique
Cannot contain NULL values
Only one primary key per table
Example:
CREATE TABLE EMPLOYEE (
ID INT PRIMARY KEY,
NAME VARCHAR(50)
);
Answer:
A foreign key is a column in one table that refers to the primary key of another table.
Purpose:
Maintains referential integrity
Establishes relationships between tables
Example:
CREATE TABLE DEPARTMENT (
DEPT_ID INT PRIMARY KEY,
DEPT_NAME VARCHAR(50)
);
CREATE TABLE EMPLOYEE (
EMP_ID INT PRIMARY KEY,
DEPT_ID INT,
FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT(DEPT_ID)
);
Answer:
DB2 is an IBM enterprise-grade RDBMS optimized for high performance and large-scale systems.
MySQL is open-source and commonly used for web applications.
Oracle is also enterprise-grade but has different licensing and ecosystem.
Differences:
DB2 has strong integration with IBM mainframes
DB2 supports advanced workload management features
DB2 uses specific tools like Data Studio for administration
SQL syntax is mostly standard but may have vendor-specific extensions
Answer:
A schema is a logical container that groups database objects like tables, views, and indexes.
Purpose:
Organizes objects
Avoids naming conflicts
Example:
CREATE SCHEMA SALES;
CREATE TABLE SALES.ORDERS (
ORDER_ID INT,
AMOUNT DECIMAL(10,2)
);
Answer:
A tablespace is a storage structure in DB2 that defines where data is physically stored.
Types:
System-managed space (SMS)
Database-managed space (DMS)
Tablespaces help:
Manage storage efficiently
Separate data for performance and maintenance
Answer:
An index is a database object that improves the speed of data retrieval.
Works like a lookup table
Reduces full table scans
Can be unique or non-unique
Example:
CREATE INDEX idx_emp_name ON EMPLOYEE(NAME);
Trade-off:
Faster reads
Slightly slower writes (INSERT/UPDATE/DELETE)
Answer:
Isolation levels define how transactions interact with each other.
Common isolation levels:
UR (Uncommitted Read): Allows dirty reads
CS (Cursor Stability): Prevents reading uncommitted data
RS (Read Stability): Ensures consistent reads within a transaction
RR (Repeatable Read): Prevents non-repeatable reads and phantom reads
Higher isolation = more consistency but less concurrency.
Answer:
A transaction is a sequence of SQL operations treated as a single unit of work.
Properties (ACID):
Atomicity: All or nothing
Consistency: Database remains valid
Isolation: Transactions don’t interfere
Durability: Changes are permanent after commit
Commands:
COMMIT;
ROLLBACK;
Answer:
Normalization is the process of organizing data to reduce redundancy and improve integrity.
Normal forms:
1NF: Eliminate repeating groups
2NF: Remove partial dependency
3NF: Remove transitive dependency
Benefits:
Reduces duplication
Improves data consistency
Answer:
A view is a virtual table based on the result of a SQL query.
Does not store data physically
Simplifies complex queries
Enhances security by restricting access
Example:
CREATE VIEW EMP_VIEW AS
SELECT NAME, SALARY FROM EMPLOYEE;
Answer:
DELETE
Removes specific rows
Can use WHERE clause
Can be rolled back
TRUNCATE
Removes all rows
Faster than DELETE
Cannot use WHERE
Usually cannot be rolled back
DROP
Deletes the entire table structure along with data
Answer:
SQL (Structured Query Language) is used to interact with DB2 databases.
Types:
DDL (CREATE, ALTER, DROP)
DML (INSERT, UPDATE, DELETE)
DQL (SELECT)
DCL (GRANT, REVOKE)
TCL (COMMIT, ROLLBACK)
Example:
SELECT * FROM EMPLOYEE WHERE SALARY > 50000;
Answer:
NULL represents missing or unknown data.
Important points:
Not equal to 0 or empty string
Requires special handling using IS NULL / IS NOT NULL
Example:
SELECT * FROM EMPLOYEE WHERE SALARY IS NULL;
Answer:
Joins are used to combine data from multiple tables.
Types:
INNER JOIN: Matching records only
LEFT JOIN: All records from left table
RIGHT JOIN: All records from right table
FULL JOIN: All records from both tables
Example:
SELECT E.NAME, D.DEPT_NAME
FROM EMPLOYEE E
INNER JOIN DEPARTMENT D
ON E.DEPT_ID = D.DEPT_ID;
Answer:
The system catalog is a set of tables that store metadata about database objects.
It contains information about:
Tables
Columns
Indexes
Schemas
Users and privileges
Used by DB2 internally and by developers for querying metadata.
Answer:
A buffer pool is memory used by DB2 to cache data pages.
Purpose:
Reduce disk I/O
Improve performance
When data is requested:
DB2 first checks buffer pool
If not found, it reads from disk and stores it in memory
Answer:
Backup and recovery ensure data protection.
Backup: Copy of database at a point in time
Recovery: Restoring data from backup and logs
Types:
Full backup
Incremental backup
Log-based recovery
Commands:
BACKUP DATABASE dbname TO /path;
RESTORE DATABASE dbname FROM /path;
Focus on SQL fundamentals
Understand indexes, joins, normalization
Be clear on transactions and isolation levels
Know basic DB2 architecture
Practice writing SQL queries
Be ready for scenario-based questions
Answer:
Db2 follows a client-server architecture and includes:
Instance
Logical environment that manages Db2 databases. An instance can have multiple databases.
Database
Collection of objects (tables, indexes, views, etc.).
Buffer Pool
Memory area where data pages are cached to reduce disk I/O.
Log Files (Transaction Logs)
Used for recovery and ensuring ACID properties.
Database Manager (DBM) Configuration
Controls instance-level settings.
Database Configuration (DB CFG)
Controls database-specific parameters.
Tablespaces
Logical storage containers for tables and indexes.
Answer:
Instance
Logical environment
Manages memory, processes, and connections
Can contain multiple databases
Database
Physical collection of data
Contains tables, indexes, schemas
Exists within an instance
👉 One instance → multiple databases.
Answer:
Db2 uses a Query Optimizer that:
Parses SQL query
Generates multiple execution plans
Estimates cost based on:
Table statistics
Index availability
Data distribution
CPU/I/O cost
Chooses the lowest-cost execution plan
Key tools:
EXPLAIN tables
Visual Explain tools
Runstats for statistics
Answer:
RUNSTATS collects statistics about database objects.
Purpose:
Helps optimizer choose efficient execution plans
Example:
RUNSTATS ON TABLE schema.table_name AND INDEXES ALL;
Impact:
Outdated statistics → poor query performance
Fresh stats → accurate cost estimation
Answer:
Clustered Index
Determines physical order of data in table
Only one per table
Improves range queries
Non-clustered Index
Separate structure pointing to data rows
Multiple indexes allowed
Useful for lookups and joins
Answer:
Steps:
Identify the query
Use monitoring tools or snapshot monitoring
Check execution plan
Use EXPLAIN
Analyze statistics
Ensure RUNSTATS is up-to-date
Check indexing
Missing or unused indexes
Look for bottlenecks
Full table scans
Sort operations
Joins on large datasets
Tune query
Rewrite query
Add indexes
Optimize joins
Answer:
Buffer pools are memory areas that store frequently accessed data pages.
Benefits:
Reduces disk I/O
Improves query performance
Tuning considerations:
Size of buffer pool
Page size (4K, 8K, 16K, 32K)
Workload patterns
Poor buffer pool sizing → increased disk reads → slow performance.
Answer:
Locking ensures data consistency and isolation in concurrent environments.
Types of locks:
Row-level locks
Table-level locks
Page-level locks
Lock modes:
Share (S)
Exclusive (X)
Update (U)
Answer:
A deadlock occurs when two or more transactions wait for each other to release locks.
Db2 handling:
Deadlock detection mechanism runs periodically
One transaction is chosen as a victim and rolled back
Prevention strategies:
Access objects in consistent order
Keep transactions short
Use appropriate isolation levels
Answer:
Offline Backup
Database is shut down
Consistent but causes downtime
Online Backup
Database remains accessible
Requires archive logging enabled
Command:
BACKUP DATABASE dbname TO /backup/location;
Answer:
Log archiving ensures transaction logs are stored for recovery.
Modes:
Circular logging (limited recovery)
Archive logging (supports point-in-time recovery)
Why important:
Enables recovery to a specific point in time
Essential for production systems
Answer:
Restoring database to a specific timestamp using:
Full backup
Logs (archived + active)
Use case:
Recover from accidental data deletion or corruption
Answer:
HADR (High Availability Disaster Recovery) provides failover capability between primary and standby databases.
Modes:
Synchronous
Asynchronous
Near-synchronous
Benefits:
High availability
Disaster recovery
Minimal downtime
Answer:
Tablespaces are logical storage containers for database objects.
Types:
System-managed space (SMS)
Database-managed space (DMS)
Usage:
Tables and indexes are stored in tablespaces
Helps manage storage efficiently
Answer:
Create indexes when:
Columns are frequently used in WHERE clauses
Columns used in JOIN conditions
Columns used in ORDER BY / GROUP BY
Avoid excessive indexing because:
Slows down INSERT/UPDATE/DELETE
Increases storage overhead
Answer:
Tools and methods:
Snapshot monitoring
Event monitors
Db2 Explain tools
Performance metrics:
Buffer pool hit ratio
Lock waits
Sort overflows
CPU usage
Answer:
Missing indexes
Outdated statistics
Poor query design
Large table scans
Insufficient memory (buffer pool)
Lock contention
Inefficient joins
Answer:
Changes in data volume
RUNSTATS updates
Index changes or missing indexes
Query execution plan changes
Parameter/config changes
Buffer pool hit ratio
Lock contention
Increased concurrent workload
Answer:
Monitor tablespace usage
Add containers to tablespaces
Archive or purge old data
Partition large tables
Reorganize tables/indexes
Optimize storage allocation
Answer:
REORG reorganizes table data to improve performance.
Why needed:
Fragmentation over time
Inefficient data access
Command:
REORG TABLE schema.table_name;
Benefits:
Improved access speed
Reduced fragmentation
Better index efficiency
