Working with the PegaRULES database — Tables for history

Normal processing of a work object through one or more flow executions produces work object history instances. They document who updated the work object, when the update occurred, the assignment task or other task within the flow, and other facts. Agent processing and other automatic processing also may add to work object history.

These records support auditing needs and the detailed history display that is typically available from the History button () on work object forms.

Normally, all such instances are rows in the pc_history_work table, which contains all classes derived from the History-Work- class. In terms of row count, this table often grows to become one of the largest in the PegaRULES database.

The pzInsKey for these instances is formed by concatenating the value of these properties:

• pxObjClass — Class corresponding to the history of the class group (work pool), such as History-PegaSample for the history of work objects in the PegaSample sample application.
• pxInsName — Formed from pxHistoryForReference, which in turn consists of the class group name, a space, and the work object ID, and the pxTimeCreated property, which records in GMT format the date and time that a history instance was saved.

For example, the pzInsKey value

HISTORY-PEGASAMPLE PEGASAMPLE C-43!20041109T204536.146 GMT

identifies a history record for the work object C-43 in the PegaSample work pool, added on November 9, 2004. The pxHistoryForReference value in this instance is PEGASAMPLE C-43.

A database view in the PegaRULES database provides an alternate means for reporting on work history. See Views and Indexes.

If a Data-Admin-DB-Table instance for a work object history class does not exist, the system by default saves the history instances in the pr_history table. This does not provide best performance. See Pega Developer Network article PRKB-24054 How to migrate work object history records that were incorrectly saved in the pr_history table.

When you save a new or updated instance of an administrative data class, Process Commander creates a corresponding history instance identifying the date, time, and operator who made the change.

These records support auditing needs and the detailed history display available from the History tab.

For example, when a calendar instance is updated — Data-Admin-Calendar class — the history instance is of class History-Data-Admin-Calendar.

Normally, all such instances are rows in the pr_history_data table.

The pzInsKey for these instances is formed by concatenating the value of these properties:

• pxObjClass — Class of the instance, a concrete class derived from the History-Data- class.
• pxHistoryforReference — Class of the instance to which this history instance refers, a concrete class derived from the Data- class, followed by the visible key parts of that instance
• A single exclamation point (!)
• pxTimeCreated — Date and time that the save operation completed.

For example, the pzInsKey value:

HISTORY-DATA-ADMIN-ORGANIZATION DATA-ADMIN-ORGANIZATION FORMCO ! 20051207T144054.968 GMT

identifies an organization data instance named FormCo that was updated on December 7, 2005 at 14:40 GMT.

When a rule is deleted, Process Commander appends a row in the history table, as with other operations. The history rows are not deleted, although the rule that caused them to be recorded is gone.

When a developer saves a new or updated instance of rule, Process Commander creates a corresponding history instance identifying the date, time, and operator who made the change. For example, when a property rule is updated — Rule-Obj-Property class — the history instance is of class History-Rule.

These records support auditing needs, the Restore toolbar button, the Recover toolbar button, and the detailed history display available from the History tab. Normally, all such instances are rows in the pr4_history_rule table.

The pzInsKey for these instances is formed by concatenating the value of these properties:

• pxObjClass — Class of the rule instance in uppercase, for example RULE-OBJ-PROPERTY
• pxHistoryforReference — Class of the instance to which this history instance refers, a concrete class derived from the Rule- class, followed by the visible key parts of that instance
• A single exclamation point (!)
• pxTimeCreated — Date and time that the save operation was committed.

For example, the pzInsKey value:

HISTORY-RULE RULE-OBJ-PROPERTY DELTA-HR-OPS RNAME #20040927T234826.687 GMT!20051109T143059.254 GMT

identifies a change to a property named Rname that applies to the class Delta-HR-Ops. The property rule was created on September 27, 2004, and updated on November 9, 2005.

When a rule is deleted, Process Commander appends a row in the history table, as with other operations. History rows are not deleted, although the rule that caused them to be recorded is gone.

The pr_history table is associated with the History- base class, and holds rows for objects in concrete classes derived from History- other than those in the tables described above. For example, as initially configured during installation, instances recording the history of delegated rules (History-System-User-MyRules class) map to the pr_history table.

The pr_history table has only few exposed columns. As a best practice for performance, evolve your PegaRULES database schema so that no classes with many saved instances or high activity are mapped to the pr_history table. Ensure that this table does not contain history instances for work objects or data objects. Some applications in production may have thousands or millions of history records for high-turnover classes such as interest rates. As a best practice, create new dedicated tables with exposed columns for such classes, or add indexes to eliminate table scans and add exposed columns to avoid retrieving the Storage Stream. FITZI webinar 9/21/06