Show
all
The PegaRULES database as initially installed consists of about XXXXX
tables that hold all the rules, data instances, work objects, history,
and other concrete objects from internal classes of your Process
Commander 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.
Related
topics
Basics
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 list view rules, summary view rules, or
the Obj-Browse method to generate SQL statements that search the
database and generate reports for managers or developers. (You can
also use Connect SQL rules to search the database, but this is not
recommended except in unusual situations.)
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 (through normal database
software capabilities) and identifying the mirror as a reports
database in the Database table instances.
For security and performance reasons, Process
Commander 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
Process Commander rules. In such configurations, ensure that
communications between the Process Commander 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 Process
Commander servers, local database replication may be necessary to
achieve good performance. CLINB clinic and SharePoint
1/6/09
Naming
conventions
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 objects, 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.
The initial
database schema
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.
Viewing and
modifying the current database schema
Use the Modify Database 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 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 How to expose a
property as a database table column.
When an existing Process Commander system is
upgraded to a new version, SQL scripts may update the standard tables.
To avoid possible collisions or other issues with future upgrades,
make schema changes (other than simple new columns) only to
copies of the standard tables and views. Adjust table rows and
Data-Admin-DB-Table mappings to reference your copies.
This is especially important for database views, as upgrade processing
may drop view definitions and add revised view definitions.
Tracing database
requests
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.
Process Commander provides multiple tools to help you debug, trace,
and analyze requests from the server to the PegaRULES database:
- After a list view or summary view rule executes, the
pxResults
page contains two properties
pxSQLStatementPre and pxSQLStatementPost.
These contain the SQL statement as executed. The
pxResultCount shows the number of rows returned. Use
the Clipboard tool to see these values.
- The Detail window of the Performance tool contains statistics
for the each user interaction or cumulative statistics since
sign-on. For example, you can see the elapsed time (in seconds)
that this user spent retrieving non-rule-resolved instances from
the database. See Performance Tool —
Detail display.
- The DB Trace facility, accessed from the Performance tool
Summary display, can create a trace of all database requests from
your session. See About the
Performance tool.
- When required, a complete trace of all database requests can be
generated, enabled through the
database/dumpStats
element in the prconfig.xml
file. See About the
Performance tool.
- Through the Alert log, you can find those database operations
that required an unusually long time to process.
Related PDN articles
See the Pega Developer Network article PRKB-26007 About PegaRULES table partitioning.
Help Home