Difference between SMS, DMS and Automatic Storage tablespaces

Let us see the differences between SMS ( System Managed Space), DMS (Database Managed Space) and Automatic Storage tablespaces.

How they are created:

• SMS: Created using MANAGED BY SYSTEM clause in the CREATE TABLESPACE command.
• DMS: Created using MANAGED BY DATABASE clause in the CREATE TABLESPACE command.
• Automatic Storage: Created using MANAGED BY AUTOMATIC STORAGE clause in the CREATE TABLESPACE command. When the MANAGED BY clause is not given in the CREATE TABLESPACE command, then DB2 will automatically treat it as automatic storage.

Container Definition:

• SMS: Containers will be defined as a directory name.
• DMS: Containers will be defined as a file name or a device name. We must specify the initial space for each container.
• Automatic Storage: We need not to define the containers, the database manager automatically creates containers on all the storage paths. Data will be striped evenly across all the containers.

Initial Space Allocation:

• SMS: The file system controls the allocation of storage.
• DMS: Space will be allocated while creating the tablespace.
• Automatic Storage: Space will be allocated while creating the tablespace, you can also specify the intial size of the tablespace.

Changes to tablespace containers: 

• SMS: We cannot do any changes the tablespace containers once created.
• DMS: We can add, extend, reduce and drop the containers.
• Automatic Storage: Containers can be reduced or dropped, if the tablespace size is reduced, tablespace can be rebalanced evenly across containers when new storage is added or dropped from the database.

Growth of the tablespace:

• SMS: Containers will grow until they reach the capacity of the file system.
• DMS: Containers can be extended beyond the allocated size manually or automatically (if auto resize is enabled) upto the file system size.
• Automatic Storage: Containers will grow until they reach the capacity of the file system. If storage paths are added to the database, containers are extended automatically.

On-going Maintenance:

• SMS: There will be no on-going maintenance for SMS tablespaces.
• DMS: We will have some maintenance activities like adding or extending containers, Lowering High Water Mark and Re-balancing.
• Automatic Storage: Lowering High Watermark and Re-balancing.

Performance:

• SMS: Generally slower than DMS and Automatically Storage.
• DMS: Good performance when compared to others.
• Automatic Storage: Same as DMS.