Skip to main content
LinkedIn
Copied!

Recommended database functionality for Pega Cloud Services

This content applies only to Cloud environments.

Pega is constantly evolving our software features to better support our clients.  The Pega Infinity software provides modern and secure methods for handling your database.

The Pega Platform version 8.x is enhanced to provide a number of database tools.  To be compatible with the Pega Cloud Services deployment model, you must make changes to your database using only the Pega Platform database functionality.  These changes will then be included when the Platform is moved to your Pega Cloud environment, and will not be overwritten by standard Cloud database processes.

For details, see Pega Cloud’s Data management services.

Database functionality in Pega Platform

In Pega Platform, access the System Database landing page by clicking Configure > System > Database. The following tabs are available:

Schema Change Tracking

View consolidated information about changes to database schema on this system.

Optimize Schema

View possible database optimizations that could improve system performance, database size, or other resource use.

You must have the ViewAndOptimizeSchema privilege to view this page.

Modify Schema

View and manually modify the database schema by using the Modify Schema Wizard.

Schema Tools

Perform advanced database tuning and maintenance for Pega Cloud applications running on Postgres.   See details below in the Schema Tools section.

Query Inspector

Generate explain plans for Pega Cloud applications running on Postgres.

Query Runner

Test SELECT statements for Pega Cloud applications running on Postgres.

Column Population Jobs

Cancel and view the status of property optimization jobs running in the background. These jobs are created by the Property Optimizer tool, which populates newly exposed database table columns with data values from the BLOB column.

You must have the SchemaPropertyOptimization privilege to access this page.

For details on these commands, see the System Database landing page.

Additional information can be found in the article Schema Tools and Query Inspector landing pages for tuning and maintaining databases in the Pega Cloud.

Schema Tools

The Schema Tools landing page lets you perform advanced database tuning and maintenance tasks including viewing the tables, columns, indexes, and views in the PegaRULES and PegaDATA database schemas, increasing the size of text columns, adding and deleting indexes, updating database statistics for a table, and defragmenting a table.

Viewing database schema information
 

You can view database schema information for tables, columns, views, and indexes. When any of these elements are clicked in the display, relevant information about that element is displayed on the right side of the landing page, along with appropriate actions for updating the element in the database.

Viewing column BLOB size
 

You can view the BLOB size for columns that have a data type of BLOB and if you have the pxDBTableUsage privilege.

Increasing column lengths
 

You can increase the text column lengths on the Schema Tools landing page.

Adding and deleting database indexes
 

You can add and delete indexes on the Schema Tools landing page. These operations can take some time to complete. After initiating an operation, you can view progress and verify completion by refreshing the landing page and clicking the History tab for the table containing the index.

Defragmenting tables
 

You can defragment a database table from the Schema Tools landing page. This operation can take some time to complete. After initiating an operation, you can view progress and verify completion by refreshing the landing page and clicking the History tab for the table.

Updating statistics
 

You can update database statistics for a selected table from the Schema Tools landing page. Updating internal database statistics for a table, such as the number of values in each index, enables the database to create more efficient execution plans for the queries. After initiating this operation, you can view progress and verify completion by refreshing the landing page and clicking the History tab for the table.

Viewing history of Schema Tools actions
 

You can view the history of actions taken on a table from the Schema Tools landing page to see the status of an operation. This information will also be reflected on the Schema Change Tracking tab.

Indexes

There are several types of indexes in Pega Platform software:

Declarative and database indexes are defined in rules, and so will be included in the application RAP.  Declarative indexes are mapped to Index- classes in the database, and are part of your application.  Therefore, you should not need to do any remediation on these rules as part of the migration process.

NOTE:  One exception to this is if you have defined a particular index that takes advantage of functionality specific to one database type, such as Oracle.  Pega Cloud Services runs on a PostgreSQL database, so if you have specific index functionality defined for other database types, you must rewrite that functionality for a PostgreSQL database.

Schema structure

In older versions of PRPC, all rules and data were stored in the same schema in the Pega database.  Starting with Pega 7, Pega recommended that clients use a “split” schema, which included:

  • Rules schema
  • Data schema

This was recommended because rules and data change at different rates – there are many more additions to data than there are rule changes - and have a different number of entries added per time period (weeks, months, quarters).  Thus, storing them in separate schema allows the database to be most efficiently tuned for best performance.

Beginning in version 7.1.8, a split schema became the default. 

Pega Infinity includes several standard database schemas as part of the application:

  • Rules
  • Data
  • Customerdata
  • MKTdata (for the Pega Marketing application)

These are the only schema names permitted in Pega Cloud Services environments.

Connect-SQL rules

Connect SQL rules are used when your application needs to run complex Structured Query Language statements such as joins or stored procedures to update or extract information from an external database.

There are two situations where you might use Connect-SQL rules:

  • Connecting to your own (Pega-managed) database
  • Connecting to external databases

Connecting to the Pega database

For Connect-SQL rules which are operating on the Pega database, you will need to verify that they will run on a Postgres database.  Any Connect-SQL rules for other database types will not work correctly, as SQL differs for each database type.  Oracle SQL is similar to Postgres SQL, and a lot of the Connect-SQL statements might just work, but you should test them all.  Therefore, if you wrote these rules for other database types, you must rewrite them for a Postgres database.  If you originally wrote them for a Postgres database, then they should work fine (but Pega still strongly recommends testing).

Custom schema names are not supported in Pega Cloud.  (See the Custom schema names section of Remediating Legacy Database Structures.)  If you had custom schema names in your on-premises installation, then as part of the migration, you will have to change those names to the Pega schema names (see Schema structure above).  This also affects the Connect-SQL rules – any of your Connect-SQL rules which reference the custom schemas will have to be rewritten to reference the default schema names.

Connecting to external databases

If your Connect-SQL rules are mapped to your data warehouse, or a reference system, or system of record, then your rule should be written for the SQL used by that system.  (If your reference system runs on an Oracle database, the Connect-SQL should be written for Oracle.) 

Pega Cloud remediation:  Verify that the mapping is set up correctly to connect to that database from your new Pega Cloud Services environment.  Since you are connecting out of a secure environment, you must ensure that your rules are correctly configured to use a secure tunnel (such as a VPN, or Direct Connect) from your environment to the on-premises system.

Function Alias rules

A function alias rule has one of two types: Java function or SQL expression.

Java function aliases provide natural-language descriptions and prompting for input parameters to an underlying function rule.  They can be used in collections, decision trees, when condition rules, Declare Expression rules, and constraints rules.

Java functions are supported in Pega Cloud Services, as they are internal rule constructs; they don’t interact with the environment.

SQL expression function aliases provide compact packaging of complex SQL expressions that can be used to query a database, and support the development of report definitions. They are defined in the Embed-UserFunction class.

Pega Cloud Remediation:  As Pega Cloud Services uses a PostgreSQL database, ensure that your SQL expression function aliases are written for the PostgreSQL database.  If your Function Alias rules are written for a different database type, you must rewrite them for PostgreSQL.

Multiple applications sharing the same database schema

If you have multiple applications sharing the same database schema and you plan to migrate them separately (as opposed to migrating everything at once), realize that the following tables are shared between applications running on the same platform:

PR_OTHER

PR_ASSIGN

PC_INDEX_WORKPARTY

PC_ASSIGN_WORKBASKET

PC_ASSIGN_WORKLIST

PC_LINK_ATTACHMENT

PC_HISTORY_WORK

PC_LINK_FOLDER

PR_SYS_QUEUE_SLA

PR_INDEX_ASSIGNMENTSKILLS

PR_INDEX_ASSIGNMENTDEPS

PC_DATA_WORKATTACH

PC_DATA_UNIQUEID

PR_SYS_QUEUES

PC_HISTORY_WORK

PR_HISTORY

PR_DATA

PC_WORK_SOCIAL

PR_DATA_FILE

PR_INDEX_EMAIL

PR_INDEX_OPERATORSKILLS

Pega Cloud remediation:  During the data migration planning process, verify that the data in these tables is mapped to their respective applications.

Suggest Edit
Did you find this content helpful?

Have a question? Get answers now.

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

Ready to crush complexity?

Experience the benefits of Pega Community when you log in.

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

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us