DB2 and Process Server query

ajseymour · #1 (**permalink**) 07-24-11, 20:21

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;

tonkuma · #2 (**permalink**) 07-24-11, 22:56

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.

tonkuma · #3 (**permalink**) 07-25-11, 18:42

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
;

Internet Services

Web Development

Digital Marketing

Consumer Tech