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?

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.

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

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

select creator,name from sysibm.sysplan where valid <> 'Y'

Is your routine (stored proc) using dynamic SQL by any chance? If so, you won't have any issues with packages.

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.

Thanks for the info