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.

The table space name is the same as the table name if the following conditions apply:

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.

Start of change 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. End of change

Db2 uses the buffer pool for the specified database. However, if any of the following conditions apply, Db2 chooses a suitable buffer pool for the table space from the subsystem parameter values TBSBPOOL, TBSBP8K, TBSBP16K, and TBSBP32K, on panel DSNTIP2:

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.

Start of change 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. End of change

Start of change 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: End of change

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.