Building block views

You can use the building block views to write queries based on the Tivoli® Common Data Model (CDM) point of view. These views are useful if you are familiar with the CDM; they do not require any knowledge of where configuration items are stored in the TADDM database base tables.

To see detailed documentation for the building block views, go to the $COLLATION_HOME/etc/views directory and open one of the following files:

For DB2® databases: create_building_block_views_db2.sql
For Oracle databases: create_building_block_views_oracle.sql

The comments in these files describe the Common Data Model object types and the corresponding database views, providing mappings between the Common Data Model and the database schema:

from CDM object type to building block database view name
from attribute to database view column name
from relationship to JOIN syntax

The name of each building block view is in the following form:

BB_%_V

In each view name, the % is the name of the object type. Each object type is mapped to a building block view, yielding more than 1,000 available building block database views representing object types.

For example, the CDM defines the sys.windows.WindowsComputerSystem object type, which is stored in the COMPSYS base table in the TADDM database. This table also includes many other object types that extend the sys.ComputerSystem object type (for example, sys.LinuxUnitaryComputerSystem).

To query sys.windows.WindowsComputerSystem configuration items from the database using a building block view, you would query the BB_WINDOWSCOMPUTERSYSTEM20_V database view.

In addition to the views representing object types, more than 800 special views support "many-to-many" relationships between object types. Each of these mapping-table building block views has a name in the following form:

BB_%J

For more information about these special views, see JOIN definitions.

View definitions

For each building block view, the comments include a section describing the view and the corresponding CDM objects. For example, the view definition section of the BB_WINDOWSCOMPUTERSYSTEM20_V building block view is as follows:

-- ######## model.topology.sys.windows.WindowsComputerSystem ########
--
-- View...... BB_WINDOWSCOMPUTERSYSTEM20_V
-- Class......................... model.topology.sys.windows.WindowsComputerSystem
-- Super classes................. model.topology.sys.ComputerSystem
-- model.topology.core.ManagedElement
-- model.ModelObject
-- model.topology.process.itil.ConfigurationItem

This example shows that the BB_WINDOWSCOMPUTERSYSTEM20_V view corresponds to the model.topology.sys.WindowsComputerSystem object type, and also lists several superclasses from which this type also inherits attributes and relationship definitions.

Column definitions for attributes

For each column corresponding to a CDM attribute, the comments include a section describing the column and the corresponding attribute. For example, the column definition section for the CPUSPEED_C column of the BB_WINDOWSCOMPUTERSYSTEM20_V view is as follows:

-- Column.... CPUSPEED_C
-- Attribute..................... CPUSpeed
-- Java Type..................... long, primitive
-- Declared By................... model.topology.sys.ComputerSystem

This example shows that the CPUSPEED_C column corresponds to the CPUSpeed attribute defined by the model.topology.sys.ComputerSystem object type. This attribute is inherited by the model.topology.sys.WindowsComputerSystem type. It also lists the Java™ type used to represent the value of the attribute.

Note: The comments do not list the database type used to store the attribute. You can determine the database type by using an SQL describe command: Describe command returns multiple columns including the names of database views, which you can use to query the data displayed in the Details Panel tab.

db2 describe table BB_WindowsComputerSystem20_V

Column definitions for [0..1] relationships

For each column representing a "zero or one" CDM relationship to another configuration item, the comments include a section describing the column used for performing the SQL JOIN operation to the configuration item on the other side of the relationship. For example, the column definition section for the PK_OSRUNNING_C column of the BB_WINDOWSCOMPUTERSYSTEM20_V view is as follows:

-- Column.... PK__OSRUNNING_C
-- Attribute..................... OSRunning
-- Java Type..................... model.topology.sys.OperatingSystem, notContained
-- Declared By................... model.topology.sys.ComputerSystem

This example shows that the PK__OSRUNNING_C column is used to perform the JOIN operation to the OperatingSystem table, which represents the OSRunning relationship defined by the model.topology.sys.ComputerSystem object type. It also shows that the Java type of the attribute value is, in this case, another CDM type (model.topology.sys.OperatingSystem).

Note: All columns that represent relationships to other configuration items (and are therefore not primitive types) have names that start with PK__. From a relational database point of view, the value of such a column is the GUID of the configuration item on the other side of the relationship.

The time stamp column in TADDM 7.3.0.3, and later

In TADDM 7.3.0.3, or later, the building block views contain additional time stamp column. For example, the view definition section of the LASTSTOREDTIME_C column of the BB_WINDOWSCOMPUTERSYSTEM20_V view contains additional (timestamp) string:

-- Column.... LASTSTOREDTIME_C
--            Attribute..................... lastStoredTime
--            Java Type..................... long, primitive (timestamp)
--            Declared By................... model.ModelObject

Each time stamp attribute contains the following two columns:

LASTSTOREDTIME_C, which is set to long type, for example 1445417251307.
LASTSTOREDTIME_T, which contains human readable time stamp, for example Oct 21, 2015 10:47:31 AM.

Like in case of the original naming convention of the time stamp column, the added column always has the same suffix, which in this case is _T.

JOIN definitions

For both "zero or one" and "many-to-many" CDM relationship, the comments provide an example SQL query showing how to accomplish the SQL JOIN operation. For example, the JOIN definition section for the OSRunning relationship of the BB_WINDOWSCOMPUTERSYSTEM20_V view is as follows:

-- Join......
-- Attribute..................... OSRunning
-- Java Type..................... model.topology.sys.OperatingSystem, notContained
-- Declared By................... model.topology.sys.ComputerSystem
-- Test Join..................... SELECT COUNT(1) FROM
--                                BB_WINDOWSCOMPUTERSYSTEM20_V T1,
--                                BB_OPERATINGSYSTEM62_V T2
--                                WHERE T1.PK__OSRUNNING_C = T2.PK_C

Some relationships between configuration items are many-to-many relationships; for example, a sys.windows.WindowsComputerSystem configuration item might have a relationship to multiple sys.FileSystem configuration items, represented by a contains relationship using the fileSystems attribute of sys.windows.WindowsComputerSystem:

-- Join......
-- Attribute..................... fileSystems
-- Java Type..................... Array of model.topology.sys.FileSystem, array
-- Declared By................... model.topology.sys.ComputerSystem
-- Test Join..................... SELECT COUNT(1) FROM
--                                BB_WINDOWSCOMPUTERSYSTEM20_V T1,
--                                BB_COMPUTERSYSTTEMS_88841D4BJ T2,
--                                BB_FILESYSTEM71_V T3
--                                WHERE T1.PK_C = T2.PK__JDOID_C
--                                AND T3.PK_C = T2.PK__FILESYSTEMS_C

Many-to-many relationships are stored in the intermediary mapping table (in this example, the BB_COMPUTERSYSTTEMS_88841D4BJ mapping table).