Warning about invalid NSQL queries after upgrade

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?

You must correct invalid NSQL queries.  

Environment

Release: All Supported Clarity releases

Resolution

NSQL queries are invalid - reason:

• With every new Clarity version the database schema is changing, some views are getting modified, some deprecated. It is expected to have some differences
• The checkinstall will check if there is NSQL that relies on database objects that are no longer there
• This is not a blocking error or something that will fail your upgrade, it's just a warning message after the upgrade for your information
• How to address each reported NSQL: 
  • All Addin NSQL queries will be updated when you update the add-in. Example (PMO Accelerator). You must apply PMO accelerator to get CSK / COP/ PMO NSQL to be valid 
  • All custom queries you will have to update yourself with the new schema changes. 
  • All deprecated queries warning you can ignore

How to 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.

 

Custom NSQL queries - use in Clarity and impacts

• The below applies only to custom NSQL queries
• Custom 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.
• In very rare cases they may be used in GEL scripts for custom processes - this is extremely rare occurrence
• Any custom NSQL that is no longer in use or deprecated portlets can be safely removed
• You can delete the custom portlet and custom NSQL accordingly to ensure this warning no longer comes up after upgrade
• Leaving them as they are is also safe, even if invalid. The impact will be that if a custom portlet is reused, it may not work and may show an error
• Note that anything you choose to leave as invalid may come up again during your next upgrade