Storage options

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.
Read syntax diagramSkip visual syntax diagram
Storage Options

   +-IN--+-dbspace--+-----------+  |                (2) |   
   |     '-extspace-'           |  '-| PUT clause |-----'   
   |                        (1) |                           
   '-| FRAGMENT BY clause |-----'                           

   |                         (3) |   
   '-| EXTENT SIZE options |-----'   

   |                       (4) |   
   '-| COMPRESSED option |-----'   

  1. See FRAGMENT BY clause
  2. See PUT Clause
  3. See EXTENT SIZE Options
  4. See COMPRESSED option for tables
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.


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.

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.

Copyright© 2019 HCL Technologies Limited