Dynamic SQL against z/os DB2 slow after upgrading to DB2 Connect V8.2

Scott Hodgin

Dynamic SQL against z/os DB2 slow after upgrading to DB2 Connect V8.2
We recently upgraded a production windows server to DB2 Connect EE v8.2 and
now it seems that our dynamic SQL applications accessing data from z/os DB2
are running much slower.

Has anyone else experienced this situation?

Thanks,

Scott Hodgin, Database Administrator
South Carolina Farm Bureau Insurance Company
[login to unmask email] <mailto:[login to unmask email]>

---------------------------------------------------------------------------------
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

Joel Goldstein

Re: Dynamic SQL against z/os DB2 slow after upgrading to DB2 Connect V8.2
(in response to Scott Hodgin)
Where is it running slower - obviously at the end, but where are the elapsed
time, cpu, and wait time components?

Regards,
Joel

----- Original Message -----
From: "Hodgin, Scott" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Tuesday, January 18, 2005 7:45 AM
Subject: [DB2-L] Dynamic SQL against z/os DB2 slow after upgrading to DB2
Connect V8.2


> We recently upgraded a production windows server to DB2 Connect EE v8.2
> and
> now it seems that our dynamic SQL applications accessing data from z/os
> DB2
> are running much slower.
>
> Has anyone else experienced this situation?
>
> Thanks,
>
> Scott Hodgin, Database Administrator
> South Carolina Farm Bureau Insurance Company
> [login to unmask email] <mailto:[login to unmask email]>
>
> ---------------------------------------------------------------------------------
> 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

Tim R - CNF Ohling

Re: Dynamic SQL against z/os DB2 slow after upgrading to DB2 Connect V8.2
(in response to Joel Goldstein)
What version of DB2 Connect did you upgrade from?

We had a similar issue when upgrading to V8 Info Integrator from Data
Joiner (v2.7)when we discovered that an undercover default taken for the
host server authentication had been changed.

Specifically, the CATALOG DATABASE command used to point to the z/OS
subsystem had left AUTHENTICATION defaulted, which used SERVER
authentication up through V7. Under V8, it was changed to
SERVER_ENCRYPT which in turn caused some USS RSA decryption code (on
z/OS 1.4 or 1.5 at the time) to be executed for every query across the
system... That added considerable overhead which took us a while to
track down.

There are lots and lots of opportunities for gotchas in the disturbed -
oops - distributed environment.

HTH,
Tim

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Hodgin, Scott
Sent: Tuesday, January 18, 2005 4:46 AM
To: [login to unmask email]
Subject: [DB2-L] Dynamic SQL against z/os DB2 slow after upgrading to
DB2 Connect V8.2

We recently upgraded a production windows server to DB2 Connect EE v8.2
and
now it seems that our dynamic SQL applications accessing data from z/os
DB2
are running much slower.

Has anyone else experienced this situation?

Thanks,

Scott Hodgin, Database Administrator
South Carolina Farm Bureau Insurance Company
[login to unmask email] <mailto:[login to unmask email]>

------------------------------------------------------------------------
---------
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

Scott Hodgin

Re: Dynamic SQL against z/os DB2 slow after upgrading to DB2 Connect V8.2
(in response to Tim R - CNF Ohling)
thanks for the replies.

I did check the authentication and mine is still SERVER.

My initial research seems to indicate that .net apps running under the oledb
provider are much slower than apps using the DB2 .net managed provider.

I'm still looking and I have opened up an incident with IBM

-----Original Message-----
From: Ohling, Tim R - CNF [mailto:[login to unmask email]
Sent: Tuesday, January 18, 2005 11:29 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Dynamic SQL against z/os DB2 slow after upgrading
to DB2 Connect V8.2


What version of DB2 Connect did you upgrade from?

We had a similar issue when upgrading to V8 Info Integrator from Data
Joiner (v2.7)when we discovered that an undercover default taken for the
host server authentication had been changed.

Specifically, the CATALOG DATABASE command used to point to the z/OS
subsystem had left AUTHENTICATION defaulted, which used SERVER
authentication up through V7. Under V8, it was changed to
SERVER_ENCRYPT which in turn caused some USS RSA decryption code (on
z/OS 1.4 or 1.5 at the time) to be executed for every query across the
system... That added considerable overhead which took us a while to
track down.

There are lots and lots of opportunities for gotchas in the disturbed -
oops - distributed environment.

HTH,
Tim

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Hodgin, Scott
Sent: Tuesday, January 18, 2005 4:46 AM
To: [login to unmask email]
Subject: [DB2-L] Dynamic SQL against z/os DB2 slow after upgrading to
DB2 Connect V8.2

We recently upgraded a production windows server to DB2 Connect EE v8.2
and
now it seems that our dynamic SQL applications accessing data from z/os
DB2
are running much slower.

Has anyone else experienced this situation?

Thanks,

Scott Hodgin, Database Administrator
South Carolina Farm Bureau Insurance Company
[login to unmask email] <mailto:[login to unmask email]>

------------------------------------------------------------------------
---------
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

---------------------------------------------------------------------------------
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

Myron Miller

Re: Dynamic SQL against z/os DB2 slow after upgrading to DB2 Connect V8.2
(in response to Scott Hodgin)
Scott,
There is an open APAR JRsomething that talks about increased signon times
with 8.2. The problem is that even with authentication SERVER, DB2 Connect
goes out and tries to do a Windows network authentication. This can take a
long time. This will be fixed in FP9 or you can get the special build for
this.

Myron
--- "Hodgin, Scott" <[login to unmask email]> wrote:

> thanks for the replies.
>
> I did check the authentication and mine is still SERVER.
>
> My initial research seems to indicate that .net apps running under the oledb
> provider are much slower than apps using the DB2 .net managed provider.
>
> I'm still looking and I have opened up an incident with IBM
>
> -----Original Message-----
> From: Ohling, Tim R - CNF [mailto:[login to unmask email]
> Sent: Tuesday, January 18, 2005 11:29 AM
> To: [login to unmask email]
> Subject: Re: [DB2-L] Dynamic SQL against z/os DB2 slow after upgrading
> to DB2 Connect V8.2
>
>
> What version of DB2 Connect did you upgrade from?
>
> We had a similar issue when upgrading to V8 Info Integrator from Data
> Joiner (v2.7)when we discovered that an undercover default taken for the
> host server authentication had been changed.
>
> Specifically, the CATALOG DATABASE command used to point to the z/OS
> subsystem had left AUTHENTICATION defaulted, which used SERVER
> authentication up through V7. Under V8, it was changed to
> SERVER_ENCRYPT which in turn caused some USS RSA decryption code (on
> z/OS 1.4 or 1.5 at the time) to be executed for every query across the
> system... That added considerable overhead which took us a while to
> track down.
>
> There are lots and lots of opportunities for gotchas in the disturbed -
> oops - distributed environment.
>
> HTH,
> Tim
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
> Behalf Of Hodgin, Scott
> Sent: Tuesday, January 18, 2005 4:46 AM
> To: [login to unmask email]
> Subject: [DB2-L] Dynamic SQL against z/os DB2 slow after upgrading to
> DB2 Connect V8.2
>
> We recently upgraded a production windows server to DB2 Connect EE v8.2
> and
> now it seems that our dynamic SQL applications accessing data from z/os
> DB2
> are running much slower.
>
> Has anyone else experienced this situation?
>
> Thanks,
>
> Scott Hodgin, Database Administrator
> South Carolina Farm Bureau Insurance Company
> [login to unmask email] <mailto:[login to unmask email]>
>
> ------------------------------------------------------------------------
> ---------
> 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
>
>
---------------------------------------------------------------------------------
> 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

Scott Hodgin

Re: Dynamic SQL against z/os DB2 slow after upgrading to DB2 Connect V8.2
(in response to Myron Miller)
Myron,

Thanks for the reply. The connect is not where the delay seems to be.
However, I did get the hotfix you are referring to, but it did not seem to
make a difference. I'll be sending some traces to IBM today.

Scott

-----Original Message-----
From: Myron Miller [mailto:[login to unmask email]
Sent: Tuesday, January 18, 2005 6:42 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Dynamic SQL against z/os DB2 slow after upgrading
to DB2 Connect V8.2


Scott,
There is an open APAR JRsomething that talks about increased signon times
with 8.2. The problem is that even with authentication SERVER, DB2 Connect
goes out and tries to do a Windows network authentication. This can take a
long time. This will be fixed in FP9 or you can get the special build for
this.

Myron
--- "Hodgin, Scott" <[login to unmask email]> wrote:

> thanks for the replies.
>
> I did check the authentication and mine is still SERVER.
>
> My initial research seems to indicate that .net apps running under the
oledb
> provider are much slower than apps using the DB2 .net managed provider.
>
> I'm still looking and I have opened up an incident with IBM
>
> -----Original Message-----
> From: Ohling, Tim R - CNF [mailto:[login to unmask email]
> Sent: Tuesday, January 18, 2005 11:29 AM
> To: [login to unmask email]
> Subject: Re: [DB2-L] Dynamic SQL against z/os DB2 slow after upgrading
> to DB2 Connect V8.2
>
>
> What version of DB2 Connect did you upgrade from?
>
> We had a similar issue when upgrading to V8 Info Integrator from Data
> Joiner (v2.7)when we discovered that an undercover default taken for the
> host server authentication had been changed.
>
> Specifically, the CATALOG DATABASE command used to point to the z/OS
> subsystem had left AUTHENTICATION defaulted, which used SERVER
> authentication up through V7. Under V8, it was changed to
> SERVER_ENCRYPT which in turn caused some USS RSA decryption code (on
> z/OS 1.4 or 1.5 at the time) to be executed for every query across the
> system... That added considerable overhead which took us a while to
> track down.
>
> There are lots and lots of opportunities for gotchas in the disturbed -
> oops - distributed environment.
>
> HTH,
> Tim
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
> Behalf Of Hodgin, Scott
> Sent: Tuesday, January 18, 2005 4:46 AM
> To: [login to unmask email]
> Subject: [DB2-L] Dynamic SQL against z/os DB2 slow after upgrading to
> DB2 Connect V8.2
>
> We recently upgraded a production windows server to DB2 Connect EE v8.2
> and
> now it seems that our dynamic SQL applications accessing data from z/os
> DB2
> are running much slower.
>
> Has anyone else experienced this situation?
>
> Thanks,
>
> Scott Hodgin, Database Administrator
> South Carolina Farm Bureau Insurance Company
> [login to unmask email] <mailto:[login to unmask email]>
>
> ------------------------------------------------------------------------
> ---------
> 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
>
>
----------------------------------------------------------------------------
-----
> 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

---------------------------------------------------------------------------------
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

Nic Honan

Re: Dynamic SQL against z/os DB2 slow after upgrading to DB2 Connect V8.2
(in response to Scott Hodgin)
Can you tell me wath the APAR number is.
I've given up trying to search for it.
Or better still give me tips on searching for it.

Ta - Nic


On Wed, 19 Jan 2005 06:31:31 -0600, Hodgin, Scott <[login to unmask email]>
wrote:

>Myron,
>
>Thanks for the reply. The connect is not where the delay seems to be.
>However, I did get the hotfix you are referring to, but it did not seem to
>make a difference. I'll be sending some traces to IBM today.
>
>Scott
>
>-----Original Message-----
>From: Myron Miller [mailto:[login to unmask email]
>Sent: Tuesday, January 18, 2005 6:42 PM
>To: [login to unmask email]
>Subject: Re: [DB2-L] Dynamic SQL against z/os DB2 slow after upgrading
>to DB2 Connect V8.2
>
>
>Scott,
> There is an open APAR JRsomething that talks about increased signon
times
>with 8.2. The problem is that even with authentication SERVER, DB2
Connect
>goes out and tries to do a Windows network authentication. This can take
a
>long time. This will be fixed in FP9 or you can get the special build for
>this.
>
>Myron
>--- "Hodgin, Scott" <[login to unmask email]> wrote:
>
>> thanks for the replies.
>>
>> I did check the authentication and mine is still SERVER.
>>
>> My initial research seems to indicate that .net apps running under the
>oledb
>> provider are much slower than apps using the DB2 .net managed provider.
>>
>> I'm still looking and I have opened up an incident with IBM
>>
>> -----Original Message-----
>> From: Ohling, Tim R - CNF [mailto:[login to unmask email]
>> Sent: Tuesday, January 18, 2005 11:29 AM
>> To: [login to unmask email]
>> Subject: Re: [DB2-L] Dynamic SQL against z/os DB2 slow after upgrading
>> to DB2 Connect V8.2
>>
>>
>> What version of DB2 Connect did you upgrade from?
>>
>> We had a similar issue when upgrading to V8 Info Integrator from Data
>> Joiner (v2.7)when we discovered that an undercover default taken for the
>> host server authentication had been changed.
>>
>> Specifically, the CATALOG DATABASE command used to point to the z/OS
>> subsystem had left AUTHENTICATION defaulted, which used SERVER
>> authentication up through V7. Under V8, it was changed to
>> SERVER_ENCRYPT which in turn caused some USS RSA decryption code (on
>> z/OS 1.4 or 1.5 at the time) to be executed for every query across the
>> system... That added considerable overhead which took us a while to
>> track down.
>>
>> There are lots and lots of opportunities for gotchas in the disturbed -
>> oops - distributed environment.
>>
>> HTH,
>> Tim
>>
>> -----Original Message-----
>> From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
>> Behalf Of Hodgin, Scott
>> Sent: Tuesday, January 18, 2005 4:46 AM
>> To: [login to unmask email]
>> Subject: [DB2-L] Dynamic SQL against z/os DB2 slow after upgrading to
>> DB2 Connect V8.2
>>
>> We recently upgraded a production windows server to DB2 Connect EE v8.2
>> and
>> now it seems that our dynamic SQL applications accessing data from z/os
>> DB2
>> are running much slower.
>>
>> Has anyone else experienced this situation?
>>
>> Thanks,
>>
>> Scott Hodgin, Database Administrator
>> South Carolina Farm Bureau Insurance Company
>> [login to unmask email] <mailto:[login to unmask email]>
>>
>> ------------------------------------------------------------------------
>> ---------
>> 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
>>
>>
>--------------------------------------------------------------------------
--
>-----
>> 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
>
>--------------------------------------------------------------------------
-------
>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 DB2-L-
[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