MQSeries.net :: View topic - workflow access passes blowing out production app

callocam · Posted: Mon May 07, 2007 4:51 pm Post subject:

Hi All,
I'm in the heat of battle right now with my production application. It seems that the access paths that are used by the dynamic sql generated by workflow when attempting to build a worklist is binding up the db2 threads.

The DBA has been attempting to optimize the access paths but is only sucessful for a limited period of time.

These are the queries that the DBA has provided us:
SELECT WIV.ACTIVITY_KIND A300, WIV.ICON, WIV.IMPL_NAME,
WIV.CATEGORY_NAME A310, WIV.TOOL_NAMES, WI.WIID, WI.PTID,
WI.BTID, WI.PIID, WI.BIID, WI.ATID, WI.AIID, WI.KIND A340,
WI.STATE A430, WI.PROCESS_STATE A400, WI.PRIORITY A380,
WI.IS_SYS_GRP_REMOTE, WI.OWNER_SYSTEM, WI.OWNER_SYS_GRP,
WI.REASON, WI.CREATED A320, WI.RECEIVED A410, WI.STARTED A420,
WI.SUSPENDED A440, WI.LAST_MODIFIED A350, WI.PROCESS A390,
WI.NAME A360, WI.OWNER A370, WI.DESCRIPTION A330 FROM
GECHP00D.WORK_ITEM_VIEW WIV, GECHP00D.WORK_ITEM WI LEFT OUTER JOIN
GECHP00D.PER_AUTH_FOR_WITMS PA ON WI.OWNER = PA.FOR_PERSON AND
PA.ENTITLED_PRS = ? WHERE WI.BTID = WIV.BTID AND WI.ATID =
WIV.ATID AND WI.STATE <> 4096 AND (WI.OWNER = ? OR
PA.ENTITLED_PRS = ? ) AND ( ( WI.PROCESS = 'EW200704240017281')
AND WI.KIND IN ( 1 ) ) OPTIMIZE FOR 1 ROWS FOR READ ONLY

SELECT WIV.ACTIVITY_KIND A300, WIV.ICON, WIV.IMPL_NAME,
WIV.CATEGORY_NAME A310, WIV.TOOL_NAMES, WI.WIID, WI.PTID,
WI.BTID, WI.PIID, WI.BIID, WI.ATID, WI.AIID, WI.KIND A340,
WI.STATE A430, WI.PROCESS_STATE A400, WI.PRIORITY A380,
WI.IS_SYS_GRP_REMOTE, WI.OWNER_SYSTEM, WI.OWNER_SYS_GRP,
WI.REASON, WI.CREATED A320, WI.RECEIVED A410, WI.STARTED A420,
WI.SUSPENDED A440, WI.LAST_MODIFIED A350, WI.PROCESS A390,
WI.NAME A360, WI.OWNER A370, WI.DESCRIPTION A330 FROM
GECHP00D.WORK_ITEM_VIEW WIV, GECHP00D.WORK_ITEM WI LEFT OUTER JOIN
GECHP00D.PER_AUTH_FOR_WITMS PA ON WI.OWNER = PA.FOR_PERSON AND
PA.ENTITLED_PRS = ? WHERE WI.BTID = WIV.BTID AND WI.ATID =
WIV.ATID AND WI.STATE <> 4096 AND (WI.OWNER = ? OR
PA.ENTITLED_PRS = ? ) AND ( ( WI.STATE = 1 AND WI.OWNER = ? AND
WI.NAME IN ( 'RC Claim Ready', 'RC NonClaim Ready' ) AND (
WI.PROCESS > 'EW' OR WI.PROCESS BETWEEN 'EW' AND
'EW999999999999999' ) ) AND WI.KIND IN ( 1 ) ) ORDER BY A390
OPTIMIZE FOR 21 ROWS FOR READ ONLY

SELECT WIV.ACTIVITY_KIND A300, WIV.ICON, WIV.IMPL_NAME,
WIV.CATEGORY_NAME A310, WIV.TOOL_NAMES, WI.WIID, WI.PTID,
WI.BTID, WI.PIID, WI.BIID, WI.ATID, WI.AIID, WI.KIND A340,
WI.STATE A430, WI.PROCESS_STATE A400, WI.PRIORITY A380,
WI.IS_SYS_GRP_REMOTE, WI.OWNER_SYSTEM, WI.OWNER_SYS_GRP,
WI.REASON, WI.CREATED A320, WI.RECEIVED A410, WI.STARTED A420,
WI.SUSPENDED A440, WI.LAST_MODIFIED A350, WI.PROCESS A390,
WI.NAME A360, WI.OWNER A370, WI.DESCRIPTION A330 FROM
WORK_ITEM_VIEW WIV, WORK_ITEM WI WHERE WI.BTID = WIV.BTID AND
WI.ATID = WIV.ATID AND WI.STATE <> 4096 AND ( ( SUBSTR(CHAR
(WI.LAST_MODIFIED),1,19) = '2007-05-07-10.00.00' ) AND WI.KIND
IN ( 4, 5 ) ) FOR READ ONLY

Does anyone see something in these queries than would cause a dramatic performance spike?

Thanks,
Ettore