The CREATE TABLESPACE statement defines a new table space
within the database, assigns containers to the table space, and records
the table space definition and attributes in the catalog.
Invocation
This statement can be embedded
in an application program or issued interactively. It is an executable
statement that can be dynamically prepared only if DYNAMICRULES run
behavior is in effect for the package (SQLSTATE 42509).
Authorization
The privileges held by the
authorization ID of the statement must include SYSCTRL or SYSADM authority.
Syntax
>>-CREATE--+-----------------------+---------------------------->
+-LARGE-----------------+
+-REGULAR---------------+
| .-SYSTEM-. |
'-+--------+--TEMPORARY-'
'-USER---'
>--TABLESPACE--tablespace-name---------------------------------->
>--+-----------------------------------------------------------+-->
| .-DATABASE PARTITION GROUP-. |
'-IN--+--------------------------+--db-partition-group-name-'
>--+--------------------------+--------------------------------->
'-PAGESIZE--integer--+---+-'
'-K-'
.-MANAGED BY--AUTOMATIC STORAGE--| storage-group |--| size-attributes |--.
>--+------------------------------------------------------------------------+-->
'-MANAGED BY--+-SYSTEM--| system-containers |--------------------------+-'
'-DATABASE--| database-containers |--| size-attributes |-'
>--+---------------------------------+-------------------------->
'-EXTENTSIZE--+-number-of-pages-+-'
'-integer--+-K-+--'
'-M-'
>--+-----------------------------------+------------------------>
'-PREFETCHSIZE--+-AUTOMATIC-------+-'
+-number-of-pages-+
'-integer--+-K-+--'
'-M-'
>--+-----------------------------+------------------------------>
'-BUFFERPOOL--bufferpool-name-'
>--+--------------------------------------+--------------------->
'-OVERHEAD--+-number-of-milliseconds-+-'
'-INHERIT----------------'
>--+------------------------+----------------------------------->
+-NO FILE SYSTEM CACHING-+
'-FILE SYSTEM CACHING----'
>--+------------------------------------------+----------------->
'-TRANSFERRATE--+-number-of-milliseconds-+-'
'-INHERIT----------------'
>--+--------------------------------+--------------------------->
'-DATA TAG--+-integer-constant-+-'
+-INHERIT----------+
'-NONE-------------'
>--+---------------------------------+-------------------------><
'-DROPPED TABLE RECOVERY--+-ON--+-'
'-OFF-'
storage-group
|--+-----------------------------------+------------------------|
'-USING STOGROUP--storagegroup-name-'
size-attributes
|--+---------------------+--+-----------------------------+----->
'-AUTORESIZE--+-NO--+-' '-INITIALSIZE--integer--+-K-+-'
'-YES-' +-M-+
'-G-'
>--+------------------------------------+----------------------->
'-INCREASESIZE--integer--+-PERCENT-+-'
'-+-K-+---'
+-M-+
'-G-'
>--+-----------------------------+------------------------------|
'-MAXSIZE--+-integer--+-K-+-+-'
| +-M-+ |
| '-G-' |
'-NONE-----------'
system-containers
.----------------------------------------------------------------------.
| .-,------------------. |
V V | |
|----USING--(----'container-string'-+--)--+-----------------------------+-+--|
'-| on-db-partitions-clause |-'
database-containers
.--------------------------------------------------------------.
V |
|----USING--| container-clause |--+-----------------------------+-+--|
'-| on-db-partitions-clause |-'
container-clause
.-,---------------------------------------------------.
V |
|--(----+-FILE---+--'container-string'--+-number-of-pages-+-+--)--|
'-DEVICE-' '-integer--+-K-+--'
+-M-+
'-G-'
on-db-partitions-clause
|--ON--+-DBPARTITIONNUM--+-------------------------------------->
'-DBPARTITIONNUMS-'
.-,--------------------------------------------------.
V |
>--(----db-partition-number1--+--------------------------+-+--)--|
'-TO--db-partition-number2-'
Description
- LARGE, REGULAR, SYSTEM TEMPORARY, or USER TEMPORARY
- Specifies the type of table space that is to be created. If no
type is specified, the default is determined by the MANAGED BY clause.
- LARGE
- Stores all permanent data. This type is only allowed on database
managed space (DMS) table spaces. It is also the default type for
DMS table spaces when no type is specified. When a table is placed
in a large table space:
- The table can be larger than a table in a regular table space.
For details on table and table space limits, see "SQL and XML limits".
- The table can support more than 255 rows per data page, which
can improve space utilization on data pages.
- Indexes that are defined on the table will require an additional
2 bytes per row entry, compared to indexes defined on a table that
resides in a regular table space.
- REGULAR
- Stores all permanent data. This type applies to both DMS and SMS
table spaces. This is the only type allowed for SMS table spaces,
and it is also the default type for SMS table spaces when no type
is specified.
- SYSTEM TEMPORARY
- Stores temporary tables, work areas used by the database manager
to perform operations such as sorts or joins. A database must always
have at least one SYSTEM TEMPORARY table space, because temporary
tables can only be stored in such a table space. A temporary table
space is created automatically when a database is created.
- USER TEMPORARY
- Stores created temporary tables and declared
temporary tables. No user temporary table spaces exist when a
database is created. To allow the definition of created
temporary tables or declared temporary tables, at least one user
temporary table space should be created with appropriate USE privileges.
- tablespace-name
- Names the table space. This is a one-part name. It is an SQL identifier
(either ordinary or delimited). The tablespace-name must
not identify a table space that already exists in the catalog (SQLSTATE
42710). The tablespace-name must not begin with
the characters 'SYS' (SQLSTATE 42939).
- IN DATABASE PARTITION GROUP db-partition-group-name
- Specifies the database partition group for the table space.
The database partition group must exist. The only database partition
group that can be specified when creating a SYSTEM TEMPORARY table
space is IBMTEMPGROUP. The DATABASE PARTITION GROUP keywords are optional.
If the database partition group is not specified, the default
database partition group (IBMDEFAULTGROUP) is used for REGULAR, LARGE,
and USER TEMPORARY table spaces. For SYSTEM TEMPORARY table spaces,
the default database partition group IBMTEMPGROUP is used.
- PAGESIZE integer [K]
- Defines the size of pages used for the table space. The valid
values for integer without the suffix K are 4 096, 8 192, 16 384,
or 32 768. The valid values for integer with
the suffix K are 4, 8, 16,
or 32. Any number of spaces is allowed between integer and
K, including no space. An error occurs if the page size is not one
of these values (SQLSTATE 428DE), or if the page size is not the same
as the page size of the buffer pool that is associated with the table
space (SQLSTATE 428CB).
The default value is provided by the pagesize database
configuration parameter, which is set when the database is created.
- MANAGED BY AUTOMATIC STORAGE
- Specifies
that the table space is to be an automatic storage table space. If
there are no storage groups defined, an error is returned (SQLSTATE
55060).
The database manager
automatically decides how the automatic storage table space is initially
created. Temporary table spaces are initialized as system managed
space (SMS) table space and permanent table spaces are initialized
as database managed space (DMS) table space. When creating a permanent
table space and the type of table space is not specified, the default
behavior is to create a large table space. With an automatic storage
table space, the database manager determines which containers are
to be assigned to the table space, based upon the storage paths that
are associated with the storage group the table space uses.
- storage-group
- Specify the storage group for an automatic storage table space.
- USING STOGROUP
- For an automatic storage table space, identifies the storage group
for the table space in which the table space data will be stored.
If a storagegroup-name is not specified,
then the currently designated default storage group is used. This
clause only applies to automatic storage table spaces (SQLSTATE 42613).
- storagegroup-name
- Identifies the storage group in which table space data will be
stored. storagegroup-name must identify
a storage group that exists at the current server (SQLSTATE 42704).
This is a one-part name.
- size-attributes
- Specify the size attributes for an automatic storage table space
or a DMS table space that is not an automatic storage table space.
SMS table spaces are not auto-resizable.
- AUTORESIZE
- Specifies whether or not the auto-resize capability of a DMS table
space or an automatic storage table space is to be enabled. Auto-resizable
table spaces automatically increase in size when they become full.
The default is NO for DMS table spaces and YES for automatic storage
table spaces.
- NO
- Specifies that the auto-resize capability of a DMS table space
or an automatic storage table space is to be disabled.
- YES
- Specifies that the auto-resize capability of a DMS table space
or an automatic storage table space is to be enabled.
- INITIALSIZE integer K | M | G
- Specifies the initial size, per database partition,
of an automatic storage table space. This option is only valid for
automatic storage table spaces. The integer value must be followed
by K (for kilobytes), M (for megabytes), or G (for gigabytes). Note
that the actual value used might be slightly smaller than what was
specified, because the database manager strives to maintain a consistent
size across containers in the table space. Moreover, if the table
space is auto-resizable and the initial size is not large enough to
contain meta-data that must be added to the new table space, the database
manager will continue to extend the table space by the value of INCREASESIZE
until there is enough space. If the INITIALSIZE clause is not specified,
the database manager determines an appropriate value. The value for integer must
be at least 48 K.
- INCREASESIZE integer PERCENT or INCREASESIZE integer K
| M | G
- Specifies the amount, per database partition, by which a table
space that is enabled for auto-resize will automatically be increased
when the table space is full, and a request for space has been made.
The integer value must be followed by:
- PERCENT to specify the amount as a percentage of the table space
size at the time that a request for space is made. When PERCENT is
specified, the integer value must be between 0 and 100 (SQLSTATE
42615).
- K (for kilobytes), M (for megabytes), or G (for gigabytes) to
specify the amount in bytes
Note that the actual value used might be slightly smaller or
larger than what was specified, because the database manager strives
to maintain consistent growth across containers in the table space.
If the table space is auto-resizable, but the INCREASESIZE clause
is not specified, the database manager determines an appropriate value.
- MAXSIZE integer K | M | G or MAXSIZE NONE
- Specifies the maximum size to which a table space that is enabled
for auto-resize can automatically be increased. If the table space
is auto-resizable, but the MAXSIZE clause is not specified, the default
is NONE.
- integer
- Specifies a hard limit on the size, per database partition, to
which a DMS table space or an automatic storage table space can automatically
be increased. The integer value must be followed by K (for kilobytes),
M (for megabytes), or G (for gigabytes). Note that the actual value
used might be slightly smaller than what was specified, because the
database manager strives to maintain consistent growth across containers
in the table space.
- NONE
- Specifies that the table space is to be allowed to grow to file
system capacity, or to the maximum table space size (described in "SQL
and XML limits").
- MANAGED BY SYSTEM
- Specifies that the table space is to be an SMS table space.
MANAGED BY SYSTEM cannot be specified
in a DB2® pureScale®
environment (SQLSTATE 42997).
Important: The SMS table space type has
been deprecated in
Version 10.1 for
user-defined permanent table spaces and might be removed in a future
release. The SMS table space type is not deprecated for catalog and
temporary table spaces. For more information, see
SMS permanent table
spaces have been deprecated.
- system-containers
- Specify the containers for an SMS table space.
- USING ('container-string', ...)
- For an SMS table space, identifies one or more containers that will belong to the table space
and in which the table space data will be stored. The container-string cannot
exceed 240 bytes in length.
Each container-string can be an absolute or
relative directory name.
The directory name, if not absolute, is relative to the database directory, and can be a path
name alias (or symbolic link) to storage that is not physically associated with the database
directory. For example, dbdir/work/c1 could be a symbolic
link to a separate file system.
If any component of the directory name does not exist, it is created by the database manager.
When a table space is dropped, all components created by the database manager are deleted. If the
directory identified by container-string exists, it must not contain any files or
subdirectories (SQLSTATE 428B2).
The format of container-string is dependent on the operating system.
Operating system |
Format of absolute path name |
Linux AIX® Solaris HP-UX |
An absolute path name begins with a forward slash (/) |
Windows |
An absolute directory path name begins with a drive letter and a colon
(:) |
A relative path name on any platform does not begin with an operating system-dependent
character.
For file-level protocols, such as NAS
and CIFS, remote resources (such as LAN-redirected drives or NFS-mounted file systems) are currently
supported only when the following technologies are used:
- Network Appliance Filers
- IBM® Network Attached Storage
- NEC iStorage S2100, S2200, or S4100
- NEC Storage NS Series with a database server on Windows
Note:
NEC Storage NS Series is supported only with the use of an
uninterrupted power supply (UPS); continuous UPS (rather than standby) is recommended.
An
NFS-mounted file system on AIX must be mounted in
uninterruptible mode using the -o nointr option.
Block-level protocols, such as iSCSI and FCP, are
supported by any backend storage that has non-volatile RAM or battery backup. The storage technology
must guarantee that successful writes are not lost in the event of failure, such as a power outage.
- on-db-partitions-clause
- Specifies the database partition or partitions on which the containers
are created in a partitioned database. If this clause is not specified,
then the containers are created on the database partitions in the
database partition group that are not explicitly specified in any
other on-db-partitions-clauses. For a SYSTEM TEMPORARY
table space defined on database partition group IBMTEMPGROUP, when
the on-db-partitions-clause is not specified, the
containers will also be created on all new database partitions added
to the database.
- MANAGED BY DATABASE
- Specifies that the table space is to be a DMS table space. When the type of table space is not
specified, the default behavior is to create a large table space.
MANAGED BY DATABASE cannot be
specified in a DB2 pureScale environment (SQLSTATE
42997).
Important: Starting
with
Version 10.1 Fix
Pack 1, the DMS table space type is deprecated for user-defined permanent
table spaces and might be removed in a future release. The DMS table
space type is not deprecated for catalog and temporary table spaces.
For more information, see
DMS permanent table
spaces have been deprecated.
- database-containers
- Specify the containers for a DMS table space.
- USING
- Introduces a container-clause.
- container-clause
- Specifies the containers for a DMS table space.
- (FILE|DEVICE 'container-string' number-of-pages, ...)
- For a DMS table space, identifies one or more containers that will belong to the
table space and in which the table space data will be stored. The type of the container (either FILE
or DEVICE) and its size (in PAGESIZE pages) are specified. The size can also be specified as an
integer value followed by K (for kilobytes), M (for megabytes) or G (for gigabytes). If specified in
this way, the floor of the number of bytes divided by the pagesize is used to determine the number
of pages for the container. A mixture of FILE and DEVICE containers can be specified. The
container-string cannot exceed 254 bytes in length.
For a FILE container,
container-string must be an absolute or relative file name. The file name, if not
absolute, is relative to the database directory. If any component of the directory name does not
exist, it is created by the database manager. If the file does not exist, it will be created and
initialized to the specified size by the database manager. When a table space is dropped, all
components created by the database manager are deleted.
Note: If the file exists, it is overwritten, and if it is smaller than specified, it is extended.
The file will not be truncated if it is larger than specified.
For a DEVICE container, container-string must be a device name. The device
must already exist.
All containers must be unique across all databases. A container can belong to only one table
space. The size of the containers can differ; however, optimal performance is achieved when all
containers are the same size. The exact format of container-string is dependent
on the operating system.
For file-level protocols, such as NAS
and CIFS, remote resources (such as LAN-redirected drives or NFS-mounted file systems) are currently
supported only when the following technologies are used:
- Network Appliance Filers
- IBM Network Attached Storage
- NEC iStorage S2100, S2200, or S4100
- NEC Storage NS Series with a database server on Windows
Note:
NEC Storage NS Series is supported only with the use of an
uninterrupted power supply (UPS); continuous UPS (rather than standby) is recommended.
Block-level protocols, such as
iSCSI and FCP, are supported by any backend storage that has non-volatile RAM or battery backup. The
storage technology must guarantee that successful writes are not lost in the event of failure, such
as a power outage.
- on-db-partitions-clause
- Specifies the database partition or partitions on which the containers
are created in a partitioned database. If this clause is not specified,
then the containers are created on the database partitions in the
database partition group that are not explicitly specified in any
other on-db-partitions-clause. For a SYSTEM TEMPORARY
table space defined on database partition group IBMTEMPGROUP, when
the on-db-partitions-clause is not specified, the
containers will also be created on all new database partitions added
to the database.
- on-db-partitions-clause
- Specifies the database partitions on which containers are created
in a partitioned database.
- ON DBPARTITIONNUMS
- Keywords indicating that individual database partitions are specified.
DBPARTITIONNUM is a synonym for DBPARTITIONNUMS.
- db-partition-number1
- Specify a database partition number.
- TO db-partition-number2
- Specify a range of database partition numbers. The value of db-partition-number2 must
be greater than or equal to the value of db-partition-number1 (SQLSTATE
428A9). Containers are to be created on each database partition between
and including the specified values. A specified database partition
must be in the database partition group for the table space.
The database partition specified by number,
and every database partition within the specified range of database
partitions must exist in the database partition group for the table
space (SQLSTATE 42729). A database partition number can only appear
explicitly or within a range in exactly one on-db-partitions-clause for
the statement (SQLSTATE 42613).
- EXTENTSIZE number-of-pages
- Specifies the number of PAGESIZE pages that will be written to a container before skipping to
the next container. The extent size value can also be specified as an integer value followed by K
(for kilobytes) or M (for megabytes). If specified in this way, the floor of the number of bytes
divided by the page size is used to determine the value for the extent size. The database manager
cycles repeatedly through the containers as data is
stored.
In a DB2 pureScale environment, you should
use an extent size of at least 32 pages. This minimum extent size reduces the amount of internal
message traffic within the DB2 pureScale environment when
extents are added for a table or index.
The default value is provided by the
dft_extent_sz database configuration parameter, which has a valid range of
2-256 pages.
- PREFETCHSIZE
- Specifies to read in data needed by a query before it being referenced
by the query, so that the query need not wait for I/O to be performed.
The default value is provided by the dft_prefetch_sz database
configuration parameter.
- AUTOMATIC
- Specifies
that the prefetch size of a table space is to be updated automatically;
that is, the prefetch size will be managed by the database manager.
The
prefetch size will be updated automatically whenever the number of
containers in a table space changes (following successful execution
of an ALTER TABLESPACE statement that adds or drops one or more containers). The prefetch
size is also automatically updated at database startup.
- number-of-pages
- Specifies the number
of PAGESIZE pages that will be read from the table space when data
prefetching is being performed. The maximum value is 32767.
- integer K | M
- Specifies the prefetch size value as an integer value followed
by K (for kilobytes) or M (for megabytes). If specified in this way,
the floor of the number of bytes divided by the page size is used
to determine the number of pages value for prefetch size.
- BUFFERPOOL bufferpool-name
- The name of the buffer pool used for tables in this table space.
The buffer pool must exist (SQLSTATE 42704). If not specified, the
default buffer pool (IBMDEFAULTBP) is used. The page size of the buffer
pool must match the page size specified (or defaulted) for the table
space (SQLSTATE 428CB). The database partition group of the table
space must be defined for the buffer pool (SQLSTATE 42735).
- OVERHEAD number-of-milliseconds or OVERHEAD
INHERIT
- Specifies
the I/O controller overhead and disk seek and latency time. This value is used to determine the cost
of I/O during query optimization. If OVERHEAD is not specified for a non-automatic storage table
space, the value will default to the database creation default described later in the description
for this keyword. If OVERHEAD is not specified for an automatic storage table space the default is
to INHERIT the value from the storage group it is using. If the OVERHEAD value at the storage group
is undefined, the OVERHEAD will default to the database creation default.
- number-of-milliseconds
- The value of number-of-milliseconds is any numeric literal (integer, decimal,
or floating point). If this value is not the same for all containers, the number should be the
average for all containers that belong to the table space.
- INHERIT
- If INHERIT is specified, the table space must be defined using automatic storage and the
OVERHEAD is dynamically inherited from the storage group. INHERIT cannot be specified if the table
space is not defined using automatic storage (SQLSTATE 42613).
For a
database that was created in DB2
Version 10.1 or
later, the default I/O controller overhead and disk seek and latency time for 4 KB PAGESIZE table
space is 6.725 milliseconds.
For a database that was upgraded from
a previous version of DB2 to DB2
Version 10.1 or
later, the default I/O controller overhead and disk seek and latency time is as follows:
- 7.5 milliseconds for a database created in DB2 version 9.7
or higher
- FILE SYSTEM CACHING or NO FILE SYSTEM CACHING
- Specifies whether or not I/O operations are to be cached at the file system level
or non-cached through the use of direct I/O. If neither option is specified, the I/O mode is
determined based on operating system, filesystem, and in the case of SMS tablespaces, data object
type. For more information, see the "File system caching configurations" topic. Note that once
a non-default file system caching option is chosen, it is not possible to return to the default
(unspecified) behaviour. Instead, the file system caching mode must be selected explicitly.
- FILE SYSTEM CACHING
- Specifies that all I/O operations in the target table space are to be cached at the file system
level.
- NO FILE SYSTEM CACHING
- Specifies that all I/O operations are to bypass the file system-level cache. LOB and Long
field data in SMS tablespaces are
excepted.
Note: You must
format the disk device to have a disk sector size according to the following table:
Operating system |
Disk sector size |
AIX |
512 bytes |
Solaris |
512 bytes |
HP-UX |
1024 bytes |
Linux |
512 bytes |
Windows |
512 bytes |
- TRANSFERRATE number-of-milliseconds or TRANSFERRATE
INHERIT
- Specifies the time to read one page into memory. If TRANSFERRATE is not specified for a
non-automatic storage table space, the value will default to the database creation default described
later in the description for this keyword. If TRANSFERRATE is not specified for an automatic storage
table space the default is to INHERIT the value from the storage group it is using. If the DEVICE
READ RATE value at the storage group is undefined, the TRANSFERRATE will default to the database
creation default.
- number-of-milliseconds
- This value is used to determine the cost of I/O during query optimization. The value of
number-of-milliseconds is any numeric literal (integer, decimal, or floating
point). If this value is not the same for all containers, the number should be the average for all
containers that belong to the table space.
- INHERIT
- If INHERIT is specified, the table space must be defined using automatic storage and the
TRANSFERRATE is dynamically inherited from the DEVICE READ RATE of the storage group. INHERIT cannot
be specified if the table space is not defined using automatic storage (SQLSTATE 42613).
When an
automatic storage table space inherits the TRANSFERRATE setting from the storage group it is using,
the DEVICE READ RATE of the storage group, which is in megabytes per second, is converted into
milliseconds per page read accounting for the PAGESIZE setting of the table space. The conversion
formula follows:
TRANSFERRATE = ( 1 / DEVICE READ RATE ) * 1000 / 1024000 * PAGESIZE
For a
database that was created in DB2Version 10.1 or
later, the default time to read one page into memory for 4 KB PAGESIZE table space is 0.04
milliseconds.
For a
database that was upgraded from a previous version of DB2 to
DB2
Version 10.1 or
later, the default time to read one page into memory is as follows:
- 0.06 milliseconds for a database created in DB2 version
9.17 or higher
- DATA
TAG integer-constant, DATA TAG INHERIT or DATA
TAG NONE
- Specifies
a tag for the data in the table space. If the DATA TAG is not specified,
the default for automatic storage table spaces is to INHERIT from
the storage group it is using and for non-automatic table spaces it
will be set to NONE. This value can be used as part of a WLM configuration
in a work class definition (see CREATE WORK CLASS SET statement)
or referenced within a threshold definition (see CREATE THRESHOLD statement). This clause cannot be specified if
TEMPORARY is also specified (SQLSTATE 42613).
- integer-constant
- Valid values for integer-constant are integers
from 1 to 9. If an integer-constant is specified
and there is an associated storage group, the data tag specified for
the table space will override any data tag value specified for the
associated storage group.
- INHERIT
- If INHERIT is specified, the table space must be defined using
automatic storage and the data tag is dynamically inherited from the
storage group. INHERIT cannot be specified if the table space is not
defined using automatic storage (SQLSTATE 42613).
- NONE
- If NONE is specified, there is no data tag.
- DROPPED TABLE RECOVERY
- Indicates whether dropped
tables in the specified table space can be recovered using the RECOVER
DROPPED TABLE option of the ROLLFORWARD DATABASE command.
This clause can only be specified for a regular or large table space
(SQLSTATE 42613).
- ON
- Specifies that dropped tables can be recovered. This is the default.
- OFF
- Specifies that dropped tables cannot be recovered.
Rules
- If automatic storage is not defined for the database, an error
is returned (SQLSTATE 55060).
- The INITIALSIZE clause cannot be specified with the MANAGED BY
SYSTEM or MANAGED BY DATABASE clause (SQLSTATE 42601).
- The AUTORESIZE, INCREASESIZE, or MAXSIZE clause cannot be specified
with the MANAGED BY SYSTEM clause (SQLSTATE 42601).
- The AUTORESIZE, INITIALSIZE, INCREASESIZE, or MAXSIZE clause cannot
be specified for the creation of a temporary automatic storage table
space (SQLSTATE 42601).
- The INCREASESIZE or MAXSIZE clause cannot be specified if the
tables space is not auto-resizable (SQLSTATE 42601).
- AUTORESIZE cannot be enabled for DMS table spaces that are defined
to use raw device containers (SQLSTATE 42601).
- A table space must initially be large enough to hold five extents
(SQLSTATE 57011).
- The maximum size of a table space must be larger than its initial
size (SQLSTATE 560B0).
- Container operations (ADD, EXTEND, RESIZE, DROP, or BEGIN NEW
STRIPE SET) cannot be performed on automatic storage table spaces,
because the database manager is controlling the space management of
such table spaces (SQLSTATE 42858).
- Each
container definition requires 53 bytes plus the number of bytes necessary
to store the container name. The combined length of all container
definitions for the table space cannot exceed 208 kilobytes (SQLSTATE
54034).
- For a partitioned database, if more than one database partition
resides on the same physical node, the same device or path cannot
be specified for more than one database partition (SQLSTATE 42730).
In this environment, either specify a unique container-string for
each database partition, or use a relative path name.
- Only automatic storage table spaces
can be created in a DB2 pureScale environment(SQLSTATE
42997).
- Container size
limits: In DMS table spaces, a container must be at least
two times the extent size pages in length (SQLSTATE 54039). The maximum
size of a container is operating system dependent.
Notes
- Choosing between a database-managed space or a system-managed
space for a table space is a fundamental choice involving trade-offs.
- When more than one TEMPORARY table space exists in the database,
they are used in round-robin fashion to balance their usage.
- The owner of the table space is granted USE
privilege with the WITH GRANT OPTION on the table space when it is
created.
- An automatic storage table space is created as either an SMS table
space or a DMS table space. DMS is chosen for large and regular table
spaces, and SMS is chosen for temporary table spaces. Note that this
behavior cannot be depended upon, because it might change in a future
release. When DMS is chosen and the type of table space is not specified,
the default behavior is to create a large table space.
- The
creation of an automatic storage table space does not include container
definitions. The database manager automatically determines the location
and size, if applicable, of the containers on the basis of the storage
paths that are associated with the specified storage group or the
default storage group. The database manager will attempt to grow large
and regular table spaces, as necessary, provided that the maximum
size has not been reached. This might involve extending existing containers
or adding containers to a new stripe set. Every time that the database
is activated, the database manager automatically reconfigures the
number and location of the containers for temporary table spaces that
are not in an abnormal state.
- A
large or regular automatic storage table space will not use new storage
paths (see the description of the ALTER STOGROUP statement) until
there is no more space in one of the existing storage paths that the
table space is using. Temporary automatic storage table spaces can
only use the new storage paths once the database has been deactivated
and then reactivated.
- Media
attributes: The following table shows how the media attributes of newly created table spaces
are treated in upgraded and newly created DB2
Version 10.1 databases.
Table 1. Media attributes across different versions of DB2Media attributes |
Upgraded Database |
Newly Created Database |
New automatic storage table spaces / storage group DEVICE READ RATE set to
undefined |
Defaults based on version database was created (no change) |
Not applicable |
New automatic storage table spaces / storage group OVERHEAD set to
undefined |
Defaults based on version database was created (no change) |
Not applicable |
New automatic storage table spaces / storage group DEVICE READ RATE is
set |
Inherit from storage group factoring in PAGESIZE |
Inherit from storage group factoring in PAGESIZE |
New automatic storage table spaces / storage group OVERHEAD is set |
Inherit from storage group |
Inherit from storage group |
New non-automatic storage table spaces |
Defaults based on version database was created (no change) |
DB2
Version 10.1
media defaults taking PAGESIZE into account |
- Default
TRANSFERRATE: The following table shows how the default TRANSFERRATE
value differs for newly created table spaces.
Table 2. Default
TRANSFERRATE PAGESIZE |
TRANSFERRATE |
4 KB |
0.04 ms per page read |
8 KB |
0.08 ms per page read |
16 KB |
0.16 ms per page read |
32 KB |
0.32 ms per page read |
- Syntax alternatives:
The following are supported for compatibility with previous versions of DB2 and with other database products. These alternatives are non-standard and
should not be used.
- NODE can be specified in place of DBPARTITIONNUM
- NODES can be specified in place of DBPARTITIONNUMS
- NODEGROUP can be specified in place of DATABASE PARTITION GROUP
- LONG can be specified in place of LARGE
Examples
- Create a large DMS table space on a Linux system
using three devices of 10 000 4K pages each. Specify their I/O
characteristics.
CREATE TABLESPACE PAYROLL
MANAGED BY DATABASE
USING (DEVICE'/dev/rhdisk6' 10000,
DEVICE '/dev/rhdisk7' 10000,
DEVICE '/dev/rhdisk8' 10000)
OVERHEAD 12.67
TRANSFERRATE 0.18
- Create
a regular SMS table space on Windows using three
directories on three separate drives, with a 64-page extent size, and a 32-page prefetch size.
CREATE TABLESPACE ACCOUNTING
MANAGED BY SYSTEM
USING ('/tbsp/acc1', '/tbsp/acc2', '/tbsp/acc3')
EXTENTSIZE 64
PREFETCHSIZE 32
- Create a system temporary DMS table space on a Linux system using two files of 50 000
pages each, and a 256-page extent size.
CREATE TEMPORARY TABLESPACE TEMPSPACE2
MANAGED BY DATABASE
USING (FILE 'dbtmp/tempspace2.f1' 50000,
FILE 'dbtmp/tempspace2.f2' 50000)
EXTENTSIZE 256
- Create a large DMS table space in database partition group ODDNODEGROUP
(database partitions 1, 3, and 5) on a Linux system.
Use the device /dev/rhdisk0 for 10 000 4K
pages on each database partition. Specify a database partition-specific
device with 40 000 4K pages for each database partition.
CREATE TABLESPACE PLANS
MANAGED BY DATABASE
USING (DEVICE '/dev/rhdisk0' 10000, DEVICE '/dev/rn1hd01' 40000)
ON DBPARTITIONNUM (1)
USING (DEVICE '/dev/rhdisk0' 10000, DEVICE '/dev/rn3hd03' 40000)
ON DBPARTITIONNUM (3)
USING (DEVICE '/dev/rhdisk0' 10000, DEVICE '/dev/rn5hd05' 40000)
ON DBPARTITIONNUM (5)
- Create a large automatic storage table space named DATATS, allowing
the system to make all decisions with respect to table space size
and growth.
CREATE TABLESPACE DATATS
or
CREATE TABLESPACE DATATS
MANAGED BY AUTOMATIC STORAGE
- Create a system temporary automatic storage table space named TEMPDATA.
CREATE TEMPORARY TABLESPACE TEMPDATA
or
CREATE TEMPORARY TABLESPACE TEMPDATA
MANAGED BY AUTOMATIC STORAGE
- Create a large automatic storage table space named USERSPACE3
with an initial size of 100 megabytes and a maximum size of 1 gigabyte.
CREATE TABLESPACE USERSPACE3
INITIALSIZE 100 M
MAXSIZE 1 G
- Create a large automatic storage table space named LARGEDATA with
a growth rate of 10 percent (that is, its total size increases by
10 percent each time that it is automatically resized) and a maximum
size of 512 megabytes. Instead of specifying the INITIALSIZE clause,
let the database manager determine an appropriate initial size for
the table space.
CREATE LARGE TABLESPACE LARGEDATA
INCREASESIZE 10 PERCENT
MAXSIZE 512 M
- Create
a large DMS table space named USERSPACE4 with two file containers
(each container being 1 megabyte in size), a growth rate of 2 megabytes,
and a maximum size of 100 megabytes.
CREATE TABLESPACE USERSPACE4
MANAGED BY DATABASE USING (FILE '/db/file1' 1 M, FILE '/db/file2' 1 M)
AUTORESIZE YES
INCREASESIZE 2 M
MAXSIZE 100 M
- Create large DMS table spaces, using RAW
devices on a Windows operating
system.
- To specify entire physical drives, use the \\.\physical-drive format:
CREATE TABLESPACE TS1
MANAGED BY DATABASE USING (DEVICE '\\.\PhysicalDrive5' 10000,
DEVICE '\\.\PhysicalDrive6' 10000)
- To specify logical partitions by using drive letters:
CREATE TABLESPACE TS2
MANAGED BY DATABASE USING (DEVICE '\\.\G:' 10000,
DEVICE '\\.\H:' 10000)
- To specify logical partitions by using volume global unique identifiers
(GUIDs), use the db2listvolumes utility to retrieve
the volume GUID for each local partition, then copy the GUID for the
logical partition that you want into the table space container clause:
CREATE TABLESPACE TS3
MANAGED BY DATABASE USING (
DEVICE '\\?\Volume{2ca6a0c1-8542-11d8-9734-00096b5322d2}\' 20000M)
You
might prefer to use volume GUIDs over the drive letter format if you
have more partitions than available drive letters on the machine.
- To
specify logical partitions by using junction points (or volume mount
points), mount the RAW partition to another NTFS-formatted volume
as a junction point, then specify the path to the junction point on
the NTFS volume as the container path. For example:
CREATE TABLESPACE TS4
MANAGED BY DATABASE USING (DEVICE 'C:\JUNCTION\DISK_1' 10000,
DEVICE 'C:\JUNCTION\DISK_2' 10000)
The
partition is queried first to see whether there is a file system on
it; if yes, the partition is not treated as a RAW device, and normal
file system I/O operations are performed on the partition.