Information repository tables

All data in the information repository is stored in database tables.

  • Common tables: Store basic information about the objects being tracked.
  • Standard tables: Store data collected by each individual collector.

Common elements table

A special table named COMMON_ELEMENTS is the root table in the schema. This table contains the most vital data about each piece of data including which BMC Database Automation entity is being described, when the collection took place, and which Agent ran the collector.

Refer to the following table for a list of the key columns in the COMMON_ELEMENTS table.

Other common tables

The remaining common tables store the most basic data about the entities being tracked. There is one table in the schema for each entity that we are tracking.

The list of entities is as follows:

  • Host (COMMON_HOST)
  • MSSQL Database (COMMON_MSSQL_DB)
  • MSSQL Instance (COMMON_MSSQL_INSTANCE)
  • Oracle Cluster Node (COMMON_ORA_CLUSTER_NODE)
  • Oracle Database Instance (COMMON_ORA_DB_INSTANCE)
  • Oracle Home (COMMON_ORA_HOME)

Each of these entity types can have one or more collectors, each with a unique goal. No matter which collector runs, the same data is put into the common tables.

Standard tables

Data specific to each collector is placed into the standard tables and can be queried by joining the common tables against the relevant standard tables. The COLLECTION_ID column is generally the best column to use to correlate data in various tables because it exists in all tables that contain collection data in the information repository.

Some example standard tables are as follows:

  • Host (HOST)
  • MSSQL Instance Parameter (MSSQL_INSTANCE_PARAMETER)
  • Oracle Cluster OCR Devices (ORA_CLUSTER_NODE_OCR)
  • Oracle Home Patches (ORA_HOME_PATCH)

Standard tables can be divided into two groups:

  • Tables that store one row per collected item: The columns in these tables represent the data that is being collected.
  • Tables that store name/value pairs: Rather than creating columns in these tables to store specific data, there are generic columns to store the name and value for each data point collected. The number of name/value pairs for each collected entity determines the number of rows sharing a COLLECTION_ID.

All point-in-time queries are all satisfied by the tables.

Frequently appearing columns

The BMC Database Automation information repository is designed to be easy to use with minimum time invested. A list of columns that are used often throughout the schema is available for your convenience.

The frequently used columns are as follows:

  • CHANGE_SINCE_PREVIOUS: This column represents whether this data represents no change, an addition, removal, or a change in value.
    • No Change - 0
    • Value Modified - 1
    • Data Added - 2
    • Data Removed - 3
  • COLLECTION_ID: This column contains a unique identifier that defines a specific data collection that was gathered for a particular BMC Database Automation entity. This column is generally the best column to use for JOINs.
  • PREVIOUS_COLLECTION_ID: When a previous collection is found for the same BMC Database Automation entity, the COLLECTION_ID of that immediately previous collection data is stored. If no previous collection is found, this column is set to NULL.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*