IBM Data and AI Ideas Portal for Customers


Shape the future of IBM!

We invite you to shape the future of IBM, including product roadmaps, by submitting ideas that matter to you the most. Here's how it works:

Post your ideas

Post ideas and requests to enhance a product or service. Take a look at ideas others have posted and upvote them if they matter to you,

  1. Post an idea

  2. Upvote ideas that matter most to you

  3. Get feedback from the IBM team to refine your idea

Help IBM prioritize your ideas and requests

The IBM team may need your help to refine the ideas so they may ask for more information or feedback. The product management team will then decide if they can begin working on your idea. If they can start during the next development cycle, they will put the idea on the priority list. Each team at IBM works on a different schedule, where some ideas can be implemented right away, others may be placed on a different schedule.

Receive notification on the decision

Some ideas can be implemented at IBM, while others may not fit within the development plans for the product. In either case, the team will let you know as soon as possible. In some cases, we may be able to find alternatives for ideas which cannot be implemented in a reasonable time.

Additional Information

To view our roadmaps: http://ibm.biz/Data-and-AI-Roadmaps

Reminder: This is not the place to submit defects or support needs, please use normal support channel for these cases

IBM Employees:

The correct URL for entering your ideas is: https://hybridcloudunit-internal.ideas.aha.io


Status Not under consideration
Components Data Server Manager
Created by Guest
Created on Mar 22, 2018

Improve performance of internal product SQL

As a part of performing some SQL performance monitoring, we have noticed that Data Server Manager (DSM) executes several poorly performing SQL statements that would greatly benefit
from some additional indexes on either the DSM Repository database (BBFREPO, by default), and/or the Autonomics Director database (DYXDB, by default).
A couple of these SQL statements appear to be directly related to the autonomics dashlets.

I have run the SQL in question through the Query Workload Tuner, and it has made the index recommendations shown below.



Poor performing SQL - 1
/* IBM_DSM */ SELECT COUNT(TRIGGER_NAME) FROM DSSCHED.QTRIGGERS WHERE NEXT_FIRE_TIME < ? AND ((TRIGGER_STATE = ?) OR (TRIGGER_STATE = ?))

Recommended Indexes (with estimated 62% performance improvement)
CREATE INDEX "DSSCHED"."QTRIGGERS_VIRT_IDX_152121288099701976" ON
"DSSCHED"."QTRIGGERS" ( "TRIGGER_STATE" ASC, "NEXT_FIRE_TIME" ASC ) NOT
PADDED FREEPAGE 0 PCTFREE 10;



Poor performing SQL - 2
/* IBM_DSM */ SELECT count(*) as CNT from SYSAUTO.AUTORUNS_HIST as AR_HIST
JOIN SYSAUTO.ACTION FOR SYSTEM_TIME FROM CURRENT_TIMESTAMP - 1 YEAR TO CURRENT_TIMESTAMP AS ACT
ON AR_HIST.HISTORY_ENTRY_ID = ACT.EVAL_HISTORY_ID
where PROC_NAME = 'AUTONOMIC BUILD' AND AR_HIST.HISTORY_ENTRY_ID = ? and PARENT = 0
AND TRANS_ID = (SELECT MAX(TRANS_ID) FROM SYSAUTO.ACTION FOR SYSTEM_TIME
FROM CURRENT_TIMESTAMP - 1 YEAR TO CURRENT_TIMESTAMP
WHERE ACTION_ID = ACT.ACTION_ID AND EVAL_HISTORY_ID = ACT.EVAL_HISTORY_ID
GROUP BY ACTION_ID)
WITH UR

/* IBM_DSM */
SELECT count(*) as CNT
from SYSAUTO.AUTORUNS_HIST as AR_HIST JOIN SYSAUTO.ACTION FOR SYSTEM_TIME
FROM CURRENT_TIMESTAMP - 1 YEAR TO CURRENT_TIMESTAMP AS ACT
ON AR_HIST.HISTORY_ENTRY_ID = ACT.AUTODIRECTOR_HISTORY_ID
where PROC_NAME = 'AUTONOMICS DIRECTOR'
AND AR_HIST.HISTORY_ENTRY_ID = ?
and PARENT = 0
AND TRANS_ID = (
SELECT MAX(TRANS_ID)
FROM SYSAUTO.ACTION FOR SYSTEM_TIME
FROM CURRENT_TIMESTAMP - 1 YEAR TO CURRENT_TIMESTAMP
WHERE ACTION_ID = ACT.ACTION_ID
AND AUTODIRECTOR_HISTORY_ID = ACT.AUTODIRECTOR_HISTORY_ID
GROUP BY ACTION_ID) WITH UR

Recommended Indexes (with estimated 73% performance improvement)
CREATE UNIQUE INDEX "SYSAUTO"."ACTION_HIST_VIRT_IDX_152121077426693163"
ON "SYSAUTO"."ACTION_HIST" ( "EVAL_HISTORY_ID" ASC, "ACTION_ID" ASC,
"STARTTS" ASC, "ENDTS" ASC ) NOT PADDED FREEPAGE 0 PCTFREE 10;

CREATE UNIQUE INDEX "SYSAUTO"."ACTION_HIST_VIRT_IDX_152121077426527789"
ON "SYSAUTO"."ACTION_HIST" ( "EVAL_HISTORY_ID" ASC, "PARENT" ASC,
"STARTTS" ASC, "ENDTS" ASC, "ACTION_ID" ASC ) NOT PADDED FREEPAGE 0
PCTFREE 10;

CREATE UNIQUE INDEX "SYSAUTO"."ACTION_VIRT_IDX_152121077426963414" ON
"SYSAUTO"."ACTION" ( "EVAL_HISTORY_ID" ASC, "PARENT" ASC, "STARTTS" ASC,
"ENDTS" ASC, "ACTION_ID" ASC ) NOT PADDED FREEPAGE 0 PCTFREE 10;



Poor performing SQL - 3
/* IBM_DSM */ SELECT TRIGGER_NAME, TRIGGER_GROUP, NEXT_FIRE_TIME, PRIORITY FROM DSSCHED.QTRIGGERS WHERE TRIGGER_STATE = ? AND NEXT_FIRE_TIME < ? AND (NEXT_FIRE_TIME >= ?) ORDER BY NEXT_FIRE_TIME ASC, PRIORITY DESC

Recommended Indexes (with estimated 27% performance improvement) – note this is the same index as recommended above
CREATE INDEX "DSSCHED"."QTRIGGERS_VIRT_IDX_152121314898807848" ON
"DSSCHED"."QTRIGGERS" ( "TRIGGER_STATE" ASC, "NEXT_FIRE_TIME" ASC ) NOT
PADDED FREEPAGE 0 PCTFREE 10;




Poor performing SQL - 4
/* IBM_DSM */ select SESSIONUSER as "sessionuser",NAMESPACE as "namespace",RESOURCENAME as resourcename, PROPERTY_KEY as "key",PROPERTY_VALUE as "value" FROM IBMPDQ.USERCRED WHERE SESSIONUSER=? AND NAMESPACE=? and RESOURCENAME LIKE ?

Recommended Indexes (with estimated 72% performance improvement)
CREATE INDEX "IBMPDQ"."USERCRED_VIRT_IDX_152121337381025375" ON
"IBMPDQ"."USERCRED" ( "SESSIONUSER" ASC, "NAMESPACE" ASC, "RESOURCENAME"
ASC ) NOT PADDED FREEPAGE 0 PCTFREE 10;
  • Admin
    Maryia Rakina
    May 28, 2021

    Thank you for submitting you Idea. While we no longer make enhancements to Data Server Manager, we will continue to improve performance of Db2 Data Management Console, which is based on Data Server Manager.

  • Guest
    Jan 18, 2019

    We understand the requirement and appreciate the detailed feedback. We will consider as part of our ongoing work to improve the performance of DSM and lower its overall impact on the monitored database.