DB2V7-Z/OS - DISABLE CACHED DYNAMIC STATEMENT

LUIZ FORONI

DB2V7-Z/OS - DISABLE CACHED DYNAMIC STATEMENT
Hi listers,

I would like to know if there is a way to disable cached dynamic statement for some queries, when I use
option CACHE DYNAMIC SQL = YES.
I don´t want queries from dsntep2 cached.


thanks in advance


Luiz Foroni
Administrador de Banco de Dados
GEINF - Gerência de Engenharia das Infras-estruturas Tecnológicas
CAIXA SEGUROS
TEL 55-61-2192-2939
FAX 55-61-3328-2108

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Steen Rasmussen

Re: DB2V7-Z/OS - DISABLE CACHED DYNAMIC STATEMENT
(in response to LUIZ FORONI)
Hello Roberto,

I know the BIND/REBIND statement has a parameter named KEEPDYNAMIC(YES/NO), so you can rebind the DSNTEP2 package using this parameter.
The dynamic statement will still go into the dynamic statement cache, but it will go away once the statement has been committed (as far as I remember).

Steen Rasmussen
CA
Senior Consultant DB2 tools
Tel : +1-630-505-6673 (US direct)
Tel : +1-815-274-9589 (US mobile)
Tel : +45-22 15 44 98 (Europe mobile)
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Luiz Roberto Foroni
Sent: Tuesday, January 16, 2007 12:44 PM
To: [login to unmask email]
Subject: [DB2-L] DB2V7-Z/OS - DISABLE CACHED DYNAMIC STATEMENT

Hi listers,

I would like to know if there is a way to disable cached dynamic statement for some queries, when I use
option CACHE DYNAMIC SQL = YES.
I don´t want queries from dsntep2 cached.


thanks in advance


Luiz Foroni
Administrador de Banco de Dados
GEINF - Gerência de Engenharia das Infras-estruturas Tecnológicas
CAIXA SEGUROS
TEL 55-61-2192-2939
FAX 55-61-3328-2108

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

William Favero

Re: DB2V7-Z/OS - DISABLE CACHED DYNAMIC STATEMENT
(in response to Steen Rasmussen)
The dynamic statement cache (global cahce) that you establish at
installation is a system wide value. It is either on for everything or off
for everything.

Willie

Original Message:
-----------------
From: Luiz Roberto Foroni [login to unmask email]
Date: Tue, 16 Jan 2007 16:43:40 -0200
To: [login to unmask email]
Subject: [DB2-L] DB2V7-Z/OS - DISABLE CACHED DYNAMIC STATEMENT


Hi listers,

I would like to know if there is a way to disable cached dynamic statement
for some queries, when I use
option CACHE DYNAMIC SQL = YES.
I don´t want queries from dsntep2 cached.


thanks in advance


Luiz Foroni
Administrador de Banco de Dados
GEINF - Gerência de Engenharia das Infras-estruturas Tecnológicas
CAIXA SEGUROS
TEL 55-61-2192-2939
FAX 55-61-3328-2108

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

--------------------------------------------------------------------
mail2web - Check your email from the web at
http://mail2web.com/ .

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

William Favero

Re: DB2V7-Z/OS - DISABLE CACHED DYNAMIC STATEMENT
(in response to William Favero)
This response is from Suresh..

Luiz,

As Willie has explained this is a all-or-nothing option - for a zparm.

However, by binding dsntep2 as reopt(vars), you will disable dynamic
statement caching for all sql it runs. Normally, this adds overhead for
static sql but here, there should be no added overhead, just the loss of
caching.

Note - I have NOT done this (why would I not want caching?!?), but I
think this should work.

Thx
Suresh

Luiz Roberto Foroni wrote:

>Hi listers,
>
>I would like to know if there is a way to disable cached dynamic statement for some queries, when I use
>option CACHE DYNAMIC SQL = YES.
>I don´t want queries from dsntep2 cached.
>
>
>thanks in advance
>
>
>Luiz Foroni
>Administrador de Banco de Dados
>GEINF - Gerência de Engenharia das Infras-estruturas Tecnológicas
>CAIXA SEGUROS
>TEL 55-61-2192-2939
>FAX 55-61-3328-2108
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
>
>
>
>

--
Willie
My DB2 blog --> http://blogs.ittoolbox.com/database/db2zos
My Cars Website --> http://www.Red-Corvettes.com
62 Black LS2, 99 MagRed (MTI 422) R8C, 03 Anniversary R8C
05 Harley Dark Cherry Red Heritage Softail
I just love dark red.. LOL
My Personal Website --> http://www.WillieFavero.com
Cowboy HOG Website --> http://www.cowboyhog.org/
National Corvette Museum --> http://www.corvettemuseum.com/

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Patrick Bossman

Re: DB2V7-Z/OS - DISABLE CACHED DYNAMIC STATEMENT
(in response to William Favero)
Dynamic SQL subject to REOPT(VARS) will not be cached. However, the SQL
will undergo two prepares.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Lockwood Lyon

Re: DB2V7-Z/OS - DISABLE CACHED DYNAMIC STATEMENT
(in response to Patrick Bossman)
So, to summarize: If I Bind Plan DSNTIAUL with REOPT(VARS), then any SQL
statement I execute with this utility will be prepared twice? Why is
that?

- Lock Lyon
Principal DBA
Fifth Third Bancorp

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Patrick Bossman
Sent: Thursday, January 18, 2007 9:39 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2V7-Z/OS - DISABLE CACHED DYNAMIC STATEMENT

Dynamic SQL subject to REOPT(VARS) will not be cached. However, the SQL
will undergo two prepares.



This e-mail transmission contains information that is confidential and may be privileged. It is intended only for the addressee(s) named above. If you receive this e-mail in error, please do not read, copy or disseminate it in any manner. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of this information is prohibited. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please erase it from your computer system. Your assistance in correcting this error is appreciated.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Patrick Bossman

Re: DB2V7-Z/OS - DISABLE CACHED DYNAMIC STATEMENT
(in response to Lockwood Lyon)
Hello Lockwood,
I did a little further tracing on this. Not all SQL will be prepared twice,
you've got to have a predicate which benefits from reoptimization. If there
are no predicates, or you don't have any parameter markers / special
registers (current date, current timestamp), then you'll avoid the
reoptimization. You're going to have all literals in DSNTIAUL, but you
could use special registers which would trigger the reoptimization at
execution time (open cursor).

Regards,
Pat

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

James Campbell

Re: DB2V7-Z/OS - DISABLE CACHED DYNAMIC STATEMENT
(in response to Patrick Bossman)
If I might poke my nose in here, I think there is a semantic disconnect in
this conversation.

I think Pat means that when some SQL is executed a second time there will
be (subject to the caveat below) a second PREPARE; I think Lockwood
interpreted the original statement to mean that there will be two PREPAREs
the first time time some SQL is executed - which would certainly call for
some further explanation.

James Campbell

On 18 Jan 2007 at 19:25, Patrick Bossman wrote:

> Hello Lockwood,
> I did a little further tracing on this. Not all SQL will be prepared twice,
> you've got to have a predicate which benefits from reoptimization. If there
> are no predicates, or you don't have any parameter markers / special
> registers (current date, current timestamp), then you'll avoid the
> reoptimization. You're going to have all literals in DSNTIAUL, but you
> could use special registers which would trigger the reoptimization at
> execution time (open cursor).
>
> Regards,
> Pat
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Lockwood Lyon

Re: DB2V7-Z/OS - DISABLE CACHED DYNAMIC STATEMENT
(in response to James Campbell)
My thanks for James' nose-poking :) That's what I (mis)understood
regarding a second prepare.

"Semantic disconnect" ?! Wow, gotta get out my Funk and Wagnals ...

- Lock Lyon
Principal DBA
Fifth Third Bancorp

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of James Campbell
Sent: Friday, January 19, 2007 1:31 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2V7-Z/OS - DISABLE CACHED DYNAMIC STATEMENT

If I might poke my nose in here, I think there is a semantic disconnect
in
this conversation.

I think Pat means that when some SQL is executed a second time there
will
be (subject to the caveat below) a second PREPARE; I think Lockwood
interpreted the original statement to mean that there will be two
PREPAREs
the first time time some SQL is executed - which would certainly call
for
some further explanation.

James Campbell

On 18 Jan 2007 at 19:25, Patrick Bossman wrote:

> Hello Lockwood,
> I did a little further tracing on this. Not all SQL will be prepared
twice,
> you've got to have a predicate which benefits from reoptimization. If
there
> are no predicates, or you don't have any parameter markers / special
> registers (current date, current timestamp), then you'll avoid the
> reoptimization. You're going to have all literals in DSNTIAUL, but
you
> could use special registers which would trigger the reoptimization at
> execution time (open cursor).
>
> Regards,
> Pat
>


This e-mail transmission contains information that is confidential and may be privileged. It is intended only for the addressee(s) named above. If you receive this e-mail in error, please do not read, copy or disseminate it in any manner. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of this information is prohibited. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please erase it from your computer system. Your assistance in correcting this error is appreciated.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Steven Lamb

Re: DB2V7-Z/OS - DISABLE CACHED DYNAMIC STATEMENT
(in response to Lockwood Lyon)
Get yet what out?!

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Paresh Adhia

Re: DB2V7-Z/OS - DISABLE CACHED DYNAMIC STATEMENT
(in response to Steven Lamb)
Patrick Bossman wrote:
> Dynamic SQL subject to REOPT(VARS) will not be cached. However, the SQL
> will undergo two prepares.
>
Just for my clarification. Will the SQL not go to the cache, or the plan
for the cached SQL will not be used? I am just asking because, let's
say, if I were to run a large mix of dynamic SQLs and with REOPT(VARS)
option, then of course they'll be reoptimized; but will these SQLs be
cached for use by some other non REOPT(VARS) plan?

Thanks

P Adhia

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Patrick Bossman

Re: DB2V7-Z/OS - DISABLE CACHED DYNAMIC STATEMENT
(in response to Paresh Adhia)
Dynamic SQL executed from within a package / plan bound with
REOPT(VARS/ALWAYS) will neither search nor populate the cache.

Happy Friday,
Pat

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm