SA-26715

Summary

User notices that Autonomic Event Services (AES) in production is lacking data for several weeks and the list of nodes under System Connectivity is blank.

Error Messages

2016-08-03 15:51:50,214 [ WebContainer : 13] [TABTHREAD1] [ ] [ AES:07.13] ( rd.queryexec.SqlQueryExecutor) ERROR your_host|your_ip you_aesuser - 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

com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: 1013 SQLState: 72000 Message: ORA-01013: user requested cancel of current operation

From: (H8F57E31CF37AA6F00E23CFDEE66349DA:your_ip)
SQL: SELECT "PC0".CLUSTERNAME AS "ClusterName", MIN("PC0".PXCREATEDATETIME) AS "pySummaryDateTime(1)" , MAX("PC0".PXCREATEDATETIME) AS "pySummaryDateTime(2)" FROM PEGA_AEU_DATA.pegaam_alert "PC0" INNER JOIN ( SELECT "LAT".CLUSTERNAME AS "SRCOL2", MAX("LAT".PXCREATEDATETIME) AS "SRCOL1" FROM PEGA_AEU_DATA.pegaam_alert "LAT" WHERE "LAT".pxObjClass = ? GROUP BY "LAT".CLUSTERNAME ) "LAT" ON ( ( "LAT".SRCOL2 = "PC0".CLUSTERNAME ) AND "PC0".PXOBJCLASS = ? ) WHERE ( "LAT".SRCOL1 >= ? AND "PC0".CLUSTERNAME IN (? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) ) GROUP BY "PC0".CLUSTERNAME ORDER BY 1 ASC, 3 ASC
SQL Inserts: <PegaAES-Data-Alert> <PegaAES-Data-Alert> <20160728T040000.000 GMT> <your_value> <your_value> <your_value> <your_value> <your_value> <your_value> <your_value> <your_value> <your_value> <your_value> <your_value> <your_value> <your_value> <your_value> <your_value> <your_value> <your_value> <your_value>

Caused by SQL Problems.
Problem #1, SQLState 72000, Error code 1013: java.sql.SQLTimeoutException: ORA-01013: user requested cancel of current operation
at com.pega.pegarules.data.internal.access.ExceptionInformation.createExceptionDueToDBFailure(ExceptionInformation.java:262)
at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:6220)
at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:5916)
at com.pega.pegarules.data.internal.rd.queryexec.SqlQueryExecutor.executeQuery(SqlQueryExecutor.java:68)
at com.pega.pegarules.data.internal.rd.queryexec.SqlQueryExecutor.executeQuery(SqlQueryExecutor.java:43)
at com.pega.pegarules.data.internal.rd.SqlReportExecutor.executeQuery(SqlReportExecutor.java:284)
at com.pega.pegarules.data.internal.rd.SqlReportExecutor.executeMainReport(SqlReportExecutor.java:128)
at com.pega.pegarules.data.internal.rd.SqlReportExecutor.execute(SqlReportExecutor.java:90)
at com.pega.pegarules.data.internal.rd.SqlReportExecutor.execute(SqlReportExecutor.java:57)
at com.pega.pegarules.data.internal.PRDataProviderImpl.executeReport(PRDataProviderImpl.java:1229)
at com.pega.pegarules.session.internal.mgmt.Executable.executeReport(Executable.java:10787)
at com.pegarules.generated.definition.ra_action_getlicensingstatistics_33e3b84c07b2f1153d80f53a3e7c683f.perform(ra_action_getlicensingstatistics_33e3b84c07b2f1153d80f53a3e7c683f.java:50)
at com.pega.pegarules.session.internal.mgmt.Executable.doAction(Executable.java:3305)
at com.pegarules.generated.activity.ra_action_pxretrievereportdata_ea2365ec0714372ba5ea7e36c543cc9a.step30_circum0(ra_action_pxretrievereportdata_ea2365ec0714372ba5ea7e36c543cc9a.java:3411)

Steps to Reproduce

1. Open up AES then for an application dashboard.
2. Select System Connectivity from AES logo, and note that it does not show any nodes.

Root Cause

A defect or configuration issue in the operating environment.

Run tracer to see that the SQL query that returns the data for System Connectivity is failing due to timeout.

This is because the report definition 'Get Licensing Statistics' has 'maximum elapsed time in seconds' value set to 30 and the SQL query in the exception above takes 90 seconds (tested from a JDBC client tool).


Run an explain plan on the SQL to realize that the indexes defined on pegaam_alert table are not in use.

Resolution

Make sure all the out of the box indexes on pegaam_alert table are present and in effect. If necessary, rebuild these indexes.