Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2010
    Posts
    67

    Unanswered: convert to large tablespace

    Hi All,
    db2 v9.7
    aix 7.1

    I need to convert one of out production tablespace (4k page) to LARGE due to the total of pages is going to reach 16777152. my question is

    is it enough just reorg online INDEXES ALL with CLEAN ONLY option ? ( tablespace does not have enough space to do reorg INDEXES without CLEAN ALL and rebuild the indexes)


    Thank you in advance for your help.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Tables in a regular tablespace have that limit, not the tablespace.

    You need to supply more information on the table, indexes and tablespace.

    Andy

  3. #3
    Join Date
    Jan 2010
    Posts
    67
    Quote Originally Posted by ARWinner View Post
    Tables in a regular tablespace have that limit, not the tablespace.

    You need to supply more information on the table, indexes and tablespace.

    Andy
    Hi Andy,
    the tablespace is DMS and regular4k page size.
    the tablespace has 10 tables and one of these table has CLOB column.
    the total page size almost reached to 16777152 and free page is not that much is use REORG INDEXS ALL with allow write access , as you know it will rebuild the index in tablespace

    hopefully I gave you necessary information.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Read the documentation on ALTER TABLESPACE ... CONVERT TO LARGE. It should help you out. You should test it first before doing it live in production.

    Andy

  5. #5
    Join Date
    Jan 2010
    Posts
    67
    Hi Andy,

    I have read already and I could not find if REORG INDEXES ALL for table_name with option CLEAN ONLY will chang RID from 3 to 6 or not ? yes the document JUST points to REORG INDEXES ALL without option CLEAN ONLY .

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    REORGing the indexes will not change the number of pages that the data uses. It will do nothing to help you. You need to move the data. The easiest method is to alter the table as I indicated earlier.

    The only thing that will change the RID to 6 is to CONVERT TO LARGE.

    Andy

  7. #7
    Join Date
    Jan 2010
    Posts
    67
    Quote Originally Posted by ARWinner View Post
    REORGing the indexes will not change the number of pages that the data uses. It will do nothing to help you. You need to move the data. The easiest method is to alter the table as I indicated earlier.

    The only thing that will change the RID to 6 is to CONVERT TO LARGE.

    Andy
    Hi Andy,

    I know that, I meant after "ALTER TABLESPACE <tablspace-name> CONVERT TO LARGE" I must use REORG INDEXES ALL for <table_name>.

    my question was if i use CONVERT TO LARGE" and then use REORG INDEXES ALL for <table_name> CLEAN ONLY . is that work or not ?


    another thing Andy , the total page limited size 16777152 is at tablespace level and not table level. I tested at test environment and when I tried to exceed 16777152 I got "too big" error.

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Doing the CONVERT TO LARGE and REORG on indexes will get you to 6 byte RIDs. You could also do a REORG on the data and maybe get more rows per page since that limit is larger as well.

    Andy

  9. #9
    Join Date
    Jan 2010
    Posts
    67
    Thank you Andy,

    reorg index with CLEAN ONLY is not going to rebuild the index do you thing still valid for going to 6 byte RIDs ?

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    No, you need to do a complete REORG. The indexes need to be rebuilt.

    Andy

  11. #11
    Join Date
    Jan 2010
    Posts
    67
    Thank you Andy for your help !!

Posting Permissions

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