IBM Tivoli Monitoring > Version 6.3 Fix Pack 2 > Installation Guides > Installation Guide > Schema Publication Tool
IBM Tivoli Monitoring, Version 6.3 Fix Pack 2
Use the Schema Publication Tool for database compression
The Schema Publication Tool allows you to generate DDL scripts that can be used against a database capable of compression.
The DDL scripts alter the tables and indexes, including the log control tables. Executing those scripts will help you to reduce the amount of disk space used and also improve database performance. Reducing storage subsystem costs can result in substantial cost savings. The Schema Publication Tool generated scripts can be modified to accommodate local security concerns, point to a particular tablespace, or otherwise customize the generated DDL.
- The Summarization and Pruning Agent must be configured and have an available database connection.
- At this time, compression on DB2 z/OS is not supported.
- Compression increases CPU use by the database application.
- When using DB V9.1 a reorganization is required for each table and index created with compression enabled, whether it is existing or new data. The reorganization creates the required compression dictionary.
- DB2 V9.5 and greater automatically creates the required compression dictionary. A reorganization is required to compress existing data, otherwise only new data is compressed.
- The Schema Publication tool uses only the compression setting stored in the Summarization and Pruning configuration file for all tables and indexes. This setting includes tables normally generated by the Warehouse Proxy Agent such as raw tables and associated control tables.
- If the compression setting is turned off after table and indexes are compressed, the Schema Publication Tool will not generate the DDL to change them back to uncompressed mode.
Ensure a compression enabled database application server is installed and properly licensed. Summarization and Pruning Agent must be configured and an active database connection available. When using Oracle, compression is on a block basis (while on DB2 it is on the entire table). A larger block size could result in significantly larger compression. Block size is defined at database creation and cannot be changed, and block size needs to be a multiple of the underlying operating system block size.
The amount of storage reduction depends on a number of factors including the following consideration: the structure of the data makes an enormous difference. More compression is generated if there are repeated values in the data since all of the database applications use dictionary compression algorithms. The total amount of storage saved is proportional to the size of the table. However, the compression ratio is based on the data itself.
The compression settings and any warnings can be found in the Summarization and Pruning Agent log files and in the Warehouse Proxy Agent and Summarization and Pruning Agent's Tivoli Enterprise Portal configuration workspaces. If the Schema Publication Tool finds the DBMS does not support compression and DB compression is enabled, then the DB compression will be set to NO, a log entry made, and also the script will issue a message to that effect. All DDL will then be created without compression.
To enable compression:
- In the Summarization and Pruning Agent environment file, set the KSY_DB_COMPRESSION environment variable to Y. The default is N.
For Windows systems, the environment file name and location is install dir\TMAINT6\KSYENV. For UNIX systems, the environment file name and location is install dir/config/sys.ini.
- Update the required settings in the Schema Publication Response file and run the tdwschema.bat (Windows) or tdwschema.sh (UNIX) command. The response file property for this setting is: KSY_PRODUCT_SELECT = "updated".
In the following situations there will be separate values and potentially conflicting options for the compression option:
- The Warehouse Proxy Agent and the Summarization and Pruning Agent have separate compression options and can be out of sync.
- If there are multiple Warehouse Proxy Agents, the values could be out of sync.
In all cases, the Schema Publication Tool uses only the Summarization and Pruning Agent setting. No mechanism is provided to coordinate the settings.
Parent topic:
Schema Publication Tool