IBM Support

IV94361: QUERY FIRED FROM REPORTS AGAINST T_ORGANIZATION MAY TAKE 100 % OF DB RESOURCES AFTER UPGRADE TO 3.5.2

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as fixed if next.

Error description

  • After upgrading to 3.5.2, customers may see similar queries
    being fired from report runs and getting system freeze after
    that since DB will be extremely busy executing them.
    
    SELECT T1.triNameTX AS T1_1098,
    T1.triIdTX AS T1_1097,
    T1.triPathTX AS T1_1117,
    T1.triFormLabelSY AS T1_1111,
    T1.SYS_TYPE1 AS T1_SYS_TYPE1,
    T1.SYS_GUIID AS T1_SYS_GUIID,
    T1.SPEC_ID AS T1_SPEC_ID
    FROM T_ORGANIZATION T1
    WHERE T1.SYS_GUIID IN (:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10
    ,:11 ,:
    12 ,:13 ,:14 ,:15 ,:16 ,:17 ,:18 ,:19 ,:20 ,:21 ,:22 ,:23 ,:24
    ,:25 ,:
    26 ,:27 ,:28 )
    AND T1.SYS_PROJECTID = :29
    AND ( (T1.triStatusCL != :30  OR T1.triStatusCL IS NULL) )
    AND UPPER(T1.triNameTX) LIKE :31
    AND T1.SYS_OBJECTID > :32
    AND (T1.SYS_GEOGRAPHYNAMEOBJID
    IN (select CHILD_SPEC_ID from IBS_SPEC_STRUCTURE START
    WITH PARENT_SPEC_ID = :33
    CONNECT BY PRIOR CHILD_SPEC_ID = PARENT_SPEC_ID
    UNION ALL
    SELECT :34  FROM DUAL)
    OR T1.SYS_GEOGRAPHYNAMEOBJID IS NULL
    OR T1.SYS_GEOGRAPHYNAMEOBJID = :35 )
    AND (T1.SYS_ORGNAMEOBJID IN (select CHILD_SPEC_ID
    from IBS_SPEC_STRUCTURE
    START WITH PARENT_SPEC_ID = :36
    CONNECT BY PRIOR CHILD_SPEC_ID = PARENT_SPEC_ID
    UNION ALL SELECT :37  FROM DUAL)
    OR T1.SYS_ORGNAMEOBJID IS NULL)
    ORDER BY UPPER(T1.triNameTX), UPPER(T1.triIdTX)
    

Local fix

  • N/A
    

Problem summary

  • For large Org/Geo tree, don't contribute sql unless needed for
    the org or the geo portions, if security allows access to all
    /Organizations, don't add the sql to check for the security
    since it is not needed, and will cause Oracle to do unnecessary
    work.
    

Problem conclusion

  • When applying Geo or Org security, a group with root level
    \Organizations and a geography set at a low level like
    \Geography\North America\USA\Nevada\Las Vegas will no longer
    apply Org level security check on queries.   The same goes for
    security groups unrestricted at the Geo level but set at a low
    level of the org.  The security check will be applied on a geo
    and/or org basis based on the root level security setup for the
    geo and/or org.
    This is targeted to the 1h2017 release as well as the 3.5.2.1
    fix pack.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IV94361

  • Reported component name

    TRI APP PLTFM R

  • Reported component ID

    5725F26RE

  • Reported release

    352

  • Status

    CLOSED FIN

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2017-03-17

  • Closed date

    2017-03-23

  • Last modified date

    2017-03-23

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

Modules/Macros

  • 999
    

Fix information

Applicable component levels

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSHEB3","label":"IBM TRIRIGA Application Platform"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"352","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]

Document Information

Modified date:
30 March 2022