IBM Tivoli Monitoring > Version 6.3 Fix Pack 2 > Administrator's Guide > Manage historical data > Performance impact of historical data requests > Use a data mart to improve long or complex queries

IBM Tivoli Monitoring, Version 6.3 Fix Pack 2

---

Sample data mart SQL script for IBM Tivoli Monitoring

The following SQL script is an sample script of how you can create and populate a data mart. Your actual script needs to be revised to reflect your environment.

-------------------------------------------------------- 
-- Example data mart SQL Script for TDW 2.1 
-------------------------------------------------------- 
-- This scripts demonstrates the creation and population 
-- of a data mart (similar to the data marts in TDW 1.x) 
-- starting from the "flat" tables in TDW 2.1. 
-- This script can be run using the DB2 UDB CLP: 
-- db2 -tvf myscript 
--------------------------------------------------------  
-- 1. Create hourly "flat" table from TDW 2.1 (simulated) 
-- One row per hour per Windows system  
drop table itmuser."Win_System_H";  
create table itmuser."Win_System_H" ( 
 WRITETIME                      CHAR( 16 ), 
 "Server_Name"                  CHAR( 64 ), 
 "Operating_System_Type"        CHAR( 16 ), 
 "Network_Address"              CHAR( 16 ), 
 "MIN_%_Total_Privileged_Time"  INTEGER, 
 "MAX_%_Total_Privileged_Time"  INTEGER, 
 "AVG_%_Total_Privileged_Time"  INTEGER, 
 "MIN_%_Total_Processor_Time"   INTEGER, 
 "MAX_%_Total_User_Time"        INTEGER, 
 "AVG_%_Total_User_Time"        INTEGER );  

-- 2. Insert example data  

insert into itmuser."Win_System_H" values ( 
'1050917030000000', 'Primary:WinServ1:NT', 'Windows_2000', '8.53.24.170', 20, 40, 30, 10, 30, 20 );  

insert into itmuser."Win_System_H" values ( 
'1050917040000000','Primary:WinServ1:NT','Windows_2000','8.53.24.170', 20, 40, 30, 10, 30, 20 );  

insert into itmuser."Win_System_H" values (
'1050917030000000','Primary:WinServ2:NT','Windows_2000','8.53.24.171', 20, 40, 30, 10, 30, 20 );  

insert into itmuser."Win_System_H" values (
'1050917040000000','Primary:WinServ2:NT','Windows_2000','8.53.24.171',   20, 40, 30, 10, 30, 20 );  

-- 3. Create a dimension table for the hosts 
-- primary key is Server_ID, a generated value 
-- alternate key is Server_Name, Network_Address  

drop table itmuser."D_Win_System"; 
create table itmuser."D_Win_System" (   
 "Server_ID" INTEGER GENERATED ALWAYS AS IDENTITY
    PRIMARY KEY NOT NULL,   
 "Server_Name"                    CHAR( 64 ),   
 "Operating_System_Type"          CHAR( 16 ),   
 "Network_Address"                CHAR( 16 ) );  

-- 4. Create an hourly fact table for the System facts 
-- Server_ID is a foreign key to D_Win_System  

drop table itmuser."F_Win_System_H"; 
create table itmuser."F_Win_System_H" (   
 WRITETIME                      CHAR( 16 ) NOT NULL,   
 "Server_ID"                    INTEGER NOT NULL,   
 "MIN_%_Total_Privileged_Time"  INTEGER,   
 "MAX_%_Total_Privileged_Time"  INTEGER,   
 "AVG_%_Total_Privileged_Time"  INTEGER,   
 "MIN_%_Total_Processor_Time"   INTEGER,   
 "MAX_%_Total_User_Time"        INTEGER,   
 "AVG_%_Total_User_Time"        INTEGER,   
 constraint SERVID foreign key ("Server_ID")     
  references itmuser."D_Win_System" ("Server_ID")  
);  

-- 5. Insert into the dimension table  
-- only insert rows that do not already exist   

insert into itmuser."D_Win_System" (   
 "Server_Name",   
 "Operating_System_Type",   
 "Network_Address" ) 
select   
 "Server_Name",   
 min("Operating_System_Type") as "Operating_System_Type",  "Network_Address" 
from   itmuser."Win_System_H" h 
where    
 not exists ( select 1 from     
 itmuser."D_Win_System" d     
 where d."Server_Name" = h."Server_Name"      
 and d."Network_Address" = h."Network_Address"    
 ) 
group by   
 "Server_Name",   
 "Network_Address" 
;  

-- 6. Check values in dimension table 
select * from itmuser."D_Win_System"  
;  

-- 7. Insert into the fact table 
-- only insert rows that do not already exist   
insert into itmuser."F_Win_System_H" 
select   
 h.WRITETIME   ,   
 d."Server_ID" ,   
 h."MIN_%_Total_Privileged_Time" ,   
 h."MAX_%_Total_Privileged_Time" ,   
 h."AVG_%_Total_Privileged_Time" ,   
 h."MIN_%_Total_Processor_Time"  ,   
 h."MAX_%_Total_User_Time"       ,   
 h."AVG_%_Total_User_Time"        
from   
 itmuser."Win_System_H" h,   
 itmuser."D_Win_System" d 
where d."Server_Name" = h."Server_Name"    
 and d."Network_Address" = h."Network_Address"   
 and not exists ( select 1 from      
 itmuser."F_Win_System_H" f      
  where f.WRITETIME = h.WRITETIME   
  and f."Server_ID" = d."Server_ID"     
  ) 
;  

-- 8. Check values in fact table 
select * from itmuser."F_Win_System_H"
;  

-- 9. Repeat"5. Insert into the dimension table" 
-- and "7. Insert into the fact table" on a daily basis

See the IBM Redbooks publication, Introduction to Tivoli Enterprise Data Warehouse at http://www.redbooks.ibm.com/ for references and additional sample SQL extract scripts.

Parent topic:

Use a data mart to improve long or complex queries

---

+

Search Tips   |   Advanced Search