Creating data queries

This topic provides general guidelines for querying data in the information repository.

To create a data query

1. Determine what information is to be gathered from the information repository.
   • Do you want information for a point-in-time or a range of time?
   • What time or time frame should be queried?
   • In which BMC Database Automation objects are you interested?
2. Use the common tables to identify which data satisfies your request for information. All of these snippets belong in the WHERE clause of a query.
    For example, to get the most recent data at a specific point-in-time:
   collection_time = (
   select
   /*+ INDEX_DESC(e2 IX_COMMON_ELEMENTS_TIME) */ collection_time
   from
   common_ora_db_instance p2
   join common_elements e2
   on p2.collection_id = e2.collection_id
   where
   e2.clarity_object_id = ENTITY_ID{}
   and
   e2.clarity_manager_id = '_MGR_ID_'
   and
   e2.collection_time <= to_date('_COLLECTION_TIME_', 'YYYY-MM-DD HH24:MI:SS')
   and
   rownum = 1
   )
   
   For example, to get all of the data between two points in time (including data that is stored in name/value pairs):
   h1.collection_time between
   to_date('_COLLECTION_START_TIME_', 'YYYY-MM-DD HH24:MI:SS')
   and
   to_date('_COLLECTION_END_TIME_', 'YYYY-MM-DD HH24:MI:SS')
3. To create a result set containing the required data for the relevant context, join the common tables (providing context) to the standard tables (providing the relevant data).
    For example, to join a data table to the COMMON_ELEMENTS table:
   select
   *
   from
   ora_db_init_parameter p1
   join common_elements e1
   on p1.collection_id = e1.collection_id
   where
   e1.clarity_object_id = ENTITY_ID{}
   and
   e1.clarity_manager_id = '_MGR_ID_'

Additional information on reporting queries, including information on tables and example queries can be found in the following topics: