Enabling and using Query runner to troubleshoot database issues

Hello, Today in this blog we are going to see Enabling and using Query runner, Inspector, Schema tools to troubleshoot database issues.

Explain about the query runner option in pega platform ?

What is the use of query Runner and how do we configure that query Runner option in Pega ?

1. So by default we don't have this option by default in Pega prpc in order to get this query Runner option in Pega platform.

2. Example now see here go to Configure > System > Database here there should be an option called it as a query runner, query inspector and schema tools by default you must have a schema change tracking , optimize schema , modify schema and column population jobs but you don't have an option schema tools query inspector and query runner. 

3. So in order to get these options in your pega platform first you must add PegaRULES:DatabaseAdministrator access role to your current access group.

4. From dev studio left side click on operator profile > click on access group.

5. Now after opening the access group add the PegaRULES:DatabaseAdministrator access role in the available roles.

6. After that just click on save button in access group and log off.

7. Now login again and go to Configure > System > Database you should see query runner , inspector & schema tools.

8. What is the use of this query runner here ?

Ans:

• You can directly run the SQL queries on the Pega Dev Studio.
• You no need to open that your PG admin tools or another database client tools.
• And also you know sometimes when you are using the Pega Cloud environments you may not have an option to connect it to the database and to trigger some SQL queries.
• So even if you want to troubleshoot because this is mainly useful for debugging and troubleshooting purposes.

9. Three Tabs [Query Runner, Query Inspector, Schema Tools]

Query Runner: If you want to troubleshoot something and if you want to verify what is the data is present in the appropriate database table you can write SQL queries and fetch the data.

Query Inspector: If you want to analyze the query, means if you run query how much time and resource is it taking & is there any improvement needs to be taken in query.

Schema Tools: In  schema tools you see all tables and views related to the specific database.

10. Now let's execute our SQL query in the query runner - In Query Runner tab we have two sub tabs a.Query Entery b.History 

Now in the Query entry we have to give SQL query to fetch records and it will fetch max 500 records.

As for testing we have records for [ ProductInfo ] in class Data-UIGallery-ProductInfo. So open this class in App Explorer and right click on class and click on the definition.

And now scroll bottom in the class definition page and click on test connection button to see the  database table of the class.

11. Now you can use this table name in Query Entry in Query runner. Select PegaDATA as database and paste the below SQL query and click on run button.

select * from data.pr_data_sample_shoppingcart

12. After running the query if you get any error like above then open the pega installed directory in system.

And then open C:\PRPCPersonalEdition\tomcat\conf folder open the context.xml file like below.

Add the below code at last of the context.xml file and save the file.

   <Resource name="jdbc/PegaRULESReadOnly"

              auth="Container"

              factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"

              type="javax.sql.DataSource"

              maxTotal="100"

              maxIdle="10"

              driverClassName="org.postgresql.Driver"

              url="jdbc:postgresql://localhost:5432/postgres"

              username="postgres"/>

 

Now open the C:\PRPCPersonalEdition\tomcat\webapps\prweb\WEB-INF\classes folder and open the prconfig.xml file like below.

Add the below code at last of the prconfig.xml file and save the file.

<env name="database/databases/PegaRULES/dataSourceReadOnly" value="java:comp/env/jdbc/PegaRULESReadOnly"/>

<env name="database/databases/PegaDATA/dataSourceReadOnly" value="java:comp/env/jdbc/PegaRULESReadOnly"/>

After configuring the above two files shut down the pega platform and start it again.

13. Now open the query runner and select PegaDATA as database type this below SQL query and click on Run button.

select * from data.pr_data_sample_shoppingcart

This time we should be able to see the output in Results tab below and can be able to export the data in PDF, Excel format's.

14. And also we can see what queries we ran before and status of those quries go to History Tab of Query Runner like below and same way it can be exported via PDF, Excel.

15. Finally if you excute the same the below SQL query in query inspector by selecting database as PegaDATA and paste the SQL statement now click on Generate Explain Plan, then you would get cost of fetching the data (time) and no of rows of records, width. These will be used to improvement in SQL query.

***************