DMS — database managed space in Db2 LUW

Long time ago the disk space in Db2 LUW was always managed by the operating system. This storage model is called (in Db2 world) the SMS (System Managed Space) model. It’s simple, but when Db2 databases were getting bigger and more complicated, we realised the SMS model has its limits:

• when database data is allocated by the OS, it’s seldom contiguous (resulting in slow data read) → it would be better to allow Db2 to control it,
• we cannot add new containers to a table space (unless you add a new partition),
• when one container is full, the whole table space is considered full,
• we cannot divide object into different table spaces based on their type (regular data into one table space, indexes into another one, LOBs into yet another one).

Therefore IBM decided to invent something new. That’s how the DMS (Database Managed System) was born.

Idea

The idea behind the DMS model is the following: we allocate a big chunk of contiguous disk space as a container and give the control over it to Db2 (more precisely: the Db2 database manager). The ideal situation would be to make the entire raw device a container. If it’s not possible, we should allocate a piece of it (hopefully contiguous) as a container in the form of a file with preallocated space.

DMS containers

Therefore there are two types of containers in the DMS storage model:

• containers as files with preallocated space
• containers as raw devices

Thanks to preallocating space, it is likely that the data is contiguous. If the file (container) becomes full, it is increased according to your configuration. However, the more often it is increased, the data is less likely to be contiguous on disk.

Creating a table space

The storage model that is being used for your data depends on which table space the data resides in. The DMS table space can be created as follows:

As you can see, the DMS table spaces are created with keywords: “managed by database”. Next, you should specify the containers. In the example above the containers have a form of devices and Db2 is told their size is 10000 pages. Theoretically, you could specify a 10000 pages container on a device that could store 20000 pages, but that wouldn't make sense.

Below there is a (temporary) DMS table space with file containers. The syntax is analogical. The files shouldn’t be created before, just prepare the directory.

Let’s see how it works in practice. As I don’t have any spare raw devices, I am going to create a DMS table space with a file container:

I created a table space with a 4 kB pagesize and a file container with 1000 pre-allocated pages. Therefore the container (file) size is 4 MB. When it becomes full, it will be expanded.

How to find objects if there is only 1 file?

In the SMS model, each database object (index, regular table data etc.) has it’s own file. Therefore Db2 knows that the first page of a given table is at the beginning of the file. But how to find the first page of this table if all the tables are in one file?

The answer is: table space logical address map. The map represents the order of extents in the table space (extent is just a group of data pages allocated together). If there is only one container, then table space equals container.

The map starts with a header that contains internal control information.

Then there is the first SMP (Space Map Page). SMP is a bit map telling us which extents are in use. So, if you delete some data (e.g. by dropping a table), it doesn’t have to be deleted physically. It’s just marked as not available in the SMP(s). It’s much faster and it’s called the logical deletion. If the table space is small, there is only one SMP that covers the entire table space (except for 2 extents: the header and the SMP itself). However, due to the fact that SMPs have size of just 1 extent, big tablespaces have more SMPs. For example, in the picture above, there is a second SMP at the extent #31968. It means the first SMP covers all the extents from #2 till # 31967 (all the extents between the first and the second SMP).

Then (extent #2 on the map) there is the Object Table. There is only one Object Table per tablespace (unless you have dozens of thousands of objects — I don’t know if it’s even possible). It is a list of pointers to each object in the table space (more precisely: a list of pointers to the first EMP of each object). For example, if you ask: “Where is table T2 located?”. The Object Table will answer: “Check the extent #6” (and indeed, if you look at the picture, the EMP of T2 is located in the extent #6).

After the header (extent #0), first SMP (#1), the Object Table (#2), at the extent #3 there is the EMP for T1 (“Extent Map for T1”). Each object, except for its standard data, has an EMP (or EMPs, if the object is huge). The EMP maps an object-relative extent number to a table space-relative page number. Let me explain this difficult sentence using an example. In the picture above you can see that the first 3 extents of T1 are located at extents: #4, #5 and #8 in the tablespace (first extent at #4, second extent at #5 and third one at #8). We need a map that will translate the object-relative address to the table-space-relative address (you might have noticed that it maps extent number to page number — it doesn’t change much in the translation mechanism).

More containers

If there is only one container, the logical-to-physical conversion is straightforward (conversion from logical page number on the table space map to physical location on disk). But, if there are more containers, Db2 allocates extents across them in the round-robin order. It’s called striping. In the picture below, there are 3 containers, therefore each of them receives every third extent:

Conclusion

The idea behind the DMS storage model was to hand over the control of data allocation from the OS to the Db2 database manager. It required inventing the table space logical address map and with its inhabitants. The DMS model is indeed more complicated that the SMS model, but in most cases results in better performance. But SMS will stay with. It is still used for temporary data and sometimes for small tablespaces.