Support Article

Pega Marketing Segment Select Values queries times-out

SA-26222

Summary



Within Pega Marketing, there are two places where a user has the ability to enter a property and hit the "Select Values" button - within a Segment when entering selection criteria in non-visual mode and in the Field Marketing portal on the Select Contacts screen (Filter criteria).

When the "Select Values" button is clicked, the system does a Select Distinct query on the Customer table for a Property. The Report Definition times-out before the results are returned to database.

The Report Definition is final, so the timeout setting for this Report Definition cannot be changed.

Error Messages



2016-07-06 21:02:39,985 [ttp-bio-8443-exec-40] [TABTHREAD1] [ MyAppRS:01.01.01] ( rd.resolve.SqlReportResolver) ERROR {server name}IP address user1 - An error occured during resolving the report definition - null
2016-07-06 21:02:40,024 [ttp-bio-8443-exec-40] [TABTHREAD1] [ MyAppRS:01.01.01] ( rd.resolve.SqlReportResolver) ERROR {server name}|IP address user1 - An error occured during resolving the report definition - null
2016-07-06 21:03:27,086 [ttp-bio-8443-exec-51] [TABTHREAD1] [ MyAppRS:01.01.01] ( rd.resolve.SqlReportResolver) ERROR {server name}|IP address user1 - An error occured during resolving the report definition - InvalidReferenceException .DEV_NME Unexposed properties cannot be selected for classes mapped to external tables
2016-07-06 21:03:27,103 [ttp-bio-8443-exec-51] [TABTHREAD1] [MyAppRS:01.01.01] ( rd.resolve.SetQueryResolver) WARN {server name}|IP address user1 - Union not applied because [InvalidReferenceException .DEV_NME Unexposed properties cannot be selected for classes mapped to external tables]
2016-07-06 21:03:57,578 [ttp-bio-8443-exec-51] [TABTHREAD1] [ MyAppRS:01.01.01] (l.access.ConnectionManagerImpl) ERROR {server name}|IP address user1 - Not returning connection 8 for database "externalmktdata" to the pool as it previously encountered the following error
User ID: user1
2016-07-06 21:03:57,589 [ttp-bio-8443-exec-51] [TABTHREAD1] [ MyAppRS:01.01.01] ( rd.queryexec.SqlQueryExecutor) ERROR {server name}|IP address user1 - An error occured on executing the query for the report definition - There was a problem getting a list: code: 1013 SQLState: 72000 Message: ORA-01013: user requested cancel of current operation
2016-07-06 21:03:57,595 [ttp-bio-8443-exec-51] [TABTHREAD1] [ MyAppRS:01.01.01] ( rd.queryexec.SqlQueryExecutor) ERROR {server name}|IP address user1 - Query: SELECT DISTINCT "PC0".DEV_NME AS "DEV_NME" FROM NBAM.SPR_CUSTOMER "PC0" ORDER BY 1 ASC


Steps to Reproduce



1.a. Create a new Segment.
1.b. Switch to non-visual mode.
1.c. Enter a Property and hit the "Select Values" button.

2.a. Create a new Campaign.
2.b. Go to the Select Contacts section.
2.c. Click the Filter option.
2.d. Enter a Property and hit the "Select Values" button.


Root Cause



The select distinct query which was getting fired was taking more than 30 seconds for fetching 10K records from the database table and hence the query was getting timed-out.


Resolution



Perform the following local-change:

​Create index on columns which will be used for Select Values.

Published July 29, 2016 - Updated August 23, 2017

Have a question? Get answers now.

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