How do you compare the performance and scalability of DMS and SMS tablespaces in DB2?

1 DMS tablespaces

A DMS tablespace is created by specifying one or more containers that are pre-allocated and exclusively used by the database. The size and location of the containers are determined by the database administrator (DBA). A DMS tablespace can be either regular, large, or automatic storage. A regular DMS tablespace has a maximum size of 4 GB per container, while a large DMS tablespace can have up to 128 TB per container. An automatic storage DMS tablespace can use multiple storage paths and automatically adjust the size of the containers as needed.

2 SMS tablespaces

An SMS tablespace is created by specifying a directory that is managed by the operating system. The database does not pre-allocate any space for an SMS tablespace, but instead relies on the file system to allocate and free space as required. An SMS tablespace can only be regular, with a maximum size of 4 GB per file. However, an SMS tablespace can have multiple files in the same directory, or in different directories on different devices, to increase the total size.

3 Performance comparison

The performance of DMS and SMS tablespaces depends on various factors, such as the workload, the configuration, the hardware, and the tuning. Generally speaking, DMS tablespaces tend to have better performance than SMS tablespaces, because they have less overhead and more control over the space allocation and fragmentation. DMS tablespaces can also use raw devices, which bypass the file system and reduce the I/O latency. However, SMS tablespaces can also perform well if the file system is optimized and the directories are well distributed and balanced.

4 Scalability comparison

The scalability of DMS and SMS tablespaces refers to how well they can handle the growth of data and the demand for resources. DMS tablespaces have more scalability options than SMS tablespaces, because they can use large or automatic storage, which can extend the size and number of containers without affecting the existing data. DMS tablespaces can also use striping, which can improve the I/O parallelism and throughput. However, SMS tablespaces can also scale up by adding more files or directories, as long as there is enough space available on the file system.

5 Advantages and disadvantages

Both DMS and SMS tablespaces have their own advantages and disadvantages, depending on the requirements and preferences of the DBA and the application. For instance, DMS tablespaces offer more flexibility and efficiency in managing the space, but require more planning and maintenance. Alternatively, SMS tablespaces have less complexity and administration in creating and dropping the tablespaces, but less control and visibility over the space usage. Additionally, DMS tablespaces have more potential for performance optimization and tuning, while SMS tablespaces have more compatibility and portability with different file systems and operating systems. However, SMS also have more limitations and restrictions on the size and number of files.

6 Best practices

When it comes to deciding which type of tablespace is best for DB2, there is no one-size-fits-all answer as it depends on the specific needs and goals of each situation. However, some best practices can help you compare and choose between DMS and SMS tablespaces. These include analyzing data characteristics such as volume, growth rate, access pattern, and retention policy; evaluating performance objectives such as response time, throughput, concurrency, and availability; assessing resource constraints such as disk space, memory, CPU, and I/O capacity; testing the tablespace design and configuration using realistic data and workload; and monitoring metrics and results. Finally, it's important to review your tablespace strategy and adjust it as needed based on the feedback and changes.

7 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?