Technical Blog Post
ITM Agent Insight: TCR Report Dimension Table and troubleshoot UDA-SQL-0107 error
This blog is intended to help you understand and walk you through steps to create the necessary Dimension Table for the Tivoli Common Reports. Preparing 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.
The following dimensions tables are created by this procedure:
- IBM_TRAM Schema
- TIME_DIMENSION table
- MONTH_LOOKUP table
- WEEKDAY_LOOKUP table
- Other dimensions
- Other dimensions conforming to the Tivoli Common Data Model, such as ComputerSystem, BusinessService, and SiteInfo.
You will need the database scripts included in the extracted reports package under the db_scripts directory.
If reports are distributed with an installer, the following manual procedures can be handled automatically by the report installer. See your agent-specific user's guide for information on automated TRAM creation.
When installing multiple report packages, the following steps need only be completed once. When installing multiple report packages the same TIME_DIMENSION tables are used. If you want to reset granularity or begin and end times, you can repeat the procedure.
You can use IBM DB2, Microsoft SQL Server and Oracle as your database. In this blog I will explain how to create it using IBM DB2 procedure.
- IBM DB2
On warehouse/tems/teps/ machine: ITM trame create time dimensions:
Follow this steps: open cmd:
Go to db directory
db2 connect to WAREHOUS <-- you want to connect as Administrator
Go to cd c:\TCR\db_scripts\db2 if you are not in the dir.
db2 call IBM_TRAM.CREATE_TIME_DIMENSION(‘2005-12-31-00.00.00.000000’,‘2021-12-31-00.00.00.000000’, 60);
The time dimension tables are complete.
If the DB2 commands are failing with the following error:
UDA-SQL-0107 A general exception has occurred during the operation "prepare".[IBM][CLI Driver][DB2/NT64] SQL0551N "ITMUSER" does not have the required authorization or privilege to perform operation "SELECT" on object "IBM_TRAM......
- Connect to the Tivoli Data Warehouse as a user with DB2 privileges.
- Issue the following grants:
- Grant select on IBM_TRAM."ComputerSystem" to ITMUSER
- Grant select on IBM_TRAM.MONTH _LOOKUP to ITMUSER
- Grant select on IBM_TRAM.TIMEZONE_ DIMENSION to ITMUSER
- Grant select on IBM_TRAM.TIME_DIMENSION to ITMUSER
- Grant select on IBM_TRAM.WEEKDAY_LOOKUP to ITMUSER
- Grant execute on procedure IBM_TRAM.CREATE_TIME_DIMENSION to ITMUSER
Subscribe and follow us for all the latest information directly on your social feeds: