Forums & Discussions Home

    A place for members, communities, and committees to have discussions online and via e-mail.
    Click a category or topic to below to start the conversation...

    You are currently in view only mode for this forum. Please click the appropriate below to login as a member and participate. If you are not a member, please CLICK HERE for more information.


    Rao A
    I have a question on REORG vs REBUILD index. (v7.0)

    To make the story short, we have a situation where one of my DBAs created a table, clustered index, followed by load. Later on, we realized that the order of columns in the clustered index is not the way we wanted. We dropped the cluster index and recreated the same index with a different column sequence.

    When one of my SQLs access that index, I see lots of getpages in the DSN activity against that index in the Omegamon.

    Should I go ahead with REORG of that index, or rebuild.. I thought both would do the same.

    On a second thought, I am thinking we should run REORG on a tablespace rather than on a index because the tablespace data pages are not in the clustered order of the index..

    Thanks
    Rao.


    ____________________________________________________________________________________
    Looking for last minute shopping deals?
    Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping

    The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
    Steen Rasmussen
    [CA technologies]
    Hello Rao,



    Basically REBUILD and REORG do two different things. REBUILD will build
    the index by reading the tablespace while REORG will read the index. I
    prefer to do the REBUILD if possible since any potential "problems" in
    the index will be eliminated.



    Steen Rasmussen
    CA

    Principal Technical Specialist DB2 tools

    IBM Certified Database Associate -- DB2 9 Fundamentals

    IBM Certified Database Administrator - DB2 9 DBA for z/OS

    ________________________________

    From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
    Behalf Of Rao A
    Sent: Thursday, February 07, 2008 11:56 AM
    To: [login to unmask email]
    Subject: [DB2-L] REORG vs REBUILD indexes



    I have a question on REORG vs REBUILD index. (v7.0)



    To make the story short, we have a situation where one of my DBAs
    created a table, clustered index, followed by load. Later on, we
    realized that the order of columns in the clustered index is not the way
    we wanted. We dropped the cluster index and recreated the same index
    with a different column sequence.



    When one of my SQLs access that index, I see lots of getpages in the DSN
    activity against that index in the Omegamon.



    Should I go ahead with REORG of that index, or rebuild.. I thought both
    would do the same.



    On a second thought, I am thinking we should run REORG on a tablespace
    rather than on a index because the tablespace data pages are not in the
    clustered order of the index..



    Thanks

    Rao.







    ________________________________

    Never miss a thing. Make Yahoo your homepage.
    < http://us.rd.yahoo.com/evt=51438/*http:/www.yahoo.com/r/hs >


    The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
    list archives, the FAQ, and delivery preferences are at www.idug.org
    < http://www.idug.org/lsidug > under the Listserv tab. While at the site,
    you can also access the IDUG Online Learning Center, Tech Library and
    Code Place, see the latest IDUG conference information
    < http://www.idug.org/lsconf > , and much more.
    If you have not yet signed up for Basic Membership in IDUG, available at
    no cost, click on Member Services < http://www.idug.org/lsms >

    The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
    Andy Lankester
    I assume you meaning CLUSTERING INDEX, in which case if you change the column order you must, as you say, REORG the tablespace with SORTDATA YES.

    Andy

    _____

    From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Rao A
    Sent: Thursday, February 07, 2008 5:56 PM
    To: [login to unmask email]
    Subject: [DB2-L] REORG vs REBUILD indexes


    I have a question on REORG vs REBUILD index. (v7.0)

    To make the story short, we have a situation where one of my DBAs created a table, clustered index, followed by load. Later on, we realized that the order of columns in the clustered index is not the way we wanted. We dropped the cluster index and recreated the same index with a different column sequence.

    When one of my SQLs access that index, I see lots of getpages in the DSN activity against that index in the Omegamon.

    Should I go ahead with REORG of that index, or rebuild.. I thought both would do the same.

    On a second thought, I am thinking we should run REORG on a tablespace rather than on a index because the tablespace data pages are not in the clustered order of the index..

    Thanks
    Rao.



    _____

    Never miss a thing. HYPERLINK "http://us.rd.yahoo.com/evt=51438/*http://www.yahoo.com/r/hs"Make Yahoo your homepage.
    The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at HYPERLINK "http://www.idug.org/lsidug"www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest HYPERLINK "http://www.idug.org/lsconf"IDUG conference information, and much more.
    If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on HYPERLINK "http://www.idug.org/lsms"Member Services


    No virus found in this incoming message.
    Checked by AVG Free Edition.
    Version: 7.5.516 / Virus Database: 269.19.21/1263 - Release Date: 06/02/2008 20:14



    No virus found in this outgoing message.
    Checked by AVG Free Edition.
    Version: 7.5.516 / Virus Database: 269.19.21/1263 - Release Date: 06/02/2008 20:14


    The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
    Avram Friedman
    Rao
    Eventually you want to reorg the base table space and do runstats.
    The urgency of this depends on the number of unique values in the high level
    columns that were not moved around vs the number of rows in the table.
    To the extent that the
    Number of rows in the table / Number of unique index values that did not
    relocate is less than the number of rows per page then REORG is generally
    speaking not urgent as the expected base table page locations have not
    changed statisticatly.

    I think your comment about the number of index get pages is a red herring.
    As a stand alone observation it just means you are using the index which
    would normally be considered to be wonderfull.
    Are you seeing
    Unexpected IO?
    Unexpected Sync Reads?
    Sync Writes?

    Of course all of the above is my view and may not apply to your own
    specialized or unique requirements.

    Avram Friedman

    On Thu, 7 Feb 2008 09:56:03 -0800, Rao A <[login to unmask email]> wrote:

    >I have a question on REORG vs REBUILD index. (v7.0)
    >
    >To make the story short, we have a situation where one of my DBAs created
    a table, clustered index, followed by load. Later on, we realized that the order
    of columns in the clustered index is not the way we wanted. We dropped the
    cluster index and recreated the same index with a different column sequence.
    >
    >When one of my SQLs access that index, I see lots of getpages in the DSN
    activity against that index in the Omegamon.
    >
    >Should I go ahead with REORG of that index, or rebuild.. I thought both
    would do the same.
    >
    >On a second thought, I am thinking we should run REORG on a tablespace
    rather than on a index because the tablespace data pages are not in the
    clustered order of the index..
    >
    >Thanks
    >Rao.
    >
    >
    >
    __________________________________________________________________
    __________________
    >Looking for last minute shopping deals?
    >Find them fast with Yahoo! Search.
    http://tools.search.yahoo.com/newsearch/category.php?category=shopping
    >
    >The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list
    archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug
    under the Listserv tab. While at the site, you can also access the IDUG
    Online Learning Center, Tech Library and Code Place, see the latest IDUG
    conference information, and much more. If you have not yet signed up for
    Basic Membership in IDUG, available at no cost, click on Member Services at
    http://www.idug.org/lsms

    The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
    Nick CIANCI
    [IBM]
    Rao

    Depends on size but I'd generally do a "CREATE INDEX ... DEFER YES"
    and then REBUILD Index. It's generally faster and you get a good index out
    of it.

    In v7 We specified SORTKEYS and got a "turbo boost" on PI rebuilds make use
    of parallelism. In v8 the key word goes and the implications are that you
    now get the parallelism auto-magically.

    It won't hurt to ReOrg the table ("all things being equal")

    However if you are seeing a lot of activity on the index rather than the
    TableSpace then ReOrging the TS may not give you anything extra. Be wary
    to distinguish between I\O and GetPages as well. If you are getting a lot
    more Get Pages than what you expect, you check for things like NearOffPosF,
    FarOffPosF & PseudoDeletes. If the "OffPosF" values are blowing out to
    frequently consider changing your FreeSpace parameters.

    For on-Line maintenance you'll need to use REORG INDEX! As as far as I'm
    aware there is no SHRLEVEL CHANGE option with REBUILD, just REORG.

    However you increased GetPage rate may just be that the index is being made
    greater use off

    HTH

    Regards,
       Nick CIANCI

    Database Administration

    IBM Certified Solutions Expert
    - DB2 UDB V7.1 Database Administration for OS/390



    Rao A
    <[login to unmask email]
    M> To
    Sent by: DB2 Data [login to unmask email]
    Base Discussion cc
    List
    <[login to unmask email] Subject
    ORG> [DB2-L] REORG vs REBUILD indexes


    08/02/08 04:56 AM


    Please respond to
    DB2 Database
    Discussion list
    at IDUG
    <[login to unmask email]
    2-l.org>






    I have a question on REORG vs REBUILD index. (v7.0)

    To make the story short, we have a situation where one of my DBAs created a
    table, clustered index, followed by load. Later on, we realized that the
    order of columns in the clustered index is not the way we wanted. We
    dropped the cluster index and recreated the same index with a different
    column sequence.

    When one of my SQLs access that index, I see lots of getpages in the DSN
    activity against that index in the Omegamon.

    Should I go ahead with REORG of that index, or rebuild.. I thought both
    would do the same.

    On a second thought, I am thinking we should run REORG on a tablespace
    rather than on a index because the tablespace data pages are not in the
    clustered order of the index..

    Thanks
    Rao.

    The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
    Rao A
    I am sorry for not being clear. You are right. I meant CLUSTERING index. Also I did check the accesspath and it is index only access on that index. Hence, REORG on tablespace might not really do any good to reduce index getpages.



    ----- Original Message ----
    From: Andy Lankester <[login to unmask email]>
    To: [login to unmask email]
    Sent: Thursday, February 7, 2008 1:20:51 PM
    Subject: Re: [DB2-L] REORG vs REBUILD indexes


    I assume you meaning CLUSTERING INDEX, in which case if you change the column order you must, as you say, REORG the tablespace with SORTDATA YES.

    Andy




    From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Rao A
    Sent: Thursday, February 07, 2008 5:56 PM
    To: [login to unmask email]
    Subject: [DB2-L] REORG vs REBUILD indexes


    I have a question on REORG vs REBUILD index. (v7.0)

    To make the story short, we have a situation where one of my DBAs created a table, clustered index, followed by load. Later on, we realized that the order of columns in the clustered index is not the way we wanted. We dropped the cluster index and recreated the same index with a different column sequence.

    When one of my SQLs access that index, I see lots of getpages in the DSN activity against that index in the Omegamon.

    Should I go ahead with REORG of that index, or rebuild.. I thought both would do the same.

    On a second thought, I am thinking we should run REORG on a tablespace rather than on a index because the tablespace data pages are not in the clustered order of the index..

    Thanks
    Rao.





    Never miss a thing. Make Yahoo your homepage.
    The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more.
    If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services

    No virus found in this incoming message.
    Checked by AVG Free Edition.
    Version: 7.5.516 / Virus Database: 269.19.21/1263 - Release Date: 06/02/2008 20:14



    No virus found in this outgoing message.
    Checked by AVG Free Edition.
    Version: 7.5.516 / Virus Database: 269.19.21/1263 - Release Date: 06/02/2008 20:14


    The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more.
    If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services


    ____________________________________________________________________________________
    Be a better friend, newshound, and
    know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

    The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
    David Seibert
    Sounds to me like for this particular process, you don't need to reorg
    the tablespace.

    BUT, assuming you have other processes which will benefit from the order
    of the data being in the new, correct clustering index order, you will
    sooner or later need to reorg the tablespace.
    As I understand the scene as you describe it, the data now is in
    clustering order by the OLD wrong clustering index.

    Dave




    The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.


    From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
    Behalf Of Rao A
    Sent: Friday, February 08, 2008 7:10 AM
    To: [login to unmask email]
    Subject: Re: [DB2-L] REORG vs REBUILD indexes


    I am sorry for not being clear. You are right. I meant CLUSTERING index.
    Also I did check the accesspath and it is index only access on that
    index. Hence, REORG on tablespace might not really do any good to reduce
    index getpages.



    ----- Original Message ----
    From: Andy Lankester <[login to unmask email]>
    To: [login to unmask email]
    Sent: Thursday, February 7, 2008 1:20:51 PM
    Subject: Re: [DB2-L] REORG vs REBUILD indexes


    I assume you meaning CLUSTERING INDEX, in which case if you change the
    column order you must, as you say, REORG the tablespace with SORTDATA
    YES.

    Andy

    ________________________________

    From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
    Behalf Of Rao A
    Sent: Thursday, February 07, 2008 5:56 PM
    To: [login to unmask email]
    Subject: [DB2-L] REORG vs REBUILD indexes


    I have a question on REORG vs REBUILD index. (v7.0)

    To make the story short, we have a situation where one of my DBAs
    created a table, clustered index, followed by load. Later on, we
    realized that the order of columns in the clustered index is not the way
    we wanted. We dropped the cluster index and recreated the same index
    with a different column sequence.

    When one of my SQLs access that index, I see lots of getpages in the DSN
    activity against that index in the Omegamon.

    Should I go ahead with REORG of that index, or rebuild.. I thought both
    would do the same.

    On a second thought, I am thinking we should run REORG on a tablespace
    rather than on a index because the tablespace data pages are not in the
    clustered order of the index..

    Thanks
    Rao.

    The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

    All Times America/New_York

    Copyright © 2014 IDUG. All Rights Reserved

    All material, files, logos and trademarks within this site are properties of their respective organizations.

    Terms of Service - Privacy Policy - Contact