Working with the PegaRULES database - Using the Column Populator utility

Show all

Administrators can use the Database Column Populator utility to extract a property value from the Storage Stream (BLOB) column into a table column. This utility can also rebuild Index- instances defined by Declare Index rules.

This utility operates outside the Developer portal, as an option of the prdbutil servlet. This utility can operate when Process Commander is not running.

A command-line version of this capability is available for populating columns in V5.3+ systems. This version requires the Apache Software Foundation's ANT tool and XML skills. To learn more or to download the tool, search the Pega Exchange area of the Pega Developer Network for "Column Repopulator".

Purpose

Use the Database Column Populator after a change to a table schema to populate column values of a table that contains rows.

For example, you can expose a Single Value property contained within the Storage Stream — the pzPVStream or BLOB column — by adding a new column to the table. Thereafter, the new column will automatically contain values only for rows added or updated later. Use the column opulator to copy the value for each existing row from the Storage Stream into the new column.

The servlet can also rebuild database indexes, and Index- instances defined through Declare Index rules that are related to the database table.

Don't confuse the Database Column Populator — which operates on a database table to expose property values — with the Revalidate and Save tool — which operates on a single rule type or data class to resave instances. See About the bulk Revalidate and Save tool.

Preparations

Before you start the Database Column Populator, identify a PegaRULES database table or tables that are to be processed and all the concrete classes that are mapped to the tables. You can use the Modify Database Schema tool to see the classes, or use the Class Explorer tool to review classes.

The system uses pattern inheritance, defined by segments of the class name ending in a dash character, to map classes to tables when no more specific Database Table instance is defined. For example, if (through a Database Table instance) class MyCo-Finance-Loans maps to a table named Loans, then classes such as MyCo-Finance-Loans-Defaulted are also mapped to Loans.

To examine the class structure on your Process Commander system based on pattern inheritance:

Click the C tab to access the Class Explorer.
Choose Customize, the bottom entry in the selection list.
Set the Hierarchy value to Pattern.
Click Apply .

Starting the Column Populator

Start the Column Populator from the Database Utilities servlet (prdbutil) menu:

1. Enter the following URL into an Internet Explorer session:

http://node:port/prdbutil/FirstSteps/FirstSteps.jsp

where

node is the server hosting Process Commander
port is the TCP/IP port for the application server
prweb is the application context

2. Locate the Utilities area in the left panel. Click to open the list of utilities. Click the Populate Exposed Columns link. The form appears:

Expose Column

Complete input fields to specify how the servlet assembles a list classes to be processed. Enter only one class per line.

Then click Expose to begin processing.

This utility does not update the Lucene indexes that support Rule Content searching. As a best practice, rebuild them after Column Populator processing completes, by using the Administration > Index Management menu item in the System Management application. You don't need to rebuild the indexes for Work- or Data- classes.

Field	Description
Column Name	Optional. Specify one or more columns to expose. Leave blank to populate all columns from the BLOB data.
Include classes	Optional. List one or more concrete classes to be processed. If a class listed has concrete subclasses (based on pattern inheritance), the subclasses are not also automatically added to the list of classes to be processed.
Include classes with descendants	Optional. List one or more classes to be processed, one per line. If a class listed has concrete subclasses (based on pattern inheritance), the subclasses are automatically also added to the list of classes to be processed.
Exclude classes	Optional. If you entered one or more classes in the Include classes with descendants text area, you can exclude specific subclasses by listing them here. Only instances of the exact class listed are excluded.
Exclude classes with descendants	Optional. If you entered one or more classes in the Include classes with descendants text area, you can exclude subclasses by listing them here. If a class listed has concrete subclasses (based on pattern inheritance), the subclasses are automatically also added to the list of classes to be excluded.
Reindex instances	Select to cause regeneration of Index- instances derived from the instances specified, based on Declare Index rules.
Starting pzInsKey	Optional. You can further restrict which instances are processed using the handle or an initial portion of a handle. Enter the lowest pzInsKey value to be processed. If you provide a pzInsKey range, specify only a single class in the Include classes field, and leave the other include/exclude class fields blank.
Ending pzInsKey	Optional. You can further restrict which instances are processed using the handle or an initial portion of a handle. Enter the highest pzInsKey value to be processed. If you provide a pzInsKey range, specify only a single class in the Include classes field, and leave the other include/exclude class fields blank.
Starting pxCreateDateTime	Optional. You can further restrict which instances are processed based on creation date. Enter a full literal starting `DateTime` value, such as 20060415T115959.123 GMT. Leave this blank if the Starting pzInsKey or Ending pzInsKey fields are not blank.
Ending pxCreateDateTime	Optional. You can further restrict which instances are processed based on creation date. Enter a full literal starting value, such as 20060415T115959.123 GMT. Leave this blank if the Starting pzInsKey or Ending pzInsKey fields are not blank.
Number of rows to update before issuing a database commit	Optional. Enter a positive integer to control how many rows are processed per database commit operation. If left blank, the default value is 1,000.
Number of threads to run	Optional. Enter a positive integer to control how many Java threads execute in the populator. The default is 1 thread. Using more than 1 thread may (or may not) increase throughput, depending on hardware, system workload, and JVM settings.

Examples

For example, a Process Commander system contains the following classes.

Data-Kingdom-
  Data-Kingdom-Animal-
    Data-Kingdom-Animal-Vertebrates
      Data-Kingdom-Animal-Vertebrates-Mammals
        Data-Kingdom-Animal-Vertebrates-Mammals-Rodents
      Data-Kingdom-Animal-Vertebrates-Amphibians-
    Data-Kingdom-Plants-
      Data-Kingdom-Plants-Trees
  Data-Kingdom-Fungi-

(Class names that end in a dash character are abstract and contain no instances — no database rows. However, subclasses of abstract classes may be concrete.)

To process all animals:

Type Data-Kingdom-Animal in the Include Classes with Descendants field.
Leave other fields blank.

To process only rodents and trees:

Type Data-Kingdom-Animal-Vertebrates-Mammals as the first line in the Include Classes field.
Type Data-Kingdom-Plants-Trees as the second line in that field.
Leave other fields blank.

To process all vertebrates except amphibians:

Type Data-Kingdom-Animal-Vertebrates in the Include Classes with Descendants field.
Type Data-Kingdom-Animal-Vertebrates-Mammals-Rodents in the Exclude Classes with Descendents field.
Leave other fields blank.

Results

After you specify the classes to be processed and other parameters, the column populator utility

Assembles a list of concrete classes to be processed.
Determines which database tables contain the rows corresponding to instances of these classes.
Performs an SQL Open statement immediately followed by a SQL Save statement for each row corresponding to those classes.

A single database table may contain rows from two or more concrete classes. The value in the pxObjClass column identifies the Process Commander class of a row.

Rows corresponding to instances of classes not on the list are unaffected by this utility.

	exposed property, Storage Stream, pattern inheritance
	About the Modify Database Schema tool

Working with the PegaRULES database

Working with the PegaRULES database — Using the Database Column Populator utility

Field

Description