Skip to Main Content
IBM Sustainability Software - Ideas Portal


This portal is to open public enhancement requests against the products and services belonging to IBM Sustainability Software. To view all of your ideas submitted to IBM, create and manage groups of Ideas, or create an idea explicitly set to be either visible by all (public) or visible only to you and IBM (private), use the IBM Unified Ideas Portal (https://ideas.ibm.com).


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:

Search existing ideas

Start by searching and reviewing ideas and requests to enhance a product or service. Take a look at ideas others have posted, and add a comment, vote, or subscribe to updates on them if they matter to you. If you can't find what you are looking for,

Post your ideas
  1. Post an idea.

  2. Get feedback from the IBM team and other customers to refine your idea.

  3. Follow the idea through the IBM Ideas process.


Specific links you will want to bookmark for future use

Welcome to the IBM Ideas Portal (https://www.ibm.com/ideas) - Use this site to find out additional information and details about the IBM Ideas process and statuses.

IBM Unified Ideas Portal (https://ideas.ibm.com) - Use this site to view all of your ideas, create new ideas for any IBM product, or search for ideas across all of IBM.

ideasibm@us.ibm.com - Use this email to suggest enhancements to the Ideas process or request help from IBM for submitting your Ideas.

Status Future consideration
Created by Guest
Created on Oct 4, 2021

TRIRIGA - Redundant security Geog/Org SQL for associated subqueries for Non-admin users

One of our client's instance holds large volumes of data 1/3 of billion rows in IBS SPEC & 3 billion rows in IBS SPEC assignments tables. The data security is applied using multiple Org based security groups & many frequent users have access to more than 5 Org data.


Many of those users experienced significant performance issues and through our analysis we have identified redundant SQL in the calls that being made to the database for non-admin group users. When a user has 10 security groups with System Organisations set, the resulting SQL creates more than 10 WHERE clauses on queries with associated subqueries. In the attached example, which runs on an OOTB report on triSurvey Module, the 10 security groups with sys orgs create 60 WHERE clauses (See attached). With the example of 'ISS Holding' this is checked 3(x2) times in total.


Note that GEO_ORG_SECURITY_CONTRIBUTE_BEHAVIOR is set to PATH in TRIRIGAWEB.properties


The issue is the way in which the platform builds out the filters for Org/Geo security. The same filters are applied in each subquery in the association filters. Applying the filter only to the main query should return the same results while significantly reducing the number of OR clauses (60 down to 20).


We also need the security statements to be added as BIND variables. Currently when a query is constructed some aspects of it are sent as BIND variables however the security element is not. When lots of different users with varying security applied to their profiles access the system the platform generates SQL that is sent to Oracle. Oracle is designed to work with BIND Variables and allows for the tuning of queries based on the SQL ID. However if the SQL keeps changing Oracle assigns multiple SQL_ID’s and this makes tuning impossible.


An additional indirect impact is that due to the sheer number of the statements generated by different users because of the differences in their security profile, the DB shared pool size keeps growing. This means that the memory available for buffer cache is limited & gets reduced as shared pool memory increases. It results in more physical reads on the disk which slows down the performance of the application/database by a great deal.

Idea priority Urgent
Needed By Yesterday (Let's go already!)
  • Guest
    Reply
    |
    Apr 24, 2024

    Here is an example of a query which uses org and geo filters at multiple levels of the query. You can see that the same filters are applied in each subquery in the association filters.

    SELECT T1.triUserMessageFlagTX AS T1_1180,
          T1.triFormLabelSY AS T1_1191,
          T1.triIdTX AS T1_1181,
          T1.triDescriptionTX AS T1_1211,
          COALESCE(T3.triNameTX, T1.triNameTX) AS T3_1178,
          T1.triMatrixRequestClas AS T1_1540,
          T1.triMatrixRequestClasObjId AS T1_1540_OBJID,
          T1.triPriorityClassCL AS T1_1516,
          T1.triPriorityClassCLObjId AS T1_1516_OBJID,
          T1.triRequestedByTX AS T1_1488,
          T1.triRequestedByTXObjId AS T1_1488_OBJID,
          T1.triRequestedForTX AS T1_1493,
          COALESCE(T4.triParentBuildingTX, T2.triParentBuildingTX) AS T4_1075,
          COALESCE(T4.triParentFloorTX, T2.triParentFloorTX) AS T4_1080,
          COALESCE(T4.triNameTX, T2.triNameTX) AS T4_1045,
          T1.triPlannedStartDT AS T1_1441,
          T1.triPlannedEndDT AS T1_1442,
          T1.triActualTotalCostNU AS T1_1447,
          T1.triStatusCL AS T1_1183,
          T1.triStatusCLObjId AS T1_1183_OBJID,
          T1.triResourceAssignmen AS T1_1559,
          T1.triResourceAssignmenObjId AS T1_1559_OBJID,
          T1.SYS_TYPE1 AS T1_SYS_TYPE1,
          T1.SYS_GUIID AS T1_SYS_GUIID,
          T1.SPEC_ID AS T1_SPEC_ID,
          T1.triCurrencyUO AS T1_1458
    FROM M_TRITASK T1
    LEFT
    OUTER JOIN IBS_SPEC_ASSIGNMENTS T5 ON T1.SPEC_ID = T5.SPEC_ID
    AND T5.ASS_SPEC_CLASS_TYPE = ?
    AND T5.ASS_TYPE = ?
    LEFT
    OUTER JOIN M_LOCATION T2 ON T5.ASS_SPEC_ID = T2.SPEC_ID
    AND T2.SYS_OBJECTID > 0
    AND T2.SYS_TYPE1 IN (?,?,?)
    LEFT
    OUTER JOIN ML_TRITASK T3 ON T1.SPEC_ID = T3.SPEC_ID
    AND T3.LANGUAGE_ID = ?
    LEFT
    OUTER JOIN ML_LOCATION T4 ON T2.SPEC_ID = T4.SPEC_ID
    AND T4.LANGUAGE_ID = ?
    WHERE T1.SYS_TYPE1 IN (?,?,?)
     AND (T1.triStatusCL IN ('Closed',
                             'Completed',
                             'Cancelled'))
     AND T1.SPEC_ID IN
       (SELECT ASS_SPEC_ID
        FROM IBS_SPEC_ASSIGNMENTS
        WHERE SPEC_ID IN
            (SELECT T1.SPEC_ID AS T1_SPEC_ID
             FROM T_TRIRESOURCE T1
             WHERE T1.SYS_GUIID = ?
               AND T1.SPEC_ID IN
                 (SELECT ASS_SPEC_ID
                  FROM IBS_SPEC_ASSIGNMENTS
                  WHERE SPEC_ID = ?
                    AND ASS_TYPE = ?)
               AND T1.SYS_OBJECTID > ?
               AND (T1.SYS_ORGNAMEOBJID IN
                      (SELECT SPEC_ID
                       FROM T_ORGANIZATION
                       WHERE SYS_OBJECTID > 0
                         AND (TRIPATHSY = '\Organizations\Customers\ABC'
                              OR TRIPATHSY LIKE '\Organizations\Customers\ABC\%'))
                    OR T1.SYS_ORGNAMEOBJID IN
                      (SELECT SPEC_ID
                       FROM T_ORGANIZATION
                       WHERE SYS_OBJECTID > 0
                         AND (TRIPATHSY = '\Organizations\Customers\DEF'
                              OR TRIPATHSY LIKE '\Organizations\Customers\DEF\%'))
                    OR T1.SYS_ORGNAMEOBJID IN
                      (SELECT SPEC_ID
                       FROM T_ORGANIZATION
                       WHERE SYS_OBJECTID > 0
                         AND (TRIPATHSY = '\Organizations\Providers\ABC'
                              OR TRIPATHSY LIKE '\Organizations\Providers\ABC\%'))
                    OR T1.SYS_ORGNAMEOBJID IS NULL
                    OR T1.SYS_ORGNAMEOBJID = ?)
               AND (T1.SYS_GEOGRAPHYNAMEOBJID IN
                      (SELECT SPEC_ID
                       FROM M_GEOGRAPHY
                       WHERE SYS_OBJECTID > 0
                         AND (TRIPATHSY = '\Geography\EMEA\Northern Europe\Norway'
                              OR TRIPATHSY LIKE '\Geography\EMEA\Northern Europe\Norway\%'))
                    OR T1.SYS_GEOGRAPHYNAMEOBJID IS NULL
                    OR T1.SYS_GEOGRAPHYNAMEOBJID = ?))
          AND ASS_TYPE = ?)
     AND T1.SYS_OBJECTID > ?
     AND (T1.SYS_ORGNAMEOBJID IN
            (SELECT SPEC_ID
             FROM T_ORGANIZATION
             WHERE SYS_OBJECTID > 0
               AND (TRIPATHSY = '\Organizations\Customers\ABC or TRIPATHSY like '\Organizations\Customers\ABC\%'))
          OR T1.SYS_ORGNAMEOBJID IN
            (SELECT SPEC_ID
             FROM T_ORGANIZATION
             WHERE SYS_OBJECTID > 0
               AND (TRIPATHSY = '\Organizations\Customers\DEF'
                    OR TRIPATHSY LIKE '\Organizations\Customers\DEF\%'))
          OR T1.SYS_ORGNAMEOBJID IN
            (SELECT SPEC_ID
             FROM T_ORGANIZATION
             WHERE SYS_OBJECTID > 0
               AND (TRIPATHSY = '\Organizations\Providers\ABC'
                    OR TRIPATHSY LIKE '\Organizations\Providers\ABC\%'))
          OR T1.SYS_ORGNAMEOBJID IS NULL
          OR T1.SYS_ORGNAMEOBJID = ?)
     AND (T1.SYS_GEOGRAPHYNAMEOBJID IN
            (SELECT SPEC_ID
             FROM M_GEOGRAPHY
             WHERE SYS_OBJECTID > 0
               AND (TRIPATHSY = '\Geography\EMEA\Northern Europe\Norway'
                    OR TRIPATHSY LIKE '\Geography\EMEA\Northern Europe\Norway\%'))
          OR T1.SYS_GEOGRAPHYNAMEOBJID IS NULL
          OR T1.SYS_GEOGRAPHYNAMEOBJID = ?)
    ORDER BY UPPER(T1.triIdTX)

  • Guest
    Reply
    |
    Nov 23, 2023

    We have received the following response for this RFE:

    In our next platform release, we plan to implement an enhancement for geo/org redundant checks, though not exactly in the way that has been requested in the Idea. But these changes should help and we will further evaluate this as part of the performance enhancements we will continue to pursue.

    We would like to understand what is meant by 'not exactly in the way that has been requested in the Idea' to ensure that it still meets our needs.

    Thanks.

  • Guest
    Reply
    |
    May 30, 2023

    Hi Mark, You solution is great if you dont need to limit data access for your users...In this instance we do need to control that. Data Security is therefore a significant consideration. Some of our environments house data for multiple clients and provide access for both customer and service provider.
    The primary issue here is that if a user is a member of multiple groups (CSR many customers) then the resulting SQL generated by the system for their queries is hugely complex within the WHERE clause and of course any sub-queries also contain their own complex WHERE clauses. As you know the security portion of the query is parsed as string literals whereas the rest of the query uses bind variables. Consequenty every user that has a differnt secuity configuration to another generates differnet SQL_ID making tuning in Oracle a challenge.
    I'd be keen to explore any other idea you might have based on the above. This issue is really killing us at the moment


  • Guest
    Reply
    |
    May 22, 2023

    Hi all,

    What we do is populate the top level Organization and Geography on the security group 'TRIRIGA Global Data Access Group' and have added this group to all of our Tririga Users. Think you may want to test this as a potential solution to your issue. All other security groups do not have Org or Geography hierarchies populated. Also if you ever migrate this group to Production you NEED to clear these two fields and manually repopulate them. Break a bunch of stuff if you don't. Don't forget.

    Best, Mark

  • Guest
    Reply
    |
    Sep 29, 2022

    Any updates on the status of this RFE Lou?

  • Guest
    Reply
    |
    May 31, 2022

    Has this RFE been scheduled for a specific release? It has high impact on the clients.

  • Guest
    Reply
    |
    Jan 20, 2022

    Could you please provide an update as to what release this is targetted for?

  • Admin
    Lou Giambalvo
    Reply
    |
    Oct 20, 2021

    Thank you for submitting your Idea. We think this is a good idea and will target this for inclusion in a future release.