This chapter lists each Oracle Communications Billing and Revenue Management (BRM) storable class and the SQL tables to which it is mapped.
You use SQL directly with the database to generate reports. If you are an experienced system administrator, you can add indexes to improve performance. The default indexes are specified in the create_indexes.source file in the BRM_Home/sys/dm_oracle/data/sql directory.
Caution:
Always use the BRM API to manipulate data. Changing data in the database without using the API can corrupt the data.
Do not use SQL commands to change data in the database. Always use the API.
Do not update or delete the default indexes.
A complete list of SQL tables and fields and their storable-class equivalents is in the file BRM_Home/sys/dd/data/dd_objects.source. Indexes are listed in the create_indexes.source file in the BRM_Home/sys/dm_oracle/data/sql directory.
For storable class-to-SQL mapping information, refer to the storable class descriptions. Each description includes the SQL mapping for every field in the class. See "Storable Class Definitions".
When looking up SQL mapping indexes, keep in mind the following exceptions.
The PIN_FLD_INTERNAL_NOTES field in the /account storable class is implemented by two fields in two separate tables: the field size is stored in the /account storable class as internal_notes_size, and the field value is stored in the table account_internal_notes_buf.
The PIN_FLD_BUFFER field in the /data storable class is implemented by two fields in two separate tables: the field size is stored in the /data storable class as buffer_size, and the field value (the buffer) is actually stored in the table data_buffer_buf.
SQL recid fields correspond to an element ID field.
All /event storable subclasses inherit a set of fields from the /event super class, but they are implemented using different tables. The following /event storable subclasses are implemented using only the event_t table:
/event/activity
/event/activity/admin
/event/billing/cycle/arrears
/event/billing/cycle/fold
/event/billing/cycle/forward
/event/billing/debit
/event/session/pcm_client
All other /event storable subclasses implemented using the event_t table plus one or more additional tables.
All /service/* storable classes inherit a set of fields from the /service storable class. In addition, /service/email and /service/pcm_client are implemented using only the service_t table, and /service/ip and /service/admin_client each require an additional table.
The /data storable class is a general data class that can be used to store any type of data, including blobs. Unless you have specifically created /data storable classes, you won't need to access them with SQL since they are generally not used by the system.
If POIDs (storable object IDs) are not being used as the join criteria, joins can be done with normal field comparisons.
If storable object IDs are being used to join tables (for example, to get information about an account and its current balances), simplified join criteria can be used. All tables have either POIDs, which are concatenations of five fields, or they have two-field storable object IDs, obj_id0 and obj_id1. The poid_id0 and poid_id1 fields in the main tables (like /account, /event, and /service) are the same as the obj_id0 and obj_id1 fields in their related tables (that are used to implement arrays and substructures), respectively. For example:
poid_id0 in account_t = obj_id0 in account_balances_t poid_id1 in account_t = obj_id1 in account_balances_t
The database number (poid_db) should be the same for all storable objects in the same database and you won't need to join on it. In most cases, just joining on the poid_id0 and poid_id1 fields are sufficient. The only case where this is not enough is in the case of array elements such as /event balance impacts where an SQL rec_id (or storable object element ID) is also required.
The poid_rev field is incremented each time a storable object is modified. This field should not be used or changed. It is not necessary as a join criteria.
rec_id fields are used to match on particular array elements.
The following storable objects/tables listed in Table 5-1 are found in home/sys/data/sql/dd_objects.source file are reserved for BRM use and should not be used by customers:
It is possible to obtain a list of SQL statements which correspond to an operation or sequence of events. See "Increasing the Level of Reporting for a DM" in BRM System Administrator's Guide for more details.