Professional Web Applications Themes

reclaim index space - IBM DB2

Hi I recently dropped a big index on a 45G tables. When I did a list tablespace, the space used did not seem to decrease. I have also done a runstats and this doesn't seem to help. Is there anyway to reclaim this space without a reorg. As you can imagine, it will take a long time for the reorg to finish. Thanks Lyn...

  1. #1

    Default reclaim index space

    Hi

    I recently dropped a big index on a 45G tables. When I did a list
    tablespace, the space used did not seem to decrease. I have also done
    a runstats and this doesn't seem to help. Is there anyway to reclaim
    this space without a reorg. As you can imagine, it will take a long
    time for the reorg to finish.

    Thanks
    Lyn
    Lyn Duong Guest

  2. #2

    Default Re: reclaim index space

    "Lyn Duong" <lyndtablimited.com.au> wrote in message
    news:8d1cda6d.0309111545.2afa3b80posting.google.c om...
    > Hi
    >
    > I recently dropped a big index on a 45G tables. When I did a list
    > tablespace, the space used did not seem to decrease. I have also done
    > a runstats and this doesn't seem to help. Is there anyway to reclaim
    > this space without a reorg. As you can imagine, it will take a long
    > time for the reorg to finish.
    >
    > Thanks
    > Lyn
    Is the index in same tablespace as the table? Not a good idea for a table
    that large.


    Mark A Guest

  3. #3

    Default Re: reclaim index space

    Hi Mark,

    No the indexes are in a seperate tablespace.

    "Mark A" <maswitchboard.net> wrote in message news:<E_78b.431$SZ.51455news.uswest.net>...
    > "Lyn Duong" <lyndtablimited.com.au> wrote in message
    > news:8d1cda6d.0309111545.2afa3b80posting.google.c om...
    > > Hi
    > >
    > > I recently dropped a big index on a 45G tables. When I did a list
    > > tablespace, the space used did not seem to decrease. I have also done
    > > a runstats and this doesn't seem to help. Is there anyway to reclaim
    > > this space without a reorg. As you can imagine, it will take a long
    > > time for the reorg to finish.
    > >
    > > Thanks
    > > Lyn
    >
    > Is the index in same tablespace as the table? Not a good idea for a table
    > that large.
    Lyn Duong Guest

  4. #4

    Default Re: reclaim index space

    "Lyn Duong" <lyndtablimited.com.au> wrote in message
    news:8d1cda6d.0309112053.2953b815posting.google.c om...
    > Hi Mark,
    >
    > No the indexes are in a seperate tablespace.
    >
    Then why would it take a long time to do a reorg? Is this OS/390 or
    Unix/Linux/Windows and if the later is the tablespace DMS or SMS tablespace?


    Mark A Guest

  5. #5

    Default Re: reclaim index space

    If index and data are separate, the tblspcs. have to be DMS.
    HAving dropped the index by now, try using db2dart to:
    1) Investigate the location of the High Water Mark (HWM)
    2) See, using db2dart how you canlower the HWM to its lowest value.
    3) From that investigation, you should be able to identify if you can:
    A) Drop some containers or
    B) Reduce container size(s).

    After Step 2, you could instead, backup the tblspc. and restore it using
    the redirect option to define new, fewer and/or smaller containers.

    HTH, Pierre.


    Mark A wrote:
    > "Lyn Duong" <lyndtablimited.com.au> wrote in message
    > news:8d1cda6d.0309112053.2953b815posting.google.c om...
    >
    >>Hi Mark,
    >>
    >>No the indexes are in a seperate tablespace.
    >>
    >
    > Then why would it take a long time to do a reorg? Is this OS/390 or
    > Unix/Linux/Windows and if the later is the tablespace DMS or SMS tablespace?
    >
    >
    P. Saint-Jacques Guest

  6. #6

    Default Re: reclaim index space

    Could have to do with the tablespace high water mark.
    I think it's irrelevant for SMS TS. Is it a DMS one?


    Look around those :
    [url]http://groups.google.ca/groups?hl=en&lr=&ie=ISO-8859-1&q=high+AND+water+AND+mark&meta=group%3Dcomp.data bases.ibm-db2[/url]

    db2dart can help reduce it.
    I think a backup also reduces it.

    PM


    PM \(pm3iinc-nospam\) Guest

  7. #7

    Default Re: reclaim index space

    I don't think Backup/restore reduces the HWM as pages are copied to the
    image with their respective displacement from the start of the tblspc.
    How else could the restored index pointers, which are just copied back
    in a restore, point to the actual rid address? I also believe that the
    page holding HWM is also restored in place (or else the tblspcs.
    descriptors would also have to be updated in the restore process.
    The tblspc in question is DMS for data and therefore DMS for index from
    the initial post.
    HTH, Pierre.

    PM (pm3iinc-nospam) wrote:
    > Could have to do with the tablespace high water mark.
    > I think it's irrelevant for SMS TS. Is it a DMS one?
    >
    >
    > Look around those :
    > [url]http://groups.google.ca/groups?hl=en&lr=&ie=ISO-8859-1&q=high+AND+water+AND+mark&meta=group%3Dcomp.data bases.ibm-db2[/url]
    >
    > db2dart can help reduce it.
    > I think a backup also reduces it.
    >
    > PM
    >
    >
    P. Saint-Jacques Guest

  8. #8

    Default Re: reclaim index space

    I don't think Backup/restore reduces the HWM as pages are copied to the
    image with their respective displacement from the start of the tblspc.
    How else could the restored index pointers, which are just copied back
    in a restore, point to the actual rid address? I also believe that the
    page holding HWM is also restored in place (or else the tblspcs.
    descriptors would also have to be updated in the restore process.
    The tblspc in question is DMS for data and therefore DMS for index from
    the initial post.
    HTH, Pierre.

    PM (pm3iinc-nospam) wrote:
    > Could have to do with the tablespace high water mark.
    > I think it's irrelevant for SMS TS. Is it a DMS one?
    >
    >
    > Look around those :
    > [url]http://groups.google.ca/groups?hl=en&lr=&ie=ISO-8859-1&q=high+AND+water+AND+mark&meta=group%3Dcomp.data bases.ibm-db2[/url]
    >
    > db2dart can help reduce it.
    > I think a backup also reduces it.
    >
    > PM
    >
    >
    P. Saint-Jacques Guest

  9. #9

    Default Re: reclaim index space

    Hi, Duong Lyn,
    I am afraid you need to tell us more information, such as:
    - Which version are you using?

    If you would like the space which occupied by the dropped indexes before
    can be used by the other tables or their indexes. AFAIK, the best way you
    can do is REORG.

    How long the reorg will take will depend on which version you are using.

    With the introduction of type-2 index in IBM DB2 UDB V8.1, you can do
    ONLINE INDEX REORG, which is very efficient, especially if you only want
    to reclaim space.

    Regards,
    FRX

    Lyn Duong wrote:
    > Hi
    >
    > I recently dropped a big index on a 45G tables. When I did a list
    > tablespace, the space used did not seem to decrease. I have also done
    > a runstats and this doesn't seem to help. Is there anyway to reclaim
    > this space without a reorg. As you can imagine, it will take a long
    > time for the reorg to finish.
    >
    > Thanks
    > Lyn
    Fan Ruo Xin Guest

  10. #10

    Default Re: reclaim index space

    My mistake.

    The only link between high water mark and space is, i guess ...
    Reorg lowers the TS high water mark so when you backup the image is smaller.
    (right?)

    In v8, pseudo-deleted rows (pseudo empty pages) use space until
    reorg/cleanup.
    Maybe that's what is meant by 'the space used did not seem to decrease'.


    PM



    PM \(pm3iinc-nospam\) Guest

  11. #11

    Default Re: reclaim index space

    Hi,

    Yes, more information.
    I am using db2 UDB V8 but the table was so large that I have not yet
    converted the indexes to type 2 so I can't do a inplace
    reorganisation. I will have to try and convert these indexes first
    before I can do the table reorganisation. What I meant with the usage
    level not being decreased is that when i did a LIST TABLESPACE, the
    space used was the same before and after the index drop. I think
    reorganising the table will reclaim the space used (they are in fact
    empty pages). I thought about reorganizing indexes but since the index
    has been dropped, there is no index to reorg!.

    It's strange but doesn't a table reorg drop and recreate indexes
    anyway? Why wouldn't the pages used go down?

    Lyn
    Lyn Duong Guest

  12. #12

    Default Re: reclaim index space



    Lyn Duong wrote:
    > Hi,
    >
    > Yes, more information.
    > I am using db2 UDB V8 but the table was so large that I have not yet
    > converted the indexes to type 2 so I can't do a inplace
    > reorganisation. I will have to try and convert these indexes first
    > before I can do the table reorganisation. What I meant with the usage
    > level not being decreased is that when i did a LIST TABLESPACE, the
    > space used was the same before and after the index drop. I think
    > reorganising the table will reclaim the space used (they are in fact
    > empty pages). I thought about reorganizing indexes but since the index
    > has been dropped, there is no index to reorg!.
    That's not strange. It is designed as this way.

    Steal a term *SEGMENT* from Oracle. Each table has its own segment. But
    all the indexes, which referred to the same table, will share the same
    segment. So reorg index means reorg all the indexes for the table. You
    can't only reorg one of the indexes which defined for the same table.

    DB2 will (automately) reclaim the space which occupied by the indexes
    through two ways:
    1). When you drop the LAST index you defined for this table.
    2). Reorg (OFFLINE Reorg or (Version 8) ONLINE INDEX REORG.)

    HTH
    FRX
    >
    >
    > It's strange but doesn't a table reorg drop and recreate indexes
    > anyway? Why wouldn't the pages used go down?
    >
    >
    > Lyn
    Fan Ruo Xin Guest

  13. #13

    Default Re: reclaim index space

    Hi Fan,

    when you do a reorg of all indexes on a particular table, does the
    index tablespace need to have free pages equal to at least the value
    of the largest index on the table ? The largest index is about 17G so
    I do not want to run out of space when I am running the reorg

    thanks
    Lyn

    Fan Ruo Xin <net> wrote in message news:<net>... 
    >
    > That's not strange. It is designed as this way.
    >
    > Steal a term *SEGMENT* from Oracle. Each table has its own segment. But
    > all the indexes, which referred to the same table, will share the same
    > segment. So reorg index means reorg all the indexes for the table. You
    > can't only reorg one of the indexes which defined for the same table.
    >
    > DB2 will (automately) reclaim the space which occupied by the indexes
    > through two ways:
    > 1). When you drop the LAST index you defined for this table.
    > 2). Reorg (OFFLINE Reorg or (Version 8) ONLINE INDEX REORG.)
    >
    > HTH
    > FRX

    > [/ref]
    Lyn Guest

Similar Threads

  1. Hide control and reclaim screen space
    By ScottGill in forum Macromedia Flex General Discussion
    Replies: 6
    Last Post: December 6th, 06:12 AM
  2. Replies: 4
    Last Post: August 3rd, 03:11 PM
  3. Index Topics and Index References
    By Tom_Cusick@adobeforums.com in forum Adobe Indesign Windows
    Replies: 2
    Last Post: July 8th, 07:20 PM
  4. Newb query: index.htm & index.php & the server default
    By Lab309 in forum PHP Development
    Replies: 7
    Last Post: September 22nd, 02:08 PM
  5. Reclaim disk storage
    By Franco Lombardo in forum IBM DB2
    Replies: 2
    Last Post: September 11th, 09:00 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139