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.