How to change or replace a query statement in CABI (Jaspersoft) to improve the query performance

book

Article ID: 210145

calendar_today

Updated On:

Products

DX Infrastructure Management CA Unified Infrastructure Management for z Systems CA Unified Infrastructure Management On-Premise (Nimsoft / UIM) CA Unified Infrastructure Management SaaS (Nimsoft / UIM) NIMSOFT PROBES

Issue/Introduction

CABI (Jaspersoft) queries seem to be consuming too much CPU and taking a long time to render the data.

Query:

select * from (select 'total_device_count' as type, count(*) as device_count       from cm_computer_system
               union
       select 'active_device_count' as type, count(DISTINCT(CASE WHEN sqs.sampletime is not NULL THEN ccs.cs_id ELSE NULL END)) as device_count
from cm_computer_system ccs
       join cm_device cd
               on ccs.cs_id = cd.cs_id
       join cm_configuration_item cci
               on cd.dev_id = cci.dev_id
       join cm_configuration_item_metric ccim
               on cci.ci_id = ccim.ci_id
       join s_qos_data sqd
               on ccim.ci_metric_id = sqd.ci_metric_id
               and sqd.origin IN (select * from (select distinct sqd.origin from S_QOS_DATA sqd union select distinct ccso.origin from CM_COMPUTER_SYSTEM_ORIGIN ccso union select distinct na.origin from NAS_ALARMS na) alias)
       join s_qos_snapshot sqs
               on sqd.table_id = sqs.table_id
               and CAST(sqs.sampletime as datetime) >= DATEADD(MINUTE, -30, CURRENT_TIMESTAMP)) src
                                pivot (sum(device_count) for type in (total_device_count,active_device_count))piv

Environment

Release : 20.3

Component : UIM OPERATOR CONSOLE - ALARM VIEWER

Resolution

Open cabi directly, e.g., http://10.xx.xxx.xxx/cabijs/login.html

and login using User ID: and password of->

superuser/superuser

Select View- > Repository- >Public->ca->UIM->reports->common->device

In CABI (Jaspersoft), navigate to the report, in this particular case-> Devices and rt-click and choose Edit.

Then select the Query button and select the radio button for "Click here to create a new query" and then click that link to create a new query. See below.

On the next screen, give the query a new unique name and click Next at the bottom of the page.

In the next screen, select/link an existing datasource via the Browse button, choose Public->ca->UIM->datasources->UIM JNDI Datasource,

Press Select.

Finally click next, and then enter the query here:

Click Save and now it will use this new query.

 

Query used in this example:

select count(DISTINCT(CASE WHEN sqs.sampletime is not NULL THEN ccs.cs_id ELSE NULL END)) as device_count from cm_computer_system ccs

join cm_device cd

on ccs.cs_id = cd.cs_id

join cm_configuration_item cci

on cd.dev_id = cci.dev_id

join cm_configuration_item_metric ccim

on cci.ci_id = ccim.ci_id

join s_qos_data sqd

on ccim.ci_metric_id = sqd.ci_metric_id

join s_qos_snapshot sqs

on sqd.table_id = sqs.table_id

and CAST(sqs.sampletime as datetime) >= DATEADD(MINUTE, -30, CURRENT_TIMESTAMP)

-- or you can change the last line to

-- and CAST(sqs.sampletime as datetime) >= DATEADD(HOUR, -24, CURRENT_TIMESTAMP)

The click Submit.

Attachments