Creating data queries

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

To create a data query

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?
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')
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:

Creating data queries

To create a data query

Comments