Managing remote table spaces

Remote table spaces are created in a remote storage group in Native Cloud Object Storage. Both user permanent table spaces and user temporary table spaces can be defined as remote table spaces.

Before you begin

You must have set up your remote storage access alias and storage groups before you create your table spaces.

Remote table spaces

A remote table space is a table space created in a remote storage group. The tables and other objects created in the remote table space are stored in object storage. Both user permanent table spaces and user temporary table spaces can be defined as remote table spaces.

The initial maximum number of remote table spaces is limited to 16 (this count includes both user and user temporary table spaces together) on user-managed environments. When the limit is reached, the create command will return error SQL0296N.

Create a remote table space

A remote table space is created using the CREATE TABLESPACE statement as described in CREATE TABLESPACE statement. You can associate a remote table space with a remote storage group with the USING STOGROUP <remote-storage-group> clause.

A remote table space must use automatic storage to be created on remote storage group, and will only be DMS table spaces. A remote table space is only associated with a single remote container, as defined in the remote storage group. A remote table space only supports AUTORESIZE YES. A remote table space supports MAXSIZE to limit the size of the table space. For example, assuming there is a remote storage group named SGOBJSTORE, the remote table space can be created with this CREATE TABLESPACE command:
CREATE TABLESPACE OBJSTORESPACE1 USING STOGROUP SGOBJSTORE
The table space will be stored within the object storage bucket path defined by the remote storage alias equivalent to the following S3 URL:
S3://s3.amazonaws.com/db2wh-instance1/sg000
This will result in the creation of one path per database partition appending the prefix:
<database name>/KF/SS<table space id>-SH<database partition id>-<internal version number>

For example, for an instance with three database partitions numbered 0, 1 and 2, and a database name and the table space OBJSTORESPACE1 having identifier number 3, will result in these paths:

S3://s3.amazonaws.com/db2wh-instance1/sg000/BLUDB/KF/SS3-SH0-b093455a-b9f6-48ce-8660-adb858dd42ce
S3://s3.amazonaws.com/db2wh-instance1/sg000/BLUDB/KF/SS3-SH1-3db68c75-1827-454e-b134-a5751d42e77d
S3://s3.amazonaws.com/db2wh-instance1/sg000/BLUDB/KF/SS3-SH2-77c3d133-f921-4334-b457-fcb4061add7

Options not available

These options are blocked for remote table spaces:
  • AUTORESIZE, INITIALSIZE and INCREASESIZE are irrelevant for remote table spaces. This is a permanent restriction.
  • NO FILE SYSTEM CACHING is the DMS default and is enabled for remote table spaces. This is a temporary restriction.

Restrictions

There are several restrictions to consider:
  • Remote table spaces cannot be explicitly created or dropped. The instance for Db2 on AWS includes a set of default remote table spaces to use for user data and user temporary tables: OBJSTORESPACE1 and OBJSTORESPACEUTMP1, respectively. For user-managed environments, there is a limit of 16 table spaces.
  • Row organized tables cannot be created in remote table spaces.
  • User created indexes for column-organized tables cannot be defined in remote table spaces (use the INDEX IN clause to define a non-remote table space in order to create an index).