7 Maintaining Databases

This chapter describes basic maintenance for the database tables underlying Oracle Communications Marketing and Advertising and Oracle Communications Services Gatekeeper.

Maintenance for Marketing and Advertising Tables

All of the Marketing and Advertising database tables are cleaned automatically based on the data retention values set in the Data Retention tab of the OCMA extension in the WebLogic Administration console.

You can separately configure retention time for statistics tables and for deleted entities, such as old campaigns and ad spaces.

For more information, see Data Retention.

Maintenance for Services Gatekeeper Tables

This section describes manual cleanup for the charging data record (CDR), alarms, and statistics tables and how to manage storage data expiration for all Services Gatekeeper database tables.

Manual Database Cleanup

Most of the Services Gatekeeper tables are cleaned automatically, but the following three tables in the database underlying Services Gatekeeper must be periodically cleaned manually to prevent them from growing too large:

SLEE_ALARM
SLEE_CHARGING
SLEE_STATISTICS_DATA

Use the instructions provided for your database to delete rows from these tables.

Oracle recommends cleaning the SLEE_STATISTICS_DATA table once a month and the SLEE_ALARM table every two months.

The interval for cleaning the SLEE_CHARGING table depends on the amount of charging traffic, so it is difficult to specify a recommended interval. There is usually one row in this table per plug-in transaction. A row can contain up to 300 bytes of data.

Typically, operators export charging data from the SLEE_CHARGING table to a file either by using a scheduled script or by integrating with a billing system such as Oracle Communications Billing and Revenue Management.

Charging data is contained in CDRs, which are a type of event data record (EDR). Typically CDRs are integrated by means of EDR listeners. You can also listen for alarms by setting up an SNMP/EDR listener. If this is done, it is possible to disable storage of CDRs and alarms in the database by setting the EDR Service's StoreAlarms or Store CDRs attributes to false. Oracle recommends storing alarms for some period of time, however, for trouble-shooting purposes. For general information about EDRs, CDRs, and alarms, as well as about the StoreCDRs and StoreAlarms attributes, see "Managing and Configuring EDRs, CDRs and Alarms" in Oracle Communications Services Gatekeeper System Administrator's Guide at:

http://download.oracle.com/docs/cd/E16625_01/doc.50/e16623/adm_edr.htm#i1124350

For more detailed information about creating an EDR listener see “Creating an EDR Listener and Generating SNMP MIBs” in the Oracle Communications Services Gatekeeper Platform Development Studio Developer's Guide at:

http://download.oracle.com/docs/cd/E16625_01/doc.50/e16619/pds_edrlistener.htm#i1114303

Storage Data Expiration

To prevent the databases underlying the Storage service from growing too large, data in the Storage service can be set to expire, after which time the data is deleted. One of the visible consequences of this is that the status of delivery receipt can be unsuccessful if an old message is deleted from storage because it aged beyond the configured expiration period.

You can configure both the data expiration period for a communication service and the interval that Services Gatekeeper checks whether data in the stores has expired.

Configuring the Expiration Period

To set the data expiration period:

Locate the store schema JAR file for the store that you want to configure.

These jar files are in under the Domain_Home/config/store_schema directory.

For example, the JAR file for the common smpp store is Domain_Home/config/store_schema/com.bea.wlcp.wlng.sms.common.store.
Extract the files from the JAR file for the store that you want to configure.
Open the wlng-cachestore-config-extensions.xml file.
In the extensions file, create or edit the <expiry> element, setting its expiry_age value. The value is in seconds.
Save the file.

If expiry_age is not set, the data never expires.

The following example shows the configuration of the MO message store setting the expiration period to 604800 seconds (one week).

<!-- MO message store (common) start -->
  <db_table name="pl_sms_mo_sms">
    <key_column name="msg_id" data_type="VARCHAR(30)"/> 
    <bucket_column name="smsdata" data_type="BLOB"/>
 
    <value_column name="notif_correlator" data_type="VARCHAR(30)">
      <methods>
        <get_method name="getNotificationInfoCorrelator"/>
        <set_method name="setNotificationInfoCorrelator"/>
      </methods>
    </value_column>
    <expiry expiry_age="604800"/> <!-- 1 week  -->
  </db_table>

Configuring the Checking Interval

At the system level, Services Gatekeeper checks whether Storage data has expired at a set interval defined by the com.bea.wlcp.wlng.storage.expiry_period variable. The default for the expiry_period is every 1800000 milliseconds (thirty minutes).

You can configure this value on server startup by passing it as a Java option when you restart the server. The option is:

JAVA_OPTIONS="${JAVA_OPTIONS} -Dcom.bea.wlcp.wlng.storage.expiry_period=expiry_period_value"

For example, the following flag configures Services Gatekeeper to check for expired data once an hour:

JAVA_OPTIONS="${JAVA_OPTIONS} -Dcom.bea.wlcp.wlng.storage.expiry_period=3600000"