Generating Virtual Columns on Event Tables

About Generating Virtual Columns on Event Tables

Oracle Database 12c allows 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 12c. 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 storable classes (/event and its subclasses) in the BRM schema to use virtual columns. You convert event storable 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 storable 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.

After the event storable 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 storable 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 storable 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 "About Adding Virtual Column Support to Your Applications" in BRM Developer's Guide.
See "Creating Control Files for Custom Event Tables in a Virtual Column-Enabled System" in BRM Migrating Accounts to the BRM Database.

Generating Virtual Columns on Event Tables

This section describes how to generate virtual columns in the BRM database for event tables (for the /event storable 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 storable 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:

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.

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 "Converting Nonpartitioned Classes to Partitioned Classes" for more information.
(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:
  1. Stop the BRM system.
  2. 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".

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.

See "Viewing and then Running Virtual-Column Tasks".
After you generate virtual columns, when you want to see the tasks that 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.

See "Viewing Virtual-Column Task Details".

For more information, see "pin_virtual_gen".

Viewing and then Running Virtual-Column Tasks

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 there is an error before the job completes 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.

Viewing Virtual-Column Task Details

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.

Setting Up Virtual Columns for RE Loader

RE Loader populates event tables. After you generate virtual columns on event tables in your BRM installation, you must run the pin_gen_classid_values.pl script. Running the script ensures that the proper mapping of BRM object types and their corresponding object IDs is created for your extended event objects in a virtual column-enabled system.

To set up RE Loader for virtual column-enabled systems:

Go to BRM_home/setup/scripts.
Open the pin_gen_classid_values.pl file and verify that the first line in the file is pointing to the location of Perl in your installation.
Run the Perl script pin_gen_classid_values.pl.

Running the script regenerates the classid_values.txt file that is used by RE Loader. The classid_values.txt file has the mapping of BRM object types (poid_types) and their corresponding object IDs (object_ids).

If you have extended BRM objects and these extended objects are new event subclasses that impact RE Loader, you must create new SQL Loader (sqlldr) control files. In virtual column-enabled systems, the RE Loader sqlldr control files must use the keywords VIRTUAL_CHAR and VIRTUAL_CONSTANT in the section that specifies the data definition of rows and also in the constant section.

Exporting a BRM Schema with Virtual Columns

After you generate virtual columns, if you need to export your BRM schema, you must remove the virtual columns from the schema before the export. In addition, if you need to restore the exported schema, you need to 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 12c 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 need to restore the database by importing the schema from the dump file back to disk, do the following:
1. Stop the BRM system.
2. 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.
3. 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).
4. Start the BRM system.