26 Partitioning Tables

This chapter explains how to organize your Oracle Communications Billing and Revenue Management (BRM) database by using partitioned tables.

Before partitioning any tables, you should be familiar with the following:

About Partitioning

Partitioning splits tables and indexes into smaller, more manageable units. When you no longer need the data stored in a partition, you can purge it from your database by deleting the partition (see "About Purging Data").

To partition tables, you need Oracle Partitioning. You license this third-party software from Oracle.

You can enable partitioning at the following times:

If you enable partitioning during installation, the following storable classes and their subclasses are automatically enabled for partitioning:

  • event

  • bill

  • invoice

  • item

  • journal

  • newsfeed

  • sepa

  • user activity

To enable partitioning for a different set of storable classes, see the following:

When you use partitioning, objects are stored in a partition according to the following criteria:

  • Nonpurgeable event and item objects are stored in partition_historic. See "About Nonpurgeable Events and Items".

  • All other objects are stored in date-based, purgeable partitions according to the date they were created. For example, if a partition includes a date range from January 15 to February 15, all objects that have a creation date in that range are included.

    You create separate partitions for real-time events and delayed events. You can purge real-time events, delayed events, or both. See "About Partitions for Delayed Events".

    Note:

    Delayed partitioning is not supported for non-event storable classes.

  • If no date-based partition exists, objects are stored in partition_last. See "About Objects Stored in partition_last and partition_last_pin".

For partitioned event tables, you can use the default purgeable partitions that pin_setup creates, or you can create custom purgeable partitions. For non-event tables, you must create custom purgeable partitions.

See "About Partitioning Schemes" and "Customizing Partition Limitations".

Note:

A partition applies to all the tables for a base storable class and its subclasses. When you remove a partition, it removes partitions in all the partitioned tables for that base storable class and its subclasses.

To create partitions and purge data, see "About Managing Partitions".

Note:

Rule-based optimization is not available for partitioned tables and indexes. See "Using Rule-Based Optimization versus Cost-Based Optimization" in BRM Installation Guide.

Overview of Enabling Partitioning during Installation

Figure 26-1 provides an overview of how to set up and use partitioning when you install BRM. All steps before you customize or add partitions must be done during installation.

For information about enabling partitioning after installation, see "About Converting Nonpartitioned Classes to Partitioned Classes".

Figure 26-1 Overview of Enabling Partitioning during Installation

Description of Figure 26-1 follows
Description of ''Figure 26-1 Overview of Enabling Partitioning during Installation''

About Partitioning Schemes

Partitions are based on dates; your partitioning scheme determines how the date-based time periods are divided. Partition time periods can be daily, weekly, or monthly. You can specify multiples of each time period; for example, you can create partitions based on five days or on three weeks.

Determining the appropriate partition size depends on many factors. Generally, partitions should not exceed 120,000,000 entries for standard BRM configurations. For more information, see the Oracle database documentation, or contact Oracle support.

You can set up partitioning schemes based on different time periods in advance; for example, you can specify three monthly partitions followed by three weekly partitions. However, every table of the same base storable class must have the same partitioning scheme; you cannot create different partitions for different tables based on the same base storable class.

Important:

When you add partitions, you define a start date for the first partition. The start date cannot be earlier than the day after tomorrow. For example, if the current date is January 1, the earliest start date for the new partition is January 3.

If you use pipeline batch rating, Rated Event (RE) Loader loads delayed events into the BRM database. You can enable partitioning for delayed events and create partitions for the delayed events. See "About Partitions for Delayed Events".

Note:

When you partition an event table, the associated index is identically partitioned by the Oracle database.

About Nonpurgeable Events and Items

By default, certain storable classes of events cannot be purged from the database. In addition, you can make other storable classes of events and items nonpurgeable.

All event and item objects belonging to nonpurgeable storable classes are automatically stored in partition_historic. You cannot remove this partition.

For the default list of events stored in partition_historic, see "Event and Item Objects Stored in partition_historic".

To modify the list of events stored in partition_historic, see "Customizing the List of Events and Items Stored in partition_historic".

Associating Items with Nonpurgeable Events

In BRM, events and items are mapped to one another in item configuration objects (/config/item_tags and /config/item_types). If an event in an item configuration is nonpurgeable, the items mapped to it should also be nonpurgeable because the event is incomplete without those items.

To ensure that nonpurgeable events are not associated with purgeable items, do one of the following:

Synchronizing the Purgeability of Events and Items in New Installations

If you enabled partitioning for item storable classes when you installed BRM, before your system begins generating objects, synchronize the purgeable/nonpurgeable status of associated event and item storable classes.

To synchronize the purgeability of events and items in new BRM installations:

  1. Make a list of the event storable classes that are nonpurgeable by default.

    To determine whether an event storable class is nonpurgeable, consult its specification. See the discussion on retrieving storable class specifications in BRM Developer's Guide.

  2. Make a list of the item storable classes associated with the nonpurgeable events listed in the previous step.

    To identify such classes, consult the following files in BRM_home/sys/data/pricing/example:

    • config_item_tags.xml

    • config_item_types.xml

    For information about how events and items are associated in those files, see "Creating Custom Bill Items" in BRM Configuring and Running Billing.

  3. Verify that all item types associated with nonpurgeable events are also nonpurgeable.

  4. If you find purgeable item types associated with nonpurgeable events, make such item types nonpurgeable.

    For more information, see the Storable Class Editor Help.

  5. If you create any custom item subclasses that will be associated with nonpurgeable events, select the subclass's Non-Purgable check box.

    For more information about storable class properties, see the Storable Class Editor Help.

Handling Items Associated with Nonpurgeable and Purgeable Events in Existing Systems

If you convert a nonpartitioned item storable class to a partitioned storable class in an existing BRM system, the system is likely to contain item objects associated with both purgeable and nonpurgeable events. Problems can occur if such items are purged. To minimize such problems, do the following before converting the nonpartitioned item storable class to a partitioned storable class:

To handle items associated with both nonpurgeable and purgeable events in existing BRM systems:

  1. Make a list of the event storable classes that are nonpurgeable by default.

    To determine whether an event storable class is nonpurgeable, consult its specification. See the discussion on retrieving storable class specifications in BRM Developer's Guide.

  2. Make a list of the item storable classes associated with the nonpurgeable events listed in the previous step.

    To identify such classes, consult the following files in BRM_home/sys/data/pricing/example:

    • config_item_tags.xml

    • config_item_types.xml

    For information about how events and items are associated in those files, see "Creating Custom Bill Items" in BRM Configuring and Running Billing.

  3. Check whether any of the item storable classes associated with nonpurgeable events are also associated with purgeable events.

    To identify such classes, consult the configuration files listed in the previous step.

  4. For item storable classes that are associated with both nonpurgeable and purgeable events, consider doing one or more of the following:

    • Split the item storable class into two classes, one for nonpurgeable events and one for purgeable events. Update the item configuration files accordingly.

      Set the Non-Purgable property correctly for each storable class. (See the Storable Class Editor Help.)

      This may add a line to your invoices. It may also affect custom code.

    • Make the nonpurgeable events associated with such items purgeable.

      Ensure that the item storable class is also purgeable.

    • Make the item storable class nonpurgeable.

      The item objects will not be purged even when associated with purgeable events, so the storage requirements for this item storable class will continue to grow and may affect performance.

    • Make the item storable class purgeable.

      The links between purged item objects and nonpurgeable events will be broken. Future operations such as event adjustments or cancellations may result in errors.

  5. Convert the nonpartitioned item storable class to a partitioned storable class.

    See "Converting Nonpartitioned Classes to Partitioned Classes".

About Objects Stored in partition_last and partition_last_pin

The partition_last partition is a spillover partition that holds objects dated after the time period covered by the regular partitions if no additional purgeable partitions are available for such objects. You cannot remove this partition for real-time event tables or non-event tables.

If your partitions are enabled for delayed events, partition_last is used for delayed events, and partition_last_pin is used for real-time events.

Caution:

Spillover partitions are not intended to store objects you want to purge or preserve; they are just temporary containers. To keep objects out of spillover partitions, make sure your tables contain partitions to hold new objects. See "Adding Partitions".

To purge objects stored in spillover partitions, stop all delayed-event loading, and then add partitions by using the partition_utils utility with the -f parameter (see "Adding Partitions"). Adding partitions in this way moves the data from the spillover partitions to the added partitions, but it takes much longer than adding partitions normally.

About the Default Partitioning Scheme

If you enable partitioning when you install BRM and choose to create the default partitions, your real-time events are stored in the following partitions:

  • partition_historic (event and item tables only)

  • partition_last

  • 12 monthly partitions

  • +1 partition for objects created on the start date

Each monthly partition stores objects created on the date of the month you install BRM through the previous date of the next month. For example, if you install BRM on January 15, the partitions cover January 15 through February 14, February 15 through March 14, and so on.

For information about partition names, see "Partition Naming Convention".

If You Do Not Create Default Partitions

If you enable partitioning when you install BRM but choose not to create the default partitions, your real-time object tables are divided into fewer partitions: partition_historic (stores all nonpurgeable events and items) and partition_last (stores all purgeable objects). This is sufficient for a test or development system in which purging by partition is not required. For a production system, however, you must add more purgeable partitions. See "Adding Partitions".

Conversion Partitioning Scheme

If you enable partitioning by converting nonpartitioned storable classes to partitioned storable classes after you install BRM, your partitioned real-time tables are divided into three partitions:

  • partition_migrate: Holds all event objects created before the conversion. The partition_utils utility cannot purge objects in this partition. To purge them, you must develop your own tools based on sound Oracle database management principles.

  • partition_historic: Holds nonpurgeable events and items created after the conversion. Nonpurgeable events and items should not be purged from the database. See "Event and Item Objects Stored in partition_historic".

  • partition_last: A spillover partition that is not intended to store objects you want to purge or preserve. If you do not add purgeable partitions to your tables before BRM resumes generating objects, purgeable objects created after the conversion are stored in this partition. See "About Objects Stored in partition_last and partition_last_pin".

About Partitions for Delayed Events

To create partitions for delayed events, you use the partition_utils utility to enable delayed-event partitions for specified event storable classes. See "Syntax for Enabling Delayed Partitions".

When you enable delayed-event partitions, partition_utils automatically runs the update operation. This adds partitions for the delayed events that are aligned with any real-time event partitions already defined in the EVENT_T table. Therefore, you do not need to explicitly add partitions for delayed events after enabling them.

To use a partitioning scheme for delayed events that differs from that already defined in the EVENT_T table, you must explicitly add delayed-event partitions. You add delayed-event partitions the same way you add real-time partitions. See "Syntax for Adding Partitions".

When partitions are added for delayed events, the event tables are divided into the default partitions plus the following partitions:

  • Partitions for delayed events: The time periods covered by these partitions do not need to match the periods covered by the partitions for real-time event objects. For example, you can use daily partitions for real-time events and weekly partitions for delayed events.

  • partition_last_pin: When delayed-event partitions are added to tables, this spillover partition is added for real-time event objects; partition_last becomes the spillover partition for delayed events for which no other partition is available. See "About Objects Stored in partition_last and partition_last_pin".

Delayed-event storage is based on the date that the delayed events are loaded into the BRM database.

If you enable delayed-event partitions for an event storable class but then decide you do not need those partitions, you can use sqlplus to remove the delayed-event partitions and then to remove the partition_last_pin partition. See "Disabling Delayed-Event Partitioning".

Overview of Partitioning Schemes

The following tables show the possible partitioning schemes.

If you do not enable partitions when installing BRM, every object table has no partitions as shown in Table 26-1.

Table 26-1 No Partitions

Table

Object table

An object table designated for partitioning is partitioned as shown in Table 26-2 if you enable partitions but do not create the 12 default partitions.

Table 26-2 Real-Time Partitioning Enabled, No Default Partitions

Partition Partition

partition_historic

(event and item tables only)

partition_last

Caution:

Do not use the partitioning scheme shown in Table 26-2 in a production system because all nonhistoric real-time objects are stored in partition_last. You must add real-time partitions.

An object table designated for partitioning is partitioned as shown in Table 26-3 if you enable partitions and accept the 12 default partitions.

Table 26-3 Real-Time Partitioning Enabled, 12 Default Partitions

Partition Partition Partition Partition Partition Partition Partition

partition_historic

(event and item tables only)

Real Time Partition 1

Real Time Partition 2

Real Time Partitions 3 through 10

Real Time Partition 11

Real Time Partition 12

partition_last

An object table designated for partitioning is partitioned as shown in Table 26-4 if you enable partitions and create custom partitions.

Table 26-4 Real-Time Partitioning Enabled, Custom Default Partitions

Partition Partition Partition Partition Partition Partition Partition

partition_historic

(event and item tables only)

Real Time Partition 1

Real Time Partition 2

Real Time Partition 3

Real Time Partitions 4 through N - 1

Real Time Partition N

partition_last

An event table is partitioned as shown in Table 26-5 if you enable partitions without the 12 default partitions and then enable delayed-event partitions.

Table 26-5 Real-Time Partitioning Enabled, Delayed Partitioning Enabled, No Default Partitions

Partition Partition Partition

partition_historic

partition_last_pin

partition_last

Caution:

Do not use the partitioning scheme shown in Table 26-5 in a production system because all nonhistoric events are stored in partition_last and partition_last_pin. You must add real-time and delayed partitions.

An event table is partitioned as shown in Table 26-6 if you enable partitions, create real-time partitions, and enable delayed-event partitions.

Table 26-6 Real-Time Partitioning Enabled, Real-Time Partitions Exist, Delayed Partitioning Enabled

Partition Partition Partition Partition Partition Partition Partition Partition

partition_historic

RT P_1

RT P_2

RT P_3

RT P_4 through RT P_(N-1)

RT P_N

partition_last_pin

partition_last

Caution:

Do not use the partitioning scheme shown in Table 26-6 in a production system because all nonhistoric delayed events are stored in partition_last. You must add delayed partitions.

An event table is partitioned as shown in Table 26-7 if you enable partitions, do not create real-time partitions, and enable and create delayed partitions.

Table 26-7 Real-Time Partitioning Enabled, No Real-Time Partitions, Delayed Partitioning Enabled, Delayed Partitions Exist

Partition Partition Partition Partition Partition Partition Partition Partition

partition_historic

partition_last_pin

D P_1

D P_2

D P_3

D P3 through D P_(N-1)

D P_N

partition_last

Caution:

Do not use the partitioning scheme shown in Table 26-7 in a production system because all nonhistoric real-time events are stored in partition_last_pin. You must add real-time partitions.

An event table is partitioned as shown in Table 26-8 if you enable partitions, create real-time partitions, and enable and create delayed partitions.

Table 26-8 Real-Time Partitioning Enabled, Real-Time Partitions Exist, Delayed Partitioning Enabled, Delayed Partitions Exist

Partition Part'n Part'n Part'n Part'n Part'n Part'n Part'n Part'n Part'n Part'n

partition_historic

RT P_1

RT P_2

RT P_3 through RT P_(N-1)

RT P_N

partition_last_pin

D P_1

D P_2

D P_3 through D P_(N-1)

D P_N

partition_last

About Managing Partitions

To manage partitions, you perform the following tasks:

Before your BRM production system begins generating objects that you want to store in partitions, add partitions to your tables if any of the following situations is true:

  • (Event tables only) You removed the 12 default monthly event partitions created during installation.

  • (Event tables only) You chose not to create the 12 default monthly event partitions when you enabled partitioning during installation.

  • You partitioned your tables by upgrading your database.

  • You enabled partitioning for any non-event storable classes.

In addition, after your partitioned production system is running, you should add purgeable partitions whenever the time periods covered by the existing purgeable partitions are about to pass. See "About Objects Stored in partition_last and partition_last_pin".

About Purging Objects

When you purge objects, you can do one of the following:

About Purging Objects by Removing Partitions

When you purge objects by removing their partitions, you specify a start date and an end date to identify the objects to be removed. For example, you can purge objects created from January 15 to March 15.

Important:

The dates you specify for purging objects might not align with the partition dates. In this case, only partitions completely within the date range are removed.

If a partition within the date range contains an event associated with an open item, by default, the partition is not removed. To change the default behavior, see "Enabling Open Items to Be Purged".

Figure 26-2 shows monthly partitions, each starting on the first of the month. If you specify to purge objects created between January 15 and April 15, only objects from February 1 through April 1 are purged because those are the only objects in partitions completely within the date range.

Figure 26-2 Purging of Complete Partitions

Description of Figure 26-2 follows
Description of ''Figure 26-2 Purging of Complete Partitions''

For more information, see "Purging Objects by Removing Partitions".

Important:

Purging partitions is considered safer than removing them. For more information, see "About Purging Objects without Removing Partitions".

Tip:

Before purging objects, run the partition_utils utility with the -p parameter to write an SQL statement that shows the partitions that will be removed. See "Running the partition_utils Utility in Test Mode".

About Purging Objects without Removing Partitions

You can delete old objects without deleting partitions by specifying the last object date to keep. If any old events are associated with active items, those particular objects are not deleted. See "Purging Objects without Removing Partitions".

Tip:

Before purging objects, run the partition_utils utility with the -p parameter to write an SQL statement that shows the partitions that will be removed. See "Running the partition_utils Utility in Test Mode".

Important:

Purging partitions is considered safer than removing them.

About Running the partition_utils Utility

Most partition management tasks are accomplished by using the partition_utils utility.

Caution:

After using your most current version of the partition_utils utility, do not use the previous versions. Doing so can corrupt your database. (Partitions created by using the previous version are, however, supported.)

You can run only one instance of this utility at a time. If you try to run more than one instance, the utility does not run and returns an error.

Caution:

After starting the utility, do not interrupt it. It might take several minutes to finish, depending on the size of your database. If the utility is interrupted, use the partition_utils restart operation to continue the previous operation. See "Restarting partition_utils".

The partition_utils utility creates the following tables. Do not delete them or use the table names for any other purpose:

  • PIN_TEMP_BRM_SERVER_TIME_T

  • PIN_TEMP_DD_INFO_T

  • PIN_TEMP_DETAILS_T

  • PIN_TEMP_HIGH_VAL_T

  • PIN_TEMP_OUT_OF_SYNC_T

  • PIN_TEMP_PURGE_LOGIC_T

  • PIN_TEMP_PURGE_PARTITIONS_T

  • PIN_TEMP_PURGE_POIDS_T

  • PIN_TEMP_PURGE_STATISTICS_T

  • PIN_TEMP_TAB_PARTITIONS_T

  • PIN_TEMP_TBL_NAMES_T

Partition Naming Convention

The naming convention for real-time partitions is the following:

P_R_MMDDYYYY

For example, the following partition name specifies that the last date used for objects is June 29, 2004:

P_R_06292004

The naming convention for delayed-event partitions is the following:

P_D_MMDDYYYY

For example, the following partition name specifies that the last date used for objects is June 29, 2004:

P_D_06292004

Running the partition_utils Utility in Test Mode

When you add, remove, or enable partitions, you can use the -p parameter to write an SQL statement of the operation to the partition_utils.log file without performing any action on the database. The partition_utils.log file is in the same directory as the partition_utils utility.

Caution:

Do not copy the SQL statements and run them against the database. This action is not supported.

Configuring a Database Connection

Before you use the partition_utils utility, configure the database connection parameters in the partition_utils.values file in BRM_home/apps/partition_utils. For example:

$MAIN_DB{'vendor'}   =  "oracle";
$MAIN_DB{'alias'}    =  "pindb.myserver.com";
$MAIN_DB{'user'}     =  "pin";
$MAIN_DB{'password'} =  "pin";

For more information, see the comments in the partition_utils file.

Improving Performance When Using partition_utils

For all partition_utils operations, you can run processes in parallel to improve performance.

To run processes in parallel, edit the NUM_OF_PROCESSES parameter in the BRM_home/apps/partition_utils/partition_utils.values file. This parameter controls how many partition_utils processes can run in parallel unless the enable operation is executed.

The valid range for NUM_OF_PROCESSES is from 1 to 10. The default is 2.

Restarting partition_utils

If partition_utils is interrupted, it is best to use partition_utils with the restart operation to continue the previous operation. This prevents your database partitions from becoming unaligned and corrupted.

The syntax is the following:

partition_utils -o restart [-b]

Use the -b (bypass) parameter to bypass the last operation and clean the status of it.

For more information, see "partition_utils".

Adding Partitions

To add partitions, run the partition_utils utility with the add operation.

Important:

The syntax is the following:

partition_utils -o add -t realtime|delayed -s start_date 
                   -u month|week|day -q quantity
                  [-c storable_class] [-w width]
                  [-f] [-p]

  • Use the -t (type) parameter to add real-time or delayed-event partitions. Only event tables can have delayed-event partitions.

    Note:

    Non-event partitions are always real-time. You can add only delayed-event partitions. To do so, you must enable partitioning for each event type you want to partition. See "Enabling Delayed-Event Partitioning".

  • Use the -s (start date) parameter to specify the start date for the first of the new partitions. The format is MMDDYYYY.

    The start date must be the day after tomorrow or later; you cannot create partitions starting on the current day or the next day. For example, if the current date is January 1, the earliest start date for the new partition is January 3.

    By default, the start date must also be earlier than six months from today. You can change these defaults by editing the BRM_home/apps/partition_utils/partition_utils.values file. See "Customizing Partition Limitations".

    If you try to create a partition with a start date within the current partition, the utility reports an error and provides the earliest date that you can use for the new partition. To override this limitation, use the -f (force) parameter.

  • Use the -u (unit) parameter to specify the time unit for the partition.

  • Use the -q (quantity) parameter to specify the number of partitions to add. Enter an integer greater than 0.

    By default, you can add the following number of partitions:

    • No more than 60 daily partitions

    • No more than 15 weekly partitions

    • No more than 6 monthly partitions

    You can change these defaults by editing the BRM_home/apps/partition_utils/partition_utils.values file. See "Customizing Partition Limitations".

  • Use the -c (storable class) parameter to specify the class of objects to be stored in the partition. The default is event.

    Note:

    To specify a storable class other than the event, bill, invoice, item, journal, newsfeed, sepa, and user activity storable classes, partitioning must have been enabled for that storable class in one of the following ways:

  • Use the -w (width) parameter to specify the number of units in a partition (for example, a partition that is 3 days wide, 2 weeks wide, or 1 month wide). Enter an integer greater than 0. The default is 1.

    By default, you can use the following maximum widths:

    • 5 days

    • 3 weeks

    • 2 months

    You can change these defaults by editing the BRM_home/apps/partition_utils/partition_utils.values file. See "Customizing Partition Limitations".

  • Use the -f (force) parameter to create a partition with a start date that falls within the time period of the current partition. The existing partition is split in two:

    • One new partition contains objects created before the specified start date.

    • The other new partition contains objects created on or after the specified start date.

    Before you use this parameter:

    • For real-time partitions, stop all BRM server processes.

    • For delayed-event partitions, stop all delayed-event loading by stopping Pipeline Manager and RE Loader.

      Important:

      If you use the -f parameter to create partitions within the time period of the current partition and you do not stop BRM processes or loading, the BRM server may write data to a partition that is in the process of being divided. That can cause a server error, such as a write operation error.

      Note:

      The -f parameter works differently when you remove partitions. In that case, it forces the removal of objects that do not meet the purging criteria, such as events associated with open items, by removing the partition that contains those objects.

  • Use the -p (print) parameter to run the utility in test mode and write an SQL statement of the operation to the partition_utils.log file without performing any action on the database. See "Running the partition_utils Utility in Test Mode".

For more information, see "partition_utils".

The following examples show how to add partitions.

Note:

Adding partitions adds the requested number of partitions and an additional partition on the specified start date unless a partition labeled with that date exists.

The following command adds five real-time event partitions, starting on July 27, 2015, and one partition for events up July 27, 2015. Each partition includes events for a two-week period:

partition_utils -o add -t realtime -s 07272015 -u week -w 2 -q 5

The following command includes the -f parameter. This creates partitions even if they include start dates, end dates, or both start and end dates in the current partition:

partition_utils -o add -t realtime -s 07272015 -u week -w 2 -q 5 -f

The following command adds six daily event partitions starting on July 27, 2015, and one partition for events up to July 27, 2015. Because the -w parameter is not used, each parameter is one day long:

partition_utils -o add -t delayed -s 07272015 -u day -q 6

The following command adds 12 monthly real-time partitions for the journal storable class starting on July 27, 2015, and one partition for journal up to July 27, 2015:

partition_utils -c /journal -o add -t realtime -s 07272015 -u month -q 12

The following command creates five weekly partitions for the journal storable class starting on July 27, 2015, and one partition for journal up to July 27, 2015:

partition_utils -c /journal -o add -t realtime -s 07272015 -u week -q 5

Enabling Delayed-Event Partitioning

To add delayed-event partitions, run the partition_utils utility with the enable operation. You must enable delayed-event partitioning before you can add delayed-event partitions.

Important:

After you enable delayed-event partitioning, add partitions for delayed events before you use the system for production. Otherwise, delayed events are stored in partition_last. See "About Objects Stored in partition_last and partition_last_pin".

Note:

You can enable delayed partitioning only for event objects. All non-event partitions are real-time partitions.

The syntax is the following:

partition_utils -o enable -t delayed [-c storable_class] [-p]

  • Use the -c (storable class) parameter to specify the event storable class for which you want to add delayed-event partitions. Delayed-event partitions cannot be used for non-event storable classes.

    To add delayed-event partitions for all subclasses of an event, use the percent sign (%) as a wildcard (for example, -c /event/session/%).

  • Use the -p (print) parameter to run the utility in test mode and to write an SQL statement of the operation to the partition_utils.log file without performing any action on the database. See "Running the partition_utils Utility in Test Mode".

    Note:

    When you enable delayed-event partitioning, the partition_utils utility automatically runs the update operation to synchronize partitioning schemes across all event tables. See "Updating Partitions".

For more information, see "partition_utils".

The following command enables delayed-event partitioning for the /event/delayed/session/telco/gsm storable class:

partition_utils -o enable -t delayed -c /event/delayed/session/telco/gsm

You can enable partitioning for all subclasses of an event by using the percent sign (%) as a wildcard:

partition_utils -o enable -t delayed -c /event/session/%

Note:

This operation supports only delayed events. If you specify anything other than -t delayed, the utility returns an error.

Disabling Delayed-Event Partitioning

If you enable delayed-event partitioning for an event storable class but then decide you do not need it, you can use sqlplus to remove the delayed-event partitions and then to remove the partition_last_pin partition.

Important:

If you remove partitions from a delayed-event table, remove all the delayed-event partitions before removing the partition_last_pin partition.

For example, to disable partitioning for the EVENT_DLYD_SESSION_TELCO_GSM_T table:

  1. Use the following sqlplus statement to ensure that no data is in partition_last_pin:

    SQL> select count (*) from event_dlyd_session_telco_gsm_t partition PARTITION_LAST_PIN

    If partition_last_pin contains data, add partitions to move the data.

    Caution:

    You must use the -f parameter. See "Adding Partitions".

  2. Use the following sqlplus statement to list the delayed partitions in the table:

    SQL> select partition_name from user_tab_partitions where table_name = UPPER(event_dlyd_session_telco_gsm_t) and partition_name like 'P_D_%';

  3. Use the following sqlplus statement to remove the listed partitions individually:

    SQL> alter table event_dlyd_session_telco_gsm_t drop partition partition_name

  4. Use the following sqlplus statement to remove partition_last_pin:

    SQL> alter table event_dlyd_session_telco_gsm_t drop partition PARTITION_LAST_PIN

  5. Use the following sqlplus statement to verify that the table contains no partitions:

    SQL> select count(*) from user_tab_partitions where table_name = UPPER(event_dlyd_session_telco_gsm_t) and partition_name like 'P_D_%';

    This should return zero rows.

Updating Partitions

The update operation enables you to align table partitioning schemes for all subclass tables with the current partitioning scheme for their base storable class table. Updating partitions enforces the following rules:

  • From the day after tomorrow, all tables with real-time objects will have the same real-time partitioning scheme as their base table (EVENT_T for event base storable class tables, ITEM_T for item base storable class tables, and so on.).

  • From the day after tomorrow, all tables with delayed events will have the same delayed-event partitioning scheme as the EVENT_T table.

You must update partitions in the following cases:

  • When you add custom partitioned storable classes.

  • When you extend an existing storable class with an array or substruct that adds a partitioned table.

  • When you add a component that includes new partitioned storable classes.

    Note:

    The update operation automatically runs once when you enable delayed partitions.

To align the partitioning schemes of new or changed object tables with the partitioning schemes of their base storable classes, run the partition_utils utility with the update operation.

The syntax is the following:

partition_utils -o update [-c storable_class] [-p]

  • Use the -c (storable class) parameter to specify the class of objects to be updated. The default is event.

  • Use the -p (print) parameter to run the utility in test mode and to write an SQL statement of the operation to the partition_utils.log file without performing any action on the database. See "Running the partition_utils Utility in Test Mode".

For more information, see "partition_utils".

Purging Objects by Removing Partitions

The general way to purge partitioned data is to remove one or more partitions. This frees up database space.

Note:

By default, partitions that contain objects associated with open items are not removed when you remove partitions. To change this default, see "Enabling Open Items to Be Purged".

Caution:

You can use the -f (force) parameter to remove partitions even if the objects are associated with open items, but this parameter must be used with care.

Important:

Before removing partitions that contain objects associated with open items, verify that doing so does not contradict your business practices.

Tip:

Before removing partitions, run the partition_utils utility with the -p parameter to write an SQL statement that shows the partitions that will be removed. See "Running the partition_utils Utility in Test Mode".

To remove partitions, run the partitions_utils utility with the remove operation. The syntax is the following:

partition_utils -o remove -s start_date -e end_date [-c storable_class][-t realtime|delayed][-f][-p]

  • Use the -s (start date) parameter to specify the start of the date range for the objects to remove. The format is MMDDYYYY.

  • Use the -e (end date) parameter to specify the end of the date range for the objects to remove. The format is MMDDYYYY.

    All partitions that are entirely within these dates are removed. See "About Purging Objects".

    By default, you can use this operation to remove only those partitions that are older than 45 days. You can change this limitation by editing the BRM_home/apps/partition_utils/partition_utils.values file. See "Customizing Partition Limitations".

  • Use the -c (base storable class) parameter to specify the partition to remove by base storable class. The default is event.

    When you remove a partition, it removes partitions in all the partitioned tables for the specified base storable class and its subclasses.

    Caution:

    To purge objects other than event, item, bill, invoice, journal, newsfeed, and sepa objects, you must remove their partitions by using the -f parameter. Operations using this parameter cannot be undone and will remove objects that are being used. Use with caution. (You can purge event, bill, invoice, item, journal, newsfeed, and sepa objects without removing their partitions. See "Purging Objects without Removing Partitions".)

  • Use the -t (type) parameter to remove real-time or delayed-event partitions. The default is to remove both real-time and delayed-event partitions.

  • Use the -f parameter to remove partitions even if they contain objects associated with open items. By default, partitions that contain events associated with open items are not removed. To change this default, see "Enabling Open Items to Be Purged".

    Note:

    The -f parameter works differently when you add partitions. In that case, it forces the splitting of partitions even when they fall within the date range of the current partition.

  • Use the -p (print) parameter to run the utility in test mode and write an SQL statement that shows the partitions that will be removed to the partition_utils.log file without performing any action on the database. See "Running the partition_utils Utility in Test Mode".

For more information, see "partition_utils".

The following command removes the partitions with real-time events from July 20, 2004, to September 20, 2004:

partition_utils -o remove -s 07202004 -e 09202004 -t realtime

The following command removes real-time partitions for item table entries from July 20, 2004, to September 20, 2004:

partition_utils -o remove -s 07202004 -e 09202004 -c /item -t realtime -f

Purging Objects without Removing Partitions

You can purge event, bill, invoice, item, journal, newsfeed, and sepa objects without removing their partitions. The event objects must be associated with closed items. To enable this utility to purge event objects associated with open items, see "Enabling Open Items to Be Purged".

Tip:

Before purging objects, run the partition_utils utility with the -p parameter to write an SQL statement that shows the partitions that will be purged. See "Running the partition_utils Utility in Test Mode".

Note:

If the specified start and end dates do not match the partition boundaries, only objects in partitions that are completely within the date range are purged.

To purge objects without removing partitions, run the partitions_utils utility with the purge operation. The syntax is as follows:

partition_utils -o purge [-s start_date] -e end_date [-t realtime|delayed] [-p]

  • (Optional) Use the -s (start date) parameter to specify the start of the date range containing the objects you want to purge. The date is inclusive. The format is MMDDYYYY. If a start date is not specified, all objects created on or before the end date are purged.

  • Use the -e (end date) parameter to specify the end of the date range containing the objects you want to purge. The date is inclusive. The format is MMDDYYYY. See "About Purging Objects".

  • Use the -t (type) parameter to purge real-time or delayed-event partitions. The default is to purge both real-time and delayed-event partitions

  • Use the -p (print) parameter to run the utility in test mode and write an SQL statement that shows the partitions that will be removed to the partition_utils.log file without performing any action on the database. See "Running the partition_utils Utility in Test Mode".

For more information, see "partition_utils".

The following command purges real-time events before July 20, 2004:

partition_utils -o purge -e 07202004 -t realtime

Finding the Maximum POID for a Date

You can use partition_utils -o maxpoid to find the maximum POID in a partition for a specified date. You may need this if you have scripts to manage partitions automatically.

The syntax is the following:

partition_utils -o maxpoid -s date -t realtime|delayed

The following command finds the maximum POID for a real-time partition:

partition_utils -o maxpoid -s 02012005 -t realtime

For more information, see "partition_utils".

Customizing Partition Limitations

The partition_utils utility specifies default limitations to prevent the creation of an unrealistic number of partitions. You can change these limitations by editing the BRM_home/apps/partition_utils/partition_utils.values file.

The default limitation values are the following:

  • Partition start date: later than the day after tomorrow, earlier than 6 months from now.

  • Maximum number of partitions:

    • 60 daily

    • 15 weekly

    • 6 monthly

  • Maximum widths:

    • 5 days

    • 3 weeks

    • 2 months

  • Removed partitions must be at least 45 days old.

  • Minimum required percent of purgeable data.

  • Partitions are purged only when purgeable POIDs are greater than 70% of the total purgeable POIDs in the EVENT_BAL_IMPACT_T table.

    The valid range for this setting is from 60 to 100.

  • Records are deleted only if there is a specified number in a chunk. The default is 1000 records.

    The valid range is from 500 to 5000.

  • When purging, the DELETE_IN_PLACE method is used if the number of purgeable events is greater than 5% of the total events or if more than 10,000 purgeable records exist.

    You can change the number of purgeable records required. The valid range is from 1000 to 20000.

Customizing the List of Events and Items Stored in partition_historic

Nonpurgeable event and items objects are stored in partition_historic.

If you do not need to save all the event types stored by default in partition_historic or if you must save additional storable classes of events and items, use the Storable Class Editor in Developer Center to customize the list of nonpurgeable event and item storable classes.

Use the Non-purgeable storable class property to specify whether an event or item is purgeable.

Note:

The Non-purgeable property for event and item storable classes has no effect unless your event and item tables are partitioned.

Caution:

  • To avoid storing event or item objects that you do not want to purge in purgeable partitions, you must customize event and item storable classes stored in partition_historic before running BRM and generating any events or items.

  • If you make an event storable class nonpurgeable, make sure that all item storable classes related to the event storable class are also nonpurgeable. See "Associating Items with Nonpurgeable Events".

For more information, see "About Purging Data".