How to Query Data in DB2: Step-by-Step Tutorial

IBM DB2 is a powerful relational database management system widely used in enterprise environments for data storage and management. Querying data effectively in DB2 using SQL can unlock valuable insights and support decision-making processes. This tutorial covers the essentials to get you started querying DB2 databases efficiently.

Prerequisites

Ensure you have IBM DB2 installed and running. For installation guidance, see our related tutorial How to Install DB2 Database: Step-by-Step Guide.
Access credentials to the DB2 database with sufficient privileges to query data.
Basic understanding of SQL syntax and database concepts.
DB2 command line processor (CLP) or any SQL client tool connected to your DB2 instance.

1. Connecting to the DB2 Database

Use the DB2 CLP or your preferred SQL client. On terminal/command prompt with CLP, run:

db2 connect to your_database_name user your_username using your_password

If the connection is successful, you will see a confirmation message.

2. Basic Data Retrieval Using SELECT

The core command to fetch data is SELECT. Basic syntax:

SELECT column1, column2 
FROM schema_name.table_name;

Example: To retrieve all columns from a table named employees within schema hr:

SELECT * FROM hr.employees;

This returns every row and column from the table.

3. Filtering Query Results with WHERE

Use the WHERE clause to restrict rows returned. For example, to find employees in department number 10:

SELECT emp_id, emp_name, department 
FROM hr.employees 
WHERE department = 10;

4. Sorting Data with ORDER BY

Sort the results by one or more columns:

SELECT emp_id, emp_name, salary 
FROM hr.employees 
ORDER BY salary DESC;

This lists employees from highest to lowest salary.

5. Using Aggregate Functions

DB2 supports functions like COUNT, SUM, AVG, MAX, and MIN for summary data:

SELECT department, COUNT(*) AS employee_count, AVG(salary) AS avg_salary 
FROM hr.employees 
GROUP BY department;

This groups employees by department, showing counts and average salaries.

6. Joining Tables for Combined Data

To query related data from multiple tables, use JOINs. Example joining employees and departments tables:

SELECT e.emp_name, d.department_name 
FROM hr.employees e 
JOIN hr.departments d ON e.department = d.department_id;

7. Limiting Rows Returned

DB2 allows limiting result rows with FETCH FIRST n ROWS ONLY:

SELECT * FROM hr.employees 
FETCH FIRST 10 ROWS ONLY;

This returns only the first 10 rows.

Troubleshooting Common Issues

Connection failures: Verify credentials, network access, and that the DB2 server is running.
Syntax errors: Check SQL case consistency, proper commas, semicolons, and clause order.
No data returned: Confirm table contains data, and your WHERE filters are correct.
Permissions denied: Ensure your user has SELECT privileges on targeted schemas/tables.