VALIDATE (RUN) maybe results in Danamic SQL ?

Patric Becker

VALIDATE (RUN) maybe results in Danamic SQL ?
Hello List :-)

We came across a problem we can´t find anything about in the manuals, but
maybe one of you can tell us if we´re right :

We have some applications using VALIDATE (RUN), because they were moved
to production before the tables were created. Everything worked well when
the tables were created and the applications were executing.

So far, so good.

Now one of our system guys found out, that some applications which are
using static SQL are locking the catalog. It seems to be that the reason
is the following :

Those applications bound with VALIDATE (RUN) are checking the catalog
for existence of the accessed tables if no REBIND had been done after
creating the tables. So every time a SQL of one of those programs is
executing, a "minibind" will occur and probably locking the catalog.

May this be correct ? Or are we simply wrong ?

Thanks a lot for your suggestions in advance :-)

Regards,
Patric



Dave Nance

Re: VALIDATE (RUN) maybe results in Danamic SQL ?
(in response to Patric Becker)
Yes, DB2 will revalidate the package everytime it is executed, which will cause some extra locking on catalog, also, will make execution time take longer. If there is no need for validate(run) any longer, then rebind using validate(bind).

>>> [login to unmask email] 01/09/02 02:17PM >>>
Hello List :-)

We came across a problem we can t find anything about in the manuals, but
maybe one of you can tell us if we re right :

We have some applications using VALIDATE (RUN), because they were moved
to production before the tables were created. Everything worked well when
the tables were created and the applications were executing.

So far, so good.

Now one of our system guys found out, that some applications which are
using static SQL are locking the catalog. It seems to be that the reason
is the following :

Those applications bound with VALIDATE (RUN) are checking the catalog
for existence of the accessed tables if no REBIND had been done after
creating the tables. So every time a SQL of one of those programs is
executing, a "minibind" will occur and probably locking the catalog.

May this be correct ? Or are we simply wrong ?

Thanks a lot for your suggestions in advance :-)

Regards,
Patric

=======================

This message, including any attachments, is intended solely for the use of the named
recipient(s) and may contain confidential and/or privileged information. Any
unauthorized review, use, disclosure or distribution of this communications is expressly
prohibited. If you are not the intended recipient, please contact the sender by reply e-mail
and destroy any and all copies of the original message. Thank you.



[login to unmask email]

Re: VALIDATE (RUN) maybe results in Danamic SQL ?
(in response to Dave Nance)
VALIDATE RUN means that each time the plan executes the catalogs will be
checked; doesn't mean anything is rebound. If the plan is marked as
invalid but operative a dynamic bind will occur for each execution.

George



Rob Crane

Re: VALIDATE (RUN) maybe results in Danamic SQL ?
(in response to truman.g.brown@VERIZON.COM)
Another option would be to bind with VALIDATE(BIND) and use
SQLERROR(CONTINUE), then you can just do a REBIND of the dependent
packages when the table changes are made.

-Rob

Patric Becker wrote:
>
> Hello List :-)
>
> We came across a problem we can´t find anything about in the manuals, but
> maybe one of you can tell us if we´re right :
>
> We have some applications using VALIDATE (RUN), because they were moved
> to production before the tables were created. Everything worked well when
> the tables were created and the applications were executing.
>
> So far, so good.
>
> Now one of our system guys found out, that some applications which are
> using static SQL are locking the catalog. It seems to be that the reason
> is the following :
>
> Those applications bound with VALIDATE (RUN) are checking the catalog
> for existence of the accessed tables if no REBIND had been done after
> creating the tables. So every time a SQL of one of those programs is
> executing, a "minibind" will occur and probably locking the catalog.
>
> May this be correct ? Or are we simply wrong ?
>
> Thanks a lot for your suggestions in advance :-)
>
> Regards,
> Patric
>
>
>



Sanjeev (CTS) S

Re: VALIDATE (RUN) maybe results in Danamic SQL ?
(in response to Rob Crane)
Patric,

VALIDATE(RUN) checks for the existence of objects and authorizations when it
is referenced during the runtime. If i am not wrong it must be taking share
lock on the catalog. I have found in Admin guide that it is called as
incremental bind but it does lesser work than what a bind does. Along with
this if REOPT(VARS) is specified, access path determination is also done.

With Best Regards,
Sanjeev

> -----Original Message-----
> From: Patric Becker [SMTP:[login to unmask email]
> Sent: Thursday, January 10, 2002 12:47 AM
> To: [login to unmask email]
> Subject: VALIDATE (RUN) maybe results in Danamic SQL ?
>
> Hello List :-)
>
> We came across a problem we can´t find anything about in the manuals, but
> maybe one of you can tell us if we´re right :
>
> We have some applications using VALIDATE (RUN), because they were moved
> to production before the tables were created. Everything worked well when
> the tables were created and the applications were executing.
>
> So far, so good.
>
> Now one of our system guys found out, that some applications which are
> using static SQL are locking the catalog. It seems to be that the reason
> is the following :
>
> Those applications bound with VALIDATE (RUN) are checking the catalog
> for existence of the accessed tables if no REBIND had been done after
> creating the tables. So every time a SQL of one of those programs is
> executing, a "minibind" will occur and probably locking the catalog.
>
> May this be correct ? Or are we simply wrong ?
>
> Thanks a lot for your suggestions in advance :-)
>
> Regards,
> Patric
>
>
>
> http://www.ryci.com/db2-l. The owners of the list can be reached at
> [login to unmask email]



Patric Becker

Re: VALIDATE (RUN) maybe results in Danamic SQL ?
(in response to Sanjeev (CTS) S)
Hi !

Thanks a lot to everyone for your help.

Greetings from germany

Patric