Skip to Content

Implicit objects in partition-by-growth table spaces

ALTER and 

BMC AMI Change Manager for Db2

 support the automatic creation of a database or a table space.

The products also support the use of the <DEFLT> keyword as the database name or table space name, as shown in the following table.

Database name

Table space name

CREATE syntax

Notes

<DEFLT>

<DEFLT>

CREATE TABLE tableName;

Db2 creates the table in an implicit database (named DSNnnnnn ) and an implicit table space.

User-defined name

DSNDB04

<DEFLT>

CREATE TABLE tableName IN DATABASE databaseName;

Db2 creates the table in an explicit database and an implicit table space.

<DEFLT>

User-defined name

ERROR

You cannot explicitly create a table space in an implicit database in ALTER and Change Manager.

For reference, the guidelines in the following table apply when you work with implicit table spaces.

Situation

Db2 or product response

You create a table that has a ROWID GENERATED BY DEFAULT column in an implicit table space.

Db2 automatically creates an enforcing index.

You create a base table in an implicit table space.

Db2 automatically creates auxiliary objects to support the table’s large object (LOB) columns.

You add a ROWID GENERATED BY DEFAULT column (or a LOB column) to a table that exists in an implicit table space within an implicit database and your changes result in a DROP TABLE and a CREATE TABLE statement.

The product re-creates the table space implicitly.

Warning

Important

You do not need to create the enforcing index for the ROWID and the auxiliary objects. However, if you want to maintain control over the names and attributes of the required enforcing index and auxiliary objects, you must re-create the table space explicitly.

You add a ROWID GENERATED BY DEFAULT column (or a LOB column) to a table that exists in an implicit table space within an explicit database (defined by the user or defined as DSNDB04).

  • If your changes result in a DROP TABLE and a CREATE TABLE statement, the product drops and re-creates the table in an explicit table space. You must create the enforcing index for the ROWID and the auxiliary objects.
  • If your changes result in an ALTER TABLE ADD COLUMN statement, the product alters the table in the implicit table space. You do not need to create the enforcing index for the ROWID and the auxiliary objects.

The table exists in an implicit table space, and you do not create an enforcing index for the primary key or unique constraints.

Db2 automatically creates the index for you.

Related topic

Conversion-of-table-spaces


 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

ALTER and BMC AMI Change Manager for Db2 13.1