Odd Stored Procedure Behavior

[Anthony Robinison]

I'm seeing some really messed up behavior from a stored procedure. Here is
the DDL:

CREATE PROCEDURE AIM.GetUserIDRe trievals (IN strUserID VARCHAR(64),
IN decAIMConnectio nID DECIMAL(13,0))
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
SPECIFIC AIM.GetUserIDRe trieval
LANGUAGE SQL
RESULT SETS 1
P1: BEGIN
DECLARE SELECT_CURSOR CURSOR WITH RETURN FOR
SELECT AIM.AIMRETRIEVA L.AIMRETRIEVALI D, AIM.AIMRETRIEVA L.DESCRIPTION,
AIM.ARCHIVERETR IEVAL.ARCHIVERE TRIEVALID, AIM.ARCHIVERETR IEVAL.STATUSID,
AIM.ARCHIVERETR IEVAL.EXPIRATIO NDATE,
AIM.ARCHIVERETR IEVAL.ESTIMATED RESPONSETIME,
AIM.ARCHIVERETR IEVAL.ARCHIVERE TRIEVALRECEIPT,
AIM.ARCHIVERETR IEVAL.ITEMCOUNT ,
(SELECT AIM.ARCHIVERETR IEVAL.ITEMCOUNT -
COUNT(AIM.AIMRE TRIEVEDITEM.AIM RETRIEVEDITEMID ) FROM AIM.AIMRETRIEVE DITEM
WHERE
(AIM.AIMRETRIEV EDITEM.ARCHIVER ETRIEVALID =
AIM.ARCHIVERETR IEVAL.ARCHIVERE TRIEVALID) AND
LENGTH(AIM.AIMR ETRIEVEDITEM.IM AGEFRONT) IS NULL)
AS NUMBERIMAGESAVA ILABLE
FROM
AIM.AIMCONNECTI ON,
AIM.AIMQUERY,
AIM.AIMRETRIEVA L,
AIM.ARCHIVERETR IEVAL,
AIM.AIMRETRIEVE DITEM,
AIM.ARCHIVETYPE ,
AIM.CHANNEL
WHERE
AIM.AIMCONNECTI ON.AIMCONNECTIO NID = AIM.AIMQUERY.AI MCONNECTIONID
AND AIM.AIMQUERY.AI MQUERYID = AIM.AIMRETRIEVA L.AIMQUERYID
AND AIM.AIMRETRIEVA L.AIMRETRIEVALI D = AIM.ARCHIVERETR IEVAL.AIMRETRIE VALID
AND AIM.ARCHIVERETR IEVAL.ARCHIVERE TRIEVALID =
AIM.AIMRETRIEVE DITEM.ARCHIVERE TRIEVALID
AND AIM.ARCHIVERETR IEVAL.ARCHIVEID = AIM.ARCHIVETYPE .ARCHIVEID
AND (AIM.ARCHIVERET RIEVAL.EXPIRATI ONDATE >= DATE(CURRENT TIMESTAMP) OR
(AIM.ARCHIVERET RIEVAL.EXPIRATI ONDATE IS NULL AND
AIM.ARCHIVERETR IEVAL.ESTIMATED RESPONSETIME >= CURRENT TIMESTAMP))
AND AIM.ARCHIVETYPE .ARCHIVETYPE = 'D'
AND AIM.AIMCONNECTI ON.USERID = strUserID
AND AIM.CHANNEL.CHA NNEL =
(SELECT CHANNEL FROM AIM.CHANNEL, AIM.CHANNELSESS ION, AIM.AIMCONNECTI ON
WHERE
AIM.CHANNELSESS ION.CHANNELSESS IONID = AIM.AIMCONNECTI ON.CHANNELSESSI ONID
AND AIM.CHANNELSESS ION.CHANNELID = AIM.CHANNEL.CHA NNELID
AND AIM.AIMCONNECTI ON.AIMCONNECTIO NID = decAIMConnectio nID)
GROUP BY AIM.AIMRETRIEVA L.AIMRETRIEVALI D, AIM.AIMRETRIEVA L.DESCRIPTION,
AIM.ARCHIVERETR IEVAL.ARCHIVERE TRIEVALID, AIM.ARCHIVERETR IEVAL.STATUSID,
AIM.ARCHIVERETR IEVAL.EXPIRATIO NDATE,
AIM.ARCHIVERETR IEVAL.ESTIMATED RESPONSETIME,
AIM.ARCHIVERETR IEVAL.ARCHIVERE TRIEVALRECEIPT,
AIM.ARCHIVERETR IEVAL.ITEMCOUNT ,
AIM.ARCHIVERETR IEVAL.ITEMCOUNT
ORDER BY AIMRETRIEVALID;
OPEN SELECT_CURSOR;
END P1

For some reason this procedure is running absolutely dog slow!!!! If I run
the SQL in the procedure ad-hoc (command line, etc.), the query completes
sub-second; the procedure takes about three minutes to return the result
set...

Can anyone shed any light on what the issue may be?!? Do stored procedures
run in a different memory pool or something? Does the query optimizer need
some help?

Any help would be greatly appreciated!!!

AMR