Nested Stored Procedures and Packages

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Anthony Robinson
    #1

    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 decArchiveRetri evalID DECIMAL(13,0);
    SET decArchiveRetri evalID = (SELECT ArchiveRetrieva lID FROM
    AIM.AIMRetrieve dItem WHERE AIMRetrievedIte mID =
    decAIMRetrieved ItemID);

    IF (SELECT ExpirationDate FROM AIM.ArchiveRetr ieval WHERE
    ArchiveRetrieva lID = decArchiveRetri evalID) < DATE(CURRENT TIMESTAMP)
    THEN
    GOTO NOINSERT;

    ELSE IF (SELECT EstimatedRespon seTime FROM AIM.ArchiveRetr ieval WHERE
    ArchiveRetrieva lID = decArchiveRetri evalID) > CURRENT TIMESTAMP
    AND (SELECT ExpirationDate FROM AIM.ArchiveRetr ieval WHERE
    ArchiveRetrieva lID = decArchiveRetri evalID) > DATE(CURRENT TIMESTAMP)
    THEN
    UPDATE AIM.AIMRetrieve dItem SET
    ResultErrorSeve rity = intResultErrorS everity,
    ResultErrorType = strResultErrorT ype,
    ResultErrorMess age = strResultErrorM essage,
    ImageErrorSever ity = intImageErrorSe verity,
    ImageErrorType = strImageErrorTy pe,
    ImageErrorMessa ge = strImageErrorMe ssage,
    ImageFront = clobImageFront,
    ImageFrontSize = intImageFrontSi ze,
    ImageFrontType = strImageFrontTy pe,
    ImageBack = clobImageBack,
    ImageBackSize = intImageBackSiz e,
    ImageBackType = strImageBackTyp e
    WHERE decAIMRetrieved ItemID = AIMRetrievedIte mID;
    SET intImageCount = (SELECT COUNT(*) FROM AIM.AIMRetrieve dItem WHERE
    ArchiveRetrieva lID = decArchiveRetri evalID and ImageFront IS NOT
    NULL);
    IF intImageCount = (SELECT ItemCount FROM AIM.ArchiveRetr ieval
    WHERE ArchiveRetrieva lID = decArchiveRetri evalID)
    THEN
    CALL AIM.UpdateArchi veRetrieval(dec ArchiveRetrieva lID, 1);
    ELSE
    CALL AIM.UpdateExpir ationDate(decAr chiveRetrievalI D);
    END IF;

    ELSE IF
    (SELECT EstimatedRespon seTime FROM AIM.ArchiveRetr ieval WHERE
    ArchiveRetrieva lID = decArchiveRetri evalID) < CURRENT TIMESTAMP
    AND (SELECT ExpirationDate FROM AIM.ArchiveRetr ieval WHERE
    ArchiveRetrieva lID = decArchiveRetri evalID) > DATE(CURRENT TIMESTAMP)
    THEN
    UPDATE AIM.AIMRetrieve dItem SET
    ResultErrorSeve rity = intResultErrorS everity,
    ResultErrorType = strResultErrorT ype,
    ResultErrorMess age = strResultErrorM essage,
    ImageErrorSever ity = intImageErrorSe verity,
    ImageErrorType = strImageErrorTy pe,
    ImageErrorMessa ge = strImageErrorMe ssage,
    ImageFront = clobImageFront,
    ImageFrontSize = intImageFrontSi ze,
    ImageFrontType = strImageFrontTy pe,
    ImageBack = clobImageBack,
    ImageBackSize = intImageBackSiz e,
    ImageBackType = strImageBackTyp e
    WHERE decAIMRetrieved ItemID = AIMRetrievedIte mID;
    SET intImageCount = (SELECT COUNT(*) FROM AIM.AIMRetrieve dItem WHERE
    ArchiveRetrieva lID = decArchiveRetri evalID and ImageFront IS NOT
    NULL);
    IF intImageCount = (SELECT ItemCount FROM AIM.ArchiveRetr ieval
    WHERE ArchiveRetrieva lID = decArchiveRetri evalID)
    THEN
    CALL AIM.UpdateArchi veRetrieval(dec ArchiveRetrieva lID, 1);
    ELSE
    CALL AIM.UpdateExpir ationDate(decAr chiveRetrievalI D);
    END IF;

    ELSE IF
    (SELECT EstimatedRespon seTime FROM AIM.ArchiveRetr ieval WHERE
    ArchiveRetrieva lID = decArchiveRetri evalID) > CURRENT TIMESTAMP
    AND (SELECT ExpirationDate FROM AIM.ArchiveRetr ieval WHERE
    ArchiveRetrieva lID = decArchiveRetri evalID) IS NULL THEN
    UPDATE AIM.AIMRetrieve dItem SET
    ResultErrorSeve rity = intResultErrorS everity,
    ResultErrorType = strResultErrorT ype,
    ResultErrorMess age = strResultErrorM essage,
    ImageErrorSever ity = intImageErrorSe verity,
    ImageErrorType = strImageErrorTy pe,
    ImageErrorMessa ge = strImageErrorMe ssage,
    ImageFront = clobImageFront,
    ImageFrontSize = intImageFrontSi ze,
    ImageFrontType = strImageFrontTy pe,
    ImageBack = clobImageBack,
    ImageBackSize = intImageBackSiz e,
    ImageBackType = strImageBackTyp e
    WHERE decAIMRetrieved ItemID = AIMRetrievedIte mID;
    SET intImageCount = (SELECT COUNT(*) FROM AIM.AIMRetrieve dItem WHERE
    ArchiveRetrieva lID = decArchiveRetri evalID and ImageFront IS NOT
    NULL);
    IF intImageCount = (SELECT ItemCount FROM AIM.ArchiveRetr ieval
    WHERE ArchiveRetrieva lID = decArchiveRetri evalID)
    THEN
    CALL AIM.UpdateArchi veRetrieval(dec ArchiveRetrieva lID, 1);
    ELSE
    CALL AIM.UpdateExpir ationDate(decAr chiveRetrievalI D);
    END IF;
    --ELSE
    --RETURN 100;
    NOINSERT: RETURN 100;
    END IF ;
    END IF;
    END IF;
    END IF;
    END


    PROCEDURE 2
    P1: BEGIN
    DECLARE dtExpirationDat e DATE;
    IF
    (SELECT
    AIM.CHANNEL.CHA NNEL
    FROM
    AIM.AIMCONNECTI ON,
    AIM.CHANNEL,
    AIM.CHANNELSESS ION,
    AIM.AIMQUERY,
    AIM.AIMRETRIEVA L,
    AIM.ARCHIVERETR IEVAL
    WHERE
    AIM.CHANNEL.CHA NNELID = AIM.CHANNELSESS ION.CHANNELID
    AND AIM.CHANNELSESS ION.CHANNELSESS IONID =
    AIM.AIMCONNECTI ON.CHANNELSESSI ONID
    AND 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 =
    decArchiveRetri evalID) IN ('RIB') THEN
    UPDATE AIM.ArchiveRetr ieval SET ExpirationDate = DATE(CURRENT
    TIMESTAMP) + 30 DAYS
    WHERE ArchiveRetrieva lID = decArchiveRetri evalID;

    ELSE IF
    (SELECT
    AIM.CHANNEL.CHA NNEL
    FROM
    AIM.AIMCONNECTI ON,
    AIM.CHANNEL,
    AIM.CHANNELSESS ION,
    AIM.AIMQUERY,
    AIM.AIMRETRIEVA L,
    AIM.ARCHIVERETR IEVAL
    WHERE
    AIM.CHANNEL.CHA NNELID = AIM.CHANNELSESS ION.CHANNELID
    AND AIM.CHANNELSESS ION.CHANNELSESS IONID =
    AIM.AIMCONNECTI ON.CHANNELSESSI ONID
    AND 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 =
    decArchiveRetri evalID) IN ('INTRANET', 'CIB') THEN
    UPDATE AIM.ArchiveRetr ieval SET ExpirationDate = (AIM.NEXT_BUS_D ATE
    (DATE(CURRENT TIMESTAMP),5))
    WHERE ArchiveRetrieva lID = decArchiveRetri evalID;
    END IF;
    END IF;
    END P1


    PROCEDURE 3
    P1: BEGIN
    IF (SELECT
    AIM.ARCHIVETYPE .ARCHIVETYPE
    FROM
    AIM.ARCHIVERETR IEVAL,
    AIM.ARCHIVETYPE
    WHERE
    AIM.ARCHIVETYPE .ARCHIVEID = AIM.ARCHIVERETR IEVAL.ARCHIVEID
    AND AIM.ARCHIVERETR IEVAL.ARCHIVERE TRIEVALID =
    decArchiveRetri evalID) = 'O'
    THEN
    UPDATE AIM.ArchiveRetr ieval SET StatusID = decStatusID,
    ArchiveRetrieva lResponse = CURRENT TIMESTAMP
    WHERE ArchiveRetrieva lID = decArchiveRetri evalID;
    ELSE IF
    (SELECT
    AIM.ARCHIVETYPE .ARCHIVETYPE
    FROM
    AIM.ARCHIVERETR IEVAL,
    AIM.ARCHIVETYPE
    WHERE
    AIM.ARCHIVETYPE .ARCHIVEID = AIM.ARCHIVERETR IEVAL.ARCHIVEID
    AND AIM.ARCHIVERETR IEVAL.ARCHIVERE TRIEVALID =
    decArchiveRetri evalID) = 'D'
    AND
    (SELECT
    AIM.CHANNEL.CHA NNEL
    FROM
    AIM.AIMCONNECTI ON,
    AIM.CHANNEL,
    AIM.CHANNELSESS ION,
    AIM.AIMQUERY,
    AIM.AIMRETRIEVA L,
    AIM.ARCHIVERETR IEVAL
    WHERE
    AIM.CHANNEL.CHA NNELID = AIM.CHANNELSESS ION.CHANNELID
    AND AIM.CHANNELSESS ION.CHANNELSESS IONID =
    AIM.AIMCONNECTI ON.CHANNELSESSI ONID
    AND 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 =
    decArchiveRetri evalID) IN ('INTRANET', 'CIB') THEN
    UPDATE AIM.ArchiveRetr ieval SET StatusID = decStatusID,
    ArchiveRetrieva lResponse = CURRENT TIMESTAMP, ExpirationDate =
    (AIM.NEXT_BUS_D ATE (DATE(CURRENT TIMESTAMP),5))
    WHERE ArchiveRetrieva lID = decArchiveRetri evalID;

    ELSE IF
    (SELECT
    AIM.ARCHIVETYPE .ARCHIVETYPE
    FROM
    AIM.ARCHIVERETR IEVAL,
    AIM.ARCHIVETYPE
    WHERE
    AIM.ARCHIVETYPE .ARCHIVEID = AIM.ARCHIVERETR IEVAL.ARCHIVEID
    AND AIM.ARCHIVERETR IEVAL.ARCHIVERE TRIEVALID =
    decArchiveRetri evalID) = 'D'
    AND
    (SELECT
    AIM.CHANNEL.CHA NNEL
    FROM
    AIM.AIMCONNECTI ON,
    AIM.CHANNEL,
    AIM.CHANNELSESS ION,
    AIM.AIMQUERY,
    AIM.AIMRETRIEVA L,
    AIM.ARCHIVERETR IEVAL
    WHERE
    AIM.CHANNEL.CHA NNELID = AIM.CHANNELSESS ION.CHANNELID
    AND AIM.CHANNELSESS ION.CHANNELSESS IONID =
    AIM.AIMCONNECTI ON.CHANNELSESSI ONID
    AND 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 =
    decArchiveRetri evalID) IN ('RIB') THEN
    UPDATE AIM.ArchiveRetr ieval SET StatusID = decStatusID,
    ArchiveRetrieva lResponse = CURRENT TIMESTAMP, ExpirationDate =
    DATE(CURRENT TIMESTAMP) + 30 DAYS
    WHERE ArchiveRetrieva lID = decArchiveRetri evalID;
    END IF;
    END IF;
    END IF;
    END P1

    Thanks again for any help...
    Tags: None
    • Serge Rielau
      #2
      Re: Nested Stored Procedures and Packages

      Take a look at SYSCAT.PACKAGED EP for these two procedures.
      (package name = SUBSTR(SYSCAT.R OUTINES.IMPLEME NTATION, 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

        Comment

        • Anthony Robinson
          #3
          Re: Nested Stored Procedures and Packages

          Serge Rielau <srielau@ca.e ye-be-em.com> wrote in message news:<c8dv19$md r$1@hanover.tor olab.ibm.com>.. .[color=blue]
          > Take a look at SYSCAT.PACKAGED EP for these two procedures.
          > (package name = SUBSTR(SYSCAT.R OUTINES.IMPLEME NTATION, 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[/color]

          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!

            Comment

            • Serge Rielau
              #4
              Re: Nested Stored Procedures and Packages

              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

                Comment

                • Anthony Robinson
                  #5
                  Re: Nested Stored Procedures and Packages

                  Serge Rielau <srielau@ca.e ye-be-em.com> wrote in message news:<c8dv19$md r$1@hanover.tor olab.ibm.com>.. .[color=blue]
                  > Take a look at SYSCAT.PACKAGED EP for these two procedures.
                  > (package name = SUBSTR(SYSCAT.R OUTINES.IMPLEME NTATION, 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[/color]

                  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.a imOfflineImage. aimOfflineImage
                  Error [IM_TASK] AEIM-ADB Agent Results Job-12
                  [Process-cacheImages,Tas k-ADBTASK3|valida teResultset] "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|EXECUT E|AIM.UPDATEEXP IRATIONDATE". 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...

                    Comment

                    • Serge Rielau
                      #6
                      Re: Nested Stored Procedures and Packages

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

                      Does that ring a bell?

                      --
                      Serge Rielau
                      DB2 SQL Compiler Development
                      IBM Toronto Lab

                        Comment

                        • Anthony Robinson
                          #7
                          Re: Nested Stored Procedures and Packages

                          Serge Rielau <srielau@ca.e ye-be-em.com> wrote in message news:<c8gars$ls $1@hanover.toro lab.ibm.com>...[color=blue]
                          > It says that it couldn't rebind begause AMROBI2 does not have EXECUTE
                          > privilege on AIM.UPDATEEXPIR ATIONDATE (anymore?).
                          >
                          > Does that ring a bell?[/color]

                          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)

                            Comment

                            • Serge Rielau
                              #8
                              Re: Nested Stored Procedures and Packages

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

                                Comment

                                Previous template Next
                                Working...