472,958 Members | 2,695 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,958 software developers and data experts.

Nested Stored Procedures and Packages

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
7 5587
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
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
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
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
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
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
IY55060 will be in FP6
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Gene Frassetto | last post by:
I have a VB program that calls stored procedures in MS SQL Server that deliver a recordset. I need to do the same thing with Oracle. Following is a simple SQL Server procedure that does this. ...
3
by: WGW | last post by:
Though I am a novice to MS SQL server (2000 I believe), I can do almost! everything I need. Maybe not efficiently, but usefully. However, I have a problem -- a complex query problem... I can...
11
by: jrefactors | last post by:
I want to know the differences between SQL Server 2000 stored procedures and oracle stored procedures? Do they have different syntax? The concept should be the same that the stored procedures...
2
by: Kent Lewandowski | last post by:
hi all, Recently I wrote some stored procedures using java jdbc code (admittedly my first stab) and then tried to implement the same within java packages (for code reuse). I encountered...
3
by: mdaetwyler | last post by:
Hi all I am trying to call a DB/2 v8.2 stored procedure from Perl DBI and am getting an error message telling me, that the routine could not be found in the library path. SQL0444N Routine...
5
by: Rhino | last post by:
I am trying to determine the behaviour of stored procedures in DB2 V8.2.x in Windows/Unix/Linux and how I can control that behaviour. Some documentation in the manuals is confusing the issue...
1
by: sasachi sachi sachi | last post by:
Hi there, I have a data manipulation process written in a Nested Stored procedure that have four levels deeper. When I run these individual procedures individually they all seems to be fine....
2
by: Praveen_db2 | last post by:
Hi All DB2 8.1.3, Windows Is there any method to find the routines dependant on any particular routine. Say, I have a stored procedure PROC1 which calls 2 stored procedures and is called by 3...
0
by: Aravindkumar | last post by:
Hi My name is Aravind. I would like to know how VARRAY and NESTED TABLES can be used in Oracle Stored Procedures / Packages. Please treat this as urgent Aravind
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.