IBM Support

ITM Agent Insight: TCR Report Dimension Table and troubleshoot UDA-SQL-0107 error

Technical Blog Post


Abstract

ITM Agent Insight: TCR Report Dimension Table and troubleshoot UDA-SQL-0107 error

Body

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. 

Procedure

  • IBM DB2

On warehouse/tems/teps/ machine: ITM trame create time dimensions:
Follow this steps: open cmd:
Go to db directory

 

image

db2cmd

db2 connect to WAREHOUS <-- you want to connect as Administrator

image

Go to cd c:\TCR\db_scripts\db2 if you are not in the dir.
Dir 

image
run this now
db2 -tf clean.db2

image
db2 -tf create_schema_IBM_TRAM.db2

image
db2 -td@ -vf gen_time_dim_granularity_min.db2 

image

 

db2 call IBM_TRAM.CREATE_TIME_DIMENSION(‘2005-12-31-00.00.00.000000’,‘2021-12-31-00.00.00.000000’, 60);

 

image

 

Results

The time dimension tables are complete.

Troubleshooting

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

Execute the following commands to resolve the issue:
  1. Connect to the Tivoli Data Warehouse as a user with DB2 privileges.
  2. 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

 

 

 

image

 

Subscribe and follow us for all the latest information directly on your social feeds:

 

 

image

 

image

 

image

 

 

  



 

Check out all our other posts and updates:

Academy Blogs:https://goo.gl/U7cYYY
Academy Videos:https://goo.gl/TLfMoF
Academy Google+:https://goo.gl/HnTs0w
Academy Twitter :https://goo.gl/AhR8CL








 

 

 

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"","label":""},"Component":"","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}}]

UID

ibm11084599

Page Feedback