SMS — system managed space in Db2 LUW

When we think about a relational database, we see tables, where you can add, delete and sort rows. We also see schemas that connect these tables. But how does it look from the storage perspective? For sure, there must be some directories and files, but how is this all related to what we see?

Table spaces

In my previous story I told you that the data page is the molecule of a Db2 database. If so, then a table space is a compound of molecules, usually thousands or millions of molecules. If you’re not a chemist, you can also think of a data page as a brick — in such case a table space is a building. And even though bricks don’t differ much, you can build very different buildings with them. In Db2 LUW, there are basically two different kinds of constructions:

• SMS (System Managed Space) table spaces,
• and DMS (Database Managed Space) table spaces.

Though this is not the only distinction we can make, this is the most important one. Both constructions might look similar from outside, but they base on two different storage models. Today I am going to take a closer look on the SMS model (SMS stands for System Managed Space, because unlike in DMS, the space for data is allocated and managed by the OS’ file system; that means Db2 doesn’t have much control over the allocation, what can lead to poor performance). The SMS model is simpler in its nature than the DMS model, but in most cases has poorer performance. In the past it was only the one. Here is what you should know before you build your SMS table space.

SMS containers

Table spaces consist of containers. If a table space is a building, then a container is a floor (storey). Your building may consist of one or more floors. SMS containers are quite intuitive, because they are simply folders. How does it work in practice? Let’s find out!

At first, I need to choose or create a table space that is managed by space. It’s called an SMS table space.

As you see in the statement (screenshot above), I am specifying my container, by passing a path to the container (folder). My path looks like this: <database/data/<instance-name>/<node-number>/<database-name>/<container-name>. In the SMS model, a container is a folder, where my object files reside (more about object files in a second). You may notice that I am passing 3 more parameters: pagesize (4kB), extentsize (two pages = 8kB) and bufferpool. At this moment the specified container must be empty. It may even not exist. So I just use ‘cd’ and ‘ls’ commands in my linux to check it. Hmm… there is already a file there, namely ‘SQLTAG.NAM’ file. This file just marks the folder as being in use so that subsequent table space creation does not attempt to use it.

Database objects

Now, let’s create a simple table (‘CURIOUS_2’).

As You can see, a new file appeared in the container. The file represents a database object, namely a table in this case. That’s why I call it an object file. The filename is managed by Db2 and it’s boring, because its name is ‘SQL00002.DAT’. The next table would be represented by ‘SQL0003.xxx’ and so on. Suffix ‘.DAT’ means that the object file consists of regular DATa (integer in this case). You might have noticed that this file is 4kB of size. Page size is also 4kB. Coincidence? I don’t think so. If my table space is a building and my container is a floor (so far my table space has only one floor), then my object file is a wall. And walls are made of… bricks! In my case, the wall has only one brick (data page). The object files cannot be smaller than 4kB, because it’s the smallest possible data page size (and the data page is the smallest indivisible unit of a Db2 database).

What if I create an index on the table?

There is a new file! This time with suffix ‘.INX’, wchich means this file represents an index. It’s because in Db2 indexes are database objects separate from tables.

So, what if I create a new table in the table space? This time except for an integer (SMALLINT) column, I’ll have an XML column.

As you can see, three new objects have been created. Each of them represented by number ‘3’ (new table = new creative filename :D). One of these objects is in ‘.XDA’ format, which is due to the XML column.

Multi-container SMS table spaces

But, you may want to divide your table into two parts — each part on a separate filesystem. In such case you just create two (or more) containers. Db2 will create one object file per container and fill the containers equally. Let’s see how it works. At first, I am creating a new table space, because I cannot just add a container to an already existing SMS table space, unless it’s a new partition.

In the next step I am creating a simple table with just one column in this brand new table space. And I am populating the table with the first 100 rows.

So, let’s check what’s inside the two containers of this table space.

They’re basically the same. Each container has one data object, 8kB of size. Since I have 2 containers, my new table is represented by 2 files. Both with the same name.

In the last step, I am adding 1000 new rows.

As a result my files grow from 8KB to 64KB. They’re still the same size and their size is a multiple of 8.

To sum up, if you have 2 containers (or more), Db2 allocates data evenly across the containers. As default, the data is being allocated extent-by-extent (which is 8kB in my table space), but you can also allocate data page-by-page. Allocating data across containers piece-by-piece is called ‘striping’ and it’s not characteristic to SMS. Striping is also used in the DMS model.

Striping

Db2 writes the data evenly across all containers in the table space, placing the extents for tables in round-robin fashion across containers (extent is just a group of pages; namely two in my examples above).

Let’s assume, you create a simple table. It has headers, but no records (rows) yet, so it is smaller than 1 extent. It means that all of the data fits in ‘Extent 0’. So far, only 1 file has been created — specifically in ‘Container 0’. The remaining two containers don’t reveal the existence of the table.

Then, you add some rows and the table size exceeds 1 extent. It means another extent is needed (namely ‘Extent 1’). As Db2 uses striping, the new extent is placed in ‘Container 1’, which means a new object file is created. The analogical situation occurs if ‘Extent 1’ overflows. Finally, we have 3 object files, one per container.

When ‘Extent 3’ is needed, Db2 comes back to ‘Container 0’ starting another stipe. The second stripe will consist of ‘Extent 3’, ‘Extent 4’ and ‘Extent 5’. As you can see in the picture above there are 4 stripes in total.

Conclusion

The SMS storage model consists of files representing database objects. Each table has at least one physical file associated exclusively with it. When you set up a table space, you decide the location of the files by creating containers. Each container in an SMS table space is associated with a directory name. Each of these directories can be located on a different physical storage device or file system. Db2 controls the names of files created for objects in each container, and the file system is responsible for managing them. By controlling the amount of data written to each file, Db2 distributes the data evenly across the table space containers (striping).

‍

‍