DB2 Basics: What is an Index?

You may also like...

9 Responses

  1. Noel says:

    Have you blogged on index reorganisation? I and I’m sure others would be interested in your experiences on performing index reorganisations against a live ecommerce site. I’ve had problems I think due to the reorg needing to take out the Z lock while the new indexes are swapped in.

    • Ember Crooks says:

      Reorgs are definately on my to-do list, and that issue specifically, and things I’ve learned coming out of the conference. It is coming.

  2. Naveed Shakur says:

    Hi, Can i define an index after creating table, in a separate tablespace.
    I created a table and not mentioned a tablespace for indexing, now i want to create an index in a newly created tablespace, is it possible?

    • Ember Crooks says:

      All indexes for a table live in one tablespace. By default that tablespace is the same as the tablespace the table lives in, unless otherwise specified. If you want to change the tablespace that all indexes for the table live in, you can do that using the admin_move_table command, which can be an online operation, depending on DB2 version and details like RI on the table. Does that help?

  3. rikdeb says:

    Nice artice on index…thnks!

  4. Mickey Barton says:

    If I create an index on a table do I need to run RUNSTATS in order for queries to use the new index that I just created

    • Ember Crooks says:

      You can use syntax to gather runstats when you create it, like this:


      This avoids having to collect statistics on everything. If you don’t use this syntax, then you do need to do runstats after.

  1. September 19, 2013

    […] had an earlier blog entry on the basics of Indexes, but there are so many details to […]

  2. December 23, 2014

    […] Looking at How Much Memory DB2 is Using How to Delete Data From a DB2 Table and Release Disk Space DB2 Basics: What is an Index? Managing db2 transaction log files DB2 Basics: Users, Authentication, and Authorization DB2 Errors: […]

Leave a Reply

Your email address will not be published. Required fields are marked *