• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2362
  • Last Modified:

How do I defragment indexes in DB2?

I've spent quite a while at this point working on and off with Microsoft's SQL Server product. One thing I learned (painfully) early on, is that database indexes have to be defragmented, or even re-built from time to time to help performance.

Now that I'm being trained on how to access a DB2 database for work, I'd like to know how to do the same thing here. With SQL, I can re-build indexes using "DBCC REINDEX" or "ALTER INDEX REBUILD", but what's the equivalent in DB2?

1 Solution
Dave FordSoftware Developer / Database AdministratorCommented:

It kind of depends on what "flavor" of DB2 you're using (e.g. Linux/Unix/Windows, zSeries, iSeries, etc)

For iSeries (a.k.a. AS/400 or "system i" or i5), you'd use RGZPFM to rebuild indexes (and re-sort the physical table into primary key order).

Inward_SpiralAuthor Commented:
In this case, I'm using the windows flavor.
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Kent OlsenData Warehouse Architect / DBACommented:

Hi Inward,

DB2 uses different syntax than does SQL Server.  Just like Oracle is different, and MySQL is different, etc....

From the command line interface, do a reorganize.  All of the options will display with:


But usually I just run a reorg for an entire table.  If the table is modestly sized, but fragmented, this can have quite a positive effect.  If the table is quite large, this can take a very long time, so be careful.

  REORG TABLE sometable;

You can also reorganize all of the indexes without reorganizing the table.

  REORG INDEXES all FOR TABLE sometable;

Or you can reorg just a single index

  REORG INDEX someindex;

Depending on what you're going to reorganize, how long it takes, when you are going to reorganize, and the need for concurrent (perhaps production) access, you may want to limit other access.


Good Luck,
reorg is the basic command you want as is ppointed out above...

DB2/udb differs considerably from sql server in the way it deals with and maintains indexes... As a DBA you have to be much more involved in the decision
making process as to how and when you maintain the indexes/statistics ...

table/index clustering is also different... (for a start the clustering index is a separate file rather than being the actual table)...

good luck  
Hi Inward

just a minor note,
KDO suggested you will run reorg for the index
make sure you don;t get confused and run an index rebuild
in db2 much like in sql server there is a difference between the two
reorg - will create the index again based on the data that is already in the index (no table access is necessary), so it is faster than rebuild, and less disruptive (the base table can be accessed), when you run reorg you must know that your index is consistent with the table upon which it is defined

rebuild - will rebuild the index based on the data in the actual table. this process is slower for a single index (but actually may be faster if you have many indexes which you process in parallel), and will not allow access to the base table in most cases (depends on your version)

either way, make sure to use the reorg index option if you only want to defrag
defrag is nice will definately help on the I/O side, especially if you have very large indexes. But for maximum performance, you should reorganize the table and specify a clustering index, or simply create a cluster index and then run the reorg, the cluster index will be used by default.

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now