I am gettign a very strange error.

I'm executing a stored procedure and am getting the strangest error:

[IBM][CLI Driver][DB2/6000] SQL0727N An error occurred during implicit system action ty
pe "1". Information returned for the error includes SQLCODE "-551", SQLSTATE "42501" and message to
kens "AMROBI2|EXECUTE|AIM.UPDATEARCHIVERETRIEVAL". SQLSTATE=56098

When I run this via the command line or any other tool, it runs just fine. When we run this through our application, that's when it throws the error.

I haven't found a whole lot on this.

Any help would be GREATLY apreciated!!!!

SQL0551N "<authorization-ID>" does not have the privilege to
perform operation "<operation>" on object
"<name>".

Looks like your problem is with a package .... Grant execute on the package of the stored procedure to the user ....

It may also be due to the user not having bind on the package ... I suggest you to check if all packages are valid and rebind them if not ... or you can issue

db2rbind <dbname>

HTH

Sathyaram

You are the man!!!

Rebinding the packages did the trick...

Could you (or anyone) shed some light on why a package(s) would become invalid or need rebinding? It just seemed to happen out of the blue. The stored procedure compiled fine and ran great for a few days and then...BAMM!!!

Any insight would be appreciated. The powers that be are looking for an explanation into the why and how.

thanks again!!

The most common cause of a package getting invalidated is the dropping objects on which the package depends ..

For eg, the package has a statement, select col1 from table1, then dropping the table1 will invalidate the package ... When the package is accessed the next time, db2 will attempt to rebind the package ... If the user requesting the package does not have the authority, it returns errors (your case) ... If the user requesting the package has the authority, then it will bind (the bind will fail if the table1 has not been recreated) ...

There may be other reasons also, but don't remember any at the moment

Cheers
sathyaram

So let's say for example, I have this stored proc. I drop and recreate it, but this time I create the stored proc with an explicit specifc name as oppsed to a system generated name.

Could this cause a bind issue?

Let me put it some other way :

When you drop a SP, the SP's package is dropped ... So, invalidation is not because of dropping a package.

The package is invalidated if objects (like table,view, alias etc) on which the procedure/package is dependent on, are dropped ...

Cheers
Sathyaram

That's what I thought. But this seems to be happening at random. I have one stored procedure that will call one of two other stored procedures, depending on certain criteria.

It appears that it's these two called procedures that keep encountering this invalid package issue. The important thing is that we are not dropping or altering any objects at all.

I've inmcluded the DDL for all three. Any insight as to what could be happening would be greatly appreciated.

MAIN PROCEDURE:

P1: BEGIN
DECLARE intImageCount INTEGER;
DECLARE strSQLString VARCHAR(256);
DECLARE decArchiveRetrievalID DECIMAL(13,0);
SET decArchiveRetrievalID = (SELECT ArchiveRetrievalID FROM AIM.AIMRetrievedItem WHERE AIMRetrievedItemID = decAIMRetrievedItemID);

IF (SELECT ExpirationDate FROM AIM.ArchiveRetrieval WHERE ArchiveRetrievalID = decArchiveRetrievalID) < DATE(CURRENT TIMESTAMP) THEN
GOTO NOINSERT;

ELSE IF (SELECT EstimatedResponseTime FROM AIM.ArchiveRetrieval WHERE ArchiveRetrievalID = decArchiveRetrievalID) > CURRENT TIMESTAMP
AND (SELECT ExpirationDate FROM AIM.ArchiveRetrieval WHERE ArchiveRetrievalID = decArchiveRetrievalID) > DATE(CURRENT TIMESTAMP) THEN
UPDATE AIM.AIMRetrievedItem SET
ResultErrorSeverity = intResultErrorSeverity,
ResultErrorType = strResultErrorType,
ResultErrorMessage = strResultErrorMessage,
ImageErrorSeverity = intImageErrorSeverity,
ImageErrorType = strImageErrorType,
ImageErrorMessage = strImageErrorMessage,
ImageFront = clobImageFront,
ImageFrontSize = intImageFrontSize,
ImageFrontType = strImageFrontType,
ImageBack = clobImageBack,
ImageBackSize = intImageBackSize,
ImageBackType = strImageBackType
WHERE decAIMRetrievedItemID = AIMRetrievedItemID;
SET intImageCount = (SELECT COUNT(*) FROM AIM.AIMRetrievedItem WHERE ArchiveRetrievalID = decArchiveRetrievalID and ImageFront IS NOT NULL);
IF intImageCount = (SELECT ItemCount FROM AIM.ArchiveRetrieval WHERE ArchiveRetrievalID = decArchiveRetrievalID)
THEN
CALL AIM.UpdateArchiveRetrieval(decArchiveRetrievalID, 1);
ELSE
CALL AIM.UpdateExpirationDate(decArchiveRetrievalID);
END IF;

ELSE IF
(SELECT EstimatedResponseTime FROM AIM.ArchiveRetrieval WHERE ArchiveRetrievalID = decArchiveRetrievalID) < CURRENT TIMESTAMP
AND (SELECT ExpirationDate FROM AIM.ArchiveRetrieval WHERE ArchiveRetrievalID = decArchiveRetrievalID) > DATE(CURRENT TIMESTAMP) THEN
UPDATE AIM.AIMRetrievedItem SET
ResultErrorSeverity = intResultErrorSeverity,
ResultErrorType = strResultErrorType,
ResultErrorMessage = strResultErrorMessage,
ImageErrorSeverity = intImageErrorSeverity,
ImageErrorType = strImageErrorType,
ImageErrorMessage = strImageErrorMessage,
ImageFront = clobImageFront,
ImageFrontSize = intImageFrontSize,
ImageFrontType = strImageFrontType,
ImageBack = clobImageBack,
ImageBackSize = intImageBackSize,
ImageBackType = strImageBackType
WHERE decAIMRetrievedItemID = AIMRetrievedItemID;
SET intImageCount = (SELECT COUNT(*) FROM AIM.AIMRetrievedItem WHERE ArchiveRetrievalID = decArchiveRetrievalID and ImageFront IS NOT NULL);
IF intImageCount = (SELECT ItemCount FROM AIM.ArchiveRetrieval WHERE ArchiveRetrievalID = decArchiveRetrievalID)
THEN
CALL AIM.UpdateArchiveRetrieval(decArchiveRetrievalID, 1);
ELSE
CALL AIM.UpdateExpirationDate(decArchiveRetrievalID);
END IF;

ELSE IF
(SELECT EstimatedResponseTime FROM AIM.ArchiveRetrieval WHERE ArchiveRetrievalID = decArchiveRetrievalID) > CURRENT TIMESTAMP
AND (SELECT ExpirationDate FROM AIM.ArchiveRetrieval WHERE ArchiveRetrievalID = decArchiveRetrievalID) IS NULL THEN
UPDATE AIM.AIMRetrievedItem SET
ResultErrorSeverity = intResultErrorSeverity,
ResultErrorType = strResultErrorType,
ResultErrorMessage = strResultErrorMessage,
ImageErrorSeverity = intImageErrorSeverity,
ImageErrorType = strImageErrorType,
ImageErrorMessage = strImageErrorMessage,
ImageFront = clobImageFront,
ImageFrontSize = intImageFrontSize,
ImageFrontType = strImageFrontType,
ImageBack = clobImageBack,
ImageBackSize = intImageBackSize,
ImageBackType = strImageBackType
WHERE decAIMRetrievedItemID = AIMRetrievedItemID;
SET intImageCount = (SELECT COUNT(*) FROM AIM.AIMRetrievedItem WHERE ArchiveRetrievalID = decArchiveRetrievalID and ImageFront IS NOT NULL);
IF intImageCount = (SELECT ItemCount FROM AIM.ArchiveRetrieval WHERE ArchiveRetrievalID = decArchiveRetrievalID)
THEN
CALL AIM.UpdateArchiveRetrieval(decArchiveRetrievalID, 1);
ELSE
CALL AIM.UpdateExpirationDate(decArchiveRetrievalID);
END IF;
--ELSE
--RETURN 100;
NOINSERT: RETURN 100;
END IF ;
END IF;
END IF;
END IF;
END

ONE OF THE CALLED PROCEDURES:
P1: BEGIN
DECLARE dtExpirationDate DATE;
IF
(SELECT
AIM.CHANNEL.CHANNEL
FROM
AIM.AIMCONNECTION,
AIM.CHANNEL,
AIM.CHANNELSESSION,
AIM.AIMQUERY,
AIM.AIMRETRIEVAL,
AIM.ARCHIVERETRIEVAL
WHERE
AIM.CHANNEL.CHANNELID = AIM.CHANNELSESSION.CHANNELID
AND AIM.CHANNELSESSION.CHANNELSESSIONID = AIM.AIMCONNECTION.CHANNELSESSIONID
AND AIM.AIMCONNECTION.AIMCONNECTIONID = AIM.AIMQUERY.AIMCONNECTIONID
AND AIM.AIMQUERY.AIMQUERYID = AIM.AIMRETRIEVAL.AIMQUERYID
AND AIM.AIMRETRIEVAL.AIMRETRIEVALID = AIM.ARCHIVERETRIEVAL.AIMRETRIEVALID
AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID = decArchiveRetrievalID) IN ('RIB') THEN
UPDATE AIM.ArchiveRetrieval SET ExpirationDate = DATE(CURRENT TIMESTAMP) + 30 DAYS
WHERE ArchiveRetrievalID = decArchiveRetrievalID;

ELSE IF
(SELECT
AIM.CHANNEL.CHANNEL
FROM
AIM.AIMCONNECTION,
AIM.CHANNEL,
AIM.CHANNELSESSION,
AIM.AIMQUERY,
AIM.AIMRETRIEVAL,
AIM.ARCHIVERETRIEVAL
WHERE
AIM.CHANNEL.CHANNELID = AIM.CHANNELSESSION.CHANNELID
AND AIM.CHANNELSESSION.CHANNELSESSIONID = AIM.AIMCONNECTION.CHANNELSESSIONID
AND AIM.AIMCONNECTION.AIMCONNECTIONID = AIM.AIMQUERY.AIMCONNECTIONID
AND AIM.AIMQUERY.AIMQUERYID = AIM.AIMRETRIEVAL.AIMQUERYID
AND AIM.AIMRETRIEVAL.AIMRETRIEVALID = AIM.ARCHIVERETRIEVAL.AIMRETRIEVALID
AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID = decArchiveRetrievalID) IN ('INTRANET', 'CIB') THEN
UPDATE AIM.ArchiveRetrieval SET ExpirationDate = (AIM.NEXT_BUS_DATE (DATE(CURRENT TIMESTAMP),5))
WHERE ArchiveRetrievalID = decArchiveRetrievalID;
END IF;
END IF;
END P1

THE SECOND CALLED PROCEDURE:
P1: BEGIN
IF (SELECT
AIM.ARCHIVETYPE.ARCHIVETYPE
FROM
AIM.ARCHIVERETRIEVAL,
AIM.ARCHIVETYPE
WHERE
AIM.ARCHIVETYPE.ARCHIVEID = AIM.ARCHIVERETRIEVAL.ARCHIVEID
AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID = decArchiveRetrievalID) = 'O'
THEN
UPDATE AIM.ArchiveRetrieval SET StatusID = decStatusID, ArchiveRetrievalResponse = CURRENT TIMESTAMP
WHERE ArchiveRetrievalID = decArchiveRetrievalID;
ELSE IF
(SELECT
AIM.ARCHIVETYPE.ARCHIVETYPE
FROM
AIM.ARCHIVERETRIEVAL,
AIM.ARCHIVETYPE
WHERE
AIM.ARCHIVETYPE.ARCHIVEID = AIM.ARCHIVERETRIEVAL.ARCHIVEID
AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID = decArchiveRetrievalID) = 'D'
AND
(SELECT
AIM.CHANNEL.CHANNEL
FROM
AIM.AIMCONNECTION,
AIM.CHANNEL,
AIM.CHANNELSESSION,
AIM.AIMQUERY,
AIM.AIMRETRIEVAL,
AIM.ARCHIVERETRIEVAL
WHERE
AIM.CHANNEL.CHANNELID = AIM.CHANNELSESSION.CHANNELID
AND AIM.CHANNELSESSION.CHANNELSESSIONID = AIM.AIMCONNECTION.CHANNELSESSIONID
AND AIM.AIMCONNECTION.AIMCONNECTIONID = AIM.AIMQUERY.AIMCONNECTIONID
AND AIM.AIMQUERY.AIMQUERYID = AIM.AIMRETRIEVAL.AIMQUERYID
AND AIM.AIMRETRIEVAL.AIMRETRIEVALID = AIM.ARCHIVERETRIEVAL.AIMRETRIEVALID
AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID = decArchiveRetrievalID) IN ('INTRANET', 'CIB') THEN
UPDATE AIM.ArchiveRetrieval SET StatusID = decStatusID, ArchiveRetrievalResponse = CURRENT TIMESTAMP, ExpirationDate = (AIM.NEXT_BUS_DATE (DATE(CURRENT TIMESTAMP),5))
WHERE ArchiveRetrievalID = decArchiveRetrievalID;

ELSE IF
(SELECT
AIM.ARCHIVETYPE.ARCHIVETYPE
FROM
AIM.ARCHIVERETRIEVAL,
AIM.ARCHIVETYPE
WHERE
AIM.ARCHIVETYPE.ARCHIVEID = AIM.ARCHIVERETRIEVAL.ARCHIVEID
AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID = decArchiveRetrievalID) = 'D'
AND
(SELECT
AIM.CHANNEL.CHANNEL
FROM
AIM.AIMCONNECTION,
AIM.CHANNEL,
AIM.CHANNELSESSION,
AIM.AIMQUERY,
AIM.AIMRETRIEVAL,
AIM.ARCHIVERETRIEVAL
WHERE
AIM.CHANNEL.CHANNELID = AIM.CHANNELSESSION.CHANNELID
AND AIM.CHANNELSESSION.CHANNELSESSIONID = AIM.AIMCONNECTION.CHANNELSESSIONID
AND AIM.AIMCONNECTION.AIMCONNECTIONID = AIM.AIMQUERY.AIMCONNECTIONID
AND AIM.AIMQUERY.AIMQUERYID = AIM.AIMRETRIEVAL.AIMQUERYID
AND AIM.AIMRETRIEVAL.AIMRETRIEVALID = AIM.ARCHIVERETRIEVAL.AIMRETRIEVALID
AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID = decArchiveRetrievalID) IN ('RIB') THEN
UPDATE AIM.ArchiveRetrieval SET StatusID = decStatusID, ArchiveRetrievalResponse = CURRENT TIMESTAMP, ExpirationDate = DATE(CURRENT TIMESTAMP) + 30 DAYS
WHERE ArchiveRetrievalID = decArchiveRetrievalID;
END IF;
END IF;
END IF;
END P1

Thanks in advance for any suggestions or assistance.