I often get the question about when SMS tablespaces should
be used and when DMS tablespaces should be used. We also now have automatic
storage that will pick SMS or DMS for you. There has been much written
about what these tablespaces are, but in this article I will share my opinions
of when to use each. First let me give very brief definitions of these
terms:

SMS

System Managed Storage tablespace.

You define the tablespace with a directory name or names

NO directories can be added after tablespace creation

Only Regular and Temporary Tablespaces (Can NOT
be defined as “Large”)

Indexes and tables must be in the same tablespace
(Except Range Partition Tables)

DMS

Database Managed Storage tablespace

You define the tablespace with a file name or names

Files can be added or extended

Can be defined as Large, Regular or Temporary

Indexes can be in the same or different tablespace

Automatic Storage

With Automatic Storage all tablespaces are still either
SMS or DMS, but you let DB2 choose which type. When you create the database,
you tell DB2 which file system or set of file systems into which you want all
tablespace data placed.

Automatic Storage

For many new databases I would now
recommend automatic storage. With it you get both the ease of administration
of SMS and the performance of DMS. Starting in DB2 v9.1, Automatic
Storage is the default when you create a new database. As I said in the
table above when you create a database with automatic storage you specify a
storage path or set of storage paths. As you create tablespaces, you do
not specify containers. DB2 will place the tablespace in the storage
path(s) that you specified at database creation time. If you specified
two or more storage paths, then DB2 will stripe your tablespaces across all of
them. In general DB2 will choose SMS tablespaces for temporary
tablespaces and DMS for all others and will use LARGE tablespaces by default
for all DMS tablespaces. Another nice thing about automatic storage is
that you don't need to do redirected restores if you want to make copies of
your databases in different places. Automatic Storage is free with all
editions of DB2 9.

Whether or not you choose to use
automatic storage, it is good to know some circumstances when you may want to
choose SMS or DMS. As I said, automatic storage is often an excellent
choice, but there are circumstances where other options are better. Even
when Automatic Storage is the best, it is still good to understand the
underling SMS and DMS models that it uses. You should also note that you
can create tablespaces that are not under Automatic Storage control in
Automatic Storage databases.

General Considerations

For many applications, DMS
tablespaces do provide somewhat better performance for data tablespaces. However,
SMS, almost universally provides the same or better performance for temporary
tablespaces. This general statement applies to all versions of DB2/LUW
including v9.5. That being said, many shops use SMS for everything
because they make the trade-off in favor of ease of administration. The
less I/O you do against the tablespace the less pronounced the performance
differences are between SMS and DMS. DMS has the advantage of allowing
you to place your indexes and LOB data in a different tablespace than your
data. With DMS you can also add containers on the fly, where once an SMS
tablespace is define no more containers can be added.

Prevent File System Filling

In many circumstances I prefer DMS
just in case there is a run-away application that starts filling one
table. In SMS the table will be allowed to consume all of the space in
the file system(s) where the directory(s) are located before an out of space
error is returned to the application. In this scenario in DMS the
application just fills one tablespace file(s) and quits, leaving free space in
my directory and all other tablespaces. When you allow DMS tablespaces to
auto-extend, you need to specify an appropriate MAXSIZE value on the tablespace
to prevent this problem. Specifying MAXSIZE should be done whether you
are using Automatic Storage or not. I always do everything that I can to
thwart Murphy's Law.

Index and LOB placement

Due to various considerations such
as performance or utility scheduling, you may want to put your indexes and
large objects into a different tablespace than your normal data. For
nearly all types of tables, you must use a DMS tablespace to do this.
When a table is in SMS the index and large object data must be placed in the
same tablespace as the data. The only exception is when you create a
range partitioned table in an SMS tablespace. With range partitioned
tables in SMS tablespaces, you can put your indexes into another
tablespace.

Test Databases

SMS is a great choice for small
test databases. This is especially true when you have several small test
databases that you wish to put in one file system. When this is the case
you frequently do not know which one will get the most data. SMS
tablespaces are only allocated a few pages when they are created. As data
is added only the tablespaces that have tables that have data added grow and
then only grow in accordance with the amount of data added. So this way
you don’t have to know which database will grow – each database and tablespace
within that database will just consume space from the file system as it is
needed. You can also do this when using Automatic Storage and specifying
small initial sizes for tablespaces, but SMS will still generally make more
efficient use of shared space.

Unknown Table Growth Rates

SMS may also be a good choice when
you have several tables where you expect significant growth, but the amount of
growth is unknown among them. The first option that you should consider
would be to put all of these tables in one DMS tablespace so that each table
can claim the space it needs, while not “trapping” unused space in a tablespace
for a table that does not grow as expected. However, there may be times
when it is not feasible to put them all into one tablespace. In this case
SMS is a good choice if you put all of the tablespaces in directories in the
same file system or file systems. Again, the tables can consume the space
as needed without allocating space that will not be used.

Large Tablespaces

DB2 v9.1 introduced a new type of
tablespace called the “Large” tablespace that allows for much larger
tablespaces. Indexes for tables in “Large” tablespaces have a larger
address or (Row ID – RID) that points to the rows in the table. Only DMS
tablespaces can be declared “Large”. This means that if you want larger
temporary tablespaces, they must be DMS. Starting in V9.1 the default of
“Large” is used when creating a DMS tablespace. Maximum tablespace size
is still determined by page size and I show those limits below. For partitioned
databases, this maximum size is the maximum size in each partition. Also,
the new large tablespace lets you have more than the maximum of 255 rows per
page that regular tablespaces allow. These and other limits are defined
on the SQL
LIMITS page.

Maximum Tablespace Sizes
(Per Database Partition)

Page Size

Large Tablespace

Regular Tablespace

4K

2 TB

64 GB

8K

4 TB

128 GB

16K

8 TB

256 GB

32K

16 TB

512 GB

Further Reading

To read more about tablespaces in general and the meaning of
“DMS”, “SMS”, and “Large” tablespaces please see chapter 9 of the Data
Servers, Databases and Database Object Guide. You can also read more
about Automatic Storage in chapter 3 of this manual.