The PegaRULES database, as initially installed, consists of about 100 tables that hold all the rules, data instances, work items, history, and other concrete objects from internal classes of your Pega 7 Platform system. Views, indexes, and stored procedures support performance and other processing requirements.
By definition, external classes correspond to tables in other databases. These tables are not part of the PegaRULES database.
A database administrator can modify and evolve the PegaRULES database to meet policy, security, performance, and reporting requirements.
Tables and views in the PegaRULES relational database become visible through 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, 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 to generate SQL statements that search the database and generate reports for managers or developers. In a production system, you can reduce the performance impact of report queries on the PegaRULES database by mirroring all or part of the database as a reports database in the Database table instances.
For security and performance reasons, Pega 7 Platform uses prepared statements when accessing the PegaRULES database. The SELECT, WHEN, and other parts of the SQL statements are parameters to the prepared statements.
You can host the PegaRULES database on a separate server from the server (or servers) that execute Pega 7 Platform rules. In such configurations, ensure that communications between the Pega 7 Platform servers and the database server have high bandwidth and low latency. In a wide area network where the database server is remote from one or more Pega 7 Platform servers, local database replication may be necessary to achieve good performance.
When initially installed, objects in the PegaRULES database follow a naming convention, using these prefixes:
pc_ identifies tables containing instances corresponding to Pega-ProCom ruleset capabilities, such as flows, work items, and assignments.pr_ and pr4_ identify tables for activities, properties, requestors, and other features of the rules engine, corresponding to Pega-RULES ruleset capabilities.pvbv4_ identifies views that speed queries and reporting.sppc_ and sppr_ identify stored procedures.trpr4_ identifies database triggers.Additional tables and views defined for custom needs need not follow these naming conventions.
When newly installed, the PegaRULES database contains 35 tables, about 70 indexes, 40 triggers, seven views, and five stored procedures.
A snapshot of the schema as installed is available in HTML format on the installation media. Open the \schema\docs\ subdirectory and display the index.html topic. This shows the mapping of classes to tables:
Click a link in the Table column to see the exposed properties in one table, such as pc_work:
This information does not reflect any changes made to your PegaRULES database schema since the system was installed.
Use the Modify Schema wizard to see the current schema of any table in any database that your system accesses.
If the Database data instance (Data-Admin-DB-Name class) for the PegaRULES database contains the appropriate User ID and password for the database, you can select a Single Value mode property from the pzPVStream column and make it an exposed column. See Property optimization.
Note: Avoid customizing standard views. If you modify standard views and then upgrade the Pega 7 Platform, the upgrade script may overwrite your customizations. Instead, customize copies of the views.
Performance of the PegaRULES database is a major factor in overall system performance, affecting response time for interactive, browser-based users, turnaround time for services, and throughput.
Pega 7 Platform provides multiple tools to help you debug, trace, and analyze requests from the server to the PegaRULES database:
pyReportContentPage page contains two properties pxSQLStatementPre and pxSQLStatementPost. These properties contain the SQL statement that ran. The pxResultCount shows the number of rows returned. Use the Clipboard tool to see these values.database/dumpStats entry in the prconfig.xml file or Dynamic System Settings. See About the Performance tool.