Results 1 to 9 of 9

Thread: Drop View

  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: Drop View

    OS/390

    V7.2 - V.8

    Just curious...If I drop a view and recreate it....do I have to recompile/bind DB2 External Stored Procedures for it to see the new view?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Apr 2008
    Posts
    51
    I believe in this case packages referencing the dropped object will be invalidated. You do not need to recompile - an explicit rebind will suffice. Not sure if automatic rebind will take care of this.
    db2topgun.com

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    just did it in v8 compatibility mode and it worked without bind/recompile

    Have to check QA which is at 7.2...which is what the prod level is at
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Apr 2008
    Posts
    51
    Looks like auto-rebind is taking place and resolving the issue for you. You can check if the package is not valid after drop and valid again after first execution attempt (when auto-rebind happens).
    db2topgun.com

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by SuperKuper
    Looks like auto-rebind is taking place and resolving the issue for you. You can check if the package is not valid after drop and valid again after first execution attempt (when auto-rebind happens).
    And where would I check? The catalog?

    Where would I look and what would I look for?

    Thanks for your help
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Apr 2008
    Posts
    51
    select creator,name from sysibm.sysplan where valid <> 'Y'
    db2topgun.com

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Is your routine (stored proc) using dynamic SQL by any chance? If so, you won't have any issues with packages.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Automatic rebind of a SP package (or any static SQL in a package) will take care of this.

    When a dependent object is dropped that a package needs, the package is marked invalid. The first time that someone executes the program (and the invalid package is invoked) DB2 will automatically do a rebind on the package. If the needed object (in this case the view) is still not present during the rebind while the package is marked as invalid, then the package will be marked as inoperative, and an explicit rebind will be needed (after the view is created).

    So if no one tried to execute the program in between the time the view was dropped and then recreated, then the automatic rebind will work.

    The same applies to z/OS except that it applies to packages and plans.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Thanks for the info
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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