Hi,

We have a procedure that was running fine once we dropped and recreated its performance dropped drastically from milli seconds to 20 seconds, this in turn is causing the application slow down.

Here is the procedure:

CREATE PROCEDURE "MMS"."SP_EQIP_LOC_VIS_GBL_POS" (
IN "@MACHINES_ID" VARCHAR(15000),
IN "@ORG_ID" INTEGER,
IN "@START_TIME" TIMESTAMP,
IN "@END_TIME" TIMESTAMP )
SPECIFIC "SP_EQIP_LOC_VIS_GBL_POS"
DYNAMIC RESULT SETS 1
LANGUAGE SQL
NOT DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
OLD SAVEPOINT LEVEL
BEGIN
DECLARE RES CURSOR WITH RETURN FOR

WITH TESTDATA(ITEM_NUMBERS) AS (VALUES CAST(@MACHINES_ID AS CLOB(15 K)))
,RECURSIVE_CTE(K, MACHID, ITEM_NUMBERS) AS
(
SELECT 0
, CAST('' AS VARCHAR(10))
, ITEM_NUMBERS||','
FROM TESTDATA
UNION ALL
SELECT K
, CAST(SUBSTR(ITEM_NUMBERS, 1, N-1) AS VARCHAR(10))
, SUBSTR(ITEM_NUMBERS, N+1)
FROM (
SELECT K + 1 AS K , LOCATE(',', ITEM_NUMBERS) AS N , ITEM_NUMBERS
FROM RECURSIVE_CTE WHERE K < 1000000
AND LENGTH(ITEM_NUMBERS) > 0
) S
)


SELECT DISTINCT C.ID as ID, C.EQIP_ID as EQIP_ID, C.CALL_IN_TS as CALL_IN_TS, C.GTWY_TYP_ID as GTWY_TYP_ID, C.GBL_POS_ID as GBL_POS_ID,
C.TRM_ID as TRM_ID,GBLPOS.LAT_QTY as LAT_QTY,GBLPOS.LONG_QTY as LONG_QTY--,GBLPOS.ALT_QTY as ALT_QTY,GBLPOS.GPS_FIX_TS as GPS_FIX_TS
FROM (SELECT A.MACH_ID,
A.CO_ID,
A.VIS_BEG_TS AS VIS_BEG_TS,
COALESCE (
VIS_END_TS,
(SELECT current timestamp - current timezone
FROM sysibm.sysdummy1))
AS VIS_END_TS
FROM SHRDM.MACH_CO_VIS A
WHERE A.mach_co_id IN (SELECT INTEGER(MACHID) FROM RECURSIVE_CTE WHERE K > 0 )) B,
MMS.EQIP_CALL_IN_INFO C, MMS.GBL_POS GBLPOS
WHERE B.MACH_ID = C.EQIP_ID
AND C.CALL_IN_TS >= B.VIS_BEG_TS
AND C.CALL_IN_TS <= B.VIS_END_TS
AND C.CALL_IN_TS >= TIMESTAMP(@START_TIME)
AND C.CALL_IN_TS <= TIMESTAMP(@END_TIME)
AND GBLPOS.GBL_POS_ID=C.GBL_POS_ID
ORDER BY C.EQIP_ID, C.CALL_IN_TS DESC
WITH UR FOR READ ONLY;

OPEN RES;
RETURN;

END;


Please suggest any changes also it will be helpful if you can help identify the bottlenecks for any procedures in general and how to go about debugging and fine tuning Procedure.

If i would like to retrieve SQL from the procedure to find explain how do i do for any procedure is there a tool or something that can be done.

So far we have run reorg, runstats and rebind also rebind with reopt on the base tables and packages associated to the above procedure.

Appreciate your help...

Kavitha

My guess is that the access plan changed when you recreated the stored procedure. Determine the package the was created when you created the stored procedure and use db2expln to show the access plan.

Andy

We have rebinded with re opt to ensure the access plan is good.

Kavitha

Have you looked at the access plan to make sure it is OK?

Andy

Access plan looks good.

Are there any options or parameters on Server/Database that we can set ?

What is the general rule of thumb to debug procedures or in general performance issues.

Any document any one can provide will be great help.

Thanks,
Kavitha

Andy pointed you in the right direction for the issue you had posted.

If you are looking for generic performance tuning guidelines, developerworks will be a good place to start.

Cheers

Sathyaram