Storage options

Use the FRAGMENT BY clause, PUT clause, and EXTENT size options of the CREATE TABLE statement to specify the storage location, distribution scheme, and extent size for the table. Use the FRAGMENT BY clause, PUT clause, EXTENT size options, and COMPRESSED option of the CREATE TABLE statement to specify the storage location, the distribution scheme, the extent size for the table, and whether the table is enabled for the automatic compression of large amounts of new row data.

This syntax fragment is part of the CREATE TABLE statement.

Storage Options

Notes:


Element	Description	Restrictions	Syntax
`dbspace`	Dbspace to store the table	Must exist. For tables in tenant databases, see Dbspaces in tenant databases below.	Identifier
`extspace`	Name declared in the onspaces command to a storage area outside the database server	Must exist	See documentation for your access method.

Usage

The storage options that specify the location, distribution scheme, and extent size for the table are an extension to the ANSI/ISO standard for SQL syntax.

If you use the USING access-method clause to specify an access method, that method must support the storage space.

You can specify a dbspace for the table that is different from the storage location for the database, or fragment the table among dbspaces, or among named fragments in one or more dbspaces.

If you specify no IN clause nor fragmentation scheme, the new table is stored in the same dbspace where the current database is stored. However, if you enabled automatic location and fragmentation, tables are created and fragmented in dbspaces that are chosen by the server. To enable the automatic location and fragmentation of tables, set the AUTOLOCATE configuration parameter or session environment variable to a positive integer. The value of the integer represents the number of fragments to initially allocate to the table. Additional fragments are added as the table grows.

If you specify no IN clause nor fragmentation scheme, the new table is stored in the same dbspace where the current database is stored.

Sbspaces for smart large objects

You can use the PUT clause to specify sbspace storage locations and storage characteristics for smart large objects, such as BLOB or CLOB column values.

Note: If your table contains simple large objects (TEXT or BYTE), you can specify a separate blobspace for each object.

Dbspaces in tenant databases

If the table is in a tenant database, the dbspace must be a dedicated dbspace in the tenant database properties list. If the table is not in a tenant database, the dbspace cannot be the name of a dbspace that is dedicated to a tenant database.