Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2014
    Posts
    10

    Unanswered: DB2 - Reorg using clusterratio ?

    Dear all,

    Can you help in questions below:

    1.Is that compulsory to do a REORG when the clusterratio is low ?

    2.Can we say for sure, that after REORG clusterratio will be 100% ?

    3. Following is the procedure we do when clusterratio is very low,

    a. Check for CLUSTERRATIO< 60% , if Yes
    b. Do a REORG and RUNSTAT the table , CLUSTERRATIO becomes 100%

    In some cases there is no improvement in CLUSTERRATIO ?

    Need your guidance to what exactly happens ? Tired of surfing.

    regards,
    Baskar

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    is this index the clustering index - because clusterratio is indicated for all indexes and a table can only be organised according 1 index
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jul 2014
    Posts
    10
    Thanks for your reply,

    Its defined as UNIQUE INDEX

  4. #4
    Join Date
    Jul 2014
    Posts
    10

    DB2 - Reorg using clusterratio ?

    thanks for your reply

    its a UNIQUE INDEX

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    A unique index is not necessarily clustered. Only one index in a table can be defined as clustered, and there do not have to be any indexes defined as clustered. Clustering specifies that the data in the table is to be same order as on of the indexes when a reorg is done (certain kinds of reorgs), and does not affect the index itself. Indexes are always in the exact order of the index columns defined. Clustering also has "some" effect on inserts.

    You can read the IBM documentation for REORG command to figure out how, and when, the table data gets re-clustered when a reorg is issued (depending on the type of reorg and reorg options).
    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
    Jul 2014
    Posts
    10
    Hi,

    Consider below are the 3 indexes for the table sample(1-unique,2-nonunique), none of the indexes is defined as CLUSTERED.

    SYSIBM.SYSINDEXES
    HTML Code:
    NAME       UNIQUERULE  COLCOUNT  CLUSTERING  CLUSTERED CLUSTERRATIO
    --+---------+---------+---------+---------+---+---------+---------+--
    Xsample1 P                  3             N            N          3
    Xsample2 D                  2             N            N          94
    Xsample3 D                  2             N            Y          100     
    From which I understand that Xsample3 is implicitly defined as clustered, and during reorg CLUSTERRATIO becomes 100%.

    Other indexes CLUSTERRATIO remains same, for instance Xsample1 is jus 3%, does it means that it is non organized ? If YES how to improve its CLUSTERRATIO ?

    Thanks

  7. #7
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    no index is implicitly defined as clustered
    when creating index you have to specify clustered
    this can be possible
    eg : index on zipcode and index on town
    there is no direct relation between town name (cluster) and zipcode
    it means that data will be ordered by town name but can not at the same time be ordered by zipcode
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  8. #8
    Join Date
    Jul 2014
    Posts
    10
    If the index is not implicitly clustered, then what CLUSTERED='Y' and CLUSTERED='N' referred in SYSIBM.SYSINDEXES?
    Because after REORG, CLUSTERRATIO of indexes that have CLUSTERED='Y' becomes 100%, other indexes remains same (eg: 17% before and after REORG)

    So the CLUSTERRATIO for the other indexes depends on the data and can be <100%?Any harm?

    Thanks again for your replies..

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Your output above looks suspiciously like SPUFI, and since you are using sysibm.sysindexes (instead of syscat.indexes) I assume you are using DB2 z/OS.

    In DB2 z/OS, if you don't define an index as clustered, the first index created is clustered by default. This is not the case in DB2 LUW where it is possible (and often occurs) that no index is defined as clustered.

    Defining an index as clustered, explicitly or by default (first index created), determines the order of the rows in the table after a reorg. Since you can only order the rows in a table one way at a time, and there may be multiple indexes, the cluster ratio for other indexes are usually not 100% after a reorg (unless by coincidence). If you don't understand this, keep thinking about it until you do understand.

    When defining a clustered index, keep in mind that it also affects where DB2 attempts to insert rows in the table, although it only "tries" to put new rows on the correct page (only if there is room on that page), and never in the exact order as the clustering index. If space on the correct data page is not available, then it tries near by pages, or gives up and puts it anywhere there is space available.

    Index rows are always in the exact correct order on a page, even if DB2 has to reorg the page on the fly (split pages, etc).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    there is very simple reading material out there on advantages of clustered indexes vs non-clustered and will depend on what you are doing within your application. You will never have all indexes with perfect cluster ratio.
    When you do not define a clustering index as is the case with the sample table you are using here, then DB2 will simply pick one to cluster the data by, typically, it chooses the first created index as its default clustering index.
    You should decide how your table will be most frequently accessed then make the appropriate index your clustering index to help increase the speed of your most frequent accesses to a table.
    Dave

  11. #11
    Join Date
    Jul 2014
    Posts
    10
    If the first created index is its default clustering index , then the index stat should be CLUSTERING='N' and CLUSTERED='Y' . correct?

    if YES for the above, in my case I have 3 indexes

    CLUSTERING CLUSTERED
    Xsample1 N N
    Xsample2 N Y
    Xsample3 N Y

    How Xsample2 & Xsample3 both clustered by default ?

    Even the case is different in testing , as below

    CLUSTERING CLUSTERED
    Xsample1 N N
    Xsample2 N N
    Xsample3 N N

    Thanks for your continuous support !

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You need to find the doc for the catalog table you are looking at and understand the definition of the CLUSTERED column.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  13. #13
    Join Date
    Jul 2014
    Posts
    10
    Ok Understood wrongly.

    That is if CLUSTERED='N' refers a significant number of rows are not in clustering order, or statistics have not been gathered.
    Also CLUSTERRATIO is not 0, so can i come to an conclusion that significant number of rows are not in clustering order.

    When a REORG done for the tablespace of that index there should be a improvement in the CLUSTERRATIO. rite? If there is no improvement what should be the case. REORG should be done with proper options as said in previous replies ?

    Hope the following will solve my problem with CLUSTERRATIO
    IBM PM44475: BAD CLUSTER RATIO AFTER REORG TABLESPACE SORTDATA NO IF THE CLUSTERING INDEX IS A NON-DATA-PARTITIONED PARTITIONING INDEX - United States

    Curious to know that any way to find the first defined index is a cluster ?

    thanks so so much for your precious time in relying

  14. #14
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by baskar2520 View Post
    When a REORG done for the tablespace of that index there should be a improvement in the CLUSTERRATIO. rite?
    Reorging an index has no effect on the cluster ratio of the index. Defining an index as clustered (you can only do this for one index per table) tells DB2 how to order the rows in the table, not how to order the rows in the index (indexes are always in correct order). Normally, you should only care if the index that is defined as clustered has a very high cluster ratio (if not, a reorg of the table is probably a good idea).

    Sometimes, coincidently, two different indexes will have a very high cluster ratio. Suppose you have the following table:

    Code:
    CREATE TABLE EMP ( 
    EMP_NO            INT NOT NULL GENERATED ALWAYS AS IDENTITY, 
    EMP_NAME          VARCHAR(50) NOT NULL, 
    RECORD_CREATE_TS  TIMESTAMP NOT NULL WITH DEFAULT CURRENT_TIMESTAMP
    )
    Assume that all 3 of the above columns have their own index, and the index on EMP_NO is the clustered index. When a table reorg is done, DB2 will order the rows in the table by EMP_NO since it is the clustered index. So EMP_NO index will have 100% cluster ratio after the reorg. But since RECORD_CREATE_TS is always sequentially increasing (like EMP_NO) it will coincidentally have a very high cluster ratio (at or near 100%). The index on EMP_NAME will obviously not have a high cluster ratio since the table is ordered by EMP_NO, but there is nothing wrong with that.

    You need to re-read the above until you understand it, even if you have to read it 10,000 times.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  15. #15
    Join Date
    Jul 2014
    Posts
    10
    Thank you so much for making me to study and understand clustering index, hope I hav some understanding

    Some more questions
    1. Anywhere to find which index is defined as clustered? (the first index created is clustered by default even if we don't define an index as clustered)..

    2. I have a table with only one index (unique index) so that should be a clustered index . Rite ? ( No where i find its a clustered , just a understanding)

    CLUSTERRATIO for that index was just 17% , did a REORG , still the same! , that should be because of FAROFFPOS and NEAROFFPOS ?

    3. Now I zoomed to FAROFFPOS and NEAROFFPOS,
    What should i concentrate to regain CLUSTERRATIO ?

    thanks

Tags for this Thread

Posting Permissions

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