App CA logs gets filled with error Error message: PooledConnection has already been closed, while previewing the Dynamic Query Lookup

book

Article ID: 207861

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

STEPS TO REPRODUCE: 

Pre requisite 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.

Cause

This is a defect DE59673 and currently in review with our engineering team 

Environment

Release : 15.9

Component : CA PPM SAAS INSTALLATION AND UPGRADE

Resolution

Workaround: 

1. Restart the clarity services or flush out the cache by going to security.caches till the defect is fixed