Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    78

    Unhappy Unanswered: up and down store procedures

    Hello,

    I am using DB2/UDB V8.1 on Windows. There is a strange situation, some of the stored procedure deployed to production machine become inoperative. The following is the exception I got:

    Exception in class Beans.CodesBean COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0572N Package "SYSFILES.P1054621" is inoperative. SQLSTATE=51028

    But it never failed on the machine that original build the SP. I'd appreciate your ideas.

    Christine

  2. #2
    Join Date
    Jul 2003
    Posts
    78

    Re: up and down store procedures

    Hi guys, Perhaps it is the following reason:

    SQL0572N Package "<pkgname>" is inoperative.


    Explanation: The package "<pkgname>" is marked as inoperative and must be explicitly rebound before it can be used. This package cannot be used because one or more user-defined functions that it depends upon have been dropped.

    User Response: Explicitly rebind the named package using either the REBIND or the BIND command.

    sqlcode: -572

    sqlstate: 51028


    I did found that one of the database object has been dropped and re-created. So does it mean everytime when I make changes on database object, I have to find all the related objects that depend on it, and re-bind them again? that is painful.

    Christine


    Originally posted by christine2003
    Hello,

    I am using DB2/UDB V8.1 on Windows. There is a strange situation, some of the stored procedure deployed to production machine become inoperative. The following is the exception I got:

    Exception in class Beans.CodesBean COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0572N Package "SYSFILES.P1054621" is inoperative. SQLSTATE=51028

    But it never failed on the machine that original build the SP. I'd appreciate your ideas.

    Christine

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: up and down store procedures

    I did found that one of the database object has been dropped and re-created. So does it mean everytime when I make changes on database object, I have to find all the related objects that depend on it, and re-bind them again? that is painful.
    Not really ..

    BSCHEAMA and BNAME columns in SYSCAT.PACKAGEDEP give you the list of tables referred to in a package

    Cheers
    Sathyaram

    Originally posted by christine2003
    Hi guys, Perhaps it is the following reason:

    SQL0572N Package "<pkgname>" is inoperative.


    Explanation: The package "<pkgname>" is marked as inoperative and must be explicitly rebound before it can be used. This package cannot be used because one or more user-defined functions that it depends upon have been dropped.

    User Response: Explicitly rebind the named package using either the REBIND or the BIND command.

    sqlcode: -572

    sqlstate: 51028


    I did found that one of the database object has been dropped and re-created. So does it mean everytime when I make changes on database object, I have to find all the related objects that depend on it, and re-bind them again? that is painful.

    Christine
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Jul 2003
    Posts
    78

    Re: up and down store procedures

    Oh, that is a useful tip. So if I want to check function dependency, I will check FUNCDEP. But where can I map the FUNCNAME(actually it looks like ID in the table) with the real FUNCNAME? Thanks a lot.

    Christine

    Originally posted by sathyaram_s
    Not really ..

    BSCHEAMA and BNAME columns in SYSCAT.PACKAGEDEP give you the list of tables referred to in a package

    Cheers
    Sathyaram

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: up and down store procedures

    Not really sure what you mean by 'looks like ID in the table' ... But based on some assumption, let me answer

    You can join syscat.funcdep and syscat.functions to get the function name

    If this does not answer the question you have in mind, please post an example ...

    For details on SYSCAT views, refer to an appendeix in SQL Reference Guide ... there is a detailed explanation for each table used and every column in the view ..

    HTH

    Sathyaram

    Originally posted by christine2003
    Oh, that is a useful tip. So if I want to check function dependency, I will check FUNCDEP. But where can I map the FUNCNAME(actually it looks like ID in the table) with the real FUNCNAME? Thanks a lot.

    Christine
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Sep 2003
    Posts
    8

    Re: up and down store procedures

    Originally posted by christine2003
    Hi guys, Perhaps it is the following reason:

    SQL0572N Package "<pkgname>" is inoperative.


    Explanation: The package "<pkgname>" is marked as inoperative and must be explicitly rebound before it can be used. This package cannot be used because one or more user-defined functions that it depends upon have been dropped.

    User Response: Explicitly rebind the named package using either the REBIND or the BIND command.

    sqlcode: -572

    sqlstate: 51028


    I did found that one of the database object has been dropped and re-created. So does it mean everytime when I make changes on database object, I have to find all the related objects that depend on it, and re-bind them again? that is painful.

    Christine

    I believe that , package was dropped --

Posting Permissions

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