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:
Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*