Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2011
    Posts
    1

    Unanswered: DB2 and Process Server query

    Hi,

    We are running WebSphere Process Server (7.0) and using DB2 as the database. Process Server occasionally sends a query to DB2 that DB2 takes 20-30 minutes to execute, chewing up all the CPU for the duration of the query. During the execution of the query, DB2 reads in around 9 million records from the database - really weird as the biggest table in the query has only around 2000 rows. I'm assuming that the query is doing something nasty or there is something wrong with our environment, but I can't work it out.

    We're running DB2 v9.5.0.7 Enterprise Server Edition on Redhat 5.

    SELECT DISTINCT
    TA.TKIID AS ID,
    TA.APPLIC_NAME AS APPLICATION_NAME,
    TA.NAME_SPACE AS Namespace,
    TT.TKTID AS TemplateID,
    TT.NAME AS TemplateName,
    TA.OWNER AS Owner,
    TA.ORIGINATOR AS Originator,
    TTD.DISPLAY_NAME AS Title,
    TTD.DESCRIPTION AS Description,
    TA.STARTED AS CreationDate,
    TA.LAST_MODIFIED AS ModificationDate,
    TA.PRIORITY AS Priority,
    TA.STATE AS State,
    TA.IS_ESCALATED AS Escalated,
    QP1.NUMBER_VALUE AS DealingID,
    QP2.STRING_VALUE AS PriorityDate,
    QP3.STRING_VALUE AS UrgencyIndicator,
    WI.OWNER_ID,
    WI.EVERYBODY,
    WI.GROUP_NAME,
    WI.REASON
    FROM
    WPSBE00.TASK1 TA
    LEFT JOIN
    WPSBE00.QUERY_PROPERTY QP3
    ON (TA.CONTAINMENT_CTX_ID = QP3.PIID)
    LEFT JOIN
    WPSBE00.QUERY_PROPERTY QP2
    ON (TA.CONTAINMENT_CTX_ID = QP2.PIID)
    LEFT JOIN
    WPSBE00.QUERY_PROPERTY QP1
    ON (TA.CONTAINMENT_CTX_ID = QP1.PIID),
    WPSBE00.TASK_TEMPL TT
    LEFT JOIN
    WPSBE00.TASK_TEMPL_DESC TTD
    ON (TT.TKTID = TTD.TKTID),
    (
    SELECT
    WPSBE00.SWI_T.EVERYBODY,
    WPSBE00.SWI_T.GROUP_NAME,
    WPSBE00.RETRIEVED_USER_T.OWNER_ID,
    WPSBE00.SWI_T.REASON,
    WPSBE00.SWI_T.WSID,
    WPSBE00.SWI_T.OBJECT_TYPE
    FROM
    WPSBE00.SWI_T,
    WPSBE00.RETRIEVED_USER_T
    WHERE
    WPSBE00.SWI_T.QIID = WPSBE00.RETRIEVED_USER_T.QIID AND
    ACCESS_KEY = 50
    UNION ALL
    SELECT
    EVERYBODY,
    GROUP_NAME,
    OWNER_ID,
    REASON,
    WSID,
    OBJECT_TYPE
    FROM WPSBE00.SWI_T
    WHERE ACCESS_KEY = 51
    UNION ALL
    SELECT
    EVERYBODY,
    GROUP_NAME,
    OWNER_ID,
    REASON,
    WSID,
    OBJECT_TYPE
    FROM WPSBE00.SWI_T
    WHERE ACCESS_KEY = 52
    )
    AS WI
    WHERE ((TA.WSID_1 = WI.WSID OR
    TA.WSID_2 = WI.WSID) AND
    TA.TKTID = TT.TKTID)
    FETCH FIRST 1 ROWS ONLY
    WITH UR;

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It seems that the query was written by a novice programmer.

    For example:
    1) "SELECT DISTINCT" and "FETCH FIRST 1 ROWS ONLY"

    2) Mix of traditional join syntax and new join syntax.

    3-1) QP1, QP2, QP3 were same table and joined with same ON condition.
    3-2) Two new column names PriorityDate and UrgencyIndicator which seems not related each others are used for a column QP2.STRING_VALUE and QP3.STRING_VALUE.
    (If ON conditions for QP2 and QP3 were different, this might be rational depending on the table design.)

    4) Unnecessary parentheses were used in very simple ON conditions and a WHERE clause.

    I thought that you had better to request to provider of this query for providing reasonable queries written by programmer(s) who have average or more knowledge about relational DBMS and SQL language.
    Last edited by tonkuma; 07-25-11 at 00:03.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although, someone may have ideas of tuning,
    I thought that all the effort of tuning without modifying bad desined/coded query would be in vain(much effort and a little gain).

    Though it may be difficult to replace the query,
    IF you can replace the query,
    this might be a starting example to improve the query.

    Before the improvement, there are some points to be clear on the requirements/specifications of the query and the design of the tables.
    1) How to select/specify QP1, QP2, QP3.
    What data/values in STRING_VALUE column.

    2) How to choose a corresponding row from UNION ALLed sub-selects inside (...) WI.
    They are rows selected from WPSBE00.SWI_T which are ACCESS_KEY IN (50 , 51 , 52).

    3) Order of choice in final result.
    The query must be assumed multiple rows returned.
    Because, it selects a row from multiple rows by "FETCH FIRST 1 ROWS ONLY"


    Example 0: Not complete, need additional code and modifications before execution.
    Code:
    SELECT -- DISTINCT
           TA.TKIID AS ID
         , TA.APPLIC_NAME   AS application_name
         , TA.NAME_SPACE    AS namespace
         , TT.TKTID         AS template_ID
         , TT.NAME          AS template_Name
         , TA.OWNER         AS owner
         , TA.ORIGINATOR    AS originator
         , TTD.DISPLAY_NAME AS title
         , TTD.DESCRIPTION  AS description
         , TA.STARTED       AS creation_date
         , TA.LAST_MODIFIED AS modification_date
         , TA.PRIORITY      AS priority
         , TA.STATE         AS state
         , TA.IS_ESCALATED  AS escalated
    /* May need to Add expression(s)
        to select/specify QP1, QP2, QP3.
         , QP1.NUMBER_VALUE AS dealing_id
         , QP2.STRING_VALUE AS priority_date
         , QP3.STRING_VALUE AS urgency_indicator
    */
         , COALESCE(
              RU.OWNER_ID
            , WI.OWNER_ID ) AS Owner_ID
         , WI.EVERYBODY
         , WI.GROUP_NAME
         , WI.REASON
     FROM
           WPSBE00.TASK1            TA
     INNER JOIN
           WPSBE00.TASK_TEMPL       TT
       ON  TT.TKTI D = TA.TKTID
     LEFT JOIN
           WPSBE00.TASK_TEMPL_DESC  TTD
       ON  TTD.TKTID = TT.TKTID
     LEFT JOIN
           WPSBE00.QUERY_PROPERTY   QP
       ON  QP.PIID   = TA.CONTAINMENT_CTX_ID
       AND <...>
     INNER JOIN
           WPSBE00.SWI_T            WI
       ON  WI.WSID  
           IN (TA.WSID_1 , TA.WSID_2)
       AND WI.ACCESS_KEY
           IN (50 , 51 , 52)
     LEFT JOIN
           WPSBE00.RETRIEVED_USER_T RU
       ON  WI.ACCESS_KEY = 50
       AND RU.QIID = WI.QIID
    /*
     WHERE TA.TKIID ...
     and/or
     ORDER BY ...
    */
    FETCH FIRST 1 ROWS ONLY
    WITH UR
    ;
    Last edited by tonkuma; 07-25-11 at 20:10.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •