---
title: "Everything You Need to Know [About] Alter Index in DB2"
description: "Here is an SQL query to alter an index in DB2. When to drop the index and when not required while altering the index, added guidelines for your reference."
url: https://srinimf.com/2022/03/19/everything-you-need-to-know-about-alter-index-in-db2/
date: 2022-03-19
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2022/03/pexels-photo-6303709.jpeg?fit=1880%2C1253&ssl=1
categories: ["SQL"]
tags: ["Alter Index"]
type: post
lang: en
---

# Everything You Need to Know [About] Alter Index in DB2

Creating an index on a Table increases search speed (you will get results quicker). Coming to indexes, you can create more than one on a Table. Don’t be confused with the Primary key.

Indexes are two types-Unique indexes and Non-unique indexes. It is up to you to [decide the number of indexes](https://srinimf.com/2020/10/11/sql-more-indexes-are-bad-tricky-interview-question/). Here’re the examples that use [in DB2](https://srinimf.com/2018/02/20/db2-index-questions/).

## [How to](https://srinimf.com/2011/10/06/db2-spufi-comparison-timestamp/) create an index

Below is the syntax to create an index. The** Unique** states that the values in this column should be unique. Here, the column name is DEPTNO. The ‘ASC’ denotes in ascending order.

```
CREATE UNIQUE INDEX SCHEMA.INDEX_NAME 
     ON SCHEMA.DEPT(DEPTNO ASC);
```

## How to alter an index

Alter index in DB2, which you can use to change the parameters. Moreover, suppose you added a new column to a table, and you want to add that column to the index. Yes, you can do that. Here is the way.

```
ALTER TABLE SCHEMA.DEPT ADD COLUMN ZIPCODE CHAR(5); 
ALTER INDEX SCHEMA.INDEX_NAME ADD COLUMN (ZIPCODE); 
COMMIT;
```

In other ways, suppose you want to delete a column from an index, then [you need](https://srinimf.com/2010/04/03/db2-dba/) to drop index, and then [you need to](https://srinimf.com/2014/07/03/db2-v10-5-an-overview/) re-create it.

## How to drop an index

Coming to drop an index, here is a syntax to drop. Here the stores_demo is a database. The ‘prod’ is an instance. The ‘Informix’ is the name of the owner.

```
DROP INDEX stores_demo@prod:"informix".SCHEMA.INDEX_NAME;
```

If data is present in the table, and you want to delete a column from an index, first drop index, then recreate it with column names as needed.

Shopping

[Best Sellers in Home & Kitchen](https://www.amazon.in/gp/bestsellers/kitchen?_encoding=UTF8&linkCode=ib1&tag=srinimf-21&linkId=amzn1.curatedcontent.A21TJRUUN4KGV.2e530050-9472-4d77-97b5-c487432167c5_1648135516869&ref_=ihub_curatedcontent_2e530050-9472-4d77-97b5-c487432167c5)

Related

- [7 DB2 Helpful Questions And Answers](https://srinimf.com/2014/05/18/ibm-db2-zos-questions-and-answers/)DateMay 18, 2014
- [DB2 SQL: How to Create Index (1 of 2)](https://srinimf.com/2015/08/09/db2-different-sql-queries-for-creating-index-1-of-2/)DateAugust 9, 2015
- [Indexes in DB2 These 3 are Must Know Rules](https://srinimf.com/2018/02/20/db2-index-questions/)DateFebruary 20, 2018

Get new content delivered directly to your inbox.

Email Address

Sign up

Related[SQL Views: Tricky Interview Question Asked in Many Companies](https://srinimf.com/2020/05/17/sql-views-present-after-modifying-table/)

### 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%2F2022%2F03%2F19%2Feverything-you-need-to-know-about-alter-index-in-db2%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2022%2F03%2Fpexels-photo-6303709.jpeg%3Ffit%3D1200%252C800%26ssl%3D1&description=Everything%20You%20Need%20to%20Know%20%5BAbout%5D%20Alter%20Index%20in%20DB2)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2022/03/19/everything-you-need-to-know-about-alter-index-in-db2/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2022/03/19/everything-you-need-to-know-about-alter-index-in-db2/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2022/03/19/everything-you-need-to-know-about-alter-index-in-db2/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2022/03/19/everything-you-need-to-know-about-alter-index-in-db2/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20Everything%20You%20Need%20to%20Know%20%5BAbout%5D%20Alter%20Index%20in%20DB2&body=https%3A%2F%2Fsrinimf.com%2F2022%2F03%2F19%2Feverything-you-need-to-know-about-alter-index-in-db2%2F&share=email)
-

### Like this:

Like Loading…