SA-4894

Summary

Developer has seen an erroneous SQL triggered from Pega AES  and failing with SQLCODE -206.
On further analysis, the error is: SQL written on non-existing column CONNECTCLIENTRESPONSEELAPSED.

SQL :-

select avg(CONNECTCLIENTRESPONSEELAPSED) as "Avg", sum(
CONNECTCLIENTRESPONSEELAPSED) as "Total", count(*) as
"Count", max(CONNECTCLIENTRESPONSEELAPSED) as "Max", min(
CONNECTCLIENTRESPONSEELAPSED) as "Min"
from pegaam_alert
where msgID = ?
and ProblemCorrelation = ?
and (CONNECTCLIENTRESPONSEELAPSED > 500


No synonyms on the pegaam_alert table

Error Messages

SQLCODE -206

com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: -206 SQLState: 42703 Message: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=CONNECTCLIENTRESPONSEELAPSED, DRIVER=3.62.73
From: (B93380BF88519674E2FA93A698856CE2A)
SQL: select avg(CONNECTCLIENTRESPONSEELAPSED) as "Avg", sum(CONNECTCLIENTRESPONSEELAPSED) as "Total", count(*) as "Count", max(CONNECTCLIENTRESPONSEELAPSED) as "Max", min(CONNECTCLIENTRESPONSEELAPSED) as "Min" from pegaam_alert where msgID = ? and ProblemCorrelation=?
SQL Inserts: <PEGA0020> <simplification_cbt:Connect Total Time:7ed684544c895d04f385f69bcb84019>

Caused by SQL Problems.
Problem #1, SQLState 42703, Error code -206: com.ibm.websphere.ce.cm.StaleConnectionException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=CONNECTCLIENTRESPONSEELAPSED, DRIVER=3.62.73
Problem #2, SQLState 42703, Error code -206: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=CONNECTCLIENTRESPONSEELAPSED, DRIVER=3.62.73
Problem #3, SQLState 26501, Error code -516: com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-516, SQLSTATE=26501, SQLERRMC=null, DRIVER=3.62.73
Problem #4, SQLState 26501, Error code -514: com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-514, SQLSTATE=26501, SQLERRMC=SQL_CURLH200C58, DRIVER=3.62.73

Steps to Reproduce

Please run the below SQL to see the problem:
select avg(CONNECTCLIENTRESPONSEELAPSED) as "Avg", sum(
CONNECTCLIENTRESPONSEELAPSED) as "Total", count(*) as
"Count", max(CONNECTCLIENTRESPONSEELAPSED) as "Max", min(
CONNECTCLIENTRESPONSEELAPSED) as "Min"
from pegaam_alert
where msgID = ?
and ProblemCorrelation = ?
and (CONNECTCLIENTRESPONSEELAPSED > 500)

Root Cause

The root cause of this problem is a defect in defect in PegaAES for Linux

Resolution

The issue is because of firing a query on a missing column in a table.

Developer was suggested to add the missing column to the table.

This issue is resolved through the following local change:  Manually added  column "CONNECTCLIENTRESPONSEELAPSED" in pegaam_alert.

ALTER TABLE pegaam_alert
ADD ConnectClientResponseElapsed DECIMAL (18,6) NULL;