IBM Tivoli Monitoring > Version 6.3 Fix Pack 2 > Administrator's Guide > Tivoli Common Reporting > Create and maintain the dimension tables > Manually creating and maintaining the dimension tables
IBM Tivoli Monitoring, Version 6.3 Fix Pack 2
Create and populating the resource dimension table
Prepare the Tivoli Data Warehouse for Tivoli Common Reporting includes creating and populating the resource dimension table "ManagedSystem", which is required for running the Cognos reports and using the data models.
You must first configure historical data collection. For more information, see Configure historical data collection before you begin.
If your site runs the Tivoli Data Warehouse, each time you install one or more monitoring agents, you must update the warehouse's ManagedSystem table.The following scripts use hardcoded user schemas. If you use a different schema, you must replace every instance of the hardcoded schema with the user you specified.
Procedure
- IBM DB2
- Log in as db2admin. Your user ID must have administrator access to create the resource dimension.
- Connect to the database that you want to create the resource dimension table for. This is the Tivoli Data Warehouse.
db2 connect to WAREHOUS
If you specified a different user from the default of ITMUSER for connecting to your warehouse, customize the provided scripts gen_resources.db2, populate_resources.db2, populate_resources_zos.db2, replacing every instance of the hardcoded schema "ITMUSER" with the user you specified.
Create the tables: db2 -tf gen_resources.db2After the command completes successfully, a new table is shown under the ITMUSER schema: ManagedSystem.
Note: If the table "ITMUSER.ManagedSystem" has already been created the following message is displayed when executing the gen_resources.db2 script and can be ignored: DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0601N The name of the object to be created is identical to the existing name "ITMUSER.MANAGEDSYSTEM" of type "TABLE". SQLSTATE=42710"
Create the stored procedure to populate the ManagedSystem table: db2 -td@ -vf populate_resources.db2
To populate ManagedSystem table, call the stored procedure: db2 "call ITMUSER.POPULATE_OSAGENTS()"Note: If you specified a different user from the default, replace ITMUSER with the user specified during your warehouse configuration.
Microsoft SQL Server
- Customize the provided scripts:
- In create_table.sql, change the default database name in the use statement (replace USE WAREHOUS) if it is different from the default.
- In create_procedure.sql, change the default database name in the use statement (replace USE WAREHOUS) if it is different from the default.
- In populate_agents.sql, change the default database name in the use statement (replace USE WAREHOUS) if it is different from the default.
- Run the scripts at the MS SQL command line in this order:
sqlcmd -i create_table.sql [-U myusername -P mypassword] [-H myhost])
sqlcmd -i create_procedure.sql [-U myusername -P mypassword] [-H myhost])
sqlcmd -i populate_agents.sql [-U myusername -P mypassword] [-H my_host])
Oracle manual installation
- Start a SQL *Plus session if it is not already running.
- Run this command (path with no spaces), and provide all the information that the script requires:
@MY_PATH\setup_populate_agents.sql
Oracle batch installation
- Start a SQL *Plus session if it is not already running.
- Create the ITMUSER.ManagedSystem table. The script must be called by the Tivoli Data Warehouse user, which is ITMUSER by default. If you used a different user name, modify the script for the correct name.
@MY_PATH\create_table.sql USER_TBSPC
- Create the procedure to populate the table:
@MY_PATH\create_procedure.sql
- Start the procedure to populate the ManagedSystem table:
begin POPULATE_OSAGENTS('ITMUSER'); end; /
Results
The resource dimension table is complete.
Parent topic:
Manually creating and maintaining the dimension tablesPrevious topic: Create and populating the time dimensions tables