Pega Exchange - Database Query Extractor

Query Extractor:

Another Tool in pega exchange. This tool is used to extract the query, which will be formed while report definition or listview or Summary view is running.

Usually you can see the sql in ReportContentPage in clipboard, with pxSQLStatementPre or pxSQLStatementPost.

pxSQLStatementPre will have table names with corresponding class name and other where conditions will be with property mentioned in the rule.
pxSQLStatementPost will have replaced class names with the corresponding table name, configured in pega Database tables. and Where condition properties are replaced with Query Strings with "?" symbol.

And in the clipboard, pyPreparedValues value list property will be there in the sequence with the values which will replace the query strings.

QueryExtractor has been developed by Pega, and they had given it for us easy use. we can download it from the below URL. It contains code archive ZIP file and the document for configuring it in your application and how to this utility can be used.

Download the Query Extractor from the below URL:
https://community1.pega.com/exchange/components/database-query-extractor

When we run this utility, it will pop up with the sql generated and Query strings replaced with the corresponding values.

So that we can get the string and directly run in the SQL databases to check what value is returned or why its taking so much time to execute it.

Download file, and extract the ZIP, "QueryExtractor.zip", and import it into your application.

Follow the steps mentioned in the document "How to use-QueryExtractor.docx"

Sample data used in our example:
pyReportName: pyInstanceList

pyReportClass: Data-Admin-Operator-ID

Steps to follow:

In pxSQLStatementPost Property value will be as:

SELECT "PC0"."pyusername" AS "pyUserName" , "PC0"."pxupdatedatetime" AS "pxUpdateDateTime" , "PC0"."pxupdateopname" AS "pxUpdateOpName" , "PC0"."pyworkgroup" AS "pyWorkGroup" , "PC0"."pyaccessgroup" AS "pyAccessGroup" , "PC0"."pzinskey" AS "pzInsKey" FROM data.pr_operators "PC0" WHERE "PC0"."pxobjclass" = ? ORDER BY 2 DESC

you can see the "?" inbetween the query which will be replaced in the run time, instead of that by the using this utillity we can get it on screen itself.

Query formed will be as follows:

SELECT "PC0"."pyusername" AS "pyUserName" , "PC0"."pxupdatedatetime" AS "pxUpdateDateTime" , "PC0"."pxupdateopname" AS "pxUpdateOpName" , "PC0"."pyworkgroup" AS "pyWorkGroup" , "PC0"."pyaccessgroup" AS "pyAccessGroup" , "PC0"."pzinskey" AS "pzInsKey" FROM data.pr_operators "PC0" WHERE "PC0"."pxobjclass" = 'Data-Admin-Operator-ID' ORDER BY 2 DESC

Note: Please let me know, if any comments need to be removed or modified. Reviewing will help us and others who are viewing this post.

How to clear node level Datapages on each node

To clear Datapage on each node in Pega PRPC:

In Pega PRPC, using data page will improve the performance of the application, but when its used as necessary way. whether if its really required for Node level or if its required for Requestor Level.

In Some applications unwantedly datapages has been used, and it will have validity of lifetime, means it wil be there till server restarts or pega destroys, when exceeding the maximum allowed now of data pages. As a result, server memory consumption will increase and it will be collected by Garbage collector.

So without disturbing the application or changing the datapage rule, we will see how we can clear the datapage daily, by running the agent, it will be bad way of design, but it will be useful for the application running in the production.

Below mentioned can be called in agent rule and to run it daily morning to clear the node and enable it in all the nodes.

Steps to clear the Datapage cache:

Pseudo code of the activity:

1. get the list of declare page currently in the node using engine api, which will return the pzinskey of the declare page.
2. iterate on it and form Page list.
3. Iterate on each page and get the declare page name
4. using engine api, to clear all the instance of the declare page.

Activity screen shot are given below:

Step 1: create a page new to hold the list of datapage names, which is of class "Code-Pega-List".

Step 2:  With Java method. Paste  below java code there.

PRNode node = tools.getRequestor().getNode();

result1 ="Node-->"+ node.getNodeUniqueID();

java.util.Set resultSet = node.getDeclarativePageNames();

ClipboardPage ServicePage = tools.findPage("ServicePage");

ClipboardProperty pxResultProperty = ServicePage.getProperty("pxResults");

Iterator Itr=resultSet.iterator();

try{

while(Itr.hasNext()){

    //ClipboardPage result2 = (ClipboardPage)Itr.next();

 //String result3 = result2.getName();

 String result3=(String)Itr.next();

  ClipboardPage cd = tools.getThread().createPage("OCBC-Div-Unit-VISA-Work-ServiceRequest","ServicePageResults");

  cd.putString("pyLabel",result3);

  pxResultProperty.add(cd);

  

}

  }

catch(Exception e){

  oLog.infoForced(e);

}

Step 3: loop on page list created.

In step 4 java method:

pega.getDeclarativePageUtils().deleteAllInstancesOfDeclarativePage(result1);

In step 5: page remove method to remove the unwanted pages.

Execute the activity and see to remove the data page from the clipboard.

Note: If you have comments or corrections, please let me know, will update in the post.