|
|
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 PRPC is not running. For an example, see PDN article 26168 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.
You can run the Database Column Populator utility when PRPC is in use. However, the utility does not acquire a database lock on the rows it modifies. If a PRPC requestor updates an object at the same time the Database Column Populator also updates the corresponding database row, one of the updates may be overwritten and lost.
PurposeUse 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.
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.
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, 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 PRPC 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 .
- 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.
- You must have the JDBC driver JAR file for your target database available.
- 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.
- Extract the contents of the PRPC software distribution ZIP file, PRPC_DistributionImage.zip, into a directory.
- Open the command-line console for your platform and change directory into the scripts/utils subdirectory.
- Edit the file to provide the database connection information.
- In the prpcUtils.properties file, find the section SETTINGS FOR EXPOSE TOOL and set the column populator parameters.
- 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: 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
|
| pega.jdbc.username pega.jdbc.password | The user name and password to use when connecting to your database |
Be sure to include the SelectMethod and SendStringParametersAsUnicode parameters for SQL Server.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:
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. 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. 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 full literal starting 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 a full literal starting Leave this blank if the Starting pzInsKey or Ending pzInsKey fields are not blank. |
For example, a PRPC 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 for the expose.classes.included property.
- Enter True for expose.included.descendent
- Leave other fields blank.
To process only rodents and trees:
- Type Data-Kingdom-Animal-Vertebrates-Mammals and Data-Kingdom-Plants-Trees in the expose.classes.included property. Separate the two entries with a comma.
- Enter True for expose.included.descendent
- Leave other fields blank.
To process all vertebrates except amphibians:
- Type Data-Kingdom-Animal-Vertebrates in the expose.classes.included property.
- Enter True for expose.included.descendent
- Type Data-Kingdom-Animal-Vertebrates-Amphibians- in the expose.classes.excluded field.
- Leave other fields blank.
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 PRPC 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 Schema tool |