Databases

A database is a logical storage unit that contains tables and indexes. Each database also contains a system catalog that tracks information about many of the elements in the database, including tables, indexes, SPL routines, and integrity constraints.

A database is stored in the dbspace that is specified by the IN clause of the CREATE DATABASE statement. When you do not explicitly name a dbspace in the CREATE DATABASE statement, the database is stored in the root dbspace, unless automatic location is enabled. You can enable automatic location by setting the AUTOLOCATE configuration parameter or session environment variable to a positive integer. The database server chooses the dbspaces in which to create new databases and new tables that are created without specified storage locations. Tables are automatically fragmented by round robin in the dbspaces that are chosen by the server.

A database is stored in the dbspace that is specified by the CREATE DATABASE statement. When you do not explicitly name a dbspace in the CREATE DATABASE statement, the database is stored in the root dbspace.

When you do specify a dbspace in the CREATE DATABASE statement, this dbspace is the location for the following tables:

  • Database system catalog tables
  • Any table that belongs to the database
The following figure shows the tables that are contained in the stores_demo database.
Figure 1: The stores_demo database

This figure shows the tables in the stores-demo database. These include the customer table, the orders table, the items table, the stock table, the catalog table, the cust_calls table, the call_type table, the manufact table, the state table, the systables system catalog table, the sysviews system catalog table, and an index.

The size limits that apply to databases are related to their location in a dbspace. To be certain that all tables in a database are created on a specific physical device, assign only one chunk to the device, and create a dbspace that contains only that chunk. Place your database in that dbspace. When you place a database in a chunk that is assigned to a specific physical device, the database size is limited to the size of that chunk.