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.GetUserIDRetrievals (IN strUserID VARCHAR(64),
IN decAIMConnectionID DECIMAL(13,0))
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
SPECIFIC AIM.GetUserIDRetrieval
LANGUAGE SQL
RESULT SETS 1
P1: BEGIN
DECLARE SELECT_CURSOR CURSOR WITH RETURN FOR
SELECT AIM.AIMRETRIEVAL.AIMRETRIEVALID, AIM.AIMRETRIEVAL.DESCRIPTION,
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID, AIM.ARCHIVERETRIEVAL.STATUSID,
AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE,
AIM.ARCHIVERETRIEVAL.ESTIMATEDRESPONSETIME,
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALRECEIPT,
AIM.ARCHIVERETRIEVAL.ITEMCOUNT,
(SELECT AIM.ARCHIVERETRIEVAL.ITEMCOUNT -
COUNT(AIM.AIMRETRIEVEDITEM.AIMRETRIEVEDITEMID) FROM AIM.AIMRETRIEVEDITEM
WHERE
(AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID =
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID) AND
LENGTH(AIM.AIMRETRIEVEDITEM.IMAGEFRONT) IS NULL)
AS NUMBERIMAGESAVAILABLE
FROM
AIM.AIMCONNECTION,
AIM.AIMQUERY,
AIM.AIMRETRIEVAL,
AIM.ARCHIVERETRIEVAL,
AIM.AIMRETRIEVEDITEM,
AIM.ARCHIVETYPE,
AIM.CHANNEL
WHERE
AIM.AIMCONNECTION.AIMCONNECTIONID = AIM.AIMQUERY.AIMCONNECTIONID
AND AIM.AIMQUERY.AIMQUERYID = AIM.AIMRETRIEVAL.AIMQUERYID
AND AIM.AIMRETRIEVAL.AIMRETRIEVALID = AIM.ARCHIVERETRIEVAL.AIMRETRIEVALID
AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID =
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
AND AIM.ARCHIVERETRIEVAL.ARCHIVEID = AIM.ARCHIVETYPE.ARCHIVEID
AND (AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE >= DATE(CURRENT TIMESTAMP) OR
(AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE IS NULL AND
AIM.ARCHIVERETRIEVAL.ESTIMATEDRESPONSETIME >= CURRENT TIMESTAMP))
AND AIM.ARCHIVETYPE.ARCHIVETYPE = 'D'
AND AIM.AIMCONNECTION.USERID = strUserID
AND AIM.CHANNEL.CHANNEL =
(SELECT CHANNEL FROM AIM.CHANNEL, AIM.CHANNELSESSION, AIM.AIMCONNECTION
WHERE
AIM.CHANNELSESSION.CHANNELSESSIONID = AIM.AIMCONNECTION.CHANNELSESSIONID
AND AIM.CHANNELSESSION.CHANNELID = AIM.CHANNEL.CHANNELID
AND AIM.AIMCONNECTION.AIMCONNECTIONID = decAIMConnectionID)
GROUP BY AIM.AIMRETRIEVAL.AIMRETRIEVALID, AIM.AIMRETRIEVAL.DESCRIPTION,
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID, AIM.ARCHIVERETRIEVAL.STATUSID,
AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE,
AIM.ARCHIVERETRIEVAL.ESTIMATEDRESPONSETIME,
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALRECEIPT,
AIM.ARCHIVERETRIEVAL.ITEMCOUNT,
AIM.ARCHIVERETRIEVAL.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