Section 8.4. Table Spaces

8.4. Table Spaces

As discussed in Chapter 7, Working with Database Objects, a table space is a logical, not physical, database object. You cannot point to anything on disk or your server and say "This is my table space." In DB2, all tables and indexes must be created in table spaces. Therefore, it is important to understand how to create and change table spaces.

A table space is a collection of one or more underlying physical storage devices known as containers. This allows you complete control over the placement of the containers on the disks, devices, and file systems available on your server.

There are two types of table spaces depending on how the physical space is allocated: system managed space (SMS) and database managed space (DMS). SMS table spaces store data in operating system files, and space for tables is allocated on-demand. In DMS table spaces, the database manager controls the storage space. You provide a list of devices or files and their size to belong to a table space when the DMS table space is defined, and DB2 then allocates the defined amount of space.

Before we can continue discussing table spaces, it is important to explain how a table space's containers work and how data is striped across the containers in a table space, based on extents.

8.4.1. Containers

When you create a table space, you define the container(s) for the table space to define the physical storage of the table space. How you define the container depends on the type of table space that you are creating. For SMS table spaces, a container can only be a directory. For DMS table spaces, a container can either be a file or a logical device or drive name.

When you create a table space, you have to define at least one container. A table space can have a number of containers associated with it, but once it has been defined, a container can belong to one and only one table space. Figure 8.9 illustrates this.

Figure 8.9. One table space can contain multiple containers, but a container can be associated to only one table space

8.4.2. Pages

DB2 stores table and index data on a page, which is the smallest unit of storage in a DB2 database. DB2 creates and manages the pages in the table space automatically, but you can control the page size for your table spaces. If you do not explicitly specify the page size when you create the table space, DB2 will use the default size of 4K. DB2 supports four different page sizes: 4K, 8K, 16K, and 32K.

It is important to note that a row within a table cannot span pages (i.e., the entire row must fit on a single page). The page size for the table must be large enough to hold all of the columns in the table. For example, if a table is created as follows:

 CREATE TABLE mytable (lastname   CHAR(100),                       firstname  CHAR(100),                       address    CHAR(4000)                      ) 

the total space required to store a row is the sum of the column sizes, and this exceeds the size of a 4K page. Therefore, this table must be created in a table space with a page size is of at least 8K.

Tables are described in detail in Chapter 7, Working with Database Objects.

NOTE

Once you have created a table space with a given page size, you cannot alter the page size. The only option is to drop and recreate the table space using a different page size.

8.4.3. Extents

An extent is a collection of consecutive pages in a table space. For performance reasons, DB2 reads and writes extents of pages rather than single pages to optimize I/O. An extent can only contain pages for one object. For example, DB2 will not allow one page within an extent to belong to table A and another one to index A or table B.

If you are familiar with RAID (redundant array of inexpensive disks) or striped file systems, you understand the concept of stripingwhere data is written to the various underlying disks in pieces. DB2 does the same basic operation within its table spaces. When you create a table space with more than one container, DB2 writes the data to the containers in a round-robin fashion. DB2 fills an extent in one container, then fills an extent in the next container, and so on until it has written an extent in all of the containers in the table space. DB2 will then fill the second extent in each of the containers, and so on.

For example, if you create a table space with four containers, and create a table in that table space, as you add data to the table, the data will be striped across the table space containers as follows: The first extent of pages for the table is placed in the first container (i.e., Container 0). Next, extent number one is written to Container 1, extent number two is written to Container 2, and extent number three is written to Container 3. At this point there is one extent in each of the four containers, so DB2 will start striping the data back at the first extent again. Therefore, extent number four will be written to Container 0, extent number five will be written to Container 1, and so on as more data is added to the table. Figure 8.10 illustrates this.

Figure 8.10. Table spaces, containers, and extents

NOTE

The first extent (Extent 0) for each object can start in any of the defined containers. Not all objects start in Container 0.

You can control the extent size when you create a table space with the EXTENTSIZE clause of the CREATE TABLESPACE statement. If you do not explicitly specify the extent size when you create the table space, DB2 uses the default extent size based on the database configuration parameter DFT_EXTENT_SZ.

NOTE

Once you have created a table space with a given extent size, you cannot alter the extent size. The only option is to drop and recreate the table space to use a different extent size.

8.4.4. Creating Table Spaces

You create a table space is with the CREATE TABLESPACE statement. Figure 8.11 shows a simplified version of the syntax diagram for this statement.

Figure 8.11. The CREATE TABLESPACE statement

              .-REGULAR---------------. >>-CREATE--+-----------------------+---------------------------->            +-LARGE-----------------+            | .-SYSTEM-.           |            '-+--------+--TEMPORARY-'              '-USER---' >--TABLESPACE--tablespace-name----------------------------------> >--+-----------------------------------------------------------+-->    |     .-DATABASE PARTITION GROUP-.                         |    '-IN--+--------------------------+--db-partition-group-name-'    .-PAGESIZE--4096-----------. >--+--------------------------+--------------------------------->    '-PAGESIZE--integer--+---+-'                         '-K-' >--MANAGED BY--+-SYSTEM--| system-containers |-----+------------>                '-DATABASE--| database-containers |-' >--+---------------------------------+-------------------------->    '-EXTENTSIZE--+-number-of-pages-+-'                  '-integer--+-K-+--'                             '-M-' >--+-----------------------------------+------------------------>    '-PREFETCHSIZE--+-number-of-pages-+-'                    '-integer--+-K-+--'                               +-M-+                               '-G-' >--+-----------------------------+------------------------------>    '-BUFFERPOOL--bufferpool-name-'    .-OVERHEAD--12.67------------------. >--+----------------------------------+------------------------->    '-OVERHEAD--number-of-milliseconds-'    .-FILE SYSTEM CACHING----. >--+------------------------+----------------------------------->    '-NO FILE SYSTEM CACHING-'    .-TRANSFERRATE--0.18-------------------. >--+--------------------------------------+--------------------->    '-TRANSFERRATE--number-of-milliseconds-' >--+---------------------------------+-------------------------><    '-DROPPED TABLE RECOVERY--+-ON--+-'                              '-OFF-' 

To summarize, when you create a table space you must define the following.

• To indicate the type of data that the table space will store, include one of these keywords:

  - REGULAR (the default)

  - LARGE

  - TEMPORARY

• To indicate the type of table space based on how it is managed use:

  - MANAGED BY SYSTEM for SMS table spaces

  - MANAGED BY DATABASE for DMS table spaces

• To indicate the page size to use for all tables and indexes in the table space use:

  - PAGESIZE integer (4K is the default)

• To indicate the extent size for the table space use:

  - EXTENTSIZE number of pages (the default is determined by the database configuration file parameter DFT_EXTENT_SZ)

• To indicate the name of the buffer pool associated to this table space use:

  - BUFFERPOOL buffer pool name. This buffer pool must exist before you create the table and must have the same page size as you specify for the table space. By default, the table space will be associated with the IBMDEFAULTBP buffer pool. You can change this when you create the table space since the page size of the table space must match the page size of the associated buffer pool. If you are using a multi-partitioned database, you also need to tell DB2 in which partition group to create the table space.

In addition, you can optionally specify the following table space characteristics:

• The I/O characteristics of the table spaces and its containers:

  - The overhead, which is the same as the seek time for the disks. The default is 12.67 milliseconds. If you have one kind of disk, then you can usually find this value on the disks or from the manufacturer. If you have a mixture of disks in the table space, you need to calculate the average overhead, seek time, and latency for the disks.

  - The transfer rate for the disks specifies the amount of time (in milliseconds) required for the I/O subsystem to read one page from disk into memory. As with the overhead, if you are using a mixture of disk types, calculate an average value for this parameter.

• The prefetch size for the table space, which indicates the number of pages fetched ahead of time to improve performance. DB2 uses the database configuration file parameter DFT_PREFTECH_SZ if you do not specify this value.

• The database partition group where the table space will be created

• Allow dropped table recovery. If you accidentally drop a table and this option is enabled, you can specify the RECOVER TABLE ON option when you are rolling forward so that the table will not be deleted and you can recover the table's data. This option can only be specified for a REGULAR table space.

By default, the table space will be associated with the IBMDEFAULTBP buffer pool. You can change this when you create the table space since the page size of the table space must match the page size of the associated buffer pool. If you are using a multi-partitioned database, you also need to tell DB2 in which partition group to create the table space.

8.4.5. Container Tags

When you create a table space, DB2 puts a special marker in all of the table space containers to identify the container. This marker is called the container tag, and in an SMS table space it is a file named SQLTAG.NAM. In a DMS table space the first extent of each container contains the container tag. This tag identifies the container, its table space, and the database it belongs to. The tag ensures that DB2 will not reuse a device, file, or directory if it is already assigned as a container for another table space.

In previous versions of DB2 the container tag for DMS containers was stored in a single page at the beginning of the container to minimize the space requirements (aka a one-page container tag). Large Storage Area Networks (SANs) and disk arrays using RAID technology have become more popular, and many databases are being created on RAID-protected disks. If DB2 uses a one-page container tag, the beginning and end of the extents in the table space cannot be made to line up with the beginning and end of a stripe on the underlying disks. This causes suboptimal I/O performance since each database I/O operation needs to access more than one disk.

To force DB2 to create the tag on a single page, the registry variable DB2_USE_PAGE_ CONTAINER_TAG must be set to ON before creating the table space:

 db2set DB2_USE_PAGE_CONTAINER_TAG=ON 

NOTE

For databases migrated from Version 7 with page-sized container tags, the tag size will not change regardless of the registry variable setting. DB2 will work fine in this case. A restore will respect the type of the containers and the size of the container tag in the backup image, whereas a redirected restore will respect the registry variable.

8.4.6. SMS Table Spaces

System-managed space (SMS) table spaces use the file system manager to manage the tables and indexes stored within the table space. The only type of container allowed for an SMS table space is a directory, which you specify in the CREATE TABLESPACE statement. When you create tables and indexes, DB2 creates a file for every object within the table space inside the directory containers.

Since you cannot add containers to an SMS table space using the ALTER TABLESPACE statement, it is very important for you to create the table space on a file system with enough space.

NOTE

Although you cannot normally add containers to an SMS table space directly, you can increase the size of the existing file system containers using operating system commands. You can add a container to an SMS table space on a partition where there are no existing containers for the table space using the SYSTEM CONTAINER clause. You can also add a container indirectly, by backing up the database and performing a redirected restore.

8.4.6.1 Creating SMS Table Spaces

You need to use the CREATE TABLESPACE statement with the MANAGED BY SYSTEM clause to create an SMS table space. You also specify the path for the containers for the table space. For example, the following statement creates an SMS table space space1 using one directory container 'c:\space1':

 CREATE TABLESPACE space1    MANAGED BY SYSTEM USING ('c:\space1') 

Note that the path is included with the USING keyword. You can specify this as an absolute or a relative path. The above example uses an absolute path (it completely specifies the location of the directory). This is the same example, but using a relative path:

 CREATE TABLESPACE space1    MANAGED BY SYSTEM USING ('space1') 

A relative path is relative to the database directory (i.e., the SQLxxxxx directory) where the database is created. The following statement creates the following directory assuming the active instance is DB2, and there is only one database created on the C: drive on Windows:

 C:\DB2\NODE0000\SQL00001\SPACE1 

For the instance db2inst1 with only one database created on /mydata file system on Linux or UNIX, the above command creates the directory:

 /mydata/db2inst1/NODE0000/SQL00001/space1 

NOTE

If the directory you specify does not exist, DB2 will create it. If the directory does exist, it cannot contain any files or subdirectories

You can create more than one container for the table space as follows:

 create tablespace space1 managed by system using ('c:\space1',  'd:\space1') 

or

 create tablespace space1 managed by system using ('/data1/space1', '/data1/space2') 

NOTE

In the preceding examples you created the containers on the same drive and file system. In practice you should not do this, as this is not an optimal configuration and could cause I/O contention.

If you create a table in an SMS table space, DB2 creates a file for each object, and stores the information for the object in that file. Whenever you create a table it is assigned an object ID. Each of the files that is created for an object associated with the same table will be assigned the same object ID by DB2 if the table is in an SMS table space. This object ID is then used in the file name for the objects in an SMS table space.

If you look inside an SMS table space, you will see several files named SQLxxxxx.DAT, SQLxxxxx.INX, SQLxxxxx.LB, SQLxxxxx.LBA, and SQLxxxxx.LF. Let's take a look at the catalog table space directory (SQLT0000.0 ) for the sales database you created earlier on Windows. Figure 8.12 provides a partial view of this directory.

Figure 8.12. The contents of an SMS table space directory

Table 8.4 describes the files based on the file extension.

Table 8.4. The Files Inside an SMS Tablespace Directory Container

Filename Extension	Contents of the File
.DAT	Data objects for the table.
.INX	Normal RID index objects for the table.
.BKM	Block indexes available for multidimensional clustering (MDC) tables.
.LF	Long varchar columns for the table.
.LB	LOB columns for the table. Every .LB file has a .LBA file associated with it.
.LBA	The placement of the LOBs within the .LB file.

If the table space has more than one container, DB2 creates the same files in all of the table space's containers.

8.4.7. DMS Table Spaces

DB2 manages the storage and retrieval of database objects from within the table space with database-managed space (DMS) table spaces. When you create a DMS table space, the only type of containers that can be specified are files, logical drives, or logical devices (raw devices). With DMS table spaces, when you create tables and indexes DB2 places the pages for these objects in the table space and keeps track of where things are located.

8.4.7.1 Creating DMS Table Spaces

To create a DMS table space, specify MANAGED BY DATABASE with the CREATE TABLESPACE statement. You then specify the path for the containers as follows:

 CREATE TABLESPACE tablespace_name     MANAGED BY DATABASE USING         (FILE 'file_name' size) 

or

 CREATE TABLESPACE tablespace_name     MANAGED BY DATABASE USING         (DEVICE 'device_name' size) 

NOTE

If the file already exists, DB2 checks to make sure it is not used as a container for another tablespace. If it is not already used, DB2 will use the file.

If you are using a logical drive or raw logical device, you must first create the drive or device using operating system commands.

8.4.7.1.1 Using Device Containers

If you are building a table space on Linux or UNIX and want to use a raw device, you must first create a logical volume using the tools provided by your operating system. If you are using Windows, create a disk partition that can be used as the container, but you need to remember not to format the partition to create a file system.

It is important to note the size of these volumes or partitions, so that when you are creating the table space and assigning the containers to the devices you do not waste space. Since the volume/partition cannot be used for any other purpose, you might as well size the container to use the whole device/partition.

NOTE

You can extend or resize the container later to use up the free space if you do leave some space on the logical volumes/disk partitions.

When you create the table space, you can specify the size of the containers in either:

• Number of pages based on the page size for the table space (the default)

• Actual size in KB, MB, or GB

The following are two examples of creating DMS table spaces with device containers.

 CREATE TABLESPACE ts1 MANAGED BY DATABASE USING     (DEVICE '/dev/rmydisk1' 20000) CREATE TABLESPACE ts2 MANAGED BY DATABASE USING     (DEVICE '\\.\G:' 200MB) 

8.4.7.1.2 Using File Containers

As with SMS containers, when you specify the name for a file container, you can use either a relative file name or the absolute file name. When you issue the CREATE TABLESPACE statement you specify the container name(s) and size(s). If the file exists, DB2 checks to see if the file is the right size and if it is used for any other purpose. If it is the right size and not used for another purpose, DB2 will use the file. If it is not the right size but is not used for any other purpose, DB2 will either expand or shrink the file to make it the right size. If the file does not exist, DB2 will create it with the size that you specfied.

In the same manner as with device containers, you can specify the size of the containers in either:

• Number of pages based on the page size for the table space (the default)

• Actual size in KB, MB, or GB

The following are two examples of creating DMS table spaces with file containers.

 CREATE TABLESPACE ts1 MANAGED BY DATABASE USING       (file '/myfile1' 2GB) CREATE TABLESPACE ts2 MANAGED BY DATABASE USING       (file 'C:\dbfiles\ts2' 20000) 

8.4.8. Table Space Considerations in a Multi-Partition Environment

When you create table spaces in a multi-partition database, the table space may be defined on more than one of the database partitions. As discussed earlier, the partition group that you specify for the table space determines on which partitions the table space will be created.

If you are using a cluster of Linux servers with one database partition on each server, and each server has its own set of physical disks, this process is a lot less confusing. However, given the popularity of SAN storage and Network Attached Storage (NAS), and the growing use of large UNIX-based SMP servers, there are many times where the database partitions will be sharing the same underlying disks. In this case it is very important that you take the time to determine a naming convention for your table spaces and containers.

You can specify the container name on each database partition using the ON DBPARTITIONNUM parameter:

 CREATE TABLESPACE ts2 MANAGED BY DATABASE       USING (FILE '/dbfiles/ts2c1p0' 2GB) ON DBPARTITIONNUM (0)       USING (FILE '/dbfiles/ts2c1p1' 2GB) ON DBPARTITIONNUM (1)       USING (FILE '/dbfiles/ts2c1p2' 2GB) ON DBPARTITIONNUM (2)       USING (FILE '/dbfiles/ts2c1p3' 2GB) ON DBPARTITIONNUM (3) 

DB2 also lets you use an expression to automatically add the partition number into the container name so the container names will be unique across the database. This partition expression can be used anywhere within the name of the container and must be preceded by a space. The expression is $N and can be used as follows:

 CREATE TABLESPACE ts2 MANAGED BY DATABASE       USING (FILE '/dbfiles/ts2c1p $N' 2GB) 

For the same four-partition database as above, it would create the containers:

/dbfiles/ts2c1p0 on partition 0

/dbfiles/ts2c1p1 on partition 1

/dbfiles/ts2c1p2 on partition 2

/dbfiles/ts2c1p3 on partition 3

You can also use this expression if the table space has more than one container:

 CREATE TABLESPACE ts2 MANAGED BY DATABASE       USING (FILE '/dbfiles/ts2c1p $N' 2GB,       FILE '/dbfiles/ts2c2p $N' 2GB) 

For the same four-partition database as above, it would create the containers:

/dbfiles/ts2c1p0 on partition 0

/dbfiles/ts2c2p0 on partition 0

/dbfiles/ts2c1p1 on partition 1

/dbfiles/ts2c2p1 on partition 1

/dbfiles/ts2c1p2 on partition 2

/dbfiles/ts2c2p2 on partition 2

/dbfiles/ts2c1p3 on partition 3

/dbfiles/ts2c2p3 on partition 3

8.4.9. Listing Table Spaces

You can get a list of all of the table spaces in your database using the LIST TABLESPACES command. This command lists every table space in the database to which you are currently connected, as well as the following information:

• The table space ID (the internal ID that DB2 uses for the table space)

• The table space name

• The table space storage type (DMS or SMS)

• The table space contents (Regular (any data), Large, or Temporary)

• The state of the table space

Figure 8.13 shows an example of the output of the LIST TABLESPACES command.

Figure 8.13. Output from the command LIST TABLESPACES

            Tablespaces for Current Database  Tablespace ID                        = 0  Name                                 = SYSCATSPACE  Type                                 = System managed space  Contents                             = Any data  State                                = 0x0000    Detailed explanation:      Normal  Tablespace ID                        = 1  Name                                 = TEMPSPACE1  Type                                 = System managed space  Contents                             = System Temporary data  State                                = 0x0000    Detailed explanation:      Normal  Tablespace ID                        = 2  Name                                 = USERSPACE1  Type                                 = System managed space  Contents                             = Any data  State                                = 0x0000    Detailed explanation:      Normal 

You can get more information about the table space by specifying the SHOW DETAIL option. This provides the following additional information about the table space.

• The total number of pages

• The number of usable pages

• The number of used pages

• The number of free pages

• The table space high-water mark (in pages)

• The page size (in bytes)

• The extent size (in bytes)

• The prefetch size (in pages)

• The number of containers in the table space

Figure 8.14 shows an example of the output of the LIST TABLESPACES SHOW DETAIL command for the USERSPACE1 table space.

Figure 8.14. Output from the LIST TABLESPACES SHOW DETAIL command

 Tablespace ID                        = 2  Name                                 = USERSPACE1  Type                                 = Database managed space  Contents                             = Any data  State                                = 0x0000    Detailed explanation:      Normal  Total pages                          = 100000  Useable pages                        = 999968  Used pages                           = 5740  Free pages                           = Not applicable  High water mark (pages)              = Not applicable  Page size (bytes)                    = 4096  Extent size (pages)                  = 32  Prefetch size (pages)                = 16  Number of containers                 = 1 

You can use this extra information to examine how full your table spaces are, so you can take action if the table space is getting close to being full.

NOTE

For SMS table spaces, the information does not indicate how full the table space is since DB2 is not aware of the size of the file system. The Health Monitor, which is part of DB2 in Version 8, does examine the file system size when it looks at the percentage of free space for the table space, so you can use this to make sure your table space is not filling the file system.

You can also get information about the table spaces in a database using the GET SNAPSHOT FOR TABLESPACES command. This command provides the following information:

• The table space ID (the internal ID that DB2 uses for the table space)

• The table space storage type (DMS or SMS)

• The table space contents (Regular (any data), Large, or Temporary)

• The page size for the table space

• The extent size for the table space

• The prefetch size for the table space

• The current buffer pool used by the table space

• The buffer pool used at the next database startup

• The table space state

• The size (in pages)

• The number of usable pages

• The number of used pages

• The number of pending free pages

• The number of free pages

• The table space high-water mark

• An indicator of whether rebalancing is occurring (rebalancing is discussed in section 8.4.10.2, Shrinking a Table Space)

• The minimum point in time for roll forward recovery

• The number of table space quiescers

• The number of containers

• Container information such as

  - The container name

  - The container ID

  - The container type

  - The total pages in the container

  - The number of usable pages in the container

  - The stripe set number

  - An indicator of whether the container is accessible

• The table space map for DMS table spaces

Figure 8.15 shows an example of the output of the GET SNAPSHOT FOR TABLESPACES command for an SMS table space; Figure 8.16 shows the output for a DMS table space.

Figure 8.15. Output from the command GET SNAPSHOT FOR TABLESPACES for an SMS table space

      Tablespace Snapshot First database connect timestamp           = 01-07-2005 15:26:42.235201 Last reset timestamp                       = Snapshot timestamp                         = 01-07-2005 15:47:20.522435 Database name                              = W Database path                              = C:\DB2\NODE0000\SQL00001\ Input database alias                       = W Number of accessed tablespaces             = 3 Tablespace name                            = SYSCATSPACE Tablespace ID                              = 0 Tablespace Type                            = System managed space Tablespace Content Type                    = Any data Tablespace Page size (bytes)               = 4096 Tablespace Extent size (pages)             = 32 Tablespace Prefetch size (pages)           = 16 Buffer pool ID currently in use            = 1 Buffer pool ID next startup                = 1 Tablespace State                           = 0x'00000000' Detailed explanation:      Normal   Total number of pages                    = 0   Number of usable pages                   = 0 Number of used pages                     = 0 Minimum Recovery Time                    = Number of quiescers                      = 0 Number of containers                     = 1 Container Name                             = C:\DB2\NODE0000\SQL00004\SQLT0000.0 Container ID                             = 0 Container Type                           = Path Total Pages in Container                 = 0 Usable Pages in Container                = 0 Stripe Set                               = 0 Container is accessible                  = Yes 

Figure 8.16. Output from the command GET SNAPSHOT FOR TABLESPACES for a DMS table space

 Tablespace name                            = TS1 Tablespace ID                            = 3 Tablespace Type                          = Database managed space Tablespace Content Type                  = Any data Tablespace Page size (bytes)             = 4096 Tablespace Extent size (pages)           = 32 Tablespace Prefetch size (pages)         = 16 Buffer pool ID currently in use          = 1 Buffer pool ID next startup              = 1 Tablespace State                         = 0x'00000000'  Detailed explanation:    Normal Total number of pages                    = 5000 Number of usable pages                   = 4960 Number of used pages                     = 160 Number of pending free pages             = 0 Number of free pages                     = 4800 High water mark (pages)                  = 160 Rebalancer Mode                          = No Rebalancing Minimum Recovery Time                    = Number of quiescers                      = 0 Number of containers                     = 1 Container Name                           = d:\ts1     Container ID                         = 0     Container Type                       = File (extent sized tag)     Total Pages in Container             = 5000     Usable Pages in Container            = 4960     Stripe Set                           = 0     Container is accessible              = Yes   Table space map:    Range  Stripe Stripe  Max         Max  Start  End    Adj. Containers    Number Set    Offset  Extent      Page Stripe Stripe    [   0] [   0]      0     154      4959      0    154   0    1 (0) 

Table 8.5 summarizes the differences between SMS and DMS table spaces. As you can see, both table space types stripe the extents in a round-robin fashion between the containers. SMS tables spaces grow and shrink as data is added or deleted, while DMS table spaces are preallocated when the table space is created. Both types of table spaces provide very good performance.

Table 8.5. Comparing SMS and DMS Characteristics

Characteristic	SMS	DMS
Striping	Yes.	Yes.
Object management	Operating system using unique file names.	DB2.
Space allocation	Grows/shrinks on demand.	Preallocated.
Ease of administration	Easy.	Average.
Performance	Very Good.	Best. Can achieve up to 5 to 10 percent advantage with raw containers. Index, LOBs, and data for a single table can be spread across table spaces.

8.4.10. Altering a Table Space

You can change the size and other characteristicssuch as the prefetch size, overhead, and transfer rateof both SMS and DMS tables spaces in your databases using the ALTER TABLESPACE statement. You can also change buffer pool assigments and bring an offline table space back online. The storage characteristics can only be modified for DMS table spaces.

To change the I/O characteristics for your table spaces, you must first connect to the database, then use the ALTER TABLESPACE statement with the parameter you want to change. For example:

 ALTER TABLESPACE   ts2   PREFETCHSIZE 128 ALTER TABLESPACE   ts1   OVERHEAD     10 ALTER TABLESPACE  mytspc TRANSFERRATE 100 

8.4.10.1 Enlarging a Table Space

You can change the amount of space available for your DMS table spaces by adding containers to the table space or by increasing the size of the current containers in the table space.

NOTE

You cannot add or remove containers from SMS table spaces. However, you can add a container to an SMS table space on a partition where there are no existing containers for the table space using the SYSTEM CONTAINER clause.

To add a new container to an existing table space, use the ADD clause of the ALTER TABLESPACE statement. If you do not explicitly tell DB2 at which offset within the table space (aka stripe set) to add the container, DB2 will choose the stripe set based on the size of the existing containers and the new container(s). When you run the ALTER TABLESPACE command, DB2 may need to asynchronously rebalance the data in the table space so that it is balanced across the containers evenly. If you do not want DB2 to rebalance the data, which can affect the performance of your system, you can specify the BEGIN NEW STRIPE SET clause; this essentially adds the container to the bottom of the table space for use as new data is added.

Let's look at some examples of adding containers to table spaces using the following table space:

 CREATE TABLESPACE myts MANAGED BY DATABASE USING     (FILE 'cont0' 50,     FILE 'cont1' 50,     FILE 'cont2' 30)     EXTENTSIZE 10 

Since each container has a tag that consumes a full extent, there will be a total of four, four, and two extents of available space in the containers respectively, as Figure 8.17 shows logically.

Figure 8.17. Table space with three containers

NOTE

The following examples show containers of different sizes within a table space for the purposes of illustration. For performance reasons it is recommended that containers within a table space are the same size.

If you add a container that is at least large enough to go from the top to the end of the table space, the container will be added at the top of the table space and will then extend stripe zero to the end of the existing table space as follows:

 ALTER TABLESPACE myts ADD (FILE 'cont3' 60) 

Figure 8.18 shows the effect of this statement.

Figure 8.18. Adding a larger container

If you add a new container that is not large enough to go from the top to the bottom of the table space, that is, it is not as large as the largest container, it will be added such that its last extent lines up with the end of the table space, as follows:

 ALTER TABLESPACE myts ADD (FILE 'cont3' 40) 

Figure 8.19 shows the effect of this statement.

Figure 8.19. Adding a smaller container

If you add multiple containers at the same time, DB2 applies the rules about where to add the container to each container individually, in the order they are added in the ALTER TABLESPACE statement. You can add two containers with five and three extents of available space as follows:

 ALTER TABLESPACE myts ADD (FILE 'cont3a' 60, FILE 'cont3b' 40) 

Figure 8.20 shows the effect of this statement.

Figure 8.20. Adding two containers of different sizes

If you specify the BEGIN NEW STRIPE SET clause when adding a container, the container will be added to the end of the table space. This avoids any rebalance, as the new container will only be used as new data is added to the table space. The statement to use is

 ALTER TABLESPACE myts ADD (FILE 'cont3' 60) BEGIN NEW STRIPE SET 

and the effect of the statement is shown in Figure 8.21.

Figure 8.21. Adding a container with a new stripe set

To make the containers in an existing table space bigger, you can either extend them or resize them. The EXTEND option increases the container by the size specified, while the RESIZE option changes the size of the container to the size specified. You can also use the RESIZE option to increase or reduce the size of a container.

Let's continue using the same table space as given earlier to illustrate these options. To recap, the table space was created as follows:

 CREATE TABLESPACE myts MANAGED BY DATABASE USING     (FILE 'cont0' 50,     FILE 'cont1' 50,     FILE 'cont2' 30)     EXTENTSIZE 10 

To increase the size of the third container (that is, cont2) to 50 pages like the other containers, you can use either of the following commands:

 ALTER TABLESPACE myts     EXTEND (FILE 'cont2' 20) 

or

 ALTER TABLESPACE myts     RESIZE (FILE 'cont2' 50) 

To increase the size of all the containers to 100 pages, you can use one of the following commands:

 ALTER TABLESPACE myts     EXTEND (FILE 'cont0' 50,  FILE 'cont1' 50,  FILE 'cont2' 70) 

or

 ALTER TABLESPACE myts     RESIZE  (FILE 'cont0' 100,       FILE 'cont1' 100,       FILE 'cont2' 100) 

or

 ALTER TABLESPACE myts     RESIZE (all containers 100) 

To increase the size of all of the containers by 100 pages, you can use the command:

 ALTER TABLESPACE myts     EXTEND  (all containers 100) 

8.4.10.2 Shrinking a Table Space

If you find that you have a lot of free space in some of your table spaces, you may be able to free up that space for use by other table spaces or for other file systems on your server. You can reduce the amount of space available for your DMS table spaces by dropping or removing containers from the table space or by reducing the size of the current containers in the table space.

DB2 will not let you remove a container or shrink the existing container(s) if the result would not leave enough space in the table space to hold all of the existing data stored in the table space. Also, DB2 will not REORG the data and indexes in the table space to remove free space from within the pages.

8.4.10.2.1 The Table Space High-Water Mark

You also cannot reduce the number of table space containers or shrink the existing containers so that the table space would be smaller than its high-water mark. The high-water mark is the first page after the highest page number that has been allocated within the table space. This is not always going to be the same as the number of used pages, because you may have inserted some data, established the table space high-water mark, then deleted some data. The table space will then show these pages where the data was deleted are available for use, but the high-water mark will not be moved down. Figure 8.22 illustrates the concept of the table space high-water mark. Consider the following:

• The table space has 1,000 usable pages and its extent size is 100. Therefore, there will be ten extents in the tablespace.

• By default, Extents 0, 1, and 2 will be used for the table space overhead.

• If you create a table named org, Extent 3 will be allocated for its object map, and Extent 4 will be allocated for its table object.

• If you create a table named dept, Extent 5 will be allocated for its object map, and Extent 6 will be allocated for its table object.

Figure 8.22. The table space high-water mark

• If you drop the table org, Extents 3 and 4 will be freed up for reuse by other data and/or objects. There will be four extents in use, which means that the total number of pages used by the tables would be 400.

• The highest allocated page number in this tablespace is 699, which means that the high-water mark is 700 (699 + 1).

DB2 will not allow you to drop a container or reduce the size of the containers if you are attempting to remove more extents from the table space than are above the table space high water mark. In this example, there are three extents above the high water mark, so you can reduce the table space by a maximum of three extents.

Let's step through some other examples. For these, assume that the high-water mark is not an issue and that there is enough free space available in the table space to allow the operation to complete successfully.

Assume that you have a table space created with the following command:

 CREATE TABLESPACE myts MANAGED BY DATABASE USING     (FILE 'cont0' 250,     FILE 'cont1' 300,     FILE 'cont2' 200)     EXTENTSIZE 10 

To decrease the size of the third container (cont2) to be only 150 pages, you can use either of the following commands:

 ALTER TABLESPACE myts     RESIZE (FILE 'cont2' 150) 

or

 ALTER TABLESPACE myts     REDUCE (FILE 'cont2' 50) 

To remove the third container (cont2) from the table space, you can use the command:

 ALTER TABLESPACE myts     DROP (FILE 'cont2') 

To decrease the size of the first and second containers to make them both 200 pages, you can use either of the following methods:

 ALTER TABLESPACE myts     RESIZE (FILE 'cont0' 200) 

plus

 ALTER TABLESPACE myts     RESIZE (FILE 'cont1' 200) 

or

 ALTER TABLESPACE myts     RESIZE (FILE 'cont0' 200, file 'cont1' 200) 

It is better to call the ALTER TABLESPACE statement once and specify both of the containers that you want to change rather than doing this in two steps. If you do this in two steps like in the first example, DB2 needs to complete a rebalance of the data after the first ALTER TABLESPACE command, and another one after the second ALTER TABLESPACE command. If you do this with one ALTER TABLESPACE command, DB2 only needs to rebalance the data once, saving time and causing less impact on the system.

When you alter a table space to either add or remove space, the table space may need to be rebalanced. In Version 8, the table space snapshot has been enhanced to include information about any table space rebalancing that may be occurring. If a rebalance is occurring, the rebalancer mode field will be either forward (starting from page zero and working down through the table space), or reverse (starting at the end of the table space and working up through the table space). The snapshot will also show when the rebalance was started and how many extents in the table space still need to be moved as part of the rebalance process. The following is part of the table space snapshot that shows this information.

 High water mark (pages)                  = 21771 Rebalancer Mode                          = Reverse     Start Time                           = 01-16-2004 11:02:33.000000     Restart Time                         = 01-16-2004 11:02:33.000000     Number of extents processed          = 1102     Number of extents remaining          = 484     Last extent moved                    = 1160 

8.4.10.3 Dropping a Table Space

While an SMS table space will not consume any space unless there are objects (i.e., tables and indexes) in the table space, a DMS table space will continue to hold all of its allocated space. If you create a table space and find that you did not create any objects in the table space, it is good practice to drop the table space.

Dropping a table space can also be a very fast and efficient method for dropping tables. If you drop a table space and there are tables in the table space, then DB2 will drop the tables as well, as long as there are no objects for the tables in other table spaces (i.e., the data object for the table is in one table space while the index object is in another table space). When you drop a table space this way, DB2 does not log all of the row and page deletions as it does for a drop table operation; therefore, this can be a much more efficient method to drop a table, especially if you have only that table defined in the table space.

To drop a table space you use the DROP TABLESPACE statement:

 DROP TABLESPACE myts 

This removes all entries for the table space from the system catalogs as well as its entries in the table spaces file, and it drops all objects defined wholly within the table space.