IBM Tivoli Monitoring > Version 6.3 Fix Pack 2 > Installation Guides > Installation Guide
IBM Tivoli Monitoring, Version 6.3 Fix Pack 2
Specify where tables and indices are created
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.
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.
- Environment variables
There are two variables that must be set in the Warehouse Proxy Agent environment file to specify the table space for the table and the indices.