nick_darlington

Locating the portlet or lookup behind problematic NSQL

Blog Post created by nick_darlington Employee on Feb 6, 2015

It isn't unusual to find a SQL error reported in the app-ca.log or bg-ca.log files, and from that see we are dealing with some NSQL that has broken somehow.

 

In order to locate this NSQL in the application UI, you need to know which lookups or queries (and why not their portlets too) they are based upon, so that you have a chance of going to correct it.

 

For me, that is where these queries come in.

 

Firstly for lookups:

-- $LIKE_PATTERN is the %something% in the NSQL you're using to identify/locate it

select l.id as lookup_type_id, lov.lookup_type_code,
  q.id as nsql_query_id, q.nsql_text
from cmn_nsql_queries q
left join cmn_list_of_values lov
on q.id = lov.sql_text_id
left join cmn_lookup_types l
on upper(l.lookup_type) = upper(lov.lookup_type_code)
where upper(q.nsql_text) like upper('$LIKE_PATTERN')
/

 

As hinted, do a find/replace in this query on the $LIKE_PATTERN term and place in it a small piece of the NSQL you've noticed is likely to help uniquely identify it (for example if this is a query on addresses and zipcodes, and there's probably only one such query in your system, then replace it with '%zipcode%' and run it).  Please note that for Microsoft SQL Server customers, the last line of the query may need to be altered to this instead, and hopefully the query sizes are under 4k bytes:

...

where upper(cast(q.nsql_text as nvarchar(4000))) like upper('$LIKE_PATTERN')

/

 

The query text is held in CMN_NSQL_QUERIES.NSQL_TEXT, we link this up with the lookup id (if it is based on a lookup) through these joins:

    CMN_NSQL_QUERIES.ID = CMN_LIST_OF_VALUES.SQL_TEXT_ID

-and-

    CMN_LIST_OF_VALUES.LOOKUP_TYPE_CODE = CMN_LOOKUPS.LOOKUP_TYPE

 

The lookup_type_code / lookup_type values are all you need to locate the lookup with the UI, but in case you want anything else from the lookup table (e.g. linkage to CMN_CAPTIONS_NLS.PK_ID where CMN_CAPTIONS_NLS.TABLE_NAME = 'CMN_LOOKUPS' for example) the join is included for that too.

 

Portlets based on queries containing particular NSQL aren't that dissimilar, but since we distinguish between Grid and Graph portlets in different tables, the query looks more complex when it's just UNIONing the results together into one set.

 

There are two queries here, one is for Oracle databases, and the other is for Microsoft SQL Server databases:

-- $LIKE_PATTERN - what identifier in the NSQL are you trying to find the
-- portlets and queries for, e.g. to find which portlets/queries are using
-- datamart tables in the database, set the pattern to %NBI%
-- Note: Your pattern should be all in uppercase, even if the content in
-- the database is mixed or lower case, due to the query design.

 

-- Oracle
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 upper(q.nsql_text) like upper('$LIKE_PATTERN')
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 upper(q.nsql_text) like upper('$LIKE_PATTERN')
/

 

-- Microsoft SQL Server
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 upper(cast(q.nsql_text as nvarchar(4000)) like upper('$LIKE_PATTERN')
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 upper(cast(q.nsql_text as nvarchar(4000)) like upper('$LIKE_PATTERN')
/

 

As with the lookups query, perform a search/replace on the $LIKE_PATTERN term to contain your desired NSQL matching criteria.  As the comments indicate, using %NBI% as your criteria for example will help identify all those queries written using Datamart content (which uses the prefix NBI_* in the schema).

 

Here the query data model is using the same location for the stored NSQL, that is CMN_NSQL_QUERIES.NSQL_TEXT but from there links through to CMN_PORTLETS either through CMN_GRIDS or CMN_GRAPHS in each half of the UNION to get the answer, using these relationship joins (using CMN_GRIDS for this example, but you could replace that with CMN_GRAPHS for the same equivalent for graph portlets):

 

    CMN_NSQL_QUERIES.ID = CMN_GG_NSQL_QUERIES.CMN_NSQL_QUERIES_ID

-and-

    CMN_GG_NSQL_QUERIES.DAL_ID = CMN_GRIDS.DAL_ID AND CMN_GRIDS.DAL_TYPE = 'nsql'

-and-

    CMN_GRIDS.PORTLET_ID = CMN_PORTLETS.ID

 

Hopefully with queries like this it will be possible to find and fix a few issues through the application UI (still with SQL/NSQL knowledge) to take advantage of the business rules and logic without needing to take the risk of a direct database modification.  Alternatively it can be used for risk assessment and planning (if we want to change the attribute on table X, which lookups or portlets might be affected).

Outcomes