Reclaimable Table Spaces

You may also like...

7 Responses

  1. kishwar says:

    Another Good Article … Thanks Ember !

    Can you please let me know, How to determine the tablespace which needs reclaim space or reduce HWM ? Do we have any formula to find it ?

    • Ember Crooks says:

      This is a judgement call. Some free space is used by subsequent data addition in the table or tablespace (depending on where the free space exists). You can find tablespaaces with more space allocated than used with this query:

      select  substr(tbsp_name,1,18) as tbsp_name,
              tbsp_type,
              tbsp_content_type as type,
              (select count(*) from syscat.tables st where st.tbspace=t.tbsp_name) as tabcount,
              tbsp_using_auto_storage as auto_sto,
              tbsp_auto_resize_enabled as auto_resize,
              RECLAIMABLE_SPACE_ENABLED,
              tbsp_page_size,
              tbsp_used_pages,
              tbsp_total_pages
      from table(mon_get_tablespace('',-2)) as t
      order by tbsp_name;
      
      TBSP_NAME          TBSP_TYPE  TYPE       TABCOUNT    AUTO_STO AUTO_RESIZE RECLAIMABLE_SPACE_ENABLED TBSP_PAGE_SIZE       TBSP_USED_PAGES      TBSP_TOTAL_PAGES
      ------------------ ---------- ---------- ----------- -------- ----------- ------------------------- -------------------- -------------------- --------------------
      SYSCATSPACE        DMS        ANY                144        1           1                         1                 4096                79604                81940
      SYSTOOLSPACE       DMS        LARGE                5        1           1                         1                 4096                  708                 8220
      SYSTOOLSTMPSPACE   SMS        USRTEMP              0        1           0                         0                 4096                    5                    5
      TAB16K             DMS        ANY                 62        1           1                         1                16384                25088                26880
      TAB8K              DMS        ANY                109        1           1                         1                 8192                37408                40960
      TEMPSPACE1         SMS        SYSTEMP              0        1           0                         0                 4096                    5                    5
      TEMPSYS16K         SMS        SYSTEMP              0        1           0                         0                16384                    5                    5
      TEMPSYS32K         SMS        SYSTEMP              0        1           0                         0                32768                    5                    5
      TEMPSYS8K          SMS        SYSTEMP              0        1           0                         0                 8192                    5                    5
      USERSPACE1         DMS        LARGE              881        1           1                         1                 4096              1399776              1450240
      

      But you don’t generally want to free up all space – if there is more than a 20% difference between TBSP_USED_PAGES and TBSP_TOTAL_PAGES, and you don’t expect much growth in that tablespace, that’s the threshold that usually would trigger me to look into this.

      If you’re not properly reorging your tables (allowing truncation), then there might be a lot of space still tied up in tables, too.

  2. Dave Prentice says:

    Thanks for the post, Ember. We recently moved 500+ tables from pre-9.7 tablespaces to new tablespaces. We agree it was painful! We don’t use auto-resize or automatic storage. We currently run a script to lower high water marks on all reclaimable storage tablespaces every week, but we don’t reduce the size of reclaimable storage tablespaces. Do you think lowering the HWM has any other benefit besides the ability to reduce reclaimable storage tablespace size?

  3. Hi,

    “The ADMIN_MOVE_TABLE tool makes this possible, though it isn’t really fully usable until at least 10.1 fix pack 4 or later”

    Was this due to security labels (http://www-01.ibm.com/support/docview.wss?uid=swg1IC97824 / http://www-01.ibm.com/support/docview.wss?uid=swg1IC98017)
    or memory corruption error (http://www-01.ibm.com/support/docview.wss?uid=swg1IC97828) or something else?

  4. Friedmar Moch says:

    We had to migrate 1000+ Instances/Databases away from DB2 9.5. We decided to create a script that automatically create the ddl statments to referate the tablespaces and tablets amd indexes etc. Then all data was exported. When all exports wehre completed successfull the databases have eben backend up. Then the databases have ben dropped an the instanze as well. Then a new instance has been create. Then the databases have been create with automatic storage plus the tablespaces and tablespaces. Load the data, create indexes and finale runstats.
    It is a complex script behaus of the differenz fall back situations. It worked great and so we could migrate all Instances from 9.5 to 10.1 without problems and earned all the benefits of automatic storage and non reclaimable dataspaces plus some otters.

    • Ember Crooks says:

      That’s the only real workaround, but the complexity is a problem, and for many databases the offline time and increased duration is a problem.

Leave a Reply

Your email address will not be published. Required fields are marked *