Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    85

    Unanswered: CLUSTER INDEX and Primary Key

    What is the rationale of the divergence between UDB z/OS and UDB non-mainframe that when creating the 1st index on the mainframe it's the clustering index while on non-mainframe by creating a table with a constraint of the primary key (and the index gets created automatically) that it's not a clustering index. And in fact, on the non-mainframe side, one would have to create a unique index specifying CLUSTER (or CLUSTERING ?) for the index to be considered the CLUS index.
    Ruby Stepansky

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 for Linux, Unix, and Windows
    Clustering (or ordering the rows) of table data (by defining an index as clustered) is relatively new to DB2 for Linux, Unix, and Windows versions (unfortunately, all DB2 platforms are called UDB these days). Before clustering was available, new INSERTs were always at the end of the table instead of DB2 trying to maintain the clustering sequence. Clustering during INSERTs is still optional on DB2 for Linux, Unix, and Windows. However, it has always been the case that the table rows can be ordered by a particular index chosen during the REORG process if no clustered index is defined.

    DB2 for Linux, Unix, and Windows
    On OS/390 (and z/OS), clustering has been there from the beginning (or at least from version 1.3 when I started with DB2 in 1987). On the mainframe DB2, clustering is always assumed even if not explicitly defined. If no clustering is ever explicitly defined, DB2 mainframe will use the first index created to be the clustering index. There is no way to order rows during a REORG other than to use the clustering index (either explicitly defined or the first index defined if no clustering index is defined). It is never a good idea to default clustering to the first index defined.

    Other considerations
    However, it is now possible on all platforms to specify that table rows are always INSERTed at the end (APPEND) even if a clustering index exists (by default or by definition).

    In the case where the index is created automatically because of a primary-key definition, then one can easily go back and ALTER the index to be the clustering index. This applies to all platforms.

    Choosing a clustering index
    Generally speaking a primary key which consists of a single unique number should not be the clustering index. For example, on the sample employee table, employee_id should not be the clustering index. Either last_name, or dept_no would be better choices.

    However, on tables where the primary key consists of multiple columns, then the primary key is often a good candidate for clustering. For example, on the order-item table (primary key is order_no, item_no), then the primary key would be a good candidate for the clustering index to keep the rows of the table in that order during INSERTs or after a REORG.

    If I failed to answer your question adequately, please ask again.

Posting Permissions

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