Creating a table space within a database assigns containers to the table space and records its definitions and attributes in the database system catalog.
For automatic storage table spaces, the database manager assigns containers to the table space based on the storage paths associated with the database.
For non-automatic storage table spaces, you must know the path, device or file names for the containers that you will use when creating your table spaces. In addition, for each device or file container you create for DMS table spaces, you must know the how much storage space you can allocate to each container.
CREATE TABLESPACE RESOURCE
MANAGED BY SYSTEM
USING ('d:\acc_tbsp', 'e:\acc_tbsp', 'f:\acc_tbsp')
CREATE TABLESPACE RESOURCE
MANAGED BY DATABASE
USING (FILE'd:\db2data\acc_tbsp' 5000,
FILE'e:\db2data\acc_tbsp' 5000)
In the previous two examples, explicit names are provided for the containers. However, if you specify relative container names, the container is created in the subdirectory created for the database.
When creating table space containers, the database manager creates any directory levels that do not exist. For example, if a container is specified as /project/user_data/container1, and the directory /project does not exist, then the database manager creates the directories /project and /project/user_data.
Because the database manager created directory levels /project/user_data with PERMISSION 700 from the first request, user2 does not have access to these directory levels and cannot create container2 in those directories. In this case, the CREATE TABLESPACE operation fails.
If a subdirectory is created by the database manager, it might also be deleted by the database manager when the table space is dropped.
IN database_partition_group_name
CREATE TABLESPACE RESOURCE
MANAGED BY DATABASE
USING (DEVICE '/dev/rdblv6' 10000,
DEVICE '/dev/rdblv7' 10000,
DEVICE '/dev/rdblv8' 10000)
OVERHEAD 7.5
TRANSFERRATE 0.06
The UNIX devices mentioned in this SQL statement must already exist, and the instance owner and the SYSADM group must be able to write to them.
CREATE TABLESPACE PLANS IN ODDGROUP
MANAGED BY DATABASE
USING (DEVICE '/dev/HDISK0' 10000, DEVICE '/dev/n1hd01' 40000)
ON DBPARTITIONNUM 1
(DEVICE '/dev/HDISK0' 10000, DEVICE '/dev/n3hd03' 40000)
ON DBPARTITIONNUM 3
(DEVICE '/dev/HDISK0' 10000, DEVICE '/dev/n5hd05' 40000)
ON DBPARTITIONNUM 5
The database manager can greatly improve the performance of sequential I/O using the sequential prefetch facility, which uses parallel I/O.
CREATE TABLESPACE SMS8K
PAGESIZE 8192
MANAGED BY SYSTEM
USING ('FSMS_8K_1')
BUFFERPOOL BUFFPOOL8K
Notice that the associated
buffer pool must also have the same 8 KB page size.The created table space cannot be used until the buffer pool it references is activated.
You can use the ALTER TABLESPACE statement to add, drop, or resize containers to a DMS table space and modify the PREFETCHSIZE, OVERHEAD, and TRANSFERRATE settings for a table space. You should commit the transaction issuing the table space statement as soon as possible following the ALTER TABLESPACE SQL statement to prevent system catalog contention.
Direct I/O (DIO) improves memory performance because it bypasses caching at the file system level. This process reduces CPU overhead and makes more memory available to the database instance.
Concurrent I/O (CIO) includes the advantages of DIO and also relieves the serialization of write accesses.
DIO and CIO are supported on AIX; DIO is supported on HP-UX, Solaris, Linux, and Windows operating systems.
The keywords NO FILE SYSTEM CACHING and FILE SYSTEM CACHING are part of the CREATE and ALTER TABLESPACE SQL statements to allow you to specify whether DIO or CIO is to be used with each table space. When NO FILE SYSTEM CACHING is in effect, the database manager attempts to use Concurrent I/O (CIO) wherever possible. In cases where CIO is not supported (for example, if JFS is used), DIO is used instead.
When you issue the CREATE TABLESPACE statement, the dropped table recovery feature is turned on by default. This feature lets you recover dropped table data using table space-level restore and rollforward operations. This is useful because it is faster than database-level recovery, and your database can remain available to users.
However, the dropped table recovery feature can have some performance impact on forward recovery when there are many drop table operations to recover or when the history file is very large.
You might want to disable this feature if you plan to run numerous drop table operations, and you either use circular logging or you do not think you will want to recover any of the dropped tables. To disable this feature, you can explicitly set the DROPPED TABLE RECOVERY option to OFF when you issue the CREATE TABLESPACE statement. Alternatively, you can turn off the dropped table recovery feature for an existing table space using the ALTER TABLESPACE statement.