For performance purposes a database administrator might
impose rules preventing the creation of tables in the default table
space. You can specify the tablespaces that are to be used as containers
for the Tivoli® Data Warehouse
tables and indices. The containers are only used when creating new
tables. Existing tables are not moved to the specified containers. Tivoli Data Warehouse tables
can be placed on separate drives from other tables in the database,
indexes can be placed on separate drives from the table data, and
so on.
On DB2® for Linux, UNIX,
and Windows there are two
types of tablespaces: system managed and database managed. For system
managed, one or more directories is specified. Data can be added to
tables until there is no more room on the file system. For database
managed tablespaces, one or more files are created. The space is pre-allocated
and data can be added until the files are filled. Database managed
tablespaces can perform better than system managed, but they require
more maintenance. The database administrator can add more containers
as needed, but the table space usage must be monitored.
If only the table tablespace is specified for the Warehouse Proxy
Agent or the Summarization and Pruning Agent, the indexes are automatically
created in the same table space as the table. The table space can
be either a system managed or database managed table space. If a table
tablespace and an index table space are specified, both must be database
managed tablespaces.
For DB2, tablespaces have
a page size of 4K, 8K, 16K, or 32K. Tables must have rows that are
less than the page size. However, there is overhead for each row,
so the maximum record size is not the same as the page size. On DB2 for Linux, UNIX,
and Windows, the row length
of a table in the database manager cannot exceed the following sizes:
- 4005 bytes in a table space with a 4K page size
- 8101 bytes in a table space with an 8K page size
- 16293 bytes in a table space with an 16K page size
- 32677 bytes in a table space with an 32K page size
To avoid errors when creating tables, the Warehouse Proxy Agent
or the Summarization and Pruning Agent will determine whether or not
a table will fit in the specified table space and take one of the
following actions:
- If the table does fit in the configured table space, create the
table in the table space.
- If the table does not fit in the configured table space, remove
both the table and index table space (if an index table space is configured)
clauses from the CREATE statement. This causes DB2 to determine the table space that should
be used based on the record size of the table (this is the existing
Warehouse Proxy Agent and the Summarization and Pruning Agent behavior).
The INDEX table space must also be dropped because DB2 may select a system managed table space which
would cause the create table to fail.
The schema publication tool works in a similar way. If a table
or index table space are specified, the schema tool will (if the tablespaces
exist) determine whether or not each table will fit in the table space
and take one of the following actions:
- If the table fits, generate CREATE statements that create the
table and indices in the specified tablespaces.
- If the table does not fit, drop the table and index table space
clauses from the CREATE statement. Warning comments in the generated
DDL file, before the CREATE statement, indicate that the clauses were
dropped and explain why they were dropped.
If the tablespaces do not exist when the schema publication tool
is executed, the table sizes cannot be checked. The schema tool will
still include the table and index table space clauses in the generated
DDL, but will output a comment at the beginning indicating that the
configured tablespaces do not exist. This scenario could happen in
a couple of different ways:
- The database administrator creates the tablespaces later just
after running the generated DDL.
- The generated DDL is executed on a different DB2 database that does have the specified tablespaces.
Note: You cannot specify where tables and indices are created if you
are using DB2 on z/OS® or Microsoft SQL.
If any of the variables in this section are set using DB2 on z/OS or Microsoft SQL, a warning message
in the trace log file states that the default index container or table
container is not supported for these database types. The Summarization
and Pruning Agent and Warehouse Proxy Agent should execute successfully
without taking into account the content of these variables.