Error "PooledConnection has already been closed" while previewing a Dynamic Query in Lookup

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Problem Statement: Error "PooledConnection has already been closed" while previewing a Dynamic Query in Lookup

 

STEPS TO REPRODUCE: 

Prerequisite to Reproduce this defect is to ensure clarity is not restarted for more than a month's time 

1. Login to clarity and navigate to any existing dynamic query lookup.
2. For testing purpose you can create the lookup with below code and this query returns data in less than a second. 

 SELECT L.LOOKUP_CODE,
 L.NAME,
 L.DESCRIPTION,
 L.LANGUAGE_CODE,
 L.ID LANGUAGE_ID,
 L.SORT_ORDER,
 L.LAST_UPDATED_DATE,
 L.IS_ACTIVE
 FROM CMN_LOOKUPS_V L
 UNION
 select r.rim_risk_issue_code lookup_code,
 r.rim_risk_issue_code as name,
 r.DESCRIPTION,
 L.LANGUAGE_CODE,
 L.ID LANGUAGE_ID,
 r.id,
 r.LAST_UPDATED_DATE,
 i.IS_ACTIVE 
 from cmn_languages l
 left outer join rim_risks_and_issues r on r.table_name = 'SRM_PROJECTS' and r.type_code = 'CHANGE'
 inner join inv_investments i on i.id = r.pk_id

3. Navigate to Lookup from Administration and on the browse Window click on preview 

Expected Results: The preview should provide the output of the query in the pop up 

Actual Results: Error 500 and at the same time app ca log is populated with below stack trace 

<?xml version="1.0" encoding="UTF-8"?>
<statementRef id="nsql_internal_nested_0" inputSource="map" inputMapIndex="0" sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" defaultSliceSize="20" ignoreIfError="false" hideResultSets="false" xmlns="http://schemas.niku.com/2002/pmd"/>

Using input: 
{nsql.execFlag=1}

 at com.niku.union.persistence.nsql.NSQLQuery._execute(NSQLQuery.java:698)
 at com.niku.union.persistence.nsql.NSQLQuery.execute(NSQLQuery.java:540)
 at com.niku.odf.service.lookup.DynamicBrowseLookupService.getLookupValues(DynamicBrowseLookupService.java:566)
 ... 65 more
Caused by: com.niku.union.persistence.PersistenceException: 
SQL error code: 0
Error message: PooledConnection has already been closed.

Environment

Release : 15.9

Component : CA PPM SAAS INSTALLATION AND UPGRADE

Cause

This is a defect DE59673

Resolution

Workaround: 

1. Defect is fixed in Version 15.9.2
2. Restart the clarity services or flush out the cache by going to security.caches till the defect is fixed