Create the dimension tables using the Schema Publication Tool

IBM Tivoli Monitoring > Version 6.3 Fix Pack 2 > Administrator's Guide > Tivoli Common Reporting > Create and maintain the dimension tables
IBM Tivoli Monitoring, Version 6.3 Fix Pack 2

Create the dimension tables using the Schema Publication Tool

Use the schema publication tool to create the dimension tables required by Tivoli Common Reporting and IBM Tivoli Monitoring. The following time dimension tables are created by this task: TIME_DIMENSION, MONTH_LOOKUP, and WEEKDAY_LOOKUP. The following resource dimension tables are also created by this task: MANAGEDSYSTEM, MANAGEDSYSTEMLIST, MANAGEDSYSTEMLISTMEMBERS.

You must have IBM Tivoli Monitoring V6.3 Fix Pack 2 or later.

Ensure that any historical collections and summarization needed for the MANAGEDSYSTEM table are enabled for each agent. See Configure historical data collection before you begin and your agent documentation.

Complete this task before any reports are executed.

For DB2 LUW and DB2 z/OS, you must create a user in the Tivoli Data Warehouse database that has privileges to create tables and execute the stored procedure with the IBM_TRAM schema. The user must have SYSADM or DBADM authority to create an IBM_TRAM schema.

For Oracle, you must create the IBM_TRAM user using the IBM_TRAM schema in the Tivoli Data Warehouse database, or you can create a user that has privileges to create tables and execute the stored procedure with the IBM_TRAM schema.

For Microsoft SQL Server, you must create a user in the Tivoli Data Warehouse database that has privileges to create tables and execute the stored procedure with the IBM_TRAM schema.

You can use this task to create both the time dimension tables and the resource dimension tables, or you can create just the time dimension tables, or just the resource dimension tables. Each set of tables can be created using a different connection to the Tivoli Data Warehouse.
The following schema publication tool modes are supported:

Installed: Generates the DDL for all the monitoring agents installed in your environment. This mode also generates the DDL for the resource dimension tables and the DDL for the time dimension tables if KSY_TRAM_ENABLE is set to Y. This mode also provides the insert statement into the WAREHOUSEID table and the create statements for the TDW functions.

Configured: Generates the DDL only for the attribute groups that have historical collections configured. The DDL for the summarized tables only exists if the summarization for those attribute groups is configured. This mode also generates the DDL for the resource dimension tables and the DDL for the time dimension tables if KSY_TRAM_ENABLE is set to Y. This mode also provides the insert statement into the WAREHOUSEID table and the create statements for the TDW functions.

Updated: This mode is the best practice. Generates the DDL only for the attribute groups that have historical collections configured, but that do not exists in the Tivoli Data Warehouse database. If the DDLs do not already exist, this mode also generates the DDL for the resource dimension tables and the DDL for the time dimension tables if KSY_TRAM_ENABLE is set to Y. This mode also provides the insert statement into the WAREHOUSEID table and the create statements for the TDW functions.

For additional information on running the schema publication tool see "Generating SQL for data warehouse tables" in the IBM Tivoli Monitoring Installation and Setup Guide.
The procedure below uses the updated mode.

Procedure
To create the resource dimension tables used by the warehouse, you must edit the response file.

install_dir\TMAITM6\tdwschema.rsp for 32-bit Windows systems

install_dir\TMAITM6_x64\tdwschema.rsp for 64-bit Windows systems

install_dir/arch/bin/tdwschema.rsp

Configure the following environment variables:

KSY_PRODUCT_SELECT = updated

KSY_SQL_OUTPUT_FILE_PATH = optional file path for SQL output

Save and close the file.

To create the time dimension tables used for reporting, you must edit the Summarization and Pruning agent environment variable file and set KSY_TRAM_ENABLE=Y.

On the computer where the Summarization and Pruning agent is installed, in the Manage Tivoli Enterprise Monitoring Services application, right-click the agent and select Advanced → Edit ENV file.

On the computer where the Summarization and Pruning agent is installed, change to the install_dir/config directory.

Open the sy.ini file in a text editor.

Save and close the file.
Scripts to create the time dimension tables will not be generated if KSY_TRAM_ENABLE=Y and you also use the KSY_TABLE_FILTER variable in the tdwschema.rsp file but do not include the time dimension tables and resource dimension tables in the list of tables to filter by.

Ensure the Tivoli Enterprise Portal Server is started.

Run the schema publication tool script:

install_dir\TMAITM6\tdwschema.bat for 32-bit Windows systems

install_dir\TMAITM6_x64\tdwschema.bat for 64-bit Windows systems

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.
Connect to the Tivoli Data Warehouse with the authorized user ID and execute the following scripts in the order listed.
To create the resource dimension tables and any other attribute group tables, execute the following SQL scripts in the order listed with the Tivoli Data Warehouse user ID:
tdw_schema_table.sql
tdw_schema_index.sql
tdw_schema_view.sql
tdw_schema_function.sql
tdw_schema_insert.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:
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.
tdw_tram_table.sql
tdw_tram_index.sql
tdw_tram_function.sql
tdw_tram_insert.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.
Example on DB2:
db2 connect to <TDW_database> user <TDW_user> using <password>
db2 -tvf tdw_schema_table.sql
db2 -tvf tdw_schema_index.sql
db2 -tvf tdw_schema_view.sql
db2 -td# -f tdw_schema_function.sql
db2 -tvf tdw_schema_insert.sql

db2 connect to <TDW_database> user <TRAM_user> using <TRAM_password>
db2 -tvf tdw_tram_table.sql
db2 -tvf tdw_tram_index.sql
db2 -td# -f tdw_tram_function.sql
db2 -tvf tdw_tram_insert.sql
Example on MSSQL:
osql -S <SQL server> -U <user ID> -P <password> -d <database name> 
  -I -i tdw_schema_table.sql
osql -S <SQL server> -U <user ID> -P <password> -d <database name> 
  -I -i tdw_schema_index.sql
osql -S <SQL server> -U <user ID> -P <password> -d <database name> 
  -I -i tdw_schema_view.sql
osql -S <SQL server> -U <user ID> -P <password> -d <database name> 
  -I -i tdw_schema_function.sql
osql -S <SQL server> -U <user ID> -P <password> -d <database name> 
  -I -i tdw_schema_insert.sql

osql -S <SQL server> -U <IBM_TRAM> -P <IBM_TRAM_password> -d <database name> 
  -I -i tdw_tram_table.sql
osql -S <SQL server> -U <IBM_TRAM> -P <IBM_TRAM_password> -d <database name> 
  -I -i tdw_tram_index.sql
osql -S <SQL server> -U <IBM_TRAM> -P <IBM_TRAM_password> -d <database name> 
  -I -i tdw_tram_function.sql
osql -S <SQL server> -U <IBM_TRAM> -P <IBM_TRAM_password> -d <database name> 
  -I -i tdw_tram_insert.sql
Example on Oracle:
sqlplus <user ID>/<password>@<TDW Database SID> @tdw_schema_table.sql
sqlplus <user ID>/<password>@<TDW Database SID> @tdw_schema_index.sql
sqlplus <user ID>/<password>@<TDW Database SID> @tdw_schema_function.sql
sqlplus <user ID>/<password>@<TDW Database SID> @tdw_schema_insert.sql

sqlplus <IBM_TRAM>/<IBM_TRAM_password>@<TDW Database SID> @tdw_tram_table.sql
sqlplus <IBM_TRAM>/<IBM_TRAM_password>@<TDW Database SID> @tdw_tram_index.sql
sqlplus <IBM_TRAM>/<IBM_TRAM_password>@<TDW Database SID> @tdw_tram_function.sql
sqlplus <IBM_TRAM>/<IBM_TRAM_password>@<TDW Database SID> @tdw_tram_insert.sql
Results
The dimension tables are now created.

What to do next

If you received errors while executing the tdw_schema_insert.sql script, see the IBM Tivoli Monitoring Troubleshooting Guide.

Parent topic:
Create and maintain the dimension tables

+
Search Tips | Advanced Search