[DB2v8 NFM z/OS 1.8] ODBC plans and packages, ISOLATION, and CURRENTDATA

Mark McCormack

[DB2v8 NFM z/OS 1.8] ODBC plans and packages, ISOLATION, and CURRENTDATA
ODBC uses plan DSNACLI and packages named DSNCLIxx. The bind commands
that were run for these in our shop were taken directly from one of the
members in the DB2v8 samplib. The bind command for the plan does not
specify ISOLATION. The bind commands for most of the packages do not
specify ISOLATION. (There are a few exceptions, ISOLATION(CS) for
DSNCLICS, for example). None of the bind commands specify CURRENTDATA.
Consequently, most default to ISOLATION(RR) and CURRENTDATA(YES).

We run an IBM product called Content Manager OnDemand, and it is running
a lot of traffic through package DSNCLINF (new with DB2v8), which takes
the default values. I suspect that we are experiencing a lot of
excessive and unnecessary locking as a result. Since IBM has no idea
about what kind of dynamic SQL will be run by its customers through
ODBC, perhaps the default bind parameters are understandable from IBM's
point of view. I think that we would be better served by rebinding
these with ISOLATION(CS) and CURRENTDATA(NO).

So my questions are aimed at ODBC users in general and CMOD users (if
any subscribe to this list) in particular.
1-Have any of you chosen to rebind plan and/or packages with
ISOLATION(CS) CURRENTDATA(NO)?
2-If so, what were your experiences?

Regards,
Mark McCormack


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Myron Miller

Re: [DB2v8 NFM z/OS 1.8] ODBC plans and packages, ISOLATION, and CURRENTDATA
(in response to Mark McCormack)
I rebound these a number of years ago exactly like you suggested and have had
zero problems since then.

Myron
--- "McCormack, Mark A" <[login to unmask email]> wrote:

> ODBC uses plan DSNACLI and packages named DSNCLIxx. The bind commands
> that were run for these in our shop were taken directly from one of the
> members in the DB2v8 samplib. The bind command for the plan does not
> specify ISOLATION. The bind commands for most of the packages do not
> specify ISOLATION. (There are a few exceptions, ISOLATION(CS) for
> DSNCLICS, for example). None of the bind commands specify CURRENTDATA.
> Consequently, most default to ISOLATION(RR) and CURRENTDATA(YES).
>
> We run an IBM product called Content Manager OnDemand, and it is running
> a lot of traffic through package DSNCLINF (new with DB2v8), which takes
> the default values. I suspect that we are experiencing a lot of
> excessive and unnecessary locking as a result. Since IBM has no idea
> about what kind of dynamic SQL will be run by its customers through
> ODBC, perhaps the default bind parameters are understandable from IBM's
> point of view. I think that we would be better served by rebinding
> these with ISOLATION(CS) and CURRENTDATA(NO).
>
> So my questions are aimed at ODBC users in general and CMOD users (if
> any subscribe to this list) in particular.
> 1-Have any of you chosen to rebind plan and/or packages with
> ISOLATION(CS) CURRENTDATA(NO)?
> 2-If so, what were your experiences?
>
> Regards,
> Mark McCormack
>
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list
> archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug
> under the Listserv tab. While at the site, you can also access the IDUG
> Online Learning Center, Tech Library and Code Place, see the latest IDUG
> conference information, and much more. If you have not yet signed up for
> Basic Membership in IDUG, available at no cost, click on Member Services at
> http://www.idug.org/lsms
>

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms