Skip Headers
Oracle® Communications Billing and Revenue Management Configuring Pipeline Rating and Discounting
Release 7.5

E16710-10
Go to Documentation Home
Home
Go to Table of Contents
Contents
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

33 Loading Prerated Events

This chapter describes how to run Oracle Communications Billing and Revenue Management (BRM) Rated Event (RE) Loader and provides information about troubleshooting and customizing.

For an overview on how RE Loader works, see "Understanding Rated Event Loader".

For information about configuring RE Loader, see "Configuring Rated Event Loader".

Loading Events Automatically

You can load events automatically in one of the following ways:

  • By running RE Loader automatically by using Batch Controller and the RE Loader batch handler. When a prerated event file is available, Batch Controller automatically starts the RE Loader batch handler, which runs the RE Loader utility (pin_rel).

    To configure Batch Controller and the RE Loader batch handler, see "Configuring the RE Loader Batch Handler".

    Important:

    Make sure you synchronize your rating and loading applications if you have configured the RE Loader batch handler to start the RE Loader utility to load rerated events. See "About Synchronizing Rating and Loading Applications" in BRM Setting Up Pricing and Rating.
  • By running RE Loader daemon. See "Running the RE Loader Daemon".

Running the RE Loader Daemon

Important:

If this is the first time you are running the RE Loader daemon on your Oracle IMDB Cache system, ensure that the parameters in the start_rel_daemon script are configured. See "Configuring the start_rel_daemon Script for an Oracle IMDB Cache System".

To run the RE Loader daemon:

  1. Open the BRM_Home/apps/pin_rel/Infranet.properties file in a text editor. BRM_Home is the directory where you installed BRM components.

  2. Specify how to run the RE Loader daemon by setting the entries given in Table 32-5, "RE Loader Daemon Entries".

  3. Run the BRM_Home/bin/start_rel_daemon script.

    This script starts the RE Loader daemon.

  4. Run the BRM_Home/bin/stop_rel_daemon script.

    This script stops the RE Loader daemon.

Loading Events Manually

Important:

Make sure you synchronize your rating and loading applications when running RE Loader. See "About Synchronizing Rating and Loading Applications" in BRM Setting Up Pricing and Rating.

To manually load pipeline-rated events, run the RE Loader utility (pin_rel) from the RE Loader directory.

Manually Loading Events from One Directory

If you have only one directory and need to load more than one event type, you must make sure pin_rel can find the prerated event data record (EDR) file. pin_rel looks for the EDR file in the directory specified in the Infranet.rel.rated_event_file entry in the Infranet.properties file. Before you run RE Loader manually, make sure the input EDR file is in this specified directory. To do this, do one of the following each time you run RE Loader:

  • Move the EDR file to the directory specified in the Infranet.properties file.

  • Change the Infranet.rel.rated_event_file entry in the Infranet.properties file to point to the directory containing the EDR file.

To run pin_rel, see "Running RE Loader Manually".

Manually Loading Events from Multiple Directories

If you have set up multiple directories, run pin_rel in the directory that corresponds to the service event type to load.

Running RE Loader Manually

You can manually run RE Loader from the command line in the following ways:

  • pin_rel event_file_name

    This command loads events from event_file_name into the BRM database and then updates the account balances, bill items, and journals.

    Account balances, bill items, and journals are updated after all events have been loaded. If an error occurs during the loading phase, RE Loader cancels the process and all events loaded in the session are deleted from the BRM database.

    Note:

    The name of the file in the command line can be found in the pipeline registry file. For more information, see "Configuring EDR Output Processing".
  • pin_rel -timesten LogicalPartID event_file_name

    Use this command for IMDB Cache-enabled systems. This command loads events from event_file_name into the BRM database and then updates the account balances, bill items, and journals in the specified IMDB Cache logical partition.

    Account balances, bill items, and journals are updated after all events have been loaded. If an error occurs during the loading phase, RE Loader cancels the process and deletes all loaded data from the BRM database. After you correct the error, run RE Loader again by using the default command, pin_rel -timesten LogicalPartID event_file_name.

  • pin_rel -override event_file_name

    This command starts an RE Loader process if one is not already running.

    Only one RE Loader process can load the same database tables at the same time because each process locks the tables while loading them. When an RE Loader process is started, it checks the status of its last process and waits if the last process is not complete. However, if the process was manually canceled, the status may not indicate that the process has ended, even though it is no longer running. In this case, you use the -override option to start a new RE Loader process.

For more information about the pin_rel utility, see "pin_rel" in BRM Setting Up Pricing and Rating.

Monitoring and Maintaining RE Loader

To monitor and maintain RE Loader, perform the following:

Troubleshooting Event Loading

There are two distinct error-handling actions that RE Loader takes, depending on when the error occurs:

  • If an error occurs while events are being loaded, the process is canceled and all events loaded in the session are deleted from the BRM database. The SQL loader errors are logged in a "bad" file (BRM_Home/apps/pin_rel/EDR_file_name.bad) and a fatal error is recorded in the RE Loader log file (Processing_directory/rel.pinlog).

  • If an error occurs while RE Loader is updating account balances, bill items, or journals, the loaded events are left in the database and an error is recorded in the RE Loader log file (Processing_directory/rel.pinlog). If RE Loader stops due to errors while updating account balances, bill items, or journals, correct the problem and run RE Loader again.

Some error messages are sent to the console. To find out if an error occurred during rated event loading, check the rel.pinlog log file. See "Checking the RE Loader Log Files for Error Codes".

RE Loader checks for status in two places:

  • The /batch/rel session status object.

    This object stores the status of the last RE Loader process. When you start RE Loader, it checks that status. If you try to reload a file that RE Loader has already successfully updated, the file is rejected because the session status indicates that the update for that file is complete.

  • The REL_SUB_PROCESSES_T table.

    This tables stores information about loading errors that occurred during the preupdating stage. See "Checking for Errors that Occurred during the PreUpdate Process".

Checking the RE Loader Log Files for Error Codes

RE Loader uses the SQL Loader utility, sqlldr, to load events into the BRM database. The sqlldr process creates a new log file for each input file so that log files from a previous process are not overwritten.

The log files and the temporary files created during preprocessing incorporate the name of the input file in their file names, making it easier to debug if an error occurs.

Error codes follow the fully qualified error code (FQEC) scheme, which consists of a major code that represents the component and a minor code that represents the error number. All BRM-defined errors use a minor code from 0 through 99, and all custom errors use minor codes 100 and above.

For information on how to create custom error codes for RE Loader scripts and utilities, see "Creating Custom Error Codes".

Note:

Because modifying a stored procedure can corrupt data and cause maintenance and upgrade problems, custom error codes cannot be created for stored procedures.

The major and minor error codes for each RE Loader component are shown in Table 33-1.

Table 33-1 RE Loader Major and Minor Error Codes

Component Description Major Code BRM Reserved Minor Codes Customer Reserved Minor Codes

All

Universal code for success.

0

N/A

N/A

RE Loader driver

pin_rel script and Java driver code.

1000

0 - 999

N/A

Failure script

Script that is called when RE Loader attempts to load a data file that previously failed to load into the BRM database.

2000

0 - 99

100 - 255

Transform script

pin_rel_transform_cdr.pl script, which converts pipeline discount files into EDR format.

3000

0 - 99

100 - 255

Preprocess script

pin_rel_preprocess_cdr.pl script, which preprocesses the data files and creates bulk-loadable (.blk) files.

4000

0 - 99

100 - 255

Load utility

sqlldr utility, which loads data into the BRM database.

5000

0

1 - 999

Preupdate stored procedure

Stored procedure for updating the loaded data before releasing the partition to other RE Loader sessions.

7000

0 - 99

Not available

Update stored procedure

Stored procedure for updating account balances, bill items, and journals.

8000

0 - 99

Not available

Success script

Script that runs automatically when RE Loader successfully loads a data file into the BRM database.

9000

0 - 99

100 - 255

Database consistency check stored procedure

Stored procedure for verifying that the database indexes are correct before loading data into the database.

10000

0 - 99

Not available


Table 33-2 shows the BRM-defined error codes and messages, where value is the value returned in the error message:

Table 33-2 BRM-Defined Error Codes

RE Loader Error Number Error Message

1000

REL encountered an error.

1002

The infranet.rel.dbtype properties value found is not supported: value

Supported values are: value

1003

The infranet.rel.partition_set_number properties value found is not valid: value

Valid values are between value and value.

1004

A table name properties value is missing for the given storable-class: value

1005

A duplicate table name properties value was found: value

1006

The load_util properties value is missing for the given storable-class: value

1007

A control file properties value is missing for the given storable-class: value

1008

The control file name could not be found in the command line.

1009

REL cannot be executed until the Event Extraction Manager is complete.

1010

An unexpected SQL exception has occurred.

1011

An error occurred while attempting to connect to the BRM database.

1012

An error occurred while attempting to connect to the CM.

Please validate the infranet.connection property value and ensure the CM is running.

1013

An error occurred while attempting to perform an opcode call.

1014

An interrupt has occurred and caused an error.

1015

The following file was not found: value

1016

An unexpected I/O error was encountered.

1017

The POID selected from the database sequence exceeds the maximum supported range of 244: value

1018

REL failed to select the partition name from the database.

1019

The poid_db could not be found in the input file.

1020

The poid_db found in the input file does not match the BRM database number for this CM connection.

Found: value

Expected: value

1021

The header record could not be found in the input file.

1022

The storable-class was not defined, or was not found in the header record.

1023

The time format found in the header record is not valid: value

1024

The creation process found in the header record is not supported: value

Valid values are: value

1026

An invalid command-line was provided.

1027

The CM and JDBC BRM database connections are not configured to the same database schema.

1028

The REL session has timed out waiting for another REL session to complete.

1029

The file has previously completed successfully so it will not be loaded again: value

1030

The file is currently being processed by another REL session: value

1031

The value key is missing from the properties file.

1032

The value value is missing from the properties file.

1033

The configured number of tables for this storable-class does not match the configured tables: value

1034

A number formatting error was encountered in the properties value for: value

1035

The infranet.rel.updater_threads properties value found is not valid: value

Valid values are between value and value.

To have REL auto-choose an appropriate number of threads, use the value: value

1036

An error occurred while attempting to parse a number for: value

1038

Cannot have control file with 'TRUNCATE' option when running REL in parallel loading mode between multiple REL processes.


Table 33-3 shows the BRM-defined failure script error codes.

Table 33-3 Failure Script Error Messages

Failure Script Error Number Error Message

2000

The failure script encountered an error.

The given command-line was: value

2001

The failure script command-line given arguments are not supported.

The given command-line was: value

2002

The failure script command-line given flags value provided is not supported.

The given command-line was: value

2003

The failure script command-line given directory could not be read.

The given command-line was: value


Table 33-4 shows the BRM-defined transform script error codes.

Table 33-4 Transform Script Error Messages

Transform Script Error Number Error Message

3000

The transform script encountered an error.

3001

The transform script command-line given arguments are not supported.

The given command-line was: value

3002

The transform script command-line given input file could not be read.

The given command-line was: value

3003

The transform script command-line given output file could not be created.

3004

The transform script command-line given negative discount carry over value is invalid.

The given command-line was: value


Table 33-5 shows the BRM-defined preprocess script error codes.

Table 33-5 Preprocess Script Error Messages

Preprocess Script Error Number Error Message

4000

The preprocess script encountered an error.

The given command-line was: value

4001

The preprocess script command-line given arguments are not supported.

The given command-line was: value

4002

The preprocess script failed to open a file.

4003

The preprocess script found the input file to be missing a balance record.

The given command-line was: value

4004

The preprocess script found the input file to be missing a detail record.

The given command-line was: value

4005

The preprocess script command-line given tables are not supported.

The given command-line was: value

4006

The preprocess script command-line given increment_by value is not valid.

The given command-line was: value

4007

The preprocess script did not find the expected number of records in the input file.

The given command-line was: value

4008

The preprocess script found the input file to be missing an EDR record.

The given command line was: value

Used by SE Loader.

4009

The preprocess script did not find the expected EDR size for an EDR record.

The given command line was: value

Used by SE Loader.

4010

The preprocess script failed to parse fields mapping data for generating the control file.

The given command line was: value

Used by SE Loader.


Table 33-6 shows the BRM-defined load utility error codes.

Table 33-6 Load Utility Error Messages

Load Utility Error Number Error Message

5000

The database load utility encountered an error.


Table 33-7 shows the BRM-defined insert stored procedure error codes.

Table 33-7 Insert Stored Procedure Error Messages

Insert Stored Procedure Error Number Error Message

6000

The insert stored procedure encountered an error.


Table 33-8 shows the BRM-defined preupdate stored procedure error codes.

Table 33-8 Preupdate Stored Procedure Error Messages

Preupdate Stored Procedure Error Number Error Message

7000

The preupdate stored procedure encountered an error.

7001

The preupdate stored procedure encountered an error on a select statement.

7002

The preupdate stored procedure encountered an error on an insert statement.

7003

The preupdate stored procedure encountered an error on an update statement.

7004

The preupdate stored procedure encountered an error on a delete statement.

7008

The preupdate stored procedure encountered a parsing error.

7010

The preupdate stored procedure could not find an item for an account.

7011

The preupdate stored procedure encountered an unexpected error.


Table 33-9 shows the BRM-defined update stored procedure error codes.

Table 33-9 Update Stored Procedure Error Messages

Update Stored Procedure Error Number Error Message

8000

The update stored procedure encountered an error.

8001

The update stored procedure encountered an error on a select statement.

8002

The update stored procedure encountered an error on an insert statement.

8003

The update stored procedure encountered an error on an update statement.

8004

The update stored procedure encountered an error on a delete statement.

8008

The update stored procedure encountered a parsing error.

8009

The update stored procedure found its record is already being processed.

8010

The update stored procedure could not find an item for an account.

8011

The update stored procedure encountered an unexpected error.

8012

The update stored procedure encountered an invalid record count error.

8013

The update stored procedure encountered an error when updating the account balances.

8014

The update stored procedure encountered an error when updating the item balances.

8015

The update stored procedure encountered an error at TREL precommit.

8016

The update stored procedure encountered an error at TREL postcommit.


Table 33-10 shows the BRM-defined success script error codes.

Table 33-10 Success Script Error Messages

Success Script Error Number Error Message

9000

The success script encountered an error.

The given command-line was: value

9001

The success script command-line given arguments are not supported.

The given command-line was: value

9002

The success script command-line given flags value provided is not supported.

The given command-line was: value

9003

The success script command-line given directory could not be read.

The given command-line was: value


Table 33-11 shows the BRM-defined database consistency check error codes.

Table 33-11 Database Consistency Check Error Messages

Database Consistency Check Error Number Error Message

10000

The database consistency check encountered an error.

10005

The database consistency check found an unpartitioned index.

10006

The database consistency check found an incorrectly partitioned index.

10007

The database consistency check found an unusable index.


Checking for Errors that Occurred during the PreUpdate Process

Errors that occur during the preupdate stage of the loading process are stored in the REL_SUB_PROCESSES_T table. To check for values in the table, run SQL*Plus.

Table 33-12 shows the error codes that are stored in the REL_SUB_PROCESSES_T table:

Table 33-12 Error Codes Stored in the REL_SUB_PROCESSES_T Table

Status Code Status Number Description

ERROR_SELECTING

–20001

An error occurred when selecting data from a table or tables.

ERROR_INSERTING

–20002

An error occurred during the insert process.

ERROR_UPDATING

–20003

An error occurred during the update process.

ERROR_DELETING

–20004

An error occurred during the delete process.

ERROR_UNPARTITIONED_INDEX

–20005

An error occurred because the index is not partitioned.

ERROR_INCORRECT_PART_INDEX

–20006

An error occurred because the index is global partitioned.

ERROR_UNUSABLE_INDEX

–20007

The index partitions are unusable.

ERROR_PARSING

–20008

An error occurred during the data parsing process.

ERROR_ALREADY_BEING_PROCESSED

–20009

An error occurred because the record is being processed by another thread.

ERROR_ITEM_NOT_IN_ACCOUNT

–20010

An error occurred because the item is already billed and pre_updater_flag is not enabled.

ERROR_UNEXPECTED

–20011

An unexpected error occurred in the pre-update procedure.

ERROR_UPDATE_ACCT_BALANCES

–20013

An error occurred while updating account balances.

ERROR_UPDATE_ITEM_BALANCES

–20014

An error occurred while updating item balances.


Fixing Event Loading Errors

In order to troubleshoot event loading errors, check the RE Loader log file BRM_Home/apps/pin_rel/rel.pinlog, where BRM_Home is the directory in which you installed BRM components. See "Checking the RE Loader Log Files for Error Codes" for more information.

At times, when RE Loader fails, the rel.pinlog file does not list the error. If this occurs, check the status column in the BATCH_T table in the BRM database for the status of the REL process. Table 33-13 lists the status entries (and the corresponding code attributes).

Table 33-13 Status Entries in the BATCH_T Table

Status Code Attribute

0

UPDATE_COMPLETE

1

LOAD_ERROR

2

UPDATE_ERROR

4

INSERT_ERROR

8

PREUPDATE_ERROR

16

REL_START

48

PRE_PROCESS

64

START_LOAD

80

LOADING

96

LOAD_COMPLETE

240

PROCESS_LOADING

256

START_INSERT

512

INSERTING

768

INSERT_COMPLETE

1024

START_PREUPDATE

1280

PREUPDATING

1536

PREUPDATE_COMPLETE

3840

PROCESS_PREUPDATING

4096

START_UPDATE

8192

UPDATING

61440

PROCESS_UPDATING


The correct troubleshooting effort for an event loading error depends upon the error scenario:

  • Load failure:

    The RE Loader log file (rel.pinlog) displays the error code 5000. The status entry for the REL process in the BATCH_T table in the BRM database displays 1 (see Table 33-13).

    In this error scenario, RE Loader failed either before or during the loading of the events in the event file. The events are deleted from the event tables.

    To troubleshoot this error, reload the events normally by using the same command to process the original event file.

  • RE Loader fails during the loading:

    The RE Loader log file (rel.pinlog) does not display any error. The status entry for the REL process in the BATCH_T table in the BRM database displays 80 (see Table 33-13).

    In this error scenario, RE Loader failed during the loading of the events and RE Loader was unable to update the session status or execute the cleanup process.

    Use the -override option to start a new process to reload the events. For example:

    pin_rel -override event_file_name

    where event_file_name is the event file.

  • Error occurs during the preupdate stored procedure:

    The RE Loader log file (rel.pinlog) displays preupdate stored procedure error codes starting at 7000 and below 8000. The status entry for the REL process in the BATCH_T table in the BRM database displays 8 (see Table 33-13).

    In this error scenario, RE Loader crashed during the execution of the preupdate stored procedure.

    To troubleshoot this error, reload the events normally by using the same command to process the original event file.

  • RE Loader fails during the updating of events:

    The RE Loader log file (rel.pinlog) does not display any error. The status entry for the REL process in the BATCH_T table in the BRM database displays 8192 (see Table 33-13).

    In this error scenario, RE Loader crashed during the updating of the events and RE Loader was unable to update the session status or execute the cleanup process.

    To troubleshoot this error, reload the events normally by using the same command to process the original event file.

  • Error occurs during the update stored procedure:

    The RE Loader log file (rel.pinlog) displays update stored procedure error codes starting at 8000 and below 9000. The status entry for the REL process in the BATCH_T table in the BRM database displays 2.

    In this error scenario, RE Loader successfully loaded the events but failed during the execution of the update stored procedure. The BATCH_REL_SUB_PROCESSES_T table lists the last commit, indicating the point at which the database update failed.

    Reload the events normally. The update starts from this point.

Debugging Mismatches between Data Files and Control Files

RE Loader customizations can sometimes cause data files and control files to become unsynchronized, resulting in SQL Loader failures. To help you debug these situations, use the pin_rel_enum_blk.pl script, which enumerates fields in your bulk-loadable files. You can then manually compare the data file entries to the control file.

To debug mismatches between your data files and control files, enter the following commands:

% cd BRM_Home/apps/pin_rel
% pin_rel_enum_blk.pl file_name [Line_num]

where:

  • file_name specifies the name of the bulk-loadable file. For example, test2.blk.

  • Line_num specifies the line number of the bulk-loadable file that you want to enumerate. The default is 1.

Preventing POID Errors in Multischema Systems

BRM multischema systems ensure that all POIDs are unique across all database schemas by using a POID-generation algorithm. This BRM algorithm sets each schema's starting sequence number to a unique value and then increments each sequence number by a set value. By default, BRM sets the increment value equal to the number of schemas in your system.

For example, if your system contains three schemas:

  • Schema 1 uses a starting sequence number of 10000

  • Schema 2 uses a starting sequence number of 10001

  • Schema 3 uses a starting sequence number of 10002

The incremental value is 3.

This example results in the following POID numbers shown in Table 33-14:

Table 33-14 Example Schema POID Numbers

Time POID for Schema 1 POID for Schema 2 POID for Schema 3

1

10000

10001

10002

2

10003

10004

10005

3

10006

10007

10008


When RE Loader loads a batch of objects into the BRM database, it reserves a group of POIDs as follows:

  1. Changes the increment value by using the following equation:

    (Number of objects to load) x (Current increment value)
    

    For example, if RE Loader needs to load 2,000 objects into the database and the current increment value is 3, it changes the increment value to 2,000 x 3 = 6,000.

  2. Allocates POIDs to objects.

  3. Returns the increment value to its original value.

However, if a major error occurs during the allocation process, the increment value can remain at the incorrect high value. To catch these situations, you can configure RE Loader to check the database increment value against a specified maximum before it reserves a group of POIDs. When the increment value exceeds the specified maximum, RE Loader exits and logs an error message, notifying your database administrator to manually reset the increment value.

To configure RE Loader to compare the increment value against a specified maximum:

  1. Open the BRM_Home/apps/pin_rel/Infranet.properties file in a text editor.

  2. Set the infranet.rel.max_increment_by entry to the number of database schemas in your system:

    infranet.rel.max_increment_by = 20
    

    The default is 20.

  3. Save and close the file.

Improving RE Loader Performance

You can improve your RE Loader system performance by:

Increasing the Number of Account Balance and Bill Item Updates

RE Loader performance might be improved by increasing the number of account balance, bill item, and journal updates performed before committing the transaction.

You can modify the preupdate batch size and update batch size in the Infranet.properties file to specify how many updates to perform before committing the transaction. For example, if updater_batch_size is set to 5, the stored procedure commits the transaction after every five updates. Increasing the number of updates might increase performance, but the updated account balances, bill items, and journals are not available until the transaction is committed. The default batch_size value is 5.

Important:

Setting the batch_size value too high can result in deadlock. The value for best performance depends on your system configuration. You should test to find the best value for your system.

To change the preupdater_batch_size and updater_batch_size values:

  1. Open the BRM_Home/apps/pin_rel/Infranet.properties file in a text editor.

    Note:

    If you have already set up your RE Loader processing directories, make sure you edit the Infranet.properties file in each directory.
  2. If necessary, edit the infranet.connection entry to point to the correct database.

    For example:

    infranet.connection=pcp://root.0.0.0.1:password@localhost:37180/service/pcm_client
    
  3. Specify the preupdater batch size value in the preupdater_batch_size entry.

    For example:

    infranet.rel.default.preupdater_batch_size = 8
    
  4. Specify the updater batch size value in the updater_batch_size entry:

    infranet.rel.default.updater_batch_size = 8
    
  5. Save and close the file.

Turning Off Index Verification to Improve Database Loading Performance

By default, RE Loader automatically verifies that your indexes are correct before loading data into the BRM database. This extra step helps you discover configuration errors when testing your system in a development environment.

In production systems, however, you should turn off index verification to improve database loading performance.

When configured to verify indexes, RE Loader performs the following before it runs the SQL Loader utility:

  1. Checks whether the indexes to load are partitioned, local, and usable.

  2. Performs one of the following:

    • If the indexes are incorrect, RE Loader aborts the loading process and logs which indexes encountered problems.

    • If the indexes are correct, RE Loader runs the SQL Loader utility to load events into the database.

When configured to skip verification, RE Loader automatically runs the SQL Loader utility to load events into the database. When the indexes are incorrect, SQL Loader fails and RE Loader logs only that the database load utility encountered an error.

To turn off index verification:

  1. Open the BRM_Home/apps/pin_rel/Infranet.properties file in a text editor.

  2. Set the Infranet.rel.validate_indexes entry to False:

    Infranet.rel.validate_indexes = False
    
  3. Save and close the file.

Turning Off Database Verification to Improve Processing Performance

By default, RE Loader automatically verifies that it is loading events into the correct database schema by validating the database number in the EDR file's first account object with the PCM database number. This extra step helps you discover configuration errors when testing your multischema system in a development environment.

In production systems, however, you should turn off database verification to improve RE Loader database loading performance.

To turn off database verification:

  1. Open the BRM_Home/apps/pin_rel/Infranet.properties file in a text editor.

  2. Set the infranet.rel.validate_dbnumber entry to False:

    infranet.rel.validate_dbnumber = False
    
  3. Save and close the file.

Pruning Your RE Loader Control and Audit Tables

RE Loader control and audit tables grow indefinitely, so you should prune them periodically to increase system performance and reduce memory usage. To make pruning easier, you can use the RE Loader purge_batch_rel_objects stored procedure, which automatically prunes the tables for you.

To prune your control and audit tables, run the following commands in SQL*Plus:

sqlplus system/manager@DatabaseAlias
pin_rel.purge_batch_rel_objects(int:Number)

where Number specifies how many days worth of data to keep in the tables.

Customizing RE Loader

Some of the steps required to customize RE Loader should be performed by a programmer and database administrator. To customize RE Loader, you should be familiar with the following topics:

You can customize RE Loader by:

  • Adding New Event Types for RE Loader to Load

  • Creating Custom Error Codes

    Important:

    Do not modify the rel_updater_sp.sql stored procedure or any other stored procedure. Modifying a stored procedure can corrupt data and cause maintenance and upgrade problems. Stored procedures are delivered in source code format due to database limitations and are not designed to be modified. If you need to modify a stored procedure, you must obtain specific permission to do so from Oracle.

    Important:

    Sub-balances are stored in events in an internal format that optimizes performance and storage efficiency. As a result, the table that stores sub-balances is not visible in the data dictionary. You should not base your customizations on this specific internal format. All sub-balance data is accessible by using the BRM API. If you need to access this internal format, contact Oracle.

Adding New Event Types for RE Loader to Load

When you offer a new service, you create a new storable class for the service event type. For information about adding a new service to BRM, see "Adding Support for a New Service" in BRM Developer's Guide.

To use RE Loader to load events from a new service or new service subclass, you must create a delayed event type for your new service and configure RE Loader to load it.

It is possible to load a subclass of a preconfigured service event type without configuring that subclass. However, BRM will not be aware of the subclass because the subclass events will be inserted into the parent class table. To track the activity of the subclass events, you configure RE Loader to load the specific subclass.

You must create a new delayed event type for RE Loader prerated events. The new event storable class type must start with /event/delayed so that BRM can distinguish it from real-time events. For example, /event/delayed/session/new_event_type.

Important:

Avoid loading prerated events by using RE Loader and another application such as an optional component or Universal Event Loader.

To add an event type for RE Loader to load:

  1. If necessary, add the new event type storable class to BRM by using Storable Class Editor. See the Storable Class Editor Help. For information about storable classes, see "About Storable Classes and Storable Objects" in BRM Developer's Guide.

    Note:

    If you installed GSM Manager, the /telephony, /fax, /data, and /sms subclasses of /event/delayed/session/telco/gsm already exist in the BRM database and do not need to be created. However, if you want to track activity specific to one of these subclasses, you must perform this entire procedure.
  2. Create partitions for the event type by running the partition_utils utility from the BRM_Home/apps/partition_utils directory.

    For example, the following command creates partitions for /event/delayed/session/telco/gsm delayed events:

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

    Important:

    You must create partitions for all subclasses of a specific service event type that you want to load.

    See "Enabling Delayed-Event Partitions" and "partition_utils" in BRM System Administrator's Guide.

  3. Create a new control file for the new event type. A control file and format file specifies the format for a single database table (array or substruct). If you added new fields to an existing array or substruct, modify the control or format file for that table. If you added a new array or substruct, create a new control or format file for the new table. For instructions on creating a control or format file, see your Oracle documentation.

  4. If you created or modified any control files, modify the RE Loader preprocess script (BRM_Home/apps/pin_rel/pin_rel_preprocess_cdr.pl) to read the new event fields from the EDR data and write the fields to the files that are loaded by SQL Loader. You can follow the steps used for /event/session/telco/gsm in the pin_rel_preprocess_cdr.pl file as a guide.

  5. Create a new RE Loader directory corresponding to the pipeline output directory. See "Setting Up RE Loader Processing Directories".

  6. Add the following entries to the RE Loader Infranet.properties file in each directory:

    • The new event type

    • A new service record type corresponding to the new event type

    • The new control file that loads the new event

    • The new event tables that hold the new event type

    For information, see "Configuring the RE Loader Infranet.properties File" and the Infranet.properties file.

  7. If you are running RE Loader automatically, you must also do the following:

    1. Configure the RE Loader batch handler in the new directory to load the new event type. See "Configuring the RE Loader Batch Handler".

    2. Add entries for the new RE Loader batch handler in the Batch Controller configuration file. See "Handler Identification" in BRM System Administrator's Guide.

Creating Custom Error Codes

You can create custom error codes for RE Loader scripts and utilities by using the RE Loader CustomErrorCodes.properties file. You use this file to list your custom error codes and messages. All entries should follow the FQEC scheme and be grouped with the correct component. For more information, see "Checking the RE Loader Log Files for Error Codes".

To create custom error codes:

  1. Modify the RE Loader script or utility to report the error. For more information, see the comments in the appropriate script or utility.

  2. Open the BRM_Home/apps/pin_rel/CustomErrorCodes.properties file in a text editor.

  3. Add your custom error code to the file, making sure you use a minor code in the customer-reserved range.

    For example, the following entry creates a custom error code for the load utility:

    5100 = Sample load utility error message for a custom return code of 100.
    
  4. Save and close the file.

Retrieving Data About Events You Load

BRM stores information about events loaded by RE Loader in a /batch/rel object. This object contains the input file name, number of records loaded, and other session information. To display the event object, use Event Browser.

Note:

If you use multiple database schemas, the /batch/rel object is created in the schema specified in the RE Loader Infranet.properties file.