Dynamic SQL Caching

Michael Ryle

Dynamic SQL Caching
One of the features of the software product my company markets for OS390
allows our users to process DB2 SQL statements dynamically through our
product in MVS and CICS. We accomplish this more or less in the standard
way, i.e., by doing a PREPARE followed by a DESCRIBE INTO a sqlda followed
by an EXECUTE, etc. We would like to be able to take advantage of the
dynamic SQL caching feature introduced in DB2 V5 but don't know how to
determine whether dynamic SQL caching is in effect for a particular DB2
subsystem. Does anyone know of a way to determine at run-time whether
dynamic SQL caching is present and enabled? Thanks.

Michael Ryle
TSI International, Inc.
Phone: (508) 240-0432
Fax: (520) 752-3548
[login to unmask email]


craig patton

Re: Dynamic SQL Caching
(in response to Michael Ryle)
Michael,

I don't know if you can find out dynamically at runtime as it is the
'CACHEDYN' DSNZPARM that determines DYNAMIC CACHING for a SSID.
There are tools available (ie. RC/QUERY from CA/Platinum) to determine the
whether or not it is turned on for each SSID.

HTH,
Craig Patton
Contract DBA




>From: Michael Ryle <[login to unmask email]>
>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
>To: [login to unmask email]
>Subject: Dynamic SQL Caching
>Date: Tue, 12 Oct 1999 09:53:51 -0400
>
>One of the features of the software product my company markets for OS390
>allows our users to process DB2 SQL statements dynamically through our
>product in MVS and CICS. We accomplish this more or less in the standard
>way, i.e., by doing a PREPARE followed by a DESCRIBE INTO a sqlda followed
>by an EXECUTE, etc. We would like to be able to take advantage of the
>dynamic SQL caching feature introduced in DB2 V5 but don't know how to
>determine whether dynamic SQL caching is in effect for a particular DB2
>subsystem. Does anyone know of a way to determine at run-time whether
>dynamic SQL caching is present and enabled? Thanks.
>
>Michael Ryle
>TSI International, Inc.
>Phone: (508) 240-0432
>Fax: (520) 752-3548
>[login to unmask email]
>
>

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com



Venkat (PCA) Pillay

Re: Dynamic SQL Caching
(in response to craig patton)
Michael

IFI is answer to that. IFCID 106 gives DSNZPARM parameter. There are
two easy way to do this -

1. Use your own IFI commands
2. Use a free-bee stored procedure DSNWZP. Installation job DSNTIJSG, which
binds and define stored procedure. This runs under DB2 SPAS. To invoke use
standard call - "CALL DSNWZP (:Host-var)" , The host-var is CHAR(8600).

HTH
Pillay

> -----Original Message-----
> From: Michael Ryle [SMTP:[login to unmask email]
> Sent: Tuesday, October 12, 1999 9:54 AM
> To: [login to unmask email]
> Subject: Dynamic SQL Caching
>
> One of the features of the software product my company markets for OS390
> allows our users to process DB2 SQL statements dynamically through our
> product in MVS and CICS. We accomplish this more or less in the standard
> way, i.e., by doing a PREPARE followed by a DESCRIBE INTO a sqlda followed
> by an EXECUTE, etc. We would like to be able to take advantage of the
> dynamic SQL caching feature introduced in DB2 V5 but don't know how to
> determine whether dynamic SQL caching is in effect for a particular DB2
> subsystem. Does anyone know of a way to determine at run-time whether
> dynamic SQL caching is present and enabled? Thanks.
>
> Michael Ryle
> TSI International, Inc.
> Phone: (508) 240-0432
> Fax: (520) 752-3548
> [login to unmask email]
>
>