This chapter describes how to generate virtual columns in the Oracle Communications Billing and Revenue Management (BRM) database. In the current release, virtual columns are generated only for event tables (for the /event class and subclasses).
Oracle Database 11g enables you to create virtual columns on tables. A virtual column is similar to a normal table column but it is defined by an expression. The result of evaluation of this expression becomes the value of the column. A virtual column contains a function upon other table columns. Virtual columns are not physically stored in the table (they are derived from data in the other columns of the table) and their values are computed at run time when you query the data. Being able to create virtual columns is enabled by default in Oracle Database 11g. See the Oracle Database documentation for detailed information about virtual columns.
Implementations of BRM have shown that a high percentage of the BRM database storage space can be used by the event tables. BRM can use virtual columns in a way that results in space savings for event records. To use virtual columns in the BRM database, you convert event classes (/event and its subclasses) in the BRM schema to use virtual columns. You convert event classes to use virtual columns by running the pin_virtual_gen utility (see "Generating Virtual Columns on Event Tables" for instructions). The savings in database storage applies to event data that the system creates after the virtual columns are generated (not to existing event data). Virtual column functionality is transparent to the BRM application.
BRM creates virtual columns on the POID field_name_type columns of event tables in the BRM database. The POID field_name_type columns are the columns that are SQL mappings for the PIN_FLDT_POID type (the POID data type). After the virtual columns are enabled, a new column is added named field_name_type_id that stores the ID mapping for the value in the POID field_name_type column.
For example, these fields within the event classes are candidates for conversion to virtual columns:
PIN_FLD_POID
The poid_type column is converted to a virtual column.
PIN_FLD_ACCOUNT_OBJ
The account_obj_type column is converted to a virtual column.
PIN_FLD_BRAND_OBJ
The brand_obj_type column is converted to a virtual column.
After the event classes have been enabled to have virtual columns, any new event subclass (for example, /event/billing/discount/new) will be virtual-column enabled. Specifically, all PIN_FLDT_POID field_name_type columns within the new event subclass will use virtual columns. Virtual columns cannot be enabled only for a subclass (the base class must be virtual-column enabled).
Note:
If you have a nonpartitioned schema and you want to enable partitions, you must enable the partitions before enabling virtual columns. After you enable virtual columns on a nonpartitioned schema, you cannot enable partitioning.See "Generating Virtual Columns on Event Tables" for instructions on converting event classes in the BRM schema to use virtual columns.
For additional information on how using virtual columns in your BRM database may impact your system, see the following documentation:
See the discussion on creating custom applications in BRM Developer's Guide for information on how to make your custom applications support virtual columns.
See the discussion on Rated Event (RE) Loader in BRM System Administrator's Guide for information on how to set up RE Loader when using a virtual column-enabled system.
See the discussion on the Conversion Manager pin_cmt utility in BRM System Administrator's Guide for information on moving legacy data when using a virtual column-enabled system.
This section describes how to generate virtual columns in the BRM database for event tables (for the /event class and subclasses).
You use the pin_virtual_gen utility to convert a standard BRM database into one with virtual columns. The utility generates virtual columns on event tables for all event classes.
For information about using virtual columns in BRM, see "About Generating Virtual Columns on Event Tables".
For information about pin_virtual_gen syntax, see "pin_virtual_gen".
Before you can generate virtual columns, do the following:
Install BRM 7.5 Patch Set 3.
See BRM Patch Set 3 Installation Guide.
Plan for downtime of your BRM system.
The duration of time for running the utility that generates virtual columns is a downtime for the BRM system.
(Optional) If you have a nonpartitioned event table and you want to enable partitioning, install the 7.5_PartitionUpg_platform_32_opt.bin package and enable partitioning for the event tables.
Important:
If you have a nonpartitioned schema and you want to enable partitions, you must enable the partitions before enabling virtual columns. After you enable virtual columns on a nonpartitioned schema, you cannot enable partitioning.(Optional) The pin_virtual_gen utility uses an Infranet.properties file which is preconfigured with the required values to run it. For information on setting the log level and number of threads for the utility, see "pin_virtual_gen".
To generate virtual columns on event tables:
Go to BRM_home/apps/pin_virtual_columns.
While your BRM system is processing, run the following command.
pin_virtual_gen -gentasks verify_types -execute
The utility checks if you have POID custom type names for your custom storable classes.
This can be a long-running process.
Do one of the following:
If the utility returns a message that it found no invalid object names, stop the BRM system.
If the utility returns a message that it found object names that are missing from the data dictionary, do the following:
Stop the BRM system.
Run the following command:
pin_virtual_gen -gentasks create_types -execute
This command stores the POID custom type names of custom storable class types in the data dictionary of the BRM schema (required for a virtual column-enabled system).
Run the following command:
pin_virtual_gen -gentasks create -execute
This command converts the tables within the /event storable class and its subclasses to use virtual columns.
If the pin_virtual_gen utility is interrupted while running this command, you can run the following command:
pin_virtual_gen -readtasks create -execute
Start the BRM system.
For more information about using the pin_virtual_gen utility, see "pin_virtual_gen" and "Viewing Tasks for Generating Virtual Columns".
When you run the pin_virtual_gen utility, various tasks (jobs) are run to generate the virtual columns. The statuses of the tasks are maintained in the database so if the utility is interrupted, you can restart it without any issue.
Each task has a task ID. You can view the tasks before or after they are run (before or after they have generated virtual columns). You can view task details of all tasks or only tasks within a task ID range.
You may want to view tasks at the following times:
Before you generate virtual columns, when you want to see what is going to happen to your database. By viewing the SQL statements of the tasks, you can see which tables will have virtual columns added to them and which tables will be renamed.
After you generate virtual columns, when you want to see the tasks that are completed. For example, if the pin_virtual_gen utility is interrupted while running, you might want to view the tasks that ran before the interruption.
For more information, see "pin_virtual_gen".
To view virtual-column tasks and then run them:
Run the following command:
pin_virtual_gen -gentasks create
This command generates the tasks and stores them in the database without executing them.
Do one of the following:
To view task details of all tasks, run the following command:
pin_virtual_gen -showtasks
To view task details for tasks within a task ID range, run the following command:
pin_virtual_gen -showtasks [minID maxID]
where you want to see tasks that have an ID greater than minID and less than maxID.
After viewing the tasks, run them by entering the following command:
pin_virtual_gen -readtasks create -execute
This command reads the tasks from the database and runs them.
Note:
If an error occurs before the job finishes after running either the gentasks or the readtasks command (for example, if there is a power outage), run the following command:
pin_virtual_gen -readtasks create -execute
The readtasks command reads the statuses of the various tasks recorded in the database and runs the appropriate tasks.
To view the details of all virtual-column tasks, run the following command:
pin_virtual_gen -showtasks 0 -
To view the details of virtual-column tasks by task ID, run the command:
pin_virtual_gen -showtasks [minID maxID]
where you want to see tasks that have an ID greater than minID and less than maxID.
If you omit minID and maxID, the details of all tasks are displayed.
After you generate virtual columns, if you must export your BRM schema, you must remove the virtual columns from the schema before the export. In addition, if you must restore the exported schema, you must add the virtual columns back after the import.
Note:
Ensure that you use the Oracle Database export and import utilities that support virtual columns. Refer to the Oracle Database 11g documentation for information.To export a BRM schema with virtual columns:
Stop the BRM system.
Go to BRM_home/apps/pin_virtual_columns and run the following command:
pin_virtual_gen -gentasks pre_export -execute
This command removes the virtual columns.
Export the schema using the Oracle Database export utility (for example, run the expdp command).
The BRM schema is now exported into a dump file and has no virtual columns.
Go to BRM_home/apps/pin_virtual_columns and run the following command:
pin_virtual_gen -gentasks post_export -execute
This command restores the virtual columns (the BRM schema virtual columns are again enabled).
Start the BRM system.
If you must restore the database by importing the schema from the dump file back to disk, do the following:
Stop the BRM system.
Import the schema using the Oracle Database import utility (for example, run the impdp command).
The imported schema does not have the virtual columns because you removed them when you exported the schema to the dump file.
Go to BRM_home/apps/pin_virtual_columns and run the following command:
pin_virtual_gen -gentasks post_export -execute
This command restores the virtual columns in your restored database (the BRM schema virtual columns are again enabled).
Start the BRM system.