Populating database columns by using the service-enabled Column Populator utility

You can use the prpcServiceUtils tool to expose a property as a database column and have the system populate the newly exposed column automatically, or extract a property value from the Storage Stream or pzInskey (BLOB) column into a newly added table column.

The prpcServiceUtils.bat or prpcServiceUtils.sh script can also rebuild Index- instances that are defined by Declare Index rules.

This improves performance when you have a running instance of the Pega 7 Platform in comparison with performing the same process using the Database Column Populator utility, which requires a connection to the database.

To expose a property as a database column, follow these steps:

1. Configure the common properties in the prpcServiceUtils.properties file.

   Property name	Action
   pega.rest.server.url	Enter the URL for the REST service in the following format: http://<hostname>:<port>/context/PRRestService/tenanthash Include the tenant hash only for multitenant systems.
   pega.rest.username	Enter the operator name on the target system with access to REST services.
   pega.rest.password	Enter the password of the specified operator.
   pega.rest.proxy.host	Optional. Enter the host name of the REST proxy server. Do not use localhost as the host name.
   pega.rest.proxy.port	Optional. Enter the port for the REST proxy server.
   pega.rest.proxy.username	Optional. Enter the operator name on the REST proxy server with import and export access.
   pega.rest.proxy.password	Optional. Enter the password of the REST proxy operator.
   pega.rest.proxy.domain	Optional. Enter the domain of the REST proxy server.
   pega.rest.proxy.workstation	Optional. Enter the workstation ID for the REST proxy server.
   pega.rest.response.type	Enter the REST response type, either xml or json. The default value is json.
   user.temp.dir	Optional: Enter the full path to the temporary directory. Leave this blank to use the default temporary directory.

2. Configure the classes.

   To specify the data to expose, Use the include and exclude classes. For example, if you include Rule- with descendants and exclude Rule-File- with descendants, the Pega 7 Platform processes everything in Rule-, except rules in Rule-File-.

   Classes to include (required)

   Property name	Action
   expose.includedClasses	Enter a comma-separated list of classes to include in the column. If you plan to specify a range of keys from pzInsKey, you must specify one class with no descendants.
   expose.includeDescendents	Optional: Set to true to exclude descendants of included classes.

   Classes to exclude

   Property name	Action
   expose.excludedClasses	Enter a comma-separated list of classes to exclude from the column.
   expose.excludeDescendents	Optional: Set to true to exclude descendants of excluded classes.

3. Optional: Configure the access group.

   Property name	Action
   expose.accessGroup	Enter the access group for the Rule-Declare-Index of the classes being exposed.

   To regenerate indexes, enter an access group or the REST user name and password (prpc.rest.proxy.username and prpc.rest.proxy.password in the common properties section). If you enter both an access group and a user name, the user name takes precedence over the access group.

4. Configure the keys or date ranges to expose.

   Specify the range of pzInsKey or pxCreateDateTime values to expose. If you specify both, the pzInsKey values override the pxCreateDateTime values.

   Property name	Action
   expose.startKey	Enter the minimum value for pzInsKey. The system will export data equal to or greater than this value.
   expose.endKey	Enter the maximum value for pzInsKey. The system will export data equal to or less than this value.
   expose.startDate	Enter the minimum value for pxCreateDateTime. The system will export data equal to or greater than this value.
   expose.endDate	Enter the maximum value for pxCreateDateTime. The system will export data equal to or less than this value.

5. Configure the reindexing behavior.

   Property name	Action
   expose.reindex	Specify whether to regenerate the database. The default is true. To expose property references, set expose.reindex=true. To regenerate indexes, enter an access group or the REST user name and password (prpc.rest.proxy.username and prpc.rest.proxy.password in the common properties section). If you enter both an access group and a user name, the user name takes precedence over the access group.
   expose.reindexType	To maximize performance, process Pega 7 Platform rulesets only when instructed to do so by Pegasystems Global Customer Support. Specify which rulesets to re-index: nonpega: (Default) process only customer rulesets full: process Pega 7 Platform and customer rulesets pega: process only Pega 7 Platform rulesets To maximize performance, process Pega 7 Platform rulesets only when instructed to do so by Pegasystems Global Customer Support.
   expose.commitRate	Specify the number of data instances processed with each database commit to balance memory usage and performance. The default of 100 is sufficient for most environments.
   expose.async	Specify whether to run the expose operation in asynchronous mode and queue the request. Default is true.

6. Save and close the prpcServiceUtils.properties file.

7. Run the prpcServiceUtils.bat or prpcServiceUtils.sh script. For example:

   prpcServiceUtils.bat expose

   For asynchronous operations, the system response includes the job status by job ID.

Open topic with navigation