C H A P T E R 3 - Database Maintenance

C H A P T E R 3

Database Maintenance

The Content Delivery Server database contains a record of all transactions performed by the Content Delivery Server. Over time, the tables containing these transactions can become large and could degrade performance. Periodically remove inactive items from tables in the database.

This section provides information on managing the amount of data in your database. You can delete data that is no longer needed or archive selected data for historical purposes.

3.1 Table Maintenance

Monitor the Content Delivery Server database tables identified in this section and remove items as needed. In addition, perform standard maintenance procedures on the database as recommended by Oracle.

3.1.1 Transaction Tables

The following tables increase in size due to the number of transactions that occur in the Content Delivery Server. You can archive some of the data in these tables to keep the tables a manageable size.

SUBSCRIBER_PURCHASE (vending schema) - Contains all of the purchases that a subscriber makes from a specific Vending Manager. This table grows relative to the number of subscribers and the number of purchases that they have attempted. Maintain this table to ensure the integrity of a subscriber's license to use the content.

Data can be purged from this table when subscribers or content that they have purchased are no longer active.

FULFILLMENT_REQUEST (vending schema) - Contains the download requests that are made on behalf of the Vending Manager with which it is deployed. This table is required for authorization of any subsequent downloads that a subscriber attempts. This table grows at the same rate as the SUBSCRIBER_PURCHASE table.

Data can be purged from this table when subscribers or content that they have purchased are no longer active.

CDS_EVENT (vending or catalog schema) - Contains all of the events that the Content Delivery Server has issued. Because there are many event types, this table grows quickly. The events in this table are used for historical reporting. This table exists in each Vending Manager database created. This table exists in the Catalog Manager database if the Catalog Manager is deployed separately from all Vending Managers.

Data can be purged from this table if the Postpaid Service is used and the data has been processed, or if the reporting service is enabled and up to date.

CAMPAIGN_PROCESSING (vending schema) - Contains all of the campaign messages that are sent to each subscriber. This table grows by a factor of the number of campaigns that were executed and the number of subscribers that were sent notifications. This data is needed as long as a campaign is active and a subscriber can purchase content from the campaign.

Data can be purged from this table after a campaign expires.

SUBSCRIBER_GIFT (vending schema) - Stores gifts that have been given by subscribers.

Data can be purged from this table after a subscriber is deactivated.

3.1.2 Entity Tables

The following tables maintain data entities, such as subscribers and downloadable content. Unused records can be eliminated from these tables and their related tables when the entities become inactive.

SUBSCRIBER (vending schema) - Contains all of the subscriber accounts for subscribers that have used the Content Delivery Server. If the Content Delivery Server is integrated with an external subscriber database, a subset of that information is kept in this table for reference to other tables, such as SUBSCRIBER_PURCHASE. When a subscriber becomes inactive, related data can be removed from the following tables:

CDS_SUBSCRIBER - Names, addresses, and other profile information

SUBSCRIBER_PLAN_MAP - Subscriber plans that are assigned to the subscriber

SUBSCRIBER_PURCHASE - All of the purchases that the subscriber made

SUBSCRIBER_WISHLIST - Items that the subscriber as put in the wish list

SUBSCRIBER_PREFERENCE - Display preferences set by the subscriber

RESOURCE_CLASS (catalog schema) - Contains all of the downloadable content submitted or registered with the Content Delivery Server. When an item becomes inactive, related data can be removed from the following tables:

RESOURCE_CLASS_LOCALE - Locale-specific names, descriptions

RESOURCE_CONTENT_DATA - Binaries for the content

RESOURCE_INSTANCE - Device-dependent editions

RESOURCE_SCREENSHOT - Screen shots for the content

INSTANCE_CAPABILITY_MAP - Cpability requirements of the content

MODEL_CLASS_INSTANCE_MAP - Mapping of the content to a device in the Catalog Manager

CAT_PRICING_MODEL_MAP - Pricing models for the content

CATEGORY_RESOURCE_MAP - Category in which the content resides

CS_PRICING_OPTION_MAP - Mapping between the pricing option and the content.

CATEGORY_ITEM (vending schema) - Contains the content for a Vending Manager. If the content is not available from the catalog, it can be removed for each Vending Manager and related data can be removed from the following tables:

CATEGORY_ITEM_MAP - Category in which the content resides

EXTERNAL_CONTENT_MAP - File location of the cached content

VEN_PRICING_MODEL_MAP - Pricing models used by the content

HANDSET_CONTENT_MAP - Mapping of the content to a device in the Vending Manager

VS_PRICING_OPTION_MAP - Mapping between the pricing option and the content.

INSTANCE_CAPABILITY_MAP - Capability requirements of the content

RESOURCE_CONTENT_DATA - Binaries for the content

3.2 Archiving Data

Tables in the Content Delivery Server database that store records that provide information on system activity can grow quickly. Tables with large amounts of data can adversely affect performance. Use the Oracle export tool exp to back up data, then remove the data from the Content Delivery Server database. See the documentation for your Oracle database for information on the export tool.

To reduce the size of the database, archive the data in the following tables on a regular basis:

CDS_EVENT Table - Records all events that are issued by the Content Delivery Server. The Catalog Manager database and each Vending Manager database has a CDS_EVENT table. The data in the CDS_EVENT table is used for the reports generated by the Vending Manager. Only the data that remains in the table appears in the report in the Vending Manager administration console. Therefore, the amount of data that you leave in the table depends on how much past data you want the Vending Manager administrator to see.

Periodically remove the historical data from the table in each Catalog Manager and Vending Manager database. The following code shows sample commands that archive data up to the date specified.

# exp USERID=user-ID/password OWNER=prefix_component_owner FILE=file-name \

  QUERY='WHERE cds_event_date < \'date\'' TABLES='CDS_EVENT'

# sqlplus system/system

SQL> DELETE FROM prefix_component_owner.cds_event where cds_event_date < 'date';

SQL> commit;

SQL> exit

user-ID is a user ID with system privileges for the Oracle database. password is the password associated with the user ID that you are using. prefix is the prefix that you specified in the database configuration file when you created the database that you are archiving. component is ps for the Catalog Manager database and vs for the Vending Manager database. file-name is the name of the file to which data is archived. date is the date up to which data is archived in a date format recognized by the Oracle database.

REPORT_DOWNLOAD Table -Records event information for every purchase and refund event. The REPORT_DOWNLOAD table exists in each Vending Manager database. This table is populated by the Postpaid Service. The data in this table is intended for use by external systems such as Crystal Reports. The amount of data that you leave in the table depends on the needs of the external system that references the data.

Periodically remove the historical data from the table in each Vending Manager database. The following code shows sample commands that archive data up to the date specified.

# exp USERID=user-ID/password OWNER=prefix_vs_owner FILE=file-name \

  QUERY='WHERE TRANSACTION_DATE < \'date\'' TABLES='REPORT_DOWNLOAD'

# sqlplus system/system

SQL> DELETE FROM prefix_es_owner.report_download

  2 where transaction_date < 'date';

SQL> commit;

SQL> exit

user-ID is a user ID with system privileges for the Oracle database. password is the password associated with the user ID that you are using. prefix is the prefix that you specified in the database configuration file when you created the database that you are archiving. file-name is the name of the file to which data is archived. date is the date up to which data is archived in a date format recognized by the Oracle database.

MESSAGE_SERVICE Table - Records the multimedia messages that are sent to subscribers. The MESSAGE_SERVICE table exists in each Vending Manager database. The status of the message is stored in the EXTERNAL_MESSAGE_KEY column of this table. When a response is received from the Multimedia Messaging Service (MMS) gateway, the status is updated. The values used for the status are dependent on the MMS gateway that you are using.

Periodically remove the messages that were successfully received from the table in each Vending Manager database using the following commands.

# exp USERID=user-ID/password OWNER=prefix_vs_owner FILE=file-name \

  QUERY='EXTERNAL_MESSAGE_KEY = \'status\'' TABLES='MMS_MESSAGE'

# sqlplus system/system

SQL> DELETE FROM cds_vs_owner.mms_message

 2 where external_message_key = 'status';

SQL> commit;

SQL> exit

user-ID is a user ID with system privileges for the Oracle database. password is the password associated with the user ID that you are using. prefix is the prefix that you specified in the database configuration file when you created the database that you are archiving. file-name is the name of the file to which data is archived. status is the value used by your MMS gateway to indicate that a message was successfully received.