Working with the PegaRULES database — Table and column basics

When newly installed, Process Commander contains over 400 concrete classes, but the initial PegaRULES database contains only 66 tables. When saving or reading an object as a row in the database, the system uses pattern inheritance based on the class name of the object to find a database table.

Pattern inheritance processing is based on dash characters in the class name. For example, for the Rule-Obj-Property-Qualifier class, the four patterns are:

1. Rule-Obj-Property-Qualifier
2. Rule-Obj-Property-
3. Rule-Obj-
4. Rule-

To fetch an instance of the class Rule-Obj-Property-Qualifier, the system looks for database table instances (Data-Admin-DB-Table class) matching these class names in the above sequence. When a matching database table instance is found, information in that instance identifies the PegaRULES database table that contains the object as a row.

If the system does not find a table by this search, it uses pr_other table, as a last, none-of-the-above choice. For example, to locate an instance of the Data-Thorr-PurchaseOrder-Detail class, the system:

1. First searches for a database table instance named Data-Thorr-PurchaseOrder-Detail.
2. If none is found, the system next searches for a database table instance named Data-Thorr-PurchaseOrder-.
3. If none is found, it searches for Data-Thorr-.
4. If none is found, it searches for Data-.

Testing table mappings

Click the  Test Connectivity   button on the Basic tab of a Class form for any concrete class to determine which table and database that Process Commander uses for instances of that class. This test works for both internal classes — mapped to the PegaRULES database — and external classes, those created by the Connector and Metadata accelerator.

Four classes are known as foundation classes:

• Rule-Obj-Class
• Data-Admin-DB-Table
• Data-Admin-DB-Name
• Data-Admin-DB-ClassGroup

Instances of these four classes are always stored in the pr4_base table (or another table identified in the database/baseTable element of the prconfig.xml file as the base table).

Never change the database table mappings for these four classes. These classes must stay in the pr4_base table as initially installed, with the Catalog and Schema fields blank.

The primary key column for all PegaRULES tables is named pzInsKey; this corresponds to the pzInsKey property known as the internal key or handle. This permanent key is generated internally by concatenating other properties present in the instance and (for rules) from the date and time the object is first saved.

Because the initial portion of the pzInsKey value contains the object class (the value of property pxObjClass), objects of different classes can be stored together in one table with no chance of key collisions. (For classes that are part of a class group, the class group name is used rather than the class name.)

The additional properties that make up the pzInsKey value vary from class to class, as defined in the Basic tab of the Class form for each class. For example, the pzInsKey value for a division named Finance — part of the organization Delta.com — is:

DATA-ADMIN-ORGDIVISION DELTA.COM!FINANCE

Key values contain no lowercase letters. An exclamation point character (!) and octothorpe character (#) appear in some pzInsKey values as separators.

When you create a new table in the PegaRULES database, include a database constraint that identifies the pzInsKey column as the primary key of the table.

All tables contain these columns:

• pzInsKey — Primary key
• pxObjClass — Class
• pxCreateDateTime — Date and time the object was first saved
• pyLabel — Short description (optional, may be null)

The table pr_other is the last resort or default destination for saved instances belonging to a class that is not mapped to any other table by the algorithm described above. To access the pr_other table, the system uses the catalog and schema specified for the pr4_base table (as identified the databases/baseTable/catalog and databases/baseTable/schema elements in the prconfig.xml file).

This table provides a convenience during development activities, as changes to the class structure do not require changes to the database schema. However, use this facility with caution. Because the pr_other table has only a few columns, most properties are stored in the Storage Stream column of this table. Accordingly, accesses to this table — reads, updates, or deletes — are often slower than accesses to the same instance when stored as a row in a "purpose built" table.

Use of pr_other to store data for an application does not by itself indicate an error in database design or operations. However, use of the pr_other table is undesirable if your application requires frequent searching of rows of this table. In such a case, as a good practice, define a new table to hold instances of the class.

You can't use the Modify Database Schema tool to expose a column in the pr_other table, because (normally) no class is mapped to that table. Create a database table for the class, move the instances to that table, create a DB Table instance, then expose columns in the new table.

Most columns in the relational database schema correspond to scalar properties of property mode Single Value. The column name is the same as the property name; these are known as exposed columns.

For example, the pzInsKey column is the primary key of every table. This corresponds to the value of the @baseclass.pzInsKey property, present in every instance of every class.

Similarly, the column named pyStatusWork in the pc_work table corresponds to the property Work-.pyStatusWork, a text value with length of 32 characters or less.

Properties of many Process Commander property types, including Text, Identifier, Password, and Decimal, corresponding to string (VARCHAR) database data types. Properties of mode Integer, Double, and Number may be stored as NUMBER database data types. Techniques for storing DateTime property values differ by database vendor.

Values of properties with a mode other than Single Value (such as mode Page or Page List) are stored in a special column named pzPVStream. This column is known as the Storage Stream. In many databases, this column has a BLOB (Binary Large Object) data type.

Three internal Storage Stream format are supported, known as 4, 5, and 6. The prconfig.xml file setting named database/storageVersion controls which format is used as table rows are added or updated; 6 is the initial and recommended value. Format 6 was introduced with V4.2SP2; it provides full UNICODE character support and best performance. Your system can contain a mix of Storage Streams in each format, as existing ones in an older format are not modified when your Process Commander system is upgraded.

You can set up data compression for this column using the DeflateStreams setting in the prconfig.xml file. When enabled, compression and decompression occur on the Process Commander server and uses the java.util.ZIP class. The Pega Developer Network article PRKB-9850 How to compress BLOB values in the PegaRULES database presents this feature for Version 4; the capability is similar for Version 5 using the prconfig.xml syntax.

In special cases, a table may contain no BLOB column. See the Pega Developer Network article PRKB-9849 How to remove an unneeded Blob column.

You can use any software that allows read-only access to the database (such as Oracle SQL Plus) to count the rows in a table. In addition, you can use the System Management application to summarize rows in a table by class.

1. Select Tools > System Management Application from the Developer portal to start the System Management application.
2. Select any node. Select the Advanced > Database Table Information menu item.
3. Select one radio button corresponding to a database table and click the  List Record Count  button.
4. The response shows each concrete class are mapped to the table that contain one or more saved instances (rows), and the count for each. (If the table is empty, only labels appear with no details.)

Every database table you add to the PegaRULES database must contain these five columns:

• pxObjClass — Process Commander class name, typically VARCHAR2(96)
• pxCreateDateTime — DATE (This column has a different name in a few tables)
• pyLabel — A Short Description VARCHAR2(64)
• pzInsKey — Unique key, always includes the pxObjClass value or a class group name, VARCHAR2 (255) NOT NULL
• pzPVStream — Storage Stream (BLOB)

To ease debugging, Process Commander by default displays extensive details about those SQL operations on the PegaRULES database that fail. In a production setting, such details are not needed by users and also may disclose schema details. To reduce the detail that appears, follow the guidance in Pega Developer Network article PRKB-24110 How to suppress the Show Details link after a Database Exception.