Skip to main content


         This documentation site is for previous versions. Visit our new documentation site for current releases.      
 

Database tables and views

Updated on May 20, 2021

You can make changes to database tables to support your Pega Platform needs. Because Data-Admin-DB-Table and Data-Admin-DB-Name instances control how the Pega Platform finds objects within the Pega Platform database, changes to the database table structure must be coordinated with changes to these data instances.

Note: Database accesses that support service rules and escalation depend on the current date and time setting on the database server. Make sure that the date and time of the database server are synchronized with the date and time on the application server node or nodes.

The Pega Platform relational database holds the rules, data instances, work items, history, and other concrete objects from the internal classes of your Pega Platform system. External classes, by definition, correspond to tables in other databases and are not part of the Pega Platform database.

Tables and views in the Pega Platform database become visible when you use Database Table instances ( Data-Admin-DB-Table ). For example, the Data-Admin-Operator-ID class defines users and their organization. The property pyUserName in that class can contain as its value a text string for a user name. When your system is first installed, instances of the Data-Admin-Operator-ID class are saved as rows in the pr_operators database table, which are identified in the database table instance named Data-Admin-Operator-ID. The column named pyUserName contains the text string for the name.

Your applications can use report definition rules or the Obj-Browse method to generate SQL statements that search the database and generate reports. For security and performance reasons, the Pega Platform uses prepared statements to access the Pega Platform database. The SELECT, WHEN, and other parts of the SQL statements are parameters of the prepared statements.

Understanding the default database tables

The Pega Platform contains database table ( Data-Admin-DB-Table ) data instances when it is initially installed. These instances are linked to various database tables and views in the Pega database, and are used to map classes or class groups to database tables or views.

The following are the default database tables in Pega Platform.

Assignment tables

Assignments are instances of a concrete class derived from the Assign- base class. A newly installed system does not contain any assignments. As work items progress, the system creates and deletes assignments. Assignments may exist only for a few seconds, or for days or months.

Assignment instances are created when a flow execution reaches an Assignment shape in a flow. Assignment instances are deleted when a user or agent or other processing responds to the assignment by recording information, making and recording decisions, and adding attachments, or otherwise updating the work item, allowing the flow execution to continue.

As initially configured during installation, three tables in the Pega Platform database contain rows that correspond to assignments:

  • pc_assign_workbasket — Holds assignments in work queues, instances of the Assign-Workbasket class.
  • pc_assign_worklist — Holds assignments on worklists ( Assign-Worklist class).
  • pr_assign — Holds external assignments ( Assign-External class), that use the directed Web access feature and assignments of all other types.

Assignments in work queues

Assignments routed to work queues are instances of the Assign-Workbasket class. In the initial Pega Platform database schema, this class is linked to the pc_assign_workbasket table.

Assignments on worklists

Assignments on operator worklists are instances of the Assign-Worklist class. In the initial Pega Platform database schema, this class is linked to the pc_assign_worklist table.

External assignments

External assignments are stored as rows of the pc_assign_worklist table, which also holds rows for assignments on worklists. The key structure of these two types of rows is identical, except that the pxObjClass value for external assignments is ASSIGN-EXTERNAL.

Other assignments

Assignments in the Assign-Corr and Assign-Service classes are stored as rows in the pr_assign table:

  • Assign-Corr assignments identify correspondence (including email) created but not yet sent.
  • Assign-Service assignments are produced when a flow execution reaches the Assign-Service shape, and identifies tasks awaiting completion by an external system.

Case tables

Cases are the primary units of processing in applications that use flows. A large fraction of database processing involves creating and updating cases. Updates to related objects, such as assignments and attachments, usually also require updates to the case.

As configured during installation, Pega Platform saves cases—instances of concrete classes derived from the Work- base class—in the pc_work table, except as described in this topic.

Class groups and work pools

A work pool, defined by a class group instance, causes all cases for an application to be stored in a single shared database table. This sharing is helpful for searching and reporting on cases. With appropriate safeguards, this also allows a case to be reclassified from one case type to another (when both are in the same work pool). Such reclassification can be a typical, normal aspect of the application workflow, or can indicate that the case was initially created with the wrong case type.

In development systems (only), the system can automatically create a database table (and associated Data-Admin-DB-Table data instance) each time you create a work pool, directly from the class form or indirectly using a wizard or tool. The structure of the new table is similar to the initial structure of the pc_work table.

pc_work table

The pc_work table demonstrates work pools, and serves as a default table for cases. As initially configured during installation, the system saves work items from the PegaSample application and from certain developer tools in the pc_work table.

As a best practice, use a dedicated table for cases for each application.

If your system hosts two or more unrelated applications each with one or more work pools, store work items for each application in a dedicated table, using the structure of the pc_work table. A dedicated database table is recommended because:

  • It improves report performance (based on SQL SELECT operations)
  • It makes insert operations faster when new cases are created

In addition, different applications might need different properties exposed as database columns

If you create the applications using the New Application wizard, each work pool has a dedicated table named pc_ZZZZZ_Work, which is a copy of the pc_work table, and you do not have to segregate cases into a dedicated table.

If your pc_work table contains cases from two or more unrelated applications, segregate cases into a dedicated table.

PEGA0041 alert

If an appropriate data table mapping is undefined for a work type (or an entire work pool), the system saves the work item in the pr_other table. This is undesirable except in rare cases where only a few rarely searched work items are expected. To notify developers or system administrators that work items are saved in that table, the system creates a PEGA0041 alert.

Note: Local data storage replaced data tables.

Data object tables

The initial Pega Platform database schema has database tables that contain rows corresponding to instances of concrete Data- classes.

Operator ID

The pr_operators table contains rows that correspond to instances of the Data-Admin-Operator-ID class.

As a best practice, retain Operator ID records permanently. Do not delete rows that correspond to former operators. You can change the operator password to prevent anyone from logging on, and can mark the operator as unavailable for work.

Caution: The Storage Stream column ( pzPVStream ) of this table contains user passwords in hashed form. For maximum security of your system, do not alter the schema for this table to expose this property as a column.

Listener and related data instances

The pi_data_connect table is associated with the Data-Admin-Connect- class. Instances of the classes derived from this class support service listeners and related interface data.

For example, JMS listeners are instances of the Data-Admin-Connect-JMSListener class.

Administrative data instances

The pr_data_admin table holds instances of classes derived from the Data-Admin- class, excluding those explicitly mapped to other tables:

  • Operators
  • Instances of classes derived from Data-Admin-Connect-
  • Instances of classes derived from Data-Admin-DB-.

For example instances of the Data-Admin-System-Settings class are mapped to the pr_data_admin table.

Data-UniqueID instances

The pc_data_uniqueid table corresponds to the Data-UniqueID class, and contains a single row for each work item ID format in use. This row records the most recently assigned work item ID of that format, usually computed by a stored procedure.

For example, if the most recent purchase order is PO-4253, then when the next purchase order is entered, the value of the pyLastReservedID property increments to 4254.

Work item attachments

Attachments to work items are instances of concrete classes derived from the Data-WorkAttach- class. In the initial Pega Platform database schema, this class is linked to the pc_data_workattach table. (However, the attachment itself is included for content or Enterprise Content Managment (ECM) attachments, which are saved in an external document repository and are accessed using Connect CMIS rules.)

Instances of the Data-WorkAttach-File class may contain an uploaded file of any type, up to 1 Gigabyte in initial size, which is converted to text, if necessary, using Base64 encoding. Instances of the Data-WorkAttach-ScanDocument and Data-WorkAttach-ScreenShot classes both contain images, which are also converted using Base64 encoding.

Database, database table, and class group instances

Instances of these three Data- classes are associated with the pr4_base table:

  • Data-Admin-DB-Table
  • Data-Admin-DB-Name
  • Data-Admin-DB-ClassGroup

These are known as foundation classes.

Automated Unit Testing results

Instances of two Data- classes that support the use of features of Automated Unit Testing are associated with the pr_data_autotest table:

  • Data-AutoTest-Result-Case
  • Data-AutoTest-Result-Suite

See Working with automated unit tests.

Content Images

Instances of the Data-Content-Image class are associated with the pr_data_file table. See About Image Content data instances.

Other data instances

When you create a data table by using the Data Table wizard, you can optionally cause instances of the data table class to be stored in a new database table. This choice simplifies future migration of the contents of the database table if necessary, and can improve performance and facilitate reporting on data table contents. Typically, the data table class is derived from the Data- base class and the new table is named pr_zzzzz, where zzzzz is the class name.

Note: In Pega 7.1.7 and later, local data storage replaced data tables.

The pr_data table holds instances of any concrete class derived from the Data- base class not covered by the tables listed in this topic. For example, (as initially configured during installation) the Data-Party-Com, Data-Party-Person and Data-Corr-Email classes map to this table.

The pr_data table has only a few exposed columns. As a best practice for good performance, configure your Pega Platform database so that classes with a high volume of saved instances or activities are not mapped to this table.

A few Data- classes (for example Data-RuleSearch and Data-Work-Summary ) are mapped to the pr_data table but do not correspond to any rows of that table. These classes are the Applies To class of specialized reports that access various database tables using custom getContent activities. Running the reports does not access the pr_data table.

History tables

The Pega Platform database tables that initially contain history instances are insert-only tables. New rows are added but existing rows are not ordinarily deleted or updated.

Work item history

Normal processing of a work item using one or more flow executions produces work item history instances. They document who updated the work item, 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 item history.

These records support auditing and the detailed history display that is typically available by clicking History on user 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 Pega Platform database.

A database view in the Pega Platform database provides an alternate means for reporting on work history. See Database views.

If a Data-Admin-DB-Table instance for a work item history class does not exist, the system saves the history instances in the pr_history table by default. This does not provide the best performance.

Data object history

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

These records support auditing needs and the detailed history display available on 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.

When a rule is deleted, a row is appended 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.

Rule history

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

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

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

Other history instances

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 a few exposed columns. As a best practice for good performance, configure your Pega Platform database so that classes with a high volume of saved instances or activity are not mapped to this table. Ensure that this table does not contain history instances for work items or data objects. Some applications might have thousands or millions of history records for high-turnover classes such as interest rates. As a best practice, create 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.

Index tables

Instances of concrete classes derived from the Index- base class support reporting and rapid data access. Such instances are created and deleted by Declare Index rules. For example, the pr-index-assignmentdeps contains rows that correspond to instances of the Index-AssignmentDep class, which supports dependent assignment processing.

Most Pega Platform database tables that support indexes have table names that start with pr_index or pc_index.

Tables that contain rows automatically created and deleted by rules of type Rule-Declare-Index are different from database indexes defined by SQL that is maintained within the database by Oracle or other database software. Both table types support alternate key access to other instances (rows), but the two indexing facilities are independent and unrelated.

Log tables

The following Pega Platform database tables can store instances of Log- classes. For example, the pr_changelog table holds instances of the concrete classes derived from Log-System-. They record schema changes made using the Property Optimization tool and the Modify Schema tab.

The key to these instances contains a date and time in UTC format.

The Pega-RULES agent normally runs the standard Code-.SystemCleaner activity once each day for housekeeping tasks, including purging older records (instances) from certain log tables.

pr_other table

The pr_other table is the default destination for saved instances that belong to a class that is not mapped to any other table. To access the pr_other table, the system uses the catalog and schema specified for the pr4_base table as identified in the databases/baseTable/catalog and databases/baseTable/schema entries in the prconfig.xml file or dynamic system settings.

Each time an object is saved to the pr_other table, the system adds a PEGA0041 alert to the alert log.

Caution: This table is convenient during development, 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.

Using pr_other to store data for an application under development does not by itself indicate an error in the database design or operations. However, using the pr_other table is undesirable if your application frequently searches rows of this table. In this case, define a new table in the Pega Platform database to hold instances of the class, and add a database table instance to map instances to the new table map saved instances to an existing table.

Rules tables

Rule instances in the Pega Platform database are heavily indexed to speed the searching operations used in rule resolution.

Custom rules types

If you create a custom rule type you can also create a dedicated database table to hold the rules and the associated triggers that the table requires. This table eliminates the risk that future Pegasystems upgrades to the standard database tables listed above might interfere with the exposed columns and other details needed by the custom rule type. However, this also means that after an upgrade you might have to take extra steps to ensure that your rule table and its associated triggers are correct and up to date. In most cases, it is easier to use an existing rule table and, if necessary, optimize a few extra columns to expose the data your rule type needs.

System tables

In the initial Pega Platform database schema, certain database tables contain instances of concrete classes derived from the System- base class.

Caution: Do not alter the mapping, schema, or structure of these tables unless advised by Pegasystems Pega Support.

Work item history types

As a work item progresses through one or more flows in your system, the system automatically appends instances to the work item history, recording the date, time, operator, and other facts about the progress. The history type of a history instance for a work item identifies the event that corresponds to the instance.

Work item history is stored as an instance of a concrete class derived from the History-Work- class. The history type is recorded as a single letter value in the pyHistoryType property. This property has one of the following values:

  • A — A user begins performing an assignment.
  • F — A user completed and submitted a flow action, completing an assignment.
  • L — A user completed and submitted a local flow action (other than a transfer), so the assignment remains open.
  • M — A user added a memo to history.
  • R — A user completed the transfer of an assignment to a different worklist or work queue.

The Operator ID of the operator who creates or updates a work item is identified in the History-Work- objects as the performer ( pyPerformer property). For flow steps that are completed automatically, the performer is identified in work item history as System.

Standard reports based on the analysis of work item history are available.

Work item reports

Standard reports on work items are based on the exposed columns in the schema of the pc_work table for work items, and the pc_history_work table for history instances for work items.

Work items are stored in the pc_work table, and work item history instances are stored in the pc_history_work table. This relationship is defined by the two database table instances Work- and History-Work-.

When you create new work types and corresponding work history classes using the Application Accelerator, the system maps the new work classes appropriately into these two tables.

However, when you create a concrete class by using the Class form, the default mapping is to the pr_other table (and the pr_history table, for history). These mappings are not appropriate for work items and may cause SQL errors or JavaScript failures. You can remedy this by adding the two appropriate database table instances.

Two standard database views are linked to the Data-Assignment-Summary and Data-Assignment-WBSummary classes. These support custom worklist and assignment reporting. Update these view definitions if you use these views and you change or copy the pc_work table.

Understanding database views

Assignment views

Assignment views in the Pega Platform database support reporting on work items with open assignments. A single work item might have more than one assignment outstanding if it is part of multiple simultaneous flow executions.

History views

The pcv4_work_history table in the Pega Platform database is associated with the Data-Work-History class, and provides a consolidated way to report on work item history across all applications and work pools.

Rules views

Rules views in the Pega Platform database support searching and reporting on rules.

Creating database tables

Create database table data instances to map classes or class groups to database tables or views. You can use the Database Table form to revise existing class-to-table relationships.

Note: The local data storage format replaced data tables. You can convert existing data tables on the Data Table landing page, which you can access by clicking ConfigureData ModelData Tables. For more information, see Creating local data storage.

Fields and controls

The following table describes the available options for relational databases on the Database Table form.

FieldDescription
DatabaseIdentify a database instance that corresponds to the database containing the table or view.
Reports Database Optional. Identify a database instance that contains a copy of this table, replicated through database software.

Complete this field only if a database administrator has created a mirrored replica of all or part of the PegaRULES database that is sufficient to support reporting needs, and established a replication process. To reduce the performance impact of report generation, you can specify that some or all reports obtain data from the reports database.

The sources for a report cannot span multiple databases. If a report definition presents data from multiple tables, all required tables must be in one database. This database can be either the PegaRULES database or a single reports database.

Catalog Name

Optional. Identify the database catalog containing the schema that defines the table or view.

In special situations, a catalog name is needed to fully qualify the table.

Schema Name

Optional. Identify the name of the schema (within the catalog) that defines the table. The schema name is required in some cases, especially if multiple PegaRULES database schemas are hosted in one database instances.

Table Name Enter the name of the specific table that is to hold instances of the specified class or class group.

When allowed by the database account, enter only an unqualified table name. Preferably, the database account converts the unqualified table name to the fully qualified table name.

A few of the database table instances that are created when your system is installed identify database views rather than tables. Views are used only for reporting. By convention, the names of views in the initial PegaRULES database schema start with pwvb4_ or pcv4_.

If you create additional views in the PegaRULES database, you can link to them to a class using a database table instance. The view data then becomes available for reporting.

Test Connectivity After you save this Data Table form, you can test connectivity to the database and table. This test does not alter the database. The test uses information on this form, the associated database data instance, and in some cases, information from the prconfig.xml file, dynamic system settings, or application server JDBC data sources.

The following table describes the available options for NoSQL databases on the Database Table form.

FieldDescription
DatabaseIdentify a database instance that corresponds to the database containing the table or view.
Table nameThis field is displayed for Apache Cassandra databases only. Enter the name of the table in which to store data.
Time-to-Live in seconds (0 = no expiration) Specify the number of elapsed seconds until a NoSQL document expires. The current TTL is applied whenever a document is saved or updated. For example, 25000. If not specified or set to zero, documents will not expire.

For Couchbase databases, valid values are 0 to 20*365*24*60*60.

Note: Changing this value does not affect existing data.
Test Connectivity After you save this Data Table form, you can test connectivity to the database and table. This test does not alter the database. The test uses information on this form, the associated database data instance, and in some cases, information from the prconfig.xml file, dynamic system settings, or application server JDBC data sources.

Viewing database tables and metadata by using the Clipboard tool

Every persistent object in the Pega Platform database has an associated class ( Rule-Obj-Class rule type). The Pega Platform uses a simple algorithm and information in Data-Admin-DB-Table instances to determine which table contains objects of which classes.

When in memory and on the clipboard, objects are known as instances and have an XML-like structure consisting of property names and text property values. These can be reviewed with the Clipboard tool.

When saved into the database, objects become rows of a table, with columns corresponding to scalar properties.

Database foundation classes

Some classes are known as foundation classes in the Pega Platform. These classes must stay in the pr4_base table as initially installed, with the Catalog and Schema fields blank. In addition, never change the database table mappings for these classes.

The classes are:

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

Hybrid data storage model (pzPVStream BLOB)

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

For example, the pzInsKey column is the primary key of every table, which 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.

Properties of many Pega Platform property types, including Text, Identifier, Password, and Decimal, correspond to string (VARCHAR) database data types. Properties of mode Integer, and Double can 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 and has a BLOB (Binary Large Object) data type.

The size of the Storage Stream values can have a significant effect on memory demand, network traffic, and overall performance. To help detect and address issues related to the BLOB size, multiple types of alerts (PEGA0004, PEGA0025, PEGA0039, and PEGA0040) monitor database activity and report exceptional conditions involving Storage Stream values.

You can set up data compression for the BLOB column by using the DeflateStreams setting in the prconfig.xml file or Dynamic System Settings. When enabled, compression and decompression occur on the Pega Platform server using the java.util.ZIP class. See the Pega Community article How to compress BLOB values in the PegaRULES database.

Note: If you have a table that does not need a BLOB column, you can remove it to improve performance. See the Pega Community article How to remove an unneeded BLOB column.

Database table primary key (handle)

The primary key column for almost all Pega Platform database tables is named pzInsKey, which corresponds to the pzInsKey property known as the internal key or handle. This permanent key is generated internally by concatenating other properties in the instance and, for rules, from the date and time the object is first saved. The key uniquely defines an instance.

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.

Note:
  • Because the pzInsKey property often contains a date and time, the system computes a handle value only when the Obj-Save method runs. Before the object is saved, the pzInsKey property is undefined.
  • The key parts defined in the class rule (the Rule-Obj-Class instance) are collectively known as the visible key.
  • When you review a handle for a rule, the creation date and time might differ from the creation date in the History tab of the rule form. The date and time on the History tab are converted to display in the time zone of the server, not GMT.
  • A handle for a rule does not explicitly identify a ruleset name or version value. The most recently created instance might not be the highest version. A relationship between versions and creation dates is not required. For example, an application developer can place a new rule in a lower version.

Example

For example, the following handle identifies an activity named ProcessAssignment that is associated with the Assign- base class. This rule instance was created on April 10, 2016 at 23:02:19 Greenwich Mean Time:

RULE-OBJ-ACTIVITY ASSIGN- PROCESSASSIGNMENT # 20160410T230219.752 GMT

Using handles

The Archive tools use handles as file names for extracted rule instances as XML documents. The number sign (#) replaces the exclamation point (!) in XML file names. Certain queries display rule instances keys as handles.

In an activity, the Obj-Open-by-Handle method uses a handle to bypass rule resolution and open an exactly specified rule instance.

In most activities, use the Obj-Open method to copy a rule from the database to the clipboard. This causes the system to use the current ruleset list and other standard facilities to find and open the most context-appropriate rule.

You can also use the Obj-Open-by-Handle method to open a specific instance even if this instance is not the one found by rule resolution.

Obj-Open-By-Handle method

The handle of an instance is a unique key, in an internal format, assembled by the system that identifies an instance in the PegaRULES database. It may differ from the visible key generally used to identify instances. (Every instance has a unique handle, but more than one instance may share the same visible key.) For rule instances, the handle includes system-generated identifying values (including the create date and time) that make it unique.

Use the Obj-Open-By-Handle method only if you can determine the unique handle that permanently identifies which instance to open. Otherwise, use the Obj-Open method.

Rows of a view (rather than in a table) in the PegaRULES database do not have a handle. This method cannot be used with classes corresponding to views.

When used on a primary page, this method clears any data on the page and reuses its name and position in an embedded Page List.

An external class (defined through the Connector and Metadata wizard ) may not contain one property that can serve as a handle. To support the Obj-Open-by-Handle method with external classes, a substitute string is assembled from the key columns of the table.

Caution: Except in rare cases, identify in the Step Page field the name of a page to be created by this method. If the Step Page field of the step is blank, this method opens the object into the primary page of the current activity, replacing the current contents of the primary page and changing the class of the primary page to match the class of the object. Typically, this is not desirable.

Function rule

You can call the standard function rule pxDoesHandleExist() in the Utilities library to determine whether a specific instance exists in the database, as an alternative to creating an activity that uses the Obj-Open method and tests whether the instance was or was not found. The pxDoesHandleExist function accepts as a single parameter a text value containing the candidate handle, and returns true or false. The clipboard is unchanged. The function execution is faster than using an activity.

Parameters

You define the parameters listed in the following table for the Obj-Open-By-Handle method.

ParameterDescription
InstanceHandleEnter the handle that identifies the instance to be opened as a literal or a property reference. This corresponds to the value of the pzInsKey property for an internal class, and a simulated handle for an external class.

Because the handle is lengthy and not easily displayed, it is often easier to identify it by a property reference, rather than attempt to enter the handle by typing.

For more information, see the section Capturing a handle in a property reference below.

LockSelect this option if you plan to change and save or delete the instance.

Your session cannot lock the object unless your access role includes the ability to update objects of the class.

You cannot lock an object unless the Allow Locking? check box (on the Locking tab of the Class form) for the object class is selected. (If the class is part of a class group, the Allow Locking? check box on the class named for the class group is applicable.)

Caution: Locking an object from an external class prevents updates by other requestors in the Pega Platform system. Locking does not prevent changes to the external database — including changes to the row open in the Pega Platform — by other systems.
ReleaseOnCommitSelect this option to cause a lock to be released when your changes are completed through the Commit method.

In most cases, select this option if you selected the Lock box. In the unusual case that you want to retain the lock after Commit (perhaps because additional changes are expected), your activity can later use the Page-Unlock method to release the lock.

LockInfoPageOptional. As a debugging aid, identify the name of a page to be created by this method to hold the results of the locking attempt when it fails. The page, of class System-Locks, contains the following properties:
  • pxOwnerID — requestor who holds the lock
  • pxExpireDateTime — date and time the lock will go soft
  • pxUpdateOperatorpyUserIdentifier value for the requestor who holds the lock
  • pxLockHandle — text string that defines the lock
If the Obj-Open-By-Handle fails to acquire a lock, this page is created and can be viewed with the Clipboard tool or the Tracer.

By convention, this page is named LockInfo in standard rules. When a lock failure occurs during a flow execution, the flow can display the standard harness Work-.LockInfo (or a similar harness incorporating the standard section System-Locks.LockInfo ) to report the situation. If the lock not acquired is held by the same user in a separate requestor session, the user can click the Release Lock button to relinquish it.

Results

This method accesses the PegaRULES database and finds the unique instance that matches the handle. It copies the object's properties and values into a new clipboard page (identified in the Step Page column, or the activity's primary page if no step page is specified).

If it finds the specified step page, the system clears any data that is on it and re-uses it. If the step page is not found, the system creates it. If the Step Page field is blank, the system clears and reuses the primary page of the activity.

  • If the Lock check box is selected and the requestor has permission to update objects of this class, the system locks the instance.
  • If the LockInfoPage parameter is not blank, the system creates a page of that name and reports results through property values on that page.

Fail and Warn conditions

The following table lists conditions that cause the method status to be set to Fail or Warn.

StatusCause
WarnThe class does not allow locking but the Lock parameter (on the Locking tab of the Class form or in some cases the Class Group form) is selected.
FailThe object is opened to the clipboard but the instance is locked by another requestor.
FailThe Lock parameter is selected but at, runtime, the requestor's access role does not allow the requestor to update objects of that class.
FailThe page of the indicated class that is created is empty except for the pxObjClass property. This occurs when the key supplied does not match the key of an instance in the database.

Avoid overwriting an existing page

You may want an activity to check whether a page of a certain name exists before you open a new page.

In the precondition to the activity step, enter the following:

= @PageExists("myPage", tools)

where myPage is the name of the clipboard page.

You may also want to check whether the page on the clipboard is the same as the one you want. For example, your activity performs an Obj-Open-By-Handle method and the handle is stored in a local variable Local.strHandle. After the PageExists check, perform the following check:

@GetInstanceHandle("myPage", tools) == Local.strHandle

If both checks are true, the page on the clipboard is the same as the one you plan to open, so you can skip the step.

Capturing a handle in a property reference

You can obtain a property reference by creating and running a list report (using the Obj-Browse or Obj-List method) earlier in your activity, before the Obj-Open-By-Handle step.

Enter the name of a list rule (instance of the Rule-Obj-List rule type) in the RuleObjList parameter of a step that uses the Obj-List method.

After you execute that step, the results appear in embedded clipboard pages under the pxResults page. Each instance listed (pxResults(1), pxResults(2), and so on) includes a property pyInsHandle that has the handle as its value.

In some settings, a user selects one element from the results list. Your activity can reference this property in the InstanceHandle parameter for the desired instance.

Checking the method status

The method updates the pxMethodStatus property. For more information, see How to test method results using a transition.

The Activity-Clear-Status method may be useful following an Obj-Open-by-Handle method.

Restrictions

The method cannot be used in a step that involves iteration.

Determining if a handle exists

You can determine whether a handle value exists on the database by using a standard function rule or an activity.

You can call the standard function rule pxDoesHandleExist() in the Utilities library to determine whether a specific instance exists in the database. The pxDoesHandleExist function accepts as a single parameter, a text value containing the candidate handle, and returns true or false. The clipboard is unchanged. The function execution is faster than using an activity as described below.

Alternatively, you can create an activity that uses the Obj-Open method and tests whether the instance was found.

Finding the handle of an object

To find the handle of any Rule- or Data- object that has a corresponding form, open the instance using the form and select Actions > View XML and search for pzInsKey.

To find the handle of other objects:

  1. Open the object on the clipboard.
  2. The pzInsKey property is not visible on the initial display of the Clipboard tool. Click Show XML to see the full contents of the clipboard.
  3. Search for pzInsKey.

Required columns for tables

Every database table that you add to the Pega Platform database must contain these five columns.

  • pxObjClass — The Pega Platform class name, typically VARCHAR2(96 ).
  • pxCreateDateTime — The date, typically DATE. This column has a different name in a few tables.
  • pyLabel — A short description, VARCHAR2(64)
  • pzInsKey — Primary key, always includes the pxObjClass value or a class group name, VARCHAR2 (255) NOT NULL. The pzInskey column is identified as the unique key to the table by using a constraint.
  • pzPVStream — The storage stream ( BLOB).

How database tables are identified at run time

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 hyphens in the class name. For example, for the Rule-Obj-Property-Qualifier class, the four patterns are:

  • Rule-Obj-Property-Qualifier
  • Rule-Obj-Property-
  • Rule-Obj-
  • 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 Pega database table that contains the object as a row.

If the system does not find a table by this search, it uses the pr_other table.

Stored procedures

The Pega Platform database uses stored procedures to ensure database integrity and consistency. Stored procedure names begin with the prefix SPPC_.

Caution: Do not alter stored procedures except as advised by Pegasystems Pega Support.

Related articles

Database tables and viewsModifying a schema by using the Modify Schema wizardPlatform-generated schema changesDatabase data instancesDatabase tables and viewsDatabase tables and viewsSegregating cases into a dedicated tableDatabase tables and viewsDatabase indexesDatabase tables and viewsDatabase tables and viewsSetting up a declarative indexUsing declarative indexesDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsPega-ProCom agentsPega-RULES agentsViewing general database informationDatabase tables and viewsDatabase tables and viewsOptimize a property from the user interfaceDatabase tables and viewsPlatform-generated schema changesDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and viewsDatabase tables and views

Tags

Have a question? Get answers now.

Visit the Support Center to ask questions, engage in discussions, share ideas, and help others.

Did you find this content helpful?

Want to help us improve this content?

We'd prefer it if you saw us at our best.

Pega.com is not optimized for Internet Explorer. For the optimal experience, please use:

Close Deprecation Notice
Contact us