IO09413: You get poor performance when you are querying the item type definition

APAR status

• Closed as program error.

Error description

• Customer determined a couple of queries the Library Server
  generates has bad performance.  The following -
  Altre informazioni importanti
  Monitoring C.M. DB2, we intercepted this query (in pkg
  ICMPLSGT):
  SELECT   T.*
  FROM (
  SELECT   DISTINCT
           A.ITEMTYPEVIEWID,
           A.ITEMTYPEID,
           C.PRIVS
  FROM     ICMADP0.ICMSTITVIEWDEFS A,
           ICMADP0.ICMSTCOMPILEDACL C
  WHERE    ( C.ACL = -1 AND C.UNUM = 2025)
  UNION
  SELECT   DISTINCT
           A.ITEMTYPEVIEWID,
           A.ITEMTYPEID,
           C.PRIVS
  FROM     ICMADP0.ICMSTITVIEWDEFS A,
           ICMADP0.ICMSTCOMPILEDACL C
  WHERE    (( C.ACL = A.ACLCODE OR  A.ITEMTYPEVIEWID<1000)
             AND C.UNUM = 2025)
  UNION
  SELECT   DISTINCT
           A.ITEMTYPEVIEWID,
           A.ITEMTYPEID,
           C.PRIVS
  FROM     ICMADP0.ICMSTITVIEWDEFS A,
           ICMADP0.ICMSTCOMPILEDACL C
  WHERE    ( ((C.ACL = A.ACLCODE ) OR (A.ITEMTYPEVIEWID<1000))
            AND C.UNUM = 1
            AND ( EXISTS ( SELECT 1
                        FROM ICMADP0.ICMSTCOMPILEDPERM P
                       WHERE P.UNUM = 2025
                         AND PRIVDEFCODE IN ( 122 )
                     )
            ))
       ) AS T
  ORDER BY 2 ASC, 1 ASC
  DB2 executes about 37.000 GP and 45sec.CPU to return the 119
  rows
  requested by the query.
  The second subquery (which returns 88 rows):
  SELECT   DISTINCT
           A.ITEMTYPEVIEWID,
           A.ITEMTYPEID,
           C.PRIVS
  FROM     ICMADP0.ICMSTITVIEWDEFS A,
           ICMADP0.ICMSTCOMPILEDACL C
  WHERE    (( C.ACL = A.ACLCODE OR  A.ITEMTYPEVIEWID<1000)
             AND C.UNUM = 2025)
  is responable of almost all these GP and CPU
  (perhaps this "piece of SQL" is also included in other
  queries?).
  *****
  The developer has worked out changes to improve performance and
  she wants this APAR to do the fix.
  

Local fix

• N/A
  

Problem summary

• When there are many item types, you might experience poor
  performance when querying the item type definition.
  

Problem conclusion

• This problem was fixed in DB2 Content Manager Version 8.3 Fix
  Pack 10.
  

Temporary fix

Comments

APAR Information

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

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

Fix information

• Fixed component name

  LIBRARY SERVER

• Fixed component ID

  5724B1907

Applicable component levels