Warning about invalid NSQL queries after upgrade : Questions

Products

• Clarity PPM On Premise

Issue/Introduction

After upgrade, we have a warning about invalid NSQL queries, is this something that could impact our upgrade?

Environment

Release: All Supported Clarity releases

 

Component:

Resolution

With every version the database schema is changing, some views are getting modified, some deprecated. This is not something that will fail your upgrade.

All Addin NSQL queries will be updated when you update the addin. Example (PMO Accelerator, for example)

All custom queries you will have to update yourself with the new schema changes. 

All deprecated queries warning you can ignore.

Usually this is only a warning about portlets that were deprecated, so nothing to worry about. We would suggest reviewing the current portlet list before seeing if you need anything to be rewritten as custom portlet. 

 

Is there a way that we can find out precisely if/where these NSQL queries are used inside the application?

We recommend keeping a document file with all customizations, and where and how they are used so you are always aware what is going on.

Please use the query to find the portlet names (replace with query code when appropriate, this query returns Graph and Grid portlets)

select p.portlet_code as portlet_code,

n.name as portlet_name,

gg.query_code as nsql_code,

'grid' as portlet_type

from cmn_portlets p

join cmn_captions_nls n

on n.language_code = 'en'

and n.table_name = 'CMN_PORTLETS'

and n.pk_id = p.id

join cmn_grids g

on p.id = g.portlet_id

join cmn_gg_nsql_queries gg

on gg.cmn_nsql_queries_id = g.dal_id

and g.dal_type = 'nsql'

and g.principal_type = 'SYSTEM'

join cmn_nsql_queries q

on gg.cmn_nsql_queries_id = q.id

and gg.query_code =' your query ID'-----replace your query id here

union all

select p.portlet_code as portlet_code,

n.name as portlet_name,

gg.query_code as nsql_code,

'graph' as portlet_type

from cmn_portlets p

join cmn_captions_nls n

on n.language_code = 'en'

and n.table_name = 'CMN_PORTLETS'

and n.pk_id = p.id

join cmn_graphs g

on p.id = g.portlet_id

join cmn_gg_nsql_queries gg

on gg.cmn_nsql_queries_id = g.dal_id

and g.dal_type = 'nsql'

and g.principal_type = 'SYSTEM'

join cmn_nsql_queries q

on gg.cmn_nsql_queries_id = q.id

and gg.query_code ='your query ID' -----replace your query id here

With the portlet name you should be able to find the portlets and see if you actually need them.

 

Can you tell me where else (besides graph and grid portlets) NSQL queries may be used in Clarity and what are the implications of deleting them.

NSQL queries in the Administration - Queries list are only used in grid, graph portlets and also in wsdl calls if you have an outside application accessing Clarity. Or in rare cases they may be used in GEL scripts for custom processes, this is something that's very rare though and you would usually know about it.

Any custom NSQL that is no longer in use or deprecated portlets can be safely removed.

You can delete them, or leave them as they are, it's also safe. Anything that you don't use and it's left there will simply remain in the list and not be of any negative impact, it may just come up again during your next upgrade.