Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Exclamation Unanswered: What is Going On?!?!

    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!!!!
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    You are the man!!!
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  4. #4
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    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!!
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    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?
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    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.
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •