DB2 10.1 DBA for Linux, UNIX, and Windows certification exam 611 prep, Part 2: Physical design

Table spaces

A table space is a storage structure containing tables, indices, large objects, and long data. They are used to organize data in a database into logical storage groupings that relate to where data is stored on a system. Table spaces are stored in database partition groups.

Table spaces consist of one or more containers. A container can be a directory name, a device name, or a file name. A single table space can have several containers. It is possible for multiple containers (from one or more table spaces) to be created on the same physical storage device (although you will get the best performance if each container you create uses a different storage device).

If you are using automatic storage table spaces, the creation and management of containers is handled automatically by the database manager. If you are not using automatic storage table spaces, you must define and manage containers yourself. To get details about the table spaces in a database, use the following commands: get snapshot for tablespaces or list tablespaces.

SMS table spaces

System Managed Space (SMS) table spaces use the file system manager provided by the operating system to allocate and manage the space where the tables are stored. Within an SMS table space, each container is an operating system directory, and table objects are created as files within that directory. When creating an SMS table space, the user must specify the name of the directory for each of the containers. DB2 will create the tables within the directories used in the table space by using unique file names for each object.

If a table space is created with more than one container, DB2 will balance the amount of data written to the containers. Since containers cannot be dynamically added to an SMS table space once it has been created, it is important to know the size requirements of the table space and create all required containers when the table space is created.

Characteristics of SMS table spaces

With SMS table spaces:

• All table data and indices share the same table space.
• Each table in a table space is given its own file name used by all containers. The file extension denotes the type of the data stored in the file.
• There is the possibility for dynamic file growth, with an upper boundary on size governed by the number of containers, OS limits on the size of the file system, and OS limits on size of individual files.
• When all space in a single container is allocated, the table space is considered full even if space remains in other containers.
• New containers can only be added to SMS on a partition that does not yet have any containers.
• On Linux or UNIX, the file system size may be increased.

SMS table spaces are very easy to administer, and are recommended for the TEMP table space.

Creating SMS table spaces

To create an SMS table space, use the following command: create table space TS1 managed by system using ('path1', 'path2', 'path3'). When the path is specified for an SMS container, it can be an absolute path or a relative path to the directory. If the directory does not exist, DB2 will create it. If the directory does exist, it cannot contain any files or subdirectories. For example, create table space ts1 managed by system using ('D:\DIR1') specifies the absolute path to the directory. DB2 would create the DIR1 directory on the D: drive on the database server if it does not already exist.

The command create table space ts2 managed by system using ('DIR1') specifies the relative path DIR1. DB2 would create the DIR1 directory under the database home directory.

The following SQL statements create an SMS table space with three containers on three separate drives or file systems. Note that the table space name is the same, as the examples are showing the differences between the Linux/UNIX and Windows table space definitions:

 
create tablespace smstbspc managed by system 
        using ('d:\tbspc1', 'e:\tbspc2', 'f:\ tbspc3')
create tablespace smstbspc managed by system 
        using ('/dbase/container1', '/dbase/container2', '/dbase/container3')
                    

Altering SMS table spaces

SMS table spaces can only be altered to change the prefetch size. Containers cannot be added to an SMS table space using the alter command. However, containers can be redefined, added, or removed during a redirected restore.

Back to top

DMS table spaces

In a database managed space (DMS) table space, the database manager controls the storage space. Unlike SMS table spaces, storage space is pre-allocated on the file system based on container definitions you specify when you create the DMS table space.

The DMS storage model consists of a limited number of files or devices where space is managed by the database manager. You decide which files and devices to use when creating containers, and you manage the space for those files and devices.

A DMS table space containing user defined tables and data can be defined as a large (the default) or regular table space that stores any table data or index data.

Characteristics of DMS table spaces

With DMS table spaces:

• The database manager uses striping to ensure an even distribution of data across all containers.
• Containers that make up a DMS table space are not required to be the same size. If any container is full, DMS table spaces use available free space from other containers.
• Because space is pre-allocated, it must be available before the table space can be created.
• Enabling DMS table spaces that use file containers for automatic resizing allows the database manager to handle the full table space condition automatically by extending existing containers for you.
• The table space is considered to be full when all of the space within the containers has been used.
• Unlike for SMS table spaces, you can add or extend containers manually, using the ALTER TABLESPACE statement, allowing more storage space to be given to the table space.

Creating DMS table spaces

The following statement creates a DMS table space without enabling auto-resize (the default):

 
CREATE TABLESPACE DMS1 MANAGED BY DATABASE 
        USING FILE '/db2files/DMS1' 10 M)

To enable the auto-resize feature, specify the AUTORESIZE YES clause for the CREATE TABLESPACE statement:

 
CREATE TABLESPACE DMS1 MANAGED BY DATABASE 
        USING (FILE '/db2files/DMS1' 10 M) AUTORESIZE YES

To create a table space that can grow to 100 MB (per database partition if the database has multiple database partitions), use the MAXSIZE clause of the CREATE TABLESPACE statement:

CREATE TABLESPACE DMS1 MANAGED BY DATABASE
        USING (FILE '/db2files/DMS1' 10 M)
        AUTORESIZE YES MAXSIZE 100 M
                

If you do not specify the MAXSIZE clause, there is no maximum limit when the auto-resize feature is enabled. The table space will grow until a file system limit is reached.

The INCREASESIZE clause of the CREATE TABLESPACE statement defines the amount of space used to increase the table space when there are no free extents within the table space. You can specify the value as an explicit size or as a percentage, as shown in the following examples:

 

CREATE TABLESPACE DMS1 MANAGED BY DATABASE
        USING (FILE '/db2files/DMS1' 10 M)
        AUTORESIZE YES INCREASESIZE 5 M
                    
CREATE TABLESPACE DMS1 MANAGED BY DATABASE
        USING (FILE '/db2files/DMS1' 10 M)
        AUTORESIZE YES INCREASESIZE 50 PERCENT
                

Altering DMS table spaces

You can enable or disable the auto-resize feature after creating a DMS table space by using ALTER TABLESPACE statement with the AUTORESIZE clause:

 
ALTER TABLESPACE DMS1 AUTORESIZE YES
ALTER TABLESPACE DMS1 AUTORESIZE NO
                

Use the ALTER TABLESPACE statement to change the value of MAXSIZE for a table space that has auto-resize already enabled, as shown in the following examples:

 
ALTER TABLESPACE DMS1 MAXSIZE 1 G
ALTER TABLESPACE DMS1 MAXSIZE NONE
                

You can also define the amount of space used to increase the table space when there are no free extents as shown in the following examples:

 
ALTER TABLESPACE DMS1 INCREASESIZE 5 M
ALTER TABLESPACE DMS1 INCREASESIZE 50 PERCENT
                

Back to top

Automatic storage

What is a automatic storage?

Automatic storage allows you to specify one or more storage paths for a database. Then when you create table space, a container file will be created automatically on each storage path by DB2. You can enable or configure automatic storage for a database when it is created, as follows:

 
db2 create database db_name automatic storage yes
db2 create database db_name on db_path1, db_path2
                    

You can add additional storage paths to a database set up for automatic storage using the add storage parameter: db2 alter database db_name add storage on db_path3. You can also drop a storage path from a database set up for automatic storage using the drop storage parameter: db2 alter database db_name drop storage on db_path3.

Using automatic storage

Once your database has been set up for automatic storage, you can create table spaces using this mechanism. You have several ways to take advantage of automatic storage once the database has been set up that way. You can simply create a table space in the database (once you are connected to the database): db2 create tablespace ts_name. Or you can create a table space and specify its initial size and growth characteristics:

db2 create tablespace ts_name
    initialsize 10M
    increasesize 10M
    maxsize 100M
                    

In this example, the table space will start out at 10 MB, and as it gets close to being full, DB2 will automatically extend it by 10 MB at a time, up to its maximum size of 100 MB.

If the database was not set up for automatic storage, you can still use automatic storage for a table space if you create it and specify its storage:

 
db2 create tablespace ts_name
        managed by automatic storage
                    

Storage groups

A storage group is a named set of storage paths where data can be stored. Storage groups are configured to represent different classes of storage available to your database system. You can assign table spaces to the storage group that best suits the data. Only automatic storage table spaces use storage groups.

A table space can be associated with only one storage group, but a storage group can have multiple table space associations. To manage storage group objects, you can use the CREATE STOGROUP, ALTER STOGROUP, RENAME STOGROUP, and DROP statements.

With the table partitioning feature, you can place table data in multiple table spaces. Using this feature, storage groups can store a subset of table data on fast storage while the remainder of the data is on one or more layers of slower storage. Use storage groups to support multi-temperature storage, which prioritizes data based on classes of storage. For example, you can create storage groups that map to the different tiers of storage in your database system. Then the defined table spaces are associated with these storage groups. More information about multi-temperature storage is available in the "Multi-temperature data feature" section.

The default storage groups

When you create a database, a default storage group named IBMSTOGROUP is automatically created. However, a database created with the AUTOMATIC STORAGE NO clause does not have a default storage group. The first storage group created with the CREATE STOGROUP statement becomes the designated default storage group. There can only be one storage group designated as the default storage group.

If a database has storage groups, the default storage group is used when an automatic storage managed table space is created without explicitly specifying the storage group.

You can designate a default storage group by using either the CREATE STOGROUP or ALTER STOGROUP statement. When you designate a different storage group as the default storage group, there is no impact to the existing table spaces using the old default storage group. To alter the storage group associated with a table space, use the ALTER TABLESPACE statement. You can determine which storage group is the default storage group by using the SYSCAT.STOGROUPS catalog view.

You cannot drop the current default storage group. You can drop the IBMSTOGROUP storage group if it is not designated as the default storage group at that time. If you drop the IBMSTOGROUP storage group, you can create another storage group with that name.

Creating storage groups

To create a storage group by using the command line, enter CREATE STOGROUP operational_sg ON '/filesystem1', '/filesystem2', '/filesystem3'... where operational_sg is the name of the storage group and /filesystem1, /filesystem2, e/filesystem3 , ... are the storage paths to be added.

Altering storage groups

You can use the ALTER STOGROUP statement to alter the definition of a storage group, including setting media attributes, setting a data tag, or setting a default storage group. You can also add and remove storage paths from a storage group.

To add storage paths '/hdd/path1' and '/hdd/path2' to storage group sg, issue the following ALTER STOGROUP statement: ALTER STOGROUP sg ADD '/hdd/path1', '/hdd/path2'. To drop storage paths '/db2/filesystem1' and e'/db2/filesystem2' from storage group sg, issue the following ALTER STOGROUP statement: ALTER STOGROUP sg DROP '/db2/filesystem1', '/db2/filesystem2'.

How to convert existing database to automatic storage

You can convert an existing non-automatic storage database to use automatic storage by using the CREATE STOGROUP statement to define the default storage group within a database.

When you define a storage group for a database, existing table spaces are not automatically converted to use automatic storage. By default, only future table spaces you create are automatic storage table spaces. You must use the ALTER TABLESPACE statement to convert existing table spaces to use automatic storage.

Example 1: Converting a database on Linux or UNIX

Assume that a database is a non-automatic storage database and that /data1/as and /data2/as are the paths you want to use for automatic storage table spaces. To convert the database to an automatic storage database, create a storage group with /data1/as and /data2/as as paths: CREATE STOGROUP sg ON '/data1/as', '/data2/as'.

Example 2: Converting a database on Windows operating systems

Assume that a database is a nonautomatic storage database, and that F:\DB2DATA and G: are the paths you want to use for automatic storage table spaces. To convert the database to an automatic storage database, create a storage group with F:\DB2DATA and G: as paths: CREATE STOGROUP sg ON 'F:\DB2DATA', 'G:'. After creating the default storage group, to convert an existing DMS table space tbspc1 to use automatic storage, issue the following statements:

 
ALTER TABLESPACE tbspc1 MANAGED BY AUTOMATIC STORAGE USING STOGROUP sg
ALTER TABLESPACE tbspc1 REBALANCE
                

The rebalance operation moves data from the non-automatic storage containers to the new automatic storage containers.

How to check if an existing database is an automatic storage

There are several ways to check if an existing database is an automatic storage database. You can use any of the following options.

• The ADMIN_GET_STORAGE_PATHS table function — It returns a list of automatic storage paths for each database storage group, including file system information for each storage path.
• The SYSIBMADM.SNAPSTORAGE_PATHS administrative view — It returns a list of automatic storage paths for the database including file system information for each storage path.
• The db2look utility — Generates the DDL statements for a database by object type.

Back to top

Using Admin_move_table feature

You can move tables online and offline using the ADMIN_MOVE_TABLE procedure. In fact, this can be used to move the data in a table to a new table object of the same name (but with possibly different storage characteristics such as a different table space) while the data remains online and available for access. You can also generate a new optimal compression dictionary when a table is moved.

The ADMIN_MOVE_TABLE stored procedure creates a protocol table comprising rows with status information and configuration options related to the table to be moved. The return set from this procedure is the rows from that protocol table related to the table to be moved.

You can invoke the ADMIN_MOVE_TABLE in one of two ways:

Method 1 — Modify only certain parts of the table definition for the target table. Fill out the data_tbsp, index_tbsp, and lob_tbsp parameters while calling the procedure, leaving the other optional parameters blank.

Example

This example calls the stored procedure using the first method, where the target table is defined within the procedure, to move a table named T1, located in the schema SCHEMA1. Additionally, the column definitions of the target table are passed to the procedure.

 
                    
CALL SYSPROC.ADMIN_MOVE_TABLE(
'SCHEMA1',
'T1',
'ACCOUNTING',
'ACCOUNT_IDX',
'ACCOUNT_LONG',
'',
'',
'',
'CUSTOMER VARCHAR(80), REGION CHAR(5), YEAR INTEGER, CONTENTS CLOB',
'',
'MOVE')
                

The above example moves the table SCHEMA1.T1 to a new table with the same name that has the column definitions ('CUSTOMER VARCHAR(80), REGION CHAR(5), YEAR INTEGER, CONTENTS CLOB') and resides in table space 'ACCOUNTING' with its indices table space 'ACCOUNT_IDX' and its LOBs table space 'ACCOUNT_LONG'.

Method 2 — create the target table and provide its name to the procedure. This provides you with more control and flexibility by allowing you to create the target table beforehand, rather than having the stored procedure create it.

Example

This example is equivalent to the previous one, but it calls the stored procedure using the second method, where the target table is created outside the procedure and is named within the target_tabname parameter, to move the same table as in the previous example.

 
                    
CREATE TABLE SCHEMA1.T1_TGT (
    CUSTOMER VARCHAR(80), 
    REGION CHAR(5), 
    YEAR INTEGER, 
    CONTENTS CLOB) 
IN ACCOUNTING 
INDEX IN ACCOUNT_IDX 
LONG IN ACCOUNT_LONG'
                

 
CALL SYSPROC.ADMIN_MOVE_TABLE(
'SCHEMA',
'T1',
'T1_TGT',
'',
'MOVE')
                

For online data movement:

1. The procedure creates a shadow table to which the data are copied.
2. Any changes to the source table during the copy phase are captured using triggers and placed in a staging table.
3. After the copy phase is completed, the changes captured in the staging table are replayed to the shadow copy.
4. Following that, the stored procedure briefly takes the source table offline and assigns the source table name and index names to the shadow copy and its indices.
5. The shadow table is then brought online, replacing the source table. By default, the source table is dropped, but you can use the KEEP option to retain it under a different name.

Obviously, the online operation costs more server resources (disk space and processing power), so make sure you only use it if you value availability more than cost, space, move performance, and transaction overhead. Additionally, avoid performing online moves for tables without indices, particularly unique ones as it might result in deadlocks and complex or expensive replay.

Handling online move failure

If the online move fails, rerun it:

1. Fix the problem that caused the table move to fail.
2. Determine the stage that was in progress when the table move failed by querying the SYSTOOLS.ADMIN_MOVE_TABLE protocol table for the status.
3. Call the stored procedure again, specifying the applicable option:
   • If the status of the procedure is INIT, use the INIT option.
   • If the status of the procedure is COPY, use the COPY option.
   • If the status of the procedure is REPLAY, use the REPLAY or SWAP option.
   • If the status of the procedure is CLEANUP, use the CLEANUP option.

You can cancel the move by specifying the CANCEL option for the stored procedure if the status of an online table move is not COMPLETED or CLEANUP.

Back to top

Table space states

Determining a table space's state

To find the state for the table spaces in a database: list tablespaces show detail. A table space can have a number of different states, as shown below.

More details about each of the above states are available at Information Center.

4 of 12 | Previous | Next

Comments

Back to top

Table of contents

• Before you start
• Creating a database
• Creating and manipulating various DB2 objects
• Table spaces
• Data organization and partitioning
• XML
• Table compression
• Temporal tables
• Multi-temperature data feature
• Conclusion
• Resources
• About the authors

---

4 of 12 | Previous | Next

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers