Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2017
    Posts
    9

    Answered: Recover disk space problem

    I have DB2 10.5 c-express. I delete same records and truncate same tables in my database but disk space of database is the same.
    How can i recover disk space ?

    I run reorg tabels (' REORG INDEXES ALL FOR TABLE my_table ALLOW READ ACCESS ;' and ' REORG TABLE ALL FOR TABLE my_table ALLOW READ ACCESS ') and
    reduce table ('alter tablespace USERSPACE1 reduce' and 'alter tablespace USERSPACE1 reduce max' ) . Disk space is no change.
    Restar db2 don't help. Where is the problem?

    Try DB2 full trial version. No result.

  2. Best Answer
    Posted by mark.bb

    "What's the result of:

    Code:
    select tbsp_usable_pages, tbsp_used_pages, tbsp_pending_free_pages, tbsp_page_top, reclaimable_space_enabled, tbsp_using_auto_storage
    , tbsp_num_containers, tbsp_extent_size
    from table(mon_get_tablespace('USERSPACE1', -2))
    "


  3. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,529
    Provided Answers: 11
    check in the tablespace view - where you have free space
    alter tablespace .. reduce max should free up space
    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

  4. #3
    Join Date
    Mar 2017
    Posts
    9
    I have 168928 free pages in USERSPACE1. I use only reduce (alter tablespace .. reduce). If i use alter tablespace .. reduce max i get error "11".

  5. #4
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    196
    Provided Answers: 30
    What's the result of:

    Code:
    select tbsp_usable_pages, tbsp_used_pages, tbsp_pending_free_pages, tbsp_page_top, reclaimable_space_enabled, tbsp_using_auto_storage
    , tbsp_num_containers, tbsp_extent_size
    from table(mon_get_tablespace('USERSPACE1', -2))
    Regards,
    Mark.

  6. #5
    Join Date
    Mar 2017
    Posts
    9
    I run reorg and reduce twice. It's work.
    Thanks.

  7. #6
    Join Date
    Mar 2017
    Posts
    9
    Unfortunatly i think it don't work. I delete 50% rows and i have 10,4 GB database where before delete was 11,7 GB.

    TBSP_USABLE_PAGES: 1337792
    TBSP_USED_PAGES: 1177344
    TBSP_PENDING_FREE_PAGES: 0
    TBSP_PAGE_TOP: 1337792
    RECLAIMABLE_SPACE_ENABLED : 0
    TBSP_USING_AUTO_STORAGE: 1
    TBSP_NUM_CONTAINERS: 1
    TBSP_EXTENT_SIZE: 32

    Why i dont have 6 GB?
    Last edited by woodman; 03-16-17 at 09:04.

  8. #7
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    196
    Provided Answers: 30
    According to this output you don't have reclaimable storage for your tablespace.
    This explains why you get SQL1763N rc=11 on 'ALRTER TABLESPACE USERSPACE1 REDUCE MAX' which must work for automatic storage tablespaces.
    I don't have express-c at the moment to check if it's such an express-c specific - to create tablespaces with such a strange characteristic.
    Regards,
    Mark.

  9. #8
    Join Date
    Mar 2017
    Posts
    9
    Quote Originally Posted by mark.bb View Post
    According to this output you don't have reclaimable storage for your tablespace.
    This explains why you get SQL1763N rc=11 on 'ALRTER TABLESPACE USERSPACE1 REDUCE MAX' which must work for automatic storage tablespaces.
    I don't have express-c at the moment to check if it's such an express-c specific - to create tablespaces with such a strange characteristic.


    'ALTER TABLESPACE USERSPACE1 REDUCE' don't reduce tablespace properly? 'ALTER TABLESPACE USERSPACE1 REDUCE max' is better since 'ALTER TABLESPACE USERSPACE1 REDUCE'? Can i change reclaimable storage without create new tablespace?

    How create new tablespace and move tables?
    Last edited by woodman; 03-17-17 at 09:10.

  10. #9
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    196
    Provided Answers: 30
    reduce without max reduces the tablespace to it's current high water mark.
    reduce with max tries to reduce the tablespace high water mark if possible first, and then to reduce it's size to this new HWM.
    Try to create new tablespace using automatic storage or find some tablespace with reclaimable_space_enabled = 1:
    If you created a new tablespace, what's its tbsp_using_auto_storage value?
    Code:
    select tbsp_name, tbsp_used_pages, tbsp_page_top, reclaimable_space_enabled, tbsp_using_auto_storage
    from table(mon_get_tablespace(null, -2))
    where tbsp_type='DMS' and tbsp_content_type not like '%TEMP'
    Regards,
    Mark.

  11. #10
    Join Date
    Mar 2017
    Posts
    9
    I create userspace2 using 'CREATE TABLESPACE userspace2 MANAGED BY AUTOMATIC STORAGE' command and execute your select. I get 4 records:

    1. Rec.
    TBSP_NAME : SYSCATSPACE
    TBSP_USED_PAGES : 27524
    TBSP_PAGE_TOP : 27524
    RECLAIMABLE_SPACE_ENABLED : 0
    TBSP_USING_AUTO_STORAGE : 1

    2. Rec.
    TBSP_NAME : USERSPACE1
    TBSP_USED_PAGES : 450912
    TBSP_PAGE_TOP : 1324448
    RECLAIMABLE_SPACE_ENABLED : 0
    TBSP_USING_AUTO_STORAGE : 1

    3. Rec.
    TBSP_NAME : SYSTOOLSPACE
    TBSP_USED_PAGES : 180
    TBSP_PAGE_TOP : 180
    RECLAIMABLE_SPACE_ENABLED : 0
    TBSP_USING_AUTO_STORAGE : 1

    4. Rec.
    TBSP_NAME : USERSPACE2
    TBSP_USED_PAGES : 96
    TBSP_PAGE_TOP : 96
    RECLAIMABLE_SPACE_ENABLED : 1
    TBSP_USING_AUTO_STORAGE : 1

    What should I do next?

  12. #11
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    196
    Provided Answers: 30
    Move your table to the USERSPACE2 tablespace using, for example, admin_move_table procedure.
    Regards,
    Mark.

  13. #12
    Join Date
    Mar 2017
    Posts
    9
    I create new tablespace userspace ('CREATE TABLESPACE USERSPACE2 MANAGED BY AUTOMATIC STORAGE') and
    move all tables to userspace2 tablespace ('CALL SYSPROC.ADMIN_MOVE_TABLE('DB2SHM','TAB1','USERSPAC E2','USERSPACE2','USERSPACE2','','','','','','MOVE ')' for all tables). After move my database have 20GB. I run reorg all tabels ('REORG TABLE TAB1 ALLOW READ ACCESS' for all tables) and index ('REORG INDEXES ALL FOR TABLE TAB1 ALLOW READ ACCESS' for all tables ).After that i run reduce max ('alter tablespace USERSPACE2 reduce max')
    Tha size is no change to much. Now I have 18,7 GB (was 10,1 GB after run reduce without max and for userspace1) (orginal size 11,7).

    What is wrong?

  14. #13
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    196
    Provided Answers: 30
    You can look at the space used by your tablespaces using the query above. It's hard to tell why this happened without these figures.
    I can't understand why to reorg table just after loading it.
    If you want to test how much space you can release to OS, then you should do something like this:
    1. run this statement before, let's say, massive delete from your big table, and remember the numbers
    2. run reorg table (no need to reorg indexes after classic table reorg - it's done automatically)
    3. run 'alter tablespace reduce max'
    4. run the statement again to compare the new numbers with those got at the step 1

    If you know that large amount of data was really deleted, but space savings are not so significant, then it's a subject for further investigations.
    For example, you could have LOB fields, and to reclaim the space you should reorg not only regular data, but LOB objects as well (see an appropriate REORG TABLE command clause in the docs).
    Regards,
    Mark.

  15. #14
    Join Date
    Mar 2017
    Posts
    9
    When i drop userspace1 database size is change to 8,83GB.

  16. #15
    Join Date
    Mar 2017
    Posts
    9
    How can i write condition statement in sql script if userspace2 don't exist then create userspace and move tables. After that run reorg.
    If userspace2 exist then only reorg. ?

Posting Permissions

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