IBM Tivoli Monitoring > Version 6.3 Fix Pack 2 > Installation Guides > Installation Guide > Schema Publication Tool > Generate SQL for data warehouse tables
IBM Tivoli Monitoring, Version 6.3 Fix Pack 2
Use the schema publication tool in updated mode
Use the schema publication tool's updated mode to create the necessary database objects whenever either historical collection is enabled for additional attribute groups or additional summarizations are enabled.
If you run the schema publication tool in updated mode on an existing IBM Tivoli Monitoring version 6.1 Tivoli Data Warehouse database, the following will not be done:
- In an IBM Tivoli Monitoring V6.1 Tivoli Data Warehouse database, indexes on aggregate tables for a given attribute group do not include all of the key columns for the attribute group. This causes performance problems with the Summarization and Pruning agent. The updated mode does not generate SQL to recreate the indexes to optimize performance.
- In IBM Tivoli Monitoring V6.1, all character data was stored in fixed-length CHAR columns. In IBM Tivoli Monitoring V6.2 and 6.3, VARCHAR is used which greatly reduces disk requirements and improves performance. The updated mode does not generate SQL to convert CHAR columns to VARCHAR.
- In IBM Tivoli Monitoring V6.1, all columns allowed NULL values. In IBM Tivoli Monitoring V6.2 and V6.3 some columns that can never be NULL were changed to include a NOT NULL constraint. In large tables this can save significant disk space. For DB2, this greatly reduced the disk requirements for indexes. The updated mode does not generate SQL to set columns to NOT NULL.
- If using DB2 for Linux, UNIX, and Windows and if the IBM Tivoli Monitoring V6.1 database is not migrated properly, the schema tool may produce SQL that fails. The tool may generate ALTER TABLE statements that cause a table not to fit into the table's page size.
- Create a new response file by copying the sample response file:
- Copy itm_install_dir\TMAITM6\tdwschema.rsp.
- Copy itm_install_dir\TMAITM6_x64\tdwschema.rsp for 64-bit Summarization and Pruning Agent on 64-bit Windows systems.
- Copy itm_install_dir/arch/bin/tdwschema.rsp.
- Use an ASCII text editor, edit the response file as follows: KSY_PRODUCT_SELECT=updated. (See the description above.)
You may also specify an output path via the KSY_SQL_OUTPUT_FILE_PATH=path parameter, as explained above.
- Use either the historical configuration interface within the Tivoli Enterprise Portal or the historical configuration CLI, make the desired changes to your site's historical configuration. If enabling historical collection for new attribute groups, configure but do not start historical collection. If collection is started, the warehouse proxy agent may attempt to create the database objects before you have a chance to generate, edit, and execute the SQL.
- Ensure the Tivoli Enterprise Portal Server is started.
- Run the schema publication tool script using the appropriate syntax for your operating system.
- itm_install_dir\TMAITM6\tdwschema.bat
- itm_install_dir\TMAITM6_x64\tdwschema.bat for 64-bit Summarization and Pruning Agent on 64-bit Windows systems.
- itm_install_dir/arch/bin/tdwschema.sh
The SQL files for the products specified in the response file are generated and written to the directory indicated by the KSY_SQL_OUTPUT_FILE_PATH keyword (or to the current working directory, if no output directory is specified).
- Make any necessary changes to the generated SQL files. For example, you might want to partition tables or assign tables to table spaces.
Do not change any of the following in the generated SQL files:
- Table or view names
- Table column names
- Table column sizes or data types
- Use the appropriate tools to run the SQL queries to create the warehouse tables, indexes, views, inserts, and functions for your relational database. Run the scripts as the Tivoli Data Warehouse user in this order:
- tdw_schema_table.sql
- tdw_schema_index.sql
- tdw_schema_view.sql
- tdw_schema_insert.sql
- tdw_schema_function.sql
To create the time dimension tables execute the following SQL scripts in the order that is listed while connected to the Tivoli Data Warehouse database with the user ID authorized to create tables with the IBM_TRAM schema.
- Reporting integration must be enabled to create tables with the IBM_TRAM schema.
- If using Oracle or Microsoft SQL Server, run the scripts as the TRAM user IBM_TRAM. If using DB2, run the scripts as a user that has privileges to create tables with the IBM_TRAM schema.
Execute the scripts in this order:
- tdw_tram_table.sql
- tdw_tram_index.sql
- tdw_tram_insert.sql
- tdw_tram_function.sql
You must run the scripts in the order that is listed or they will fail. The tdw_tram_insert.sql calls the stored procedure in the tdw_tram_function.sql script to populate the IBM_TRAM.TIME_DIMENSION table after the tdw_tram_table.sql script is executed.
For more information and examples of Creating the dimension tables using the Schema Publication Tool, see "Tivoli Common Reporting" in the IBM Tivoli Monitoring Administrator's Guide.
- Use either the historical configuration interface within the portal or the historical configuration CLI, start historical collection for the newly configured attribute groups.
Parent topic:
Generate SQL for data warehouse tables