What could be SQL used for in Pega projects?

SQL usage might help increase of Pega applications performance or investigate data inconsistencies.  If there can be observed warnings generated in logs, it’s worth to review logs and look for potential improvements. Reports are one of the first items which could be analyzed, queries executed behind reports after analysis might be improved by creating SQL indexes. Another aspect is to have “big picture” approach in Pega applications. Once potential issue is spotted one thing is to fix code, however for complex resolution following action should be identification of affected objects for which SQL queries might be very useful.

Information described in this article are validated in Pega Platform version 8.8.

In attachments, you can find a pdf file version which contains some examples and screenshots.

Query runner usage

Pega DEV Studio provides a tool to execute SQL queries. It’s named Query Runner and can be found in the DEV Studio header by selecting Configure -> System -> Database -> Query Runner. However, to be able to see it, the current operator's access group must have been assigned the PegaRULES: DatabaseAdministrator role.

If the access group does not include the mentioned role Query Runner wouldn’t be visible.

For proper execution of queries by Query runner changes need to be done in configuration files context.xml and prconfig.xml by qualified people from the Operations team according to steps described in another support article https://community.pega.com/support/support-articles/error-running-any-query-using-query-runner . It’s out of the scope of the current article, assumption for that is necessary steps were already done.

In the Query to run text area can be provided body of the SQL query to be executed.

The table name of the class for which the query needs to be executed can be found by looking at the database table record. Columns available for a specific table can be checked by finding it in Database Class Mappings, for that it should be navigated at the top of the DEV Studio header Configure - > Data Model -> Classes & Properties -> Database Class Mappings.

Once the class of interest is identified, clicking on the link in Columns (which should show a number of columns in the specific table) should open the window with a list of columns. By knowing what table and column names are SQL query could be created.

To execute the query button Run placed below Query to run text area should be used. Once query execution is finished Results should appear below the query body. It’s possible to Export results to an external Excel or PDF file by using the Export button in the top right corner of the Results area.

Query runner could be very useful for retrieving values of not optimized properties. It’s not possible to do that with Report Definitions. For such purpose three below functions could be used, depending on the type of property.

• pr_read_decimal_from_stream (for decimal properties)
• pr_read_int_from_stream (for integer properties)
• pr_read_from_stream (for text properties)
•  

General syntax of function could be presented as pr_read_from_stream('PropertyPath',TableAlias.pzInsKey,TableAlias.pzPVStream) AS ColumnName

Assuming that value of embedded property pyWorkPage.pxStageHistory(1).pxProcesses(1).pxProcessName should be retrieved.

The line in SQL query for retrieving this embedded property will be   

pr_read_from_stream ('.pxStageHistory(1).pxProcesses(1).pxProcessName',BJ.pzInsKey,BJ.pzPVStream) AS ProcessName

RDB methods

There exist four RDB (Relation Database) methods that make available writing SQL code which then can be executed e.g. in activities by invoke method and setup needed parameters. Methods are:

• RDB-Delete
• RDB-List
• RDB-Open
• RDB-Save

Methods should be used carefully as operate directly on database records. However, it can be useful to take such actions without involving the Operations team.

To be able to use any of the above methods there needs to be a created rule of type Connect SQL which stores SQL code.  Then this Connect SQL rule is used in activity with method RDB-Delete, the class and name of the Connect SQL rule are given.

Thanks to this method instead of looping through records in activity code and adding steps for that just this one step with RDB-Delete is enough.

Explain plan execution and indexes creation

Performance is an important factor in Pega applications. One of the reasons that could impact the environment load could be queries executed when reports are invoked. There is the possibility to run an explain plan for each query which could give insights into potential improvements.

Using the tracer tool, it could be checked what SQL query is executed when the report is running. To figure this out in the tracer settings area Event types, the checkbox for DB Query should be ticked.

Then it should be checked what table is behind the report class. It can be verified from Dev Studio by navigating Configure - > Data Model -> Classes & Properties -> Database Class Mappings.

Once this is known tracer tool should be running then in Dev Studio report needs to be executed by Actions - > Run. Once report execution is finished search in collected tracer entries for the table name.

Narrow down the row with “RDB operation returned result set…” and click select on that row (marked by arrow). This will open the page with information about the query. SQL contains query body and SQL inserts show values.

To collect the complete query “?” (question mark) from SQL should be replaced by values from SQL inserts (in the same order these are listed).

Following that query can be executed in the external tool (e.g. pgAdmin for Postgres database). Such tools offer the generating of an explain plan for query which shows what is a most cost in query execution.

Once it’s identified SQL index can be created for specific columns which should improve query time execution. Here is a link to Postgres documentation on how to use and interpret the explain plan:

https://www.postgresql.org/docs/current/using-explain.htm