IBM Support

How to manually extend automatic storage tablespace containers upfront

How To


Summary

Starting from Db2 v10.1, system managed (SMS) and database managed (DMS) table spaces have been deprecated and automatic storage table spaces have become the default user table space.

As automatic storage table spaces manage container storage for you, the database manager seeks to maximize space utilization by creating and extending table space containers only as needed.  Therefore, maintenance operations to manually extend automatic storage table spaces are not available.

One benefit of extending table space containers upfront is avoiding the penalty of multiple extensions later as the table space grows.

Steps

The INCREASESIZE attribute of the table space configuration is used to define the size of the increase, but normally this increase only happens organically as usage increases.
To manually force the extension of an automatic storage table spaces, consider explicitly lowering the high water mark of the table space to reduce its size by the maximum amount possible, so that the next operation that allocates an extent will trigger the table space extension.
For instance, if you hope to extend the automatic storage table space to N bytes:
  1. Determine currently used table space size: 
    • $ db2pd -db dbname -tablespace

Tablespace Statistics:
Address            Id    TotalPgs   UsablePgs  UsedPgs    PndFreePgs FreePgs    HWM        Max HWM    State      MinRecTime NQuiescers PathsDropped TrackmodState    
0x00007FA90C444700 2     8192       8160       96         0          8064       96         96         0x00000000 0          0          No           n/a

      where used table space size is UsedPgs * page_size. ex) 96 * 4096

  2. Calculate a delta between N bytes and current table space size:
    • delta = N - (UsablePgs * page_size)
  3. Alter tablespace increasesize to the delta:
    • db2 alter tablespace <tablespace_name> increasesize <delta>
  4. Issue alter tablespace reduce max:
    • db2 alter tablespace <tablespace_name> reduce max
  5. Wait until the reduce max finishes:
    • db2pd -extentmovement
  6. Create a dummy table to allocate an extent in the table space for the auto resize:
    • db2 create table <table_name> in <tablespace_name>

Document Location

Worldwide

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
10 October 2019

UID

ibm11078515

Page Feedback