Nested Stored Procedures and Packages

Anthony Robinson

Have been encountering an odd issue. Every now and again, certain
packages of stored procedures just become invalid. I'm aware that
dropping or altering an underlying table would render a package
invalid, but we are doing no such thing...

After banging my head on the wall for a bit I noticed that the two
stored procedures that are experiencing this behavior are procedures
that are called from within another procedure (they're not both
called, one or the other is called depending on criteria). I also came
across something in the newsgroup referring to issues with nested
procs and packages.

My question: is there a proper approach to implpementing nested stored
procedures as to avoid package becoming invalid?

I'm currently running v8, FixPak 4 on AIX. I've included the SQL of
the stored procedures in question. The first procedure, is the main
one (the caller). The other two are the callees.

Thanks AGAIN

PROCEDURE 1
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
PROCEDURE 2
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
PROCEDURE 3
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 again for any help...

Nov 12 '05 #1

Serge Rielau

Take a look at SYSCAT.PACKAGEDEP for these two procedures.
(package name = SUBSTR(SYSCAT.ROUTINES.IMPLEMENTATION, 1, 8))
That may give you a hint. I don't think there is such a thing as getting
invalidated because of being nested.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #2

Anthony Robinson

Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<c8**********@hanover.torolab.ibm.com>...

Take a look at SYSCAT.PACKAGEDEP for these two procedures.
(package name = SUBSTR(SYSCAT.ROUTINES.IMPLEMENTATION, 1, 8))
That may give you a hint. I don't think there is such a thing as getting
invalidated because of being nested.

Cheers
Serge

Serge:

Thanks for the reply...but what exactly am I looking for? I found the
two procedures in PACKAGEDEP. I found the package in ROUTINES.

Not sure what this tells me...if you could shed some light that would
be great.

Thanks!

Nov 12 '05 #3

Serge Rielau

You can dump a list of all the objects that this package depends on.
Then you can ask (and hoepfully answer) the question:
Does any of these objects change? I.e dropping an index, altering a
generated column, ...

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #4

Anthony Robinson

Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<c8**********@hanover.torolab.ibm.com>...

Take a look at SYSCAT.PACKAGEDEP for these two procedures.
(package name = SUBSTR(SYSCAT.ROUTINES.IMPLEMENTATION, 1, 8))
That may give you a hint. I don't think there is such a thing as getting
invalidated because of being nested.

Cheers
Serge

A little more info on this issue:

I rebound the two packages that were invalid, everything ran fine for
some time and then - voila - one of them became invalid again!

I have NO idea what the scoop is...

We're getting this error:
17May04 16:47:49:177 CDT TIBIM.5.0.1V5.aimOfflineImage.aimOfflineImage
Error [IM_TASK] AEIM-ADB Agent Results Job-12
[Process-cacheImages,Task-ADBTASK3|validateResultset] "the error
encountered by the ADB agent is
[IBM][CLI Driver][DB2/6000] SQL0727N An error occurred during
implicit system action type "1". Information returned for the error
includes SQLCODE "-551", SQLSTATE "42501" and message tokens
"AMROBI2|EXECUTE|AIM.UPDATEEXPIRATIONDATE". SQLSTATE=56098

The only thing I can think of is that the userid we use to create the
stored procedures is AMROBI2. That user is also the owner and the
binder of all packages. It has full rights: BIND, EXECUTE, and
CONTROL.

The user id that is calling the stored procedure is different than
AMROBI2.

Could this be an issue where the userid calling the stored procedure
needs to be the same as the creator?

I may be grasping at straws here...

Nov 12 '05 #5

Serge Rielau

It says that it couldn't rebind begause AMROBI2 does not have EXECUTE
privilege on AIM.UPDATEEXPIRATIONDATE (anymore?).

Does that ring a bell?

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #6

Anthony Robinson

Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<c8*********@hanover.torolab.ibm.com>...

It says that it couldn't rebind begause AMROBI2 does not have EXECUTE
privilege on AIM.UPDATEEXPIRATIONDATE (anymore?).

Does that ring a bell?

I actually found the answer:

There is an APAR out there - IY55060 - which has the description:
INVALID EXECUTE AUTHORITY ON A STORED PROCEDURE OR FUNCTION IS
INCORRECTLY RETURNED.

This is precidely what is happening in my situation The USER AMROBI2
has EXCUTE permission on every stored procedure. And the user cvalling
the stored procedure isn't even AMROBI2. However, AMROBI2 is the user
that bound all the packages.

The APAR goes on to describe an issue with DB2's internal logic when
it compares a bind timestamp and a grant timestamp. This is what
results in the SQL0551.

At present, the ONLY fix is to rebind the invalid package(s)

Nov 12 '05 #7

Serge Rielau

IY55060 will be in FP6
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #8