Implicitly defined table spaces
Db2 implicitly creates a partition-by-growth or partition-by-range table space when you issue a CREATE TABLE statement that does not specify an existing table space name.
When Db2 defines a table space implicitly, it generates an implicity created table space for the table.
- No other table space or index space in the database already has that name.
- The table name has no more than eight characters.
- The characters are all alphanumeric, and the first character is not a digit.
However, if another table space in the database already has the same name as the table, Db2 assigns a name of the form xxxxnyyy, where xxxx is the first four characters of the table name, and nyyy is a single digit and three letters that guarantee uniqueness.
If the IN database clause is not specified, Db2 generates a database for you with the name DSNxxxxx, where xxxxx is a five-digit number.
- The IN database-name clause is not specified.
- The IN database-name clause is specified, and the table record length does not fit in the database buffer pool page size.
If the CREATE TABLE statement omits the PARTITION BY clause or specifies PARTITION BY SIZE, Db2 uses the default values of the partition-by-growth specification of the CREATE TABLESPACE statement. If the CREATE TABLE statement specifies PARTITION BY (with or without the RANGE keyword) and a set of column values as partition limit keys, Db2 uses the default values of the partition-by-range specification.
For other attributes of the implicitly created table space, if they are not specified in a CREATE TABLE statement (only some can be specified), the result is the same as an explicit CREATE TABLESPACE statement, except for the following attributes:
Table space attribute | How it is determined |
---|---|
COMPRESS | If not specified in the CREATE TABLE statement, the value is determined by the IMPTSCMP subsystem parameter. |
DEFINE | Always determined by the value of the IMPDSDEF subsystem parameter. |
DSSIZE | If not specified in the CREATE TABLE statement, the value is determined by the IMPDSSIZE subsystem parameter. |
LOCKMAX | LOCKMAX SYSTEM is always used. |
LOCKSIZE | LOCKSIZE ROW is always used. |
PAGENUM | If not specified in the CREATE TABLE statement, the value is determined by value of the PAGESET_PAGENUM subsystem parameter. |
SEGSIZE | For a partition-by-range table space, SEGSIZE 32 is always used. |
TRACKMOD | If not specified in the CREATE TABLE statement, the value is determined by the IMPTKMOD subsystem parameter. |
In certain situations, Db2 can also implicitly create an LOB table space, auxiliary table, and auxiliary index for a LOB column, and underlying XML objects for an XML column. For more information, see LOB table space implicit creation and XML table space implicit creation. In this case, Db2 uses the default storage group, SYSDEFLT.
Db2 also creates the following objects:
- Unique indexes for UNIQUE constraints.
- The primary key index.
- The ROWID index, if the ROWID column is defined as GENERATED BY DEFAULT.
Db2 stores the names and attributes of all table spaces in the SYSIBM.SYSTABLESPACE catalog table, regardless of whether you define the table spaces explicitly or Db2 creates them implicitly.