In a development system with appropriate database rights, you can expose a (top-level Single Value) property as a database column, and have the system populate the newly exposed column automatically, using the Property Optimizer tool. When appropriate and available, this provides a convenient and recommended alternative to the command-line-based Column Populator utility. See About the Property Optimizer tool.
You can use the Database Column Populator utility to extract a property value from the Storage Stream or pzInskey (BLOB) column into a newly added table column. Typically, use this utility immediately after using the Modify Schema wizard to expose a property as a column. This utility can also rebuild Index- instances defined by Declare Index rules.
The Column Populator is an alternative to the Property Optimization wizard and the Modify Schema wizard.
You run this utility by calling the expose
option of the prpcutils
command-line utility. This utility can operate when Pega 7 Platform is not running. For an example, see the PDN article How to use the Column Populator utility.
If you expose a property and then populate the new column in a development system, don't forget to repeat this processing in the corresponding test and production systems, if such systems exist.
Caution: You can run the Database Column Populator utility when Pega 7 Platform is in use. However, the utility does not acquire a database lock on the rows it modifies. If a requestor updates an object at the same time that the Database Column Populator updates the corresponding database row, one of the updates might be overwritten and lost.
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 populator to copy the value for each existing row from the Storage Stream into the new column.
The utility can also rebuild database indexes, and Index- instances defined through Declare Index rules that are related to the database table.
Note: 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.
Refer to the How to use the Column Populator utlity article on the PDN for more information.
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 Schema tool to see the 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.
Before running the prpcutils script, you must complete the following prerequisites:
To run the column populator command-line option:
scripts/utils
subdirectory../prpcUtils.sh expose
Property |
Description |
pega.jdbc.driver.jar |
The full path to the JDBC driver file for your database Oracle 10g/11g: DB/2 UDB: db2jcc.jar (universal driver) SQL Server 2005,2008: |
pega.jdbc.driver.class |
The class name of the JDBC driver for your database: Oracle 10g/11g: oracle.jdbc.OracleDriver |
pega.database.type |
The type of database: Oracle 10g/11g: oracledate |
pega.jdbc.url |
The connection URL for your database, for example: Oracle 10g/11g (Native OCI Client): jdbc:oracle:oci:@your_tns_name Be sure to include the SelectMethod and SendStringParametersAsUnicode parameters for SQL Server. |
pega.jdbc.username pega.jdbc.password |
The user name and password to use when connecting to your database |
Complete the following properties to specify how the utility assembles a list classes to be processed.
Property |
Description |
expose.classes.included |
Optional. List one or more concrete classes to be processed. Separate classes with commas. |
expose.included.descendent |
Optional. True or False. If true and a listed class has concrete subclasses (based on pattern inheritance), the subclasses are automatically also added to the list of classes to be processed. |
expose.classes.excluded |
Optional. If you entered one or more classes in the expose.classes.included property, you can exclude specific subclasses by listing them here. Only instances of the exact class listed are excluded. For example, if you included Rule- classes with descendants you can use this property to filter specific classes. |
expose.excluded.descendent |
Optional. True or False. If true and a class listed to be excluded has concrete subclasses (based on pattern inheritance), the subclasses are automatically also added to the list of classes to be excluded. |
expose.reindex |
True or False. Set to true to cause regeneration of Index- instances derived from the instances specified, based on Declare Index rules. |
expose.reindexType |
Set to limit which rulesets are to be processed:
In most cases, enter nonpega. You do not need to process Pega rulesets unless you are instructed to process them by Global Customer Support. |
expose.startKey |
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, in double quotes. 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. |
expose.endKey |
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, enclosed in double quotes. 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. |
expose.startDate |
Optional. You can further restrict which instances are processed based on creation date. Enter a start date value in YYYYMMDD format. Leave this blank if the Starting pzInsKey or Ending pzInsKey fields are not blank. |
expose.endDate |
Optional. You can further restrict which instances are processed based on creation date. Enter an end date value in YYYYMMDD format. The end date cannot be the same as the start date: Ideally, leave at least a one day gap between the dates. Leave this blank if the Starting pzInsKey or Ending pzInsKey fields are not blank. |
expose.declareIndex.accessgroup | Specifies the access group in which the Rule-Declare-Index of the class being exposed exists. This is used when pega.username or pega.password is not provided. |
expose.commitRate | Specifies how often to commit to the database. |
expose.numOfThreads | Specifies the number of threads to use. The default is the number of classes being updated if not value is specified. When updating large sets of data, set to a higher value. |
The expose.startDate and expose.endDate must have a minimum of a one day gap between them and use the following format: YYMMDD. For example:
expose.startDate=20151014
expose.endDate=20151016
The pzInsKey range values limit the number of rows returned using a character sort and not a numerical one. If you need to process a set of work objects that have similar pzInsKey numerical values, then it is recommended that you use date ranges for limiting the range of rows to return.
For example, a 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:
To process only rodents and trees:
To process all vertebrates except amphibians:
After you specify the classes to be processed and other parameters, the column populator utility
A single database table may contain rows from two or more concrete classes. The value in the pxObjClass column identifies the Pega 7 Platform class of a row.
Rows corresponding to instances of classes not on the list are unaffected by this utility.