---
title: "DB2 5 Effective Ways to Tune SQL Queries"
description: "DB2 SQL query performance tuning ideas. Here're the ways that you can use."
url: https://srinimf.com/2021/05/15/db2-5-effective-ways-to-tune-sql-queries/
date: 2021-05-15
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2021/05/pexels-photo-6527065.jpeg?fit=1880%2C1255&ssl=1
categories: ["DB2"]
tags: ["SQL Query Tuning"]
type: post
lang: en
---

# DB2 5 Effective Ways to Tune SQL Queries

Here’re the ways you can tune SQL queries. SQL is the top language in all RDBMS flavors. Below is the tuning list that you can focus on.

## DB2 Performance Tuning

1. Avoid Colum functions in WHERE clause.
2. Preferable not to use NOT clause in WHERE clause.
3. Use only needed columns in GROUP BY and ORDER BY since both do sorting. So, they consume more CPU time.
4. Use very few WHEN conditions in the CASE statement.
5. Code COUNT(*) proper way

*DB2 5 Effective Ways to Tune SQL Queries*

## Avoid Colum functions in WHERE clause

The best example is WHERE YEAR(HIRE_DATE) = 2003. You can code as below.

```
WHERE HIREDATE BETWEEN '2003-01-01' and '2003-12-31'
```

The bottom-line is avoid coding of scalar functions in WHERE clause. What happens when you code DB2 ignore index on that column.

Related[10 Top IDMS Database Interview Questions](https://srinimf.com/2015/06/30/mainframe-top-idms-interview-questions-1-of-5/)

## Preferable not to use NOT clause in WHERE clause

The best example is WHERE NOT HIREDATE > :WS-DATE. You can code as below.

```
Where HIREDATE <= :WS-DATE
```

The takeaway is especially for DATE use <= for better performance.

## Use only needed columns in GROUP BY and ORDER BY since both do sorting. So, they consume more CPU time.

The best example is:

GROUP BY JOIN_DATE, COUNTRY, BASIC_SAL, EMP_ID, BLOOD_GRP. [You need](https://srinimf.com/2016/06/30/tricky-db2-question-on-dclgen/) to use few columns. More columns means more CPU time.

```
GROUP BY JOIN_DATE, COUNTRY
ORDER BY JOIN_DATE, COUNTRY
```

> Here’s more on SQL-for-data-analytics

## Use very few conditions in CASE statement

Here, avoid to use more WHEN conditions.

```
SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, 
CASE 
 WHEN EDLEVEL < 15 THEN 'SECONDARY' 
 WHEN EDLEVEL < 19 THEN 'COLLEGE' 
 ELSE 'POST GRADUATE' 
END 
FROM EMPLOYEE
```

Still you want more, these you have to code in your program (not in SQL query).

## Code COUNT(*) proper way

Use FETCH 1 ROW ONLY in SQL query. Also add check for SQLCODE

```
SELECT COUNT(*)
FROM EMPLOYEE
WHERE EMPLOYEE_ID <= 30100
AND SQLCODE = 0
FETCH 1 ROW ONLY;
```

Related Posts

- [DB2 SQL Query Performance Tuning (1 of 3)](https://srinimf.com/2019/03/17/3-sql-tuning-ideas-on-table-columns-to-apply/)DateMarch 17, 2019
- [How to Delete a Row: SQL Explained](https://srinimf.com/2018/03/08/db2-sql-to-delete-a-row/)DateMarch 8, 2018
- [DB2 SQL Query Performance Tuning (2 of 3)](https://srinimf.com/2018/08/26/db2-performance-methods/)DateAugust 26, 2018

References

- [DB2 SQL For Beginners](https://www.amazon.in/DB2-11-1-LUW-Application-Developers-ebook/dp/B07SLNVYRJ/ref=sr_1_8?dchild=1&keywords=db2+sql&qid=1621088038&sr=8-8?tag=srinimf-21)

SQL Question

Get new content delivered directly to your inbox.

Type your email…

Sign up

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2021%2F05%2F15%2Fdb2-5-effective-ways-to-tune-sql-queries%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2021%2F05%2Fpexels-photo-6527065.jpeg%3Ffit%3D1200%252C801%26ssl%3D1&description=DB2%205%20Effective%20Ways%20to%20Tune%20SQL%20Queries)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2021/05/15/db2-5-effective-ways-to-tune-sql-queries/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2021/05/15/db2-5-effective-ways-to-tune-sql-queries/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2021/05/15/db2-5-effective-ways-to-tune-sql-queries/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2021/05/15/db2-5-effective-ways-to-tune-sql-queries/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20DB2%205%20Effective%20Ways%20to%20Tune%20SQL%20Queries&body=https%3A%2F%2Fsrinimf.com%2F2021%2F05%2F15%2Fdb2-5-effective-ways-to-tune-sql-queries%2F&share=email)
-

### Like this:

Like Loading…