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?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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,

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.