---
title: "DB2 Indexes Type 1 Vs. Type 2"
description: "How DB2 manages indexes. There are two popular indexes present in DB2. How to differentiate these two indexes explained in simplified way."
url: https://srinimf.com/2010/07/24/db2-index/
date: 2010-07-24
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2021/08/pexels-photo-6120172.jpeg?fit=867%2C1300&ssl=1
categories: ["DB2", "DB2 V11"]
tags: ["All About DB2 Type-1 and Type-2 Indexes", "DB2 Index Types", "DB2 Tips and Solutions"]
type: post
lang: en
---

# DB2 Indexes Type 1 Vs. Type 2

[![DB2 indexes Typ-1 and Type-2](https://i0.wp.com/srinimf.com/wp-content/uploads/2010/07/id-10059513.jpg?resize=319%2C212&ssl=1)](https://i0.wp.com/srinimf.com/wp-content/uploads/2010/07/id-10059513.jpg?ssl=1)*DB2 indexes Type-1 and Type-2*

Related[Three Secrets About DB2 Row Id](https://srinimf.com/2010/05/21/db2-_row-id/)

You can create [indexes on DB2](http://www.indeed.com/jobs?q=CICS+DB2&indpubnum=6634974704162507) table columns to speed up query processing. An index uses pointers to actual data to more efficiently access specific pieces of data.

Once created, DB2 automatically maintains each index as data is added, modified, and deleted from the table. As data is selected from the table, DB2 can choose [to use](https://srinimf.com/2015/09/11/top-featutures-of-db2-repair-utility/) the index to access the data more efficiently.

It is important to remember that index modification and usage is automatic—you do not need to tell DB2 to modify indexes as data changes nor can you tell DB2 [to use](https://srinimf.com/2015/08/20/db2-max-column-function-best-example/) a particular index to access data. This automation and separation makes it easy to deploy DB2 indexes.

Related[USA-Cross Cultural Sensitivity](https://srinimf.com/2014/07/06/usa-cross-culture-sensitivity/)

**Index:==>[Leaf page](https://srinimf.com/2021/07/26/db2-how-to-understand-b-tree-structure-of-index/) ==> Non leaf page ==> Non leaf page ==> Root Page**

[In DB2](https://srinimf.com/2013/01/05/what-is-new-in-db2-v10-part-1/) t[here is](https://srinimf.com/2023/06/17/the-pandas-in-python-and-key-data-types-to-know/) TYPE2 index being used currently. Load utility updates indexes efficiently. [Type-2 index](http://www.mullinsconsulting.com/db2_type.htm) is an enhancement to the Type-1 index. It has numerous benefits.

Creating index on tables is trial and error process i.e how many or what cloumns need [to use](https://srinimf.com/2016/11/08/how-to-use-alter-table-best-examples-to-save-time/) for INDEX. As of DB2 V8 the [Index Key](https://srinimf.com/2015/08/09/db2-different-sql-queries-for-creating-index-1-of-2/) size is 2,0000 Bytes.

We need to avoid indexes on INSERT, DELETE and UPDATE. For SELECT, we can use indexes, and we can retrieve rows with less CPU time.

We can give index on columns of WHERE, GROUPBY, ORDERBY,UNION ALL etc. DB2 requires [3 types of](https://srinimf.com/2014/05/17/imsdb-different-ssa-types/) Data pages to form an internal INDEX in DB2.

**Before creating index on a Table, we need to analyze the following factors:**

- Percentage of table access versus table modification (that is, the frequency of INSERT, UPDATE, and DELETE operations)
- Data access patterns, both ad hoc and planned
- Amount of data accessed by each query against the table
- Impact on recovery
- Performance requirements of accessing the table
- Performance requirements of modifying the table
- Storage requirements
- Impact of reorganization
- Impact on the LOAD utility

Related[How to keep command does not disappear in TSO command line](https://srinimf.com/2015/09/03/how-to-keep-command-does-not-disappear-in-tso-command-line/)

### 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%2F2010%2F07%2F24%2Fdb2-index%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2021%2F08%2Fpexels-photo-6120172.jpeg%3Ffit%3D800%252C1200%26ssl%3D1&description=DB2%20Indexes%20Type%201%20Vs.%20Type%202)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2010/07/24/db2-index/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2010/07/24/db2-index/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2010/07/24/db2-index/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2010/07/24/db2-index/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20DB2%20Indexes%20Type%201%20Vs.%20Type%202&body=https%3A%2F%2Fsrinimf.com%2F2010%2F07%2F24%2Fdb2-index%2F&share=email)
-

### Like this:

Like Loading…