ITM 6.1 Data Warehouse good news bad news

I guess by now you have noticed that all of the tables and columns in the ITM 6.1 historical database (i.e., DW) are double quoted. The good news is that all of the attribute names are relatively consistent across all of the ITM 6.1 components. Unlike the ITM 5.x attributes names, the ITM 6.1 names are basically the same when you process them for monitoring, detailed historical tables, and summarized tables (Note: columns in the summarized tables are prefixed with their summarization type).

For example, in the "NT_Logical_Disk" attribute group the names are as follows:

In the "Situation Editor" for NT Logical Disk: % Used
In the "Query Editor" for NT Logical Disk: % Used
In the "NT_Logical_Disk" Detailed Table: %_Used
In the "NT_Logical_Disk_D" Summary table: AVG_%_Used

The bad news is that in your SQL queries you have to double quote all of the table and column names and all the names are case sensitive. For example,

select
"Server_Name",
"%_Used",
"%_Free"
from ITMUSER."NT_Logical_Disk"
where "Server_Name" = LIKE 'Primary:GBS%' ;

Notice I have to quote all the column names and the table otherwise the query will fail.

To make my life simpler I have created a bunch of views in my data warehouse database to make the processing of these tables easier. Now with my NT_LOGICAL_DISK view I don't have to quote or worry about the case.

Example,

Select * from nt_logical_disk where where server_name LIKE 'Primary:GBS%' ;

Here is the sample view create code:

Note: I also included the formating of the CTIME timestamp attribute in the view.

drop view nt_logical_disk;
create view nt_logical_disk as
select
"Server_Name" as server_name,
SUBSTR("Timestamp", 2, 2) || '-' ||
SUBSTR("Timestamp", 4, 2) || '-' ||
SUBSTR("Timestamp", 6, 2) || ' ' ||
SUBSTR("Timestamp", 8, 2) || ':' ||
SUBSTR("Timestamp", 10, 2) || ':' ||
SUBSTR("Timestamp", 12, 2) as timestamp,
"Disk_Name" as disk_name,
"%_Disk_Read_Time",
"%_Disk_Time",
"%_Disk_Write_Time",
"Avg_Disk_ms/Read",
"Disk_Bytes/Sec",
"Disk_Queue_Length",
"Disk_Read_Bytes/Sec",
"Disk_Reads/Sec",
"Disk_Transfers/Sec",
"Disk_Writes/Sec",
"Disk_Write_Bytes/Sec",
"Free_Megabytes",
"Total_Size",
"%_Used",
"%_Free"
from ITMUSER."NT_Logical_Disk";