Thread: DB2 and Process Server query
07-24-11, 21:21 #1Registered User
- Join Date
- Jul 2011
Unanswered: DB2 and Process Server query
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 v220.127.116.11 Enterprise Server Edition on Redhat 5.
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,
ON (TA.CONTAINMENT_CTX_ID = QP3.PIID)
ON (TA.CONTAINMENT_CTX_ID = QP2.PIID)
ON (TA.CONTAINMENT_CTX_ID = QP1.PIID),
ON (TT.TKTID = TTD.TKTID),
WPSBE00.SWI_T.QIID = WPSBE00.RETRIEVED_USER_T.QIID AND
ACCESS_KEY = 50
WHERE ACCESS_KEY = 51
WHERE ACCESS_KEY = 52
WHERE ((TA.WSID_1 = WI.WSID OR
TA.WSID_2 = WI.WSID) AND
TA.TKTID = TT.TKTID)
FETCH FIRST 1 ROWS ONLY
07-24-11, 23:56 #2Registered User
- Join Date
- Feb 2008
It seems that the query was written by a novice programmer.
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.
07-25-11, 19:42 #3Registered User
- Join Date
- Feb 2008
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.
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.