You are here: Reference > Tools, accelerators, and wizards > Column Populator batch utility

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

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.

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 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.

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 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:

  1. You must have a JDK installed (Java 5 or later), and the path to that JDK must be defined in a JAVA_HOME environment variable.
  2. You must have the JDBC driver JAR file for your target database available.
  3. You must be able to provide connection information for the target database to the script, including the location of the driver file, the driver class name, the database connection URL and the user's username and password.

Running the Column Populator

To run the column populator command-line option:

  1. Extract the contents of the Pega 7 Platform distribution file, PRPC_DistributionImage.zip, into a directory.
  2. Open the command-line console for your platform and change directory to the scripts/utils subdirectory.
  3. Edit the file to provide the database connection information.
  4. In the prpcUtils.properties file, find the section SETTINGS FOR EXPOSE TOOL and set the column populator parameters.
  5. Save the properties file and run the appropriate utilities script for your platform, prpcUtils.bat for Windows or prpcUtils.sh for Unix systems, with the expose option. For example:

    ./prpcUtils.sh expose

Setting the Database Properties

Property

Description
pega.jdbc.driver.jar

The full path to the JDBC driver file for your database

Oracle 10g/11g:
For Java 5: ojdbc5.jar
For Java 6: ojdbc6.jar

DB/2 UDB: db2jcc.jar (universal driver)

SQL Server 2005,2008:
For Java 5: sqljdbc.jar
For Java 6: sqljdbc4.jar
pega.jdbc.driver.class

The class name of the JDBC driver for your database:

Oracle 10g/11g: oracle.jdbc.OracleDriver
IBM DB/2 Type 4: com.ibm.db2.jcc.DB2Driver
SQL Server 2005, 2008: com.microsoft.sqlserver.jdbc.SQLServerDriver
pega.database.type

The type of database:

Oracle 10g/11g: oracledate
DB/2 UDB: udb
SQL Server 2005, 2008: mssql
pega.jdbc.url

The connection URL for your database, for example:

Oracle 10g/11g (Native OCI Client): jdbc:oracle:oci:@your_tns_name

Oracle 10g/11g (Thin Client): jdbc:oracle:thin:@myServer:1521:myDatabase

DB/2 UDB: jdbc:db2://serverName:port/dbName

SQL Server 2005, 2008:
jdbc:sqlserver:// <your_sql_server_host>:1433; databaseName=<your-DBname>SelectMethod=cursor; SendStringParametersAsUnicode=false;

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

Setting the Column Populator Properties

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:

  • full — process Pega and non-Pega rulesets
  • pega — process only Pega rulesets
  • nonpega — process only non-Pega rulesets

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.

Date Settings

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

Range Settings

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.

Examples

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:

Results

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

  1. Assembles a list of concrete classes to be processed.
  2. Determines which database tables contain the rows corresponding to instances of these classes.
  3. 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 Pega 7 Platform class of a row.

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

Related Topics Link IconRelated Information

Open topic with navigation