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

Generate SQL for data warehouse tables

You can use the schema publication tool to generate the SQL statements needed to create the database objects required for initial setup of the Tivoli Data Warehouse.

The schema publication tool is installed with the Summarization and Pruning agent. You should perform this task after product installation and after configuring the Summarization and Pruning agent, but before starting the Warehouse Proxy agent and the Summarization and Pruning agent for the first time.


By default, the database objects required for the data warehouse are created automatically by the installer, the Warehouse Proxy agent and the Summarization and Pruning agent. The schema publication tool enables you to create the data warehouse database objects manually rather than allowing them to be created automatically. There are several situations in which you might want to do this:

The schema publication tool is a script that generates the SQL required to create the data warehouse tables, indexes, functions, views, and ID table inserts required for the selected products. You can then modify the generated SQL files before using them to create the tables and indexes.

  1. Create a new response file by making a copy of the sample response file:

    • itm_install_dir\TMAITM6\tdwschema.rsp

    • itm_install_dir\TMAITM6_x64\tdwschema.rsp for 64-bit Summarization and Pruning Agent on 64-bit Windows systems.

    • itm_install_dir/arch/bin/tdwschema.rsp

  2. Use an ASCII text editor, edit the response file to indicate the options you want to use. The keywords in the response file affect which SQL statements are generated, as well as other options:

    KSY_PRODUCT_SELECT=category

    The category of products to generate SQL files:
    Value Description
    installed All installed Tivoli Enterprise Portal Server products.

    This can produce DDL for a large number of tables.

    configured All configured portal server products. Products that have historical collections defined. DDL is only generated for the attribute groups that have historical collections defined.

    DDL for summary tables is generated based on the summarization and pruning settings. For example, if an attribute group has hourly and monthly summarization enabled, DDL is generated for only the hourly and monthly summary tables.

    updated Configured portal server products with configuration changes that have not yet been deployed to the database. The updated value captures the changes that are necessary to bring the Tivoli Data Warehouse up to date to the current configuration. If you require the entire schema that is configured, use the configured value. Examples of updates:

    • Historical collections created for an attribute group that previously had no collections.

    • Enable additional summarizations for one or more attribute groups.

    • Software update that changes existing attribute groups. Existing warehouse tables will need to have columns added.

    • Database compression is enabled at the database and the Summarization and Pruning agent is configured to use database compression. DDL is generated to compress existing tables and indexes.

    This keyword is required.

    KSY_PRODUCT_FILTER=product_filter

    An optional filter to indicate that only certain specific products are included. (If you do not specify a filter, all products in the specified category are included by default.) Specify the three-letter product codes of the products you want to include, separated by commas. You can find these codes using the tacmd histListProduct command (for more information, refer to the IBM Tivoli Monitoring Command Reference).

    KSY_TABLE_FILTER=list_of_tables

    You can use this filter in addition to KSY_PRODUCT_FILTER. Use the following command to get the list of tables that are available for a given product: 

      tacmd histListAttributeGroups -t <productcode>

    Replace each space in the attribute group name with an underscore character.

    KSY_SUMMARIZATION_SELECTION=summarization_filter

    An optional filter to indicate that only certain summarization options are to be included in the generated tables:
    Value Description
    H Hourly
    D Daily
    W Weekly
    M Monthly
    Q Quarterly
    Y Yearly

    This keyword is valid only with KSY_PRODUCT_SELECT=installed.

    KSY_SQL_OUTPUT_FILE_PATH=path

    An optional path to the directory where the generated SQL files are to be written. If you do not include this keyword, the current working directory is used.

    For more details and the complete syntax for each keyword, refer to the comments in the tdwschema.rsp sample response file.

  3. Make sure the Tivoli Enterprise Portal Server is started.

  4. 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).

  5. 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 the names of any tables specified in the generated SQL files.

  6. Use the appropriate tools to run the SQL queries to create the warehouse tables, indexes, views, inserts, and functions for your relational database. Execute the scripts in this order:

    1. tdw_schema_table.sql

    2. tdw_schema_index.sql

    3. tdw_schema_view.sql

    4. tdw_schema_insert.sql

    5. tdw_schema_function.sql

    The following examples are for the DB2 commands: 

      db2 -tvf tdw_schema_table.sql
db2 -tvf tdw_schema_index.sql
db2 -tvf tdw_schema_view.sql
db2 -tvf tdw_schema_insert.sql
db2 -td# -f tdw_schema_function.sql

    The different invocation for the 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.

    1. Reporting integration must be enabled to create tables with the IBM_TRAM schema.

    2. 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:

    1. tdw_tram_table.sql

    2. tdw_tram_index.sql

    3. tdw_tram_insert.sql

    4. tdw_tram_function.sql

    The following examples are for the DB2 commands: 

      db2 -tvf tdw_tram_table.sql
db2 -tvf tdw_tram_index.sql
db2 -tvf tdw_tram_insert.sql
db2 -td# -f 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.


Parent topic:

Schema Publication Tool

+

Search Tips   |   Advanced Search