DB2 Tutorial – Complete Guide
Learn DB2 from scratch to advanced level. This tutorial covers DB2 architecture, commands, SQL, administration, performance tuning, and real‑world examples.
About This Tutorial
This DB2 tutorial is designed for beginners and professionals who want to learn DB2 in a clear, practical way. Each topic includes explanation, syntax, examples, and best practices.
1.1 What is DB2?
DB2 is a Relational Database Management System (RDBMS) developed by IBM. It is used to store, manage, and retrieve large volumes of data efficiently in enterprise applications.
Key Features
- High performance and scalability
- Secure data management
- Supports SQL for data access
- Handles large databases efficiently
- Supports both online and batch processing
1.2 Why Use DB2?
DB2 is widely used in banking, insurance, healthcare, and retail industries because it provides reliable and fast data processing.
Benefits
- High availability
- Strong security features
- Excellent performance
- Supports large‑scale applications
1.3 DB2 Architecture Overview
DB2 organizes data using key components that work together to store and retrieve information efficiently:
- Databases
- Tablespaces
- Tables
- Indexes
1.4 Real‑World Example
In a banking application:
- Customer details are stored in DB2 tables.
- Account information is retrieved using SQL queries.
- Transactions are processed securely and efficiently.
Summary
DB2 is a powerful relational database system used for storing and managing enterprise data. It provides:
- Fast data access
- High security
- Scalability
- Reliable transaction processing
DB2 is one of the most widely used databases in mainframe and enterprise environments.
2.1 Introduction
DB2 Architecture defines how data is organized, stored, and accessed within a DB2 database system. Understanding the architecture helps developers and DBAs manage databases efficiently.
Benefits
- Better database organization
- Faster data retrieval
- Improved performance
- Easier administration
2.2 Database
A Database is the highest‑level container in DB2 that stores all data objects.
Example
BANKDBA database can contain multiple tablespaces and tables.
2.3 Tablespace
A Tablespace is a logical storage area where tables are stored.
Benefits
- Organizes data efficiently
- Improves storage management
- Enhances performance
Example
CUSTOMER_TS2.4 Table
A Table stores data in rows and columns.
Example
Tables are the primary objects used to store business data.
2.5 Index
An Index is used to speed up data retrieval.
Example
INDEX ON CUSTOMER_IDBenefits
- Faster searches
- Improved query performance
- Reduced response time
2.6 Buffer Pool
A Buffer Pool is memory used to temporarily store frequently accessed data pages.
Benefits
- Reduces disk I/O
- Improves application performance
- Faster query execution
2.7 DB2 Catalog
The DB2 Catalog contains metadata about database objects.
Stores Information About
- Tables
- Indexes
- Views
- Users
- Tablespaces
Summary
The main components of DB2 Architecture are:
- Database – Top‑level container
- Tablespace – Logical storage area
- Table – Stores data
- Index – Speeds up data access
- Buffer Pool – Improves performance
- DB2 Catalog – Stores metadata
Understanding these components is essential for designing, managing, and optimizing DB2 databases.
3.1 Introduction
Before working with DB2, it must be installed and configured properly. A correct setup ensures smooth database creation, application connectivity, and SQL execution.
Benefits
- Ready‑to‑use DB2 environment
- Better database management
- Smooth application integration
3.2 DB2 Installation
DB2 can be installed on various platforms such as:
- Mainframe (z/OS)
- Linux
- UNIX
- Windows
Installation Steps
- Download the DB2 software.
- Run the installation wizard.
- Select installation options.
- Complete the setup process.
3.3 Creating a DB2 Instance
A DB2 Instance is an environment where databases run.
Example
db2icrt db2inst1Purpose
- Manages databases
- Controls DB2 services
- Provides user access
3.4 Creating a Database
After installation, create a database to store data.
Example
CREATE DATABASE BANKDB;Benefits
- Stores application data
- Organizes database objects
3.5 Starting and Stopping DB2
Start DB2
db2startStop DB2
db2stopPurpose
- Start database services
- Stop services for maintenance
3.6 Connecting to a Database
Applications and users must connect to a database before performing operations.
Example
CONNECT TO BANKDB;Purpose
- Access database objects
- Execute SQL statements
3.7 Verifying the Environment
After setup, verify that DB2 is working correctly.
Common Commands
db2leveldb2 list db directoryBenefits
- Confirms successful installation
- Displays database information
Summary
DB2 setup involves:
- Installing DB2 software
- Creating an instance
- Creating a database
- Starting DB2 services
- Connecting to databases
- Verifying the environment
A properly configured DB2 environment provides the foundation for database development, administration, and application support.
4.1 Introduction
A DB2 Database is a logical container that stores application data, while a Tablespace is a storage structure where tables and indexes are physically stored.
Benefits
- Organized data storage
- Better performance
- Easier database management
4.2 What is a Database?
A Database is the highest‑level storage object in DB2.
Example
CREATE DATABASE BANKDB;Purpose
- Stores tables, indexes, and views
- Organizes application data
4.3 What is a Tablespace?
A Tablespace is a logical storage area within a database where tables are stored.
Benefits
- Efficient data organization
- Improved performance
- Better space management
Example
CREATE TABLESPACE CUSTOMER_TS;4.4 Types of Tablespaces
Simple Tablespace
Stores one or more tables together.
Segmented Tablespace
Stores data in segments for better performance.
Partitioned Tablespace
Divides large tables into partitions for faster access.
Universal Tablespace (UTS)
Modern DB2 tablespace combining partitioning and segmentation features.
4.5 Creating a Table in a Tablespace
A table can be created within a specific tablespace.
Example
CREATE TABLE CUSTOMER
(
CUSTOMER_ID INT,
CUSTOMER_NAME VARCHAR(50)
)
IN CUSTOMER_TS;Purpose
- Stores business data
- Uses tablespace storage
4.6 Database Objects
A DB2 database commonly contains:
- Tables
- Tablespaces
- Indexes
- Views
- Stored Procedures
These objects work together to manage data efficiently.
Best Practices
- Use meaningful database and tablespace names.
- Separate large tables into dedicated tablespaces.
- Monitor tablespace usage regularly.
- Use partitioned tablespaces for large datasets.
Summary
DB2 databases store and organize application data, while tablespaces manage the physical storage of tables and indexes.
Key Concepts
- Database – Logical container for data
- Tablespace – Storage area for tables
- Simple Tablespace – Basic storage structure
- Segmented Tablespace – Improved performance
- Partitioned Tablespace – Handles large tables
- Universal Tablespace – Recommended modern tablespace type
Understanding databases and tablespaces is essential for effective DB2 administration and performance management.
5.1 Introduction
A Table is the most important object in DB2. It stores data in rows and columns, making it easy to organize and retrieve information.
Benefits
- Stores business data
- Easy data retrieval
- Supports data manipulation using SQL
5.2 Structure of a Table
A table consists of:
- Rows (Records)
- Columns (Fields)
Example
5.3 Creating a Table
Tables are created using the CREATE TABLE statement.
Example
CREATE TABLE EMPLOYEE
(
EMP_ID INTEGER,
EMP_NAME VARCHAR(50),
DEPARTMENT VARCHAR(20)
);
Purpose
- Store employee information
- Define data structure
5.1 Introduction
A Table is the most important object in DB2. It stores data in rows and columns, making it easy to organize and retrieve information.
Benefits
- Stores business data
- Easy data retrieval
- Supports data manipulation using SQL
5.2 Structure of a Table
A table consists of:
- Rows (Records)
- Columns (Fields)
Example
5.3 Creating a Table
Tables are created using the CREATE TABLE statement.
Example
CREATE TABLE EMPLOYEE
(
EMP_ID INTEGER,
EMP_NAME VARCHAR(50),
DEPARTMENT VARCHAR(20)
);
Purpose
- Store employee information
- Define data structure
5.4 Primary Key
A Primary Key uniquely identifies each row in a table.
Example
EMP_ID INTEGER PRIMARY KEY
Benefits
- Prevents duplicate records
- Ensures data integrity
5.5 Data Types
Each column must have a data type.
Common Data Types
5.6 Altering a Table
The ALTER TABLE statement modifies an existing table.
Example
ALTER TABLE EMPLOYEE
ADD COLUMN SALARY DECIMAL(10,2);
Purpose
- Add columns
- Modify table structure
5.7 Dropping a Table
The DROP TABLE statement removes a table permanently.
Example
DROP TABLE EMPLOYEE;
Purpose
- Delete unused tables
- Free storage space
Best Practices
- Use meaningful table names.
- Define primary keys for all tables.
- Choose appropriate data types.
- Avoid storing duplicate data.
Summary
DB2 Tables are used to store business data in rows and columns.
Key Concepts
- CREATE TABLE – Create a new table
- Primary Key – Unique row identifier
- Data Types – Define column values
- ALTER TABLE – Modify table structure
- DROP TABLE – Delete a table
Tables form the foundation of every DB2 database and are essential for storing and managing application data.
SELECT basics
Learn the essentials of SELECT statements in DB2.
WHERE, ORDER BY
Filter and sort results correctly.
JOIN basics
Combine data from multiple tables using joins.
GROUP BY & HAVING
Aggregate and filter grouped results.
6.1 Introduction
An Index is a database object used to improve the speed of data retrieval from a table. Instead of scanning the entire table, DB2 uses the index to locate records quickly.
Benefits
- Faster query execution
- Improved database performance
- Reduced data access time
6.2 Why Use Indexes?
Without an index, DB2 may need to scan every row in a table to find matching data.
Example
An index helps DB2 locate the record much faster.
6.3 Creating an Index
Indexes are created using the CREATE INDEX statement.
Example
CREATE INDEX IDX_EMPLOYEE
ON EMPLOYEE (EMP_ID);
Purpose
- Speeds up searches
- Improves SELECT query performance
6.4 Unique Index
A Unique Index prevents duplicate values in a column.
Example
CREATE UNIQUE INDEX IDX_EMPID
ON EMPLOYEE (EMP_ID);
Benefits
- Ensures data uniqueness
- Improves data integrity
6.5 Composite Index
A Composite Index is created on multiple columns.
Example
CREATE INDEX IDX_EMP_DEPT
ON EMPLOYEE (EMP_ID, DEPARTMENT);
Benefits
- Faster searches using multiple columns
- Better query optimization
6.6 Dropping an Index
An index can be removed when it is no longer needed.
Example
DROP INDEX IDX_EMPLOYEE;
Purpose
- Remove unused indexes
- Reduce storage overhead
6.7 Advantages and Disadvantages
Advantages
- Faster data retrieval
- Improved query performance
- Efficient searching and sorting
Disadvantages
- Requires additional storage
- Slightly slower INSERT, UPDATE, and DELETE operations
Best Practices
- Create indexes on frequently searched columns.
- Use primary key columns as indexed fields.
- Avoid creating unnecessary indexes.
- Monitor index usage regularly.
Summary
Indexes improve DB2 query performance by providing fast access to table data.
Key Concepts
- CREATE INDEX – Create an index
- UNIQUE INDEX – Prevent duplicate values
- COMPOSITE INDEX – Index multiple columns
- DROP INDEX – Remove an index
Indexes are essential for optimizing database performance, especially when working with large tables containing millions of records.
Subqueries
Use nested queries for complex logic.
CTE (WITH clause)
Use CTEs to improve query readability.
Set operators
Combine result sets using UNION and more.
7.1 Introduction
SQL (Structured Query Language) is the standard language used to interact with DB2 databases. It allows users to create, retrieve, update, and delete data.
Benefits
- Easy data management
- Fast data retrieval
- Industry‑standard database language
7.2 SQL Categories
DB2 SQL commands are divided into different categories:
DDL (Data Definition Language)
Used to create and modify database objects.
Examples: CREATE, ALTER, DROP
DML (Data Manipulation Language)
Used to work with data stored in tables.
Examples: INSERT, UPDATE, DELETE
DQL (Data Query Language)
Used to retrieve data.
Example: SELECT
7.3 SELECT Statement
The SELECT statement retrieves data from a table.
Example
SELECT * FROM EMPLOYEE;
Purpose
- Display records
- Search for information
- Generate reports
7.4 INSERT Statement
The INSERT statement adds new records to a table.
Example
INSERT INTO EMPLOYEE
VALUES (101,'JOHN','IT');
Purpose
- Add new data
- Store business information
7.5 UPDATE Statement
The UPDATE statement modifies existing records.
Example
UPDATE EMPLOYEE
SET DEPARTMENT='HR'
WHERE EMP_ID=101;
Purpose
- Change existing data
- Correct records
7.6 DELETE Statement
The DELETE statement removes records from a table.
Example
DELETE FROM EMPLOYEE
WHERE EMP_ID=101;
Purpose
- Remove unwanted records
- Clean up data
7.7 WHERE Clause
The WHERE clause filters records based on a condition.
Example
SELECT * FROM EMPLOYEE
WHERE DEPARTMENT='IT';
Benefit: Returns only matching records.
Best Practices
- Use WHERE clauses carefully.
- Always test UPDATE and DELETE statements before execution.
- Use meaningful table and column names.
- Retrieve only the required columns instead of using SELECT *.
Summary
SQL is the primary language used in DB2 for managing data.
Common SQL Statements
Understanding SQL fundamentals is essential because every DB2 application uses SQL to interact with database tables.
8.1 Introduction
A DB2 Function is a database object that performs a specific task and returns a value. Functions help simplify SQL queries and reduce repetitive coding.
Benefits
- Reusable logic
- Simplified SQL statements
- Improved code maintenance
- Better performance
8.2 Types of DB2 Functions
Built‑in Functions
Functions provided by DB2.
Examples: UPPER(), LOWER(), COUNT(), SUM(), AVG()
User‑Defined Functions (UDF)
Functions created by developers to perform custom business logic.
8.3 Scalar Functions
A Scalar Function returns a single value for each row.
Example
SELECT UPPER(EMP_NAME)
FROM EMPLOYEE;
Result
8.4 Aggregate Functions
Aggregate Functions operate on multiple rows and return a single result.
Example
SELECT COUNT(*)
FROM EMPLOYEE;
Common Aggregate Functions
- COUNT()
- SUM()
- AVG()
- MAX()
- MIN()
8.5 User‑Defined Function (UDF)
Developers can create custom functions for specific business requirements.
Example
CREATE FUNCTION CALC_BONUS()
Uses
- Salary calculations
- Tax calculations
- Business rule implementation
8.6 Advantages of Functions
Reusability
Write once and use multiple times.
Reduced Coding
Eliminates repetitive SQL logic.
Easy Maintenance
Changes can be made in one place.
Best Practices
- Use built‑in functions whenever possible.
- Create UDFs for reusable business logic.
- Keep functions simple and efficient.
- Avoid unnecessary function calls in large queries.
Summary
DB2 Functions are used to perform calculations and data manipulation within SQL statements.
Main Types
Functions help make SQL code cleaner, reusable, and easier to maintain in DB2 applications.
9.1 Introduction
A View is a virtual table in DB2 that is created from one or more tables. A view does not store data itself; it displays data from underlying tables.
Benefits
- Simplifies complex queries
- Improves security
- Provides customized data access
- Reduces coding effort
9.2 Why Use Views?
Views allow users to access only the required data without exposing the complete table structure.
Example
9.3 Creating a View
A view is created using the CREATE VIEW statement.
Example
CREATE VIEW EMP_VIEW AS
SELECT EMP_ID,
EMP_NAME,
DEPARTMENT
FROM EMPLOYEE;
Purpose
- Simplify data retrieval
- Hide unnecessary columns
- Reuse frequently used queries
9.4 Querying a View
Views can be queried just like normal tables.
Example
SELECT *
FROM EMP_VIEW;
Benefit: Users can retrieve data without knowing the underlying table structure.
9.5 Types of Views
Simple View
Created from a single table.
CREATE VIEW EMP_VIEW AS
SELECT EMP_ID, EMP_NAME
FROM EMPLOYEE;
Complex View
Created from multiple tables or using joins and functions.
CREATE VIEW EMP_DEPT_VIEW AS
SELECT E.EMP_NAME,
D.DEPT_NAME
FROM EMPLOYEE E,
DEPARTMENT D;
9.6 Dropping a View
A view can be removed using the DROP VIEW statement.
Example
DROP VIEW EMP_VIEW;
Purpose
- Remove unused views
- Simplify database maintenance
Advantages of Views
Security
Restricts access to sensitive columns.
Simplicity
Hides complex SQL statements.
Reusability
Allows the same query logic to be used multiple times.
Best Practices
- Use meaningful view names.
- Create views for commonly used queries.
- Restrict access to sensitive information.
- Avoid creating too many nested views.
Summary
DB2 Views provide a virtual representation of data stored in tables.
Key Concepts
Views improve security, simplify query writing, and provide a convenient way to access data in DB2 applications.
10.1 Introduction
An Index is a database object that improves the speed of data retrieval from a table. DB2 uses indexes to find records quickly without scanning the entire table.
Benefits
- Faster query execution
- Improved database performance
- Reduced data access time
- Better search efficiency
10.2 Why Use Indexes?
Without an index, DB2 performs a table scan to locate records.
Example
An index helps DB2 locate the record much faster.
10.3 Creating an Index
Indexes are created using the CREATE INDEX statement.
Example
CREATE INDEX IDX_EMPLOYEE
ON EMPLOYEE (EMP_ID);
Purpose
- Improve search performance
- Speed up SELECT statements
10.4 Types of Indexes
Unique Index
Prevents duplicate values in a column.
CREATE UNIQUE INDEX IDX_EMPID
ON EMPLOYEE (EMP_ID);
Non‑Unique Index
Allows duplicate values.
CREATE INDEX IDX_DEPT
ON EMPLOYEE (DEPARTMENT);
10.5 Composite Index
A Composite Index is created on multiple columns.
Example
CREATE INDEX IDX_EMP_DEPT
ON EMPLOYEE (EMP_ID, DEPARTMENT);
Benefits
- Improves performance for multi‑column searches
- Optimizes complex queries
10.6 Dropping an Index
An index can be removed when it is no longer required.
Example
DROP INDEX IDX_EMPLOYEE;
Purpose
- Remove unused indexes
- Reduce maintenance overhead
Advantages of Indexes
Faster Searches
Quickly locates records.
Better Query Performance
Reduces response time for SELECT statements.
Efficient Sorting
Improves ORDER BY and JOIN operations.
Best Practices
- Create indexes on frequently searched columns.
- Use indexes on primary key columns.
- Avoid excessive indexes on small tables.
- Review and remove unused indexes periodically.
Summary
DB2 Indexes improve data retrieval performance by providing quick access to table records.
Key Concepts
Indexes are essential for optimizing DB2 database performance, especially when working with large tables and complex queries.
11.1 Introduction
DB2 Authorization is the process of controlling who can access and perform actions on database objects such as tables, views, indexes, and databases.
Benefits
- Protects sensitive data
- Controls user access
- Improves database security
- Prevents unauthorized operations
11.2 What is Authorization?
Authorization determines what actions a user is allowed to perform in DB2.
Examples
A user may be allowed to:
- Read data
- Insert records
- Update records
- Delete records
But may not be allowed to create or drop database objects.
11.3 DB2 Privileges
Privileges are permissions granted to users.
Common Privileges
11.4 GRANT Statement
The GRANT statement is used to give permissions to users.
Example
GRANT SELECT
ON EMPLOYEE
TO USER1;
Purpose
- Allow access to database objects
- Assign specific permissions
11.5 REVOKE Statement
The REVOKE statement removes permissions from users.
Example
REVOKE SELECT
ON EMPLOYEE
FROM USER1;
Purpose
- Remove access rights
- Enhance security
11.6 Database Administrator (DBA)
A DBA has the highest level of authority in DB2.
Responsibilities
- Manage users
- Grant permissions
- Monitor database security
- Control database objects
Advantages of Authorization
Security
Protects confidential information.
Controlled Access
Users can access only required data.
Compliance
Helps organizations meet security standards.
Best Practices
- Follow the Principle of Least Privilege.
- Grant only necessary permissions.
- Regularly review user access.
- Revoke unused privileges.
- Limit DBA access to authorized personnel.
Summary
DB2 Authorization controls access to database resources and ensures data security.
Key Concepts
Authorization is a critical part of DB2 security and helps ensure that users can access only the data and functions required for their roles.
12.1 Introduction
DB2 Commands are used to manage databases, tables, users, and database operations. These commands help administrators and developers perform day‑to‑day DB2 activities efficiently.
Benefits
- Simplify database management
- Control database operations
- Monitor system performance
- Manage database objects
12.2 Database Commands
Create Database
Used to create a new database.
CREATE DATABASE BANKDB;
Connect to Database
Used to establish a connection.
CONNECT TO BANKDB;
Disconnect Database
Used to end the connection.
CONNECT RESET;
12.3 Table Commands
Create Table
Creates a new table.
CREATE TABLE EMPLOYEE
(
EMP_ID INTEGER,
EMP_NAME VARCHAR(50)
);
Alter Table
Modifies an existing table.
ALTER TABLE EMPLOYEE
ADD COLUMN SALARY DECIMAL(10,2);
Drop Table
Deletes a table permanently.
DROP TABLE EMPLOYEE;
12.4 Data Manipulation Commands
Insert Data
Adds new records.
INSERT INTO EMPLOYEE
VALUES (101,'JOHN');
Update Data
Modifies existing records.
UPDATE EMPLOYEE
SET EMP_NAME='DAVID'
WHERE EMP_ID=101;
Delete Data
Removes records.
DELETE FROM EMPLOYEE
WHERE EMP_ID=101;
12.5 Query Commands
Select Data
Retrieves records from a table.
SELECT * FROM EMPLOYEE;
Count Records
Returns the total number of rows.
SELECT COUNT(*)
FROM EMPLOYEE;
12.6 Authorization Commands
Grant Privileges
Provides access to users.
GRANT SELECT
ON EMPLOYEE
TO USER1;
Revoke Privileges
Removes access from users.
REVOKE SELECT
ON EMPLOYEE
FROM USER1;
12.7 Utility Commands
db2leveldb2 list db directorydb2startdb2stopBest Practices
- Use commands carefully in production environments.
- Always take backups before major changes.
- Verify SQL statements before execution.
- Grant only required privileges to users.
Summary
DB2 Commands are essential for database administration and development.
Common Commands
These commands form the foundation of DB2 database management and are frequently used in real‑world enterprise applications.
13.1 Introduction
DB2 Utilities are built‑in tools and commands used to maintain, move, and recover data. They help keep databases healthy and performant in real‑world environments.
Common Uses
- Backup and recovery
- Data load and migration
- Performance maintenance
13.2 Backup & Restore
Use backup and restore utilities to protect data and recover from failures.
Quick Examples
BACKUP DATABASE BANKDBRESTORE DATABASE BANKDBTip: Regularly test restores on a non‑production system.
13.3 Load / Import / Export
These utilities help move data between files and DB2 tables.
- LOAD – Fast bulk load (best for large volumes)
- IMPORT – Insert rows from a file
- EXPORT – Write query/table data to a file
Quick Examples
EXPORT TO employee.del OF DEL SELECT * FROM EMPLOYEEIMPORT FROM employee.del OF DEL INSERT INTO EMPLOYEE13.4 RUNSTATS & REORG (Basics)
These utilities support performance by keeping optimizer statistics and storage structures healthy.
- RUNSTATS updates table/index statistics used by the optimizer.
- REORG reorganizes table/index data to improve access efficiency.
Quick Examples
RUNSTATS ON TABLE EMPLOYEE WITH DISTRIBUTION AND DETAILED INDEXES ALLREORG TABLE EMPLOYEE14.1 Overview
Performance tuning in DB2 is the practice of making your database respond faster while using less CPU, memory, and disk I/O. It is not only about “making a query fast”—it is about keeping the whole system stable when many users run workloads together.
What you tune in DB2
- SQL & access paths (how DB2 decides to read data)
- Indexes (support faster filtering, joining, sorting)
- Statistics (helps the optimizer choose the best plan)
- Table / index organization (reduce page splits and random I/O)
- Locks & concurrency (avoid long waits and deadlocks)
Typical symptoms of performance issues
- Queries suddenly become slow after data growth or changes
- High CPU usage during peak hours
- Long lock waits / blocking sessions
- Heavy disk I/O and slow response times
14.2 Quick Wins
These are the most common changes that often give immediate improvements, especially on large tables.
SQL improvements
- Avoid SELECT *; fetch only needed columns
- Use WHERE filters early and correctly (avoid functions on indexed columns when possible)
- Prefer correct JOIN conditions (avoid accidental cross joins)
- Limit result sets when needed (pagination / FETCH FIRST)
Index improvements
- Create indexes on frequently used WHERE and JOIN columns
- Use a composite index when queries filter by multiple columns in a stable order
- Remove unused indexes (too many indexes slow down INSERT/UPDATE/DELETE)
Maintenance quick wins
- Run RUNSTATS after big data loads or large deletes
- Run REORG when tables/indexes become fragmented
- Review long-running transactions that hold locks too long
14.3 Common Tools (RUNSTATS / REORG / EXPLAIN)
RUNSTATS
Updates table and index statistics so the optimizer can choose better access paths.
RUNSTATS ON TABLE EMPLOYEE WITH DISTRIBUTION AND DETAILED INDEXES ALL
REORG
Reorganizes a table or index to reduce fragmentation and improve access efficiency.
REORG TABLE EMPLOYEE
EXPLAIN
Shows how DB2 plans to run a query (join order, index usage, sorts). It helps you understand why a query is slow.
- Is DB2 using an index or doing a full table scan?
- Are there large sorts or temporary tables?
- Are join methods efficient for the data size?
15.1 Overview
Backup & Recovery ensures your DB2 databases can be restored after failures (hardware issues, corruption, human errors).
Goals
- Protect business data
- Reduce downtime
- Support point‑in‑time recovery (when logs are available)
15.2 Backup Types
- Full backup – complete copy of the database
- Incremental backup – only changes since last backup
- Online/Offline – depends on database configuration and needs
BACKUP DATABASE BANKDB
15.3 Restore Basics
A restore brings back a backup image. If logs are available, you can rollforward to reach a consistent or point‑in‑time state.
RESTORE DATABASE BANKDBROLLFORWARD DATABASE BANKDB TO END OF LOGS AND COMPLETETip: Always test recovery regularly.
16.1 Overview
Monitoring helps detect performance issues early and keeps DB2 stable in production environments.
Why it matters
- Find slow queries quickly
- Detect resource bottlenecks
- Prevent outages
16.2 What to Monitor
- Database connections and active sessions
- Lock waits / deadlocks
- Buffer pool and disk I/O
- Log space usage
- Top SQL (highest cost queries)
16.3 Quick Troubleshooting
- Identify the slow statement (top SQL)
- Check locks and long‑running transactions
- Update stats (RUNSTATS) after major loads
17.1 Core Concepts (Short + Detailed)
Short: Instance = DB2 environment; Database = data container inside that environment.
Detailed: An instance includes DB2 services, configuration, and memory structures that manage one or more databases. A database contains objects such as tables, indexes, and views and is created/managed within an instance.
Short: A tablespace organizes where table/index data is stored.
Detailed: Tablespaces help separate data across storage, simplify administration, and improve performance by placing large or high‑I/O objects in dedicated storage areas. They also support better capacity planning and maintenance.
Short: CHAR is fixed‑length; VARCHAR is variable‑length.
Detailed: CHAR pads with spaces to the defined length and is useful when values are always the same size. VARCHAR stores only the actual length (plus overhead) and is better when values vary significantly, saving space.
Short: Primary key uniquely identifies rows; foreign key links tables and enforces relationships.
Detailed: Primary keys prevent duplicates and ensure row identity. Foreign keys ensure referential integrity by restricting values to those that exist in the referenced table’s primary/unique key.
17.2 SQL & Performance (Short + Detailed)
Short: Indexes let DB2 find matching rows quickly without scanning the full table.
Detailed: Indexes speed up filtering (WHERE), joins, and sometimes sorting (ORDER BY). However, they add overhead for writes because INSERT/UPDATE/DELETE must maintain indexes, so choose them carefully.
Short: RUNSTATS updates optimizer statistics so DB2 can pick better query plans.
Detailed: Run it after large loads, mass updates/deletes, or when query performance changes unexpectedly. Without fresh stats, DB2 may choose inefficient access paths.
Short: Identify the SQL → check the plan → fix stats/indexes → retest.
Detailed: Capture the exact statement and parameters, review the access path (EXPLAIN), confirm indexes exist for filters/joins, run RUNSTATS if data changed, and check for sorts, scans, and lock waits. Also review system resource usage during the slowdown.
Short: Locks come from transactions; deadlocks happen when transactions block each other in a cycle.
Detailed: Long transactions, different update order across tables, missing indexes (causing wider scans/locks), and high concurrency can increase lock waits. A deadlock occurs when two sessions each hold locks needed by the other.
17.3 Backup & Recovery (Short + Detailed)
Short: Backup = create a safe copy; Restore = bring it back after loss/failure.
Detailed: Backups are taken regularly as part of a protection plan. Restore is performed during recovery to rebuild the database from a backup image.
Short: Rollforward applies logs after restore to reach a consistent or point‑in‑time state.
Detailed: If logging is enabled, rollforward replays transactions recorded in logs so you can recover changes made after the backup was taken and achieve point‑in‑time recovery.
Short: RPO = acceptable data loss; RTO = acceptable downtime.
Detailed: Decide backup frequency, log retention, and HA/DR architecture based on business needs. Validate by measuring restore + rollforward time in drills, and adjust the strategy until targets are met.
Short: Test regularly (at least quarterly for many systems).
Detailed: Critical systems may require monthly or after major changes. A good test includes restore to a separate environment, rollforward (if applicable), and validation of application connectivity and data correctness.
String functions
Common functions for text processing.
Date/time functions
Work with dates and timestamps.
Aggregate functions
Summarize results using aggregates.
Create & use views
Simplify complex queries with views.
Security with views
Limit access using views.
Performance tips
Understand how views impact performance.
Index types
Common DB2 index types.
Design best practices
How to design indexes effectively.
Common mistakes
Avoid frequent indexing pitfalls.
Authentication vs authorization
Authentication verifies identity; authorization controls permissions.
Privileges & roles
Bundle permissions and manage access effectively.
GRANT & REVOKE basics
Use GRANT to give access and REVOKE to remove it.
Common CLP commands
Use CLP for database administration tasks.
Connect & database commands
Commands to connect and list database directory.
Config commands (GET/UPDATE)
View and update configuration carefully.
Backup utility
Create backups using DB2 utilities.
Restore & rollforward
Restore databases and rollforward logs.
Import/Export/Load
Move data using import/export/load tools.
Index & query tuning
Tune indexes and queries for performance.
RUNSTATS & statistics
Keep optimizer statistics up to date.
Explain plan basics
Analyze access paths using explain plan.
Backup strategies
Plan backups for reliability and speed.
Restore scenarios
Practice restores and validate procedures.
Rollforward concepts
Apply logs for point-in-time recovery.
Key monitoring metrics
Track metrics for performance and health.
Snapshots/monitor views
Use built-in views and tools for monitoring.
Locks & deadlocks
Find and resolve blocking and deadlocks.
DB2 basics Q&A
Common DB2 interview questions on fundamentals.
SQL & performance Q&A
Questions on SQL tuning and performance topics.
Scenario-based questions
Scenario-driven troubleshooting questions.