asked on

How do I defragment indexes in DB2?

All,
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?

Thanks!

jsthursday

check this info out

http://www.databasejournal.com/scripts/article.php/3466971
http://www.sswug.org/searchresults.asp?keywordstofind=De-fragment
http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/c0005495.htm

Member_2_2484401

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).

HTH,
DaveSlash

Inward_Spiral

ASKER

In this case, I'm using the windows flavor.

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lowfatspread

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

momi_sabag

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

ghp7000

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.