Results 1 to 7 of 7

Thread: cluster index

  1. #1
    Join Date
    Mar 2004
    Posts
    448

    Unanswered: cluster index

    DB2 ver7/8 on aix

    if a cluster index is created on a table that already has data , do db2
    make changes to that data , so that the logical order of the
    data is same as the physcial order.
    I know that the new data inserts follow the cluster index order.

    Does that mean that reorginzation is the only method(not counting the
    loading in specific order) that provide the same physical/logical ordering.

    It means that the correct way of doing that is:

    create cluster index.
    reorg table on that index.

    clarification:

    logical ordering: means the oder in which the index created.
    physcial ordering: means the rows in the pages.

    regards,

    mujeeb

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Creating a clustering index (or altering an index to be clustering) does not change the existing data in the table until a reorg is done. When the reorg is done, the table rows are re-ordered, and free space is created on each page for new inserts (which DB2 will try to do on the correct page according to the clustering index).

    Therefore the amount of percent free on the table needs to be coordinated with the reorg frequency and the insert/delete activity in between reorgs to make sure that sufficient free space is available for new rows on each page (unless data is added at the end of the table). The same percent free parameter needs to be established for indexes, taking into account how often they will be reorged.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi Marcus_A,

    Quote Originally Posted by Marcus_A
    Creating a clustering index (or altering an index to be clustering)
    How can index be altered? Can you post some command? Is this new feature of db2 v8?

    Thanks,
    Grofaty

  4. #4
    Join Date
    Mar 2004
    Posts
    448
    I have done some testing which I want to share.

    1. create a table,load data into that.

    2. runstats on that table.

    3. create a cluster index on that table and insert into that more.I find not much change in clusterfactor( there is also a clusterratio but it remains -1 through out my test).

    4. create another index on that table and check its clustorfactor.

    it varies through out the test supporting that only 1 cluster index is on a table.

    5. Now I run reorg, it makes clusterfactor on the cluster index close to 1.

    It also changes the clusterfactor of the other index but not much.

    6. Now I deleted a lot of data. the clusterfactor changes but not much may be due to the datadistribution.

    7. reorg the table again and get the same 1 on clusterfactor.

    the clusterfactor of the other index remains the same.

    regards,

    mujeeb

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Sorry about that. Indexes cannot be altered in DB2 for UNIX. Linux, and Windows. Indexes can be altered in DB2 for OS/390 and z/OS, but the clustering attribute cannot be changed.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    When you run reorg on version 8, did you also reorg the indexes. I believe on version 8 it is possible to reorg the table only.

    Also, when running runstats, did you specify stats be gatthered on the indexes indexes?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Mar 2004
    Posts
    448
    yes it is possible to reorg table and indexes seperatly.
    I did reorg the table and indexes both.

    I will again check this thing to be absolutely sure.

    regards,

    mujeeb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •