Indus PassPort 8.0 with DB2 V 6.1

Amir Sadeghi

Indus PassPort 8.0 with DB2 V 6.1
Hi team,

For the past few days, I have been asked by a client to look at their
performance problems after they upgraded to PassPort 8.0. On the surface,
the response time is unacceptable.

The monitors show that CPU usage is very high and maxes out many times
during the day. I believe some of us on the list have experienced this
pain already. I have gathered a bunch of information and see some of the
problems but am sure there are more.

I am hoping I can get some good pointers from you experts that survived
this battle in the past.

Thanks



Norman Daley

Re: Indus PassPort 8.0 with DB2 V 6.1
(in response to Amir Sadeghi)
Amir,
Cinergy migrated production to Passport 8 almost 2 years ago. As you said, performance was immediately awful. Average CPU doubled. In the first day or two, we were able to fix a few obvious offenders, and get response time close to useable. Unfortunately, many of the fixes involved code changes (customizing purchased applications is definitely frowned upon here). Adding and changing indexes, some use of reopt(vars), and jacking the DB2 stats to affect the optimizer were also required.

If you have some specific packages that are misbehaving, I might have some recommendations. What worked for me might not work for you, as installations vary on the use of the many pieces of Passport.

Norm Daley
Cinergy


-----Original Message-----
From: Amir Sadeghi [mailto:[login to unmask email]
Sent: Friday, December 13, 2002 12:19 AM
To: [login to unmask email]
Subject: Indus PassPort 8.0 with DB2 V 6.1

Hi team,

For the past few days, I have been asked by a client to look at their
performance problems after they upgraded to PassPort 8.0. On the surface,
the response time is unacceptable.

The monitors show that CPU usage is very high and maxes out many times
during the day. I believe some of us on the list have experienced this
pain already. I have gathered a bunch of information and see some of the
problems but am sure there are more.

I am hoping I can get some good pointers from you experts that survived
this battle in the past.

Thanks



Amir Sadeghi

Re: Indus PassPort 8.0 with DB2 V 6.1
(in response to Norman Daley)
Hi Norman,

At this stage, there are a bunch of packages that are bringing the system
to its knees, e.g., TIGWOTS1, TIGWOTS4, TIGM215C, etc.

What I've obsereved so far:

- There are a bunch of hot tables being accessed by both online and batch
PassPort jobs
- A whole lot of nested loop joins
- The use of BETWEEN to populate the navigational screens is wreaking havoc
- Heavy SORTing for every single query (which means we need to take a
closer look at our indexes and clustering) .... lot of I/O on DSNDB07
- The use of || along with BETWEEN for comparisons, in almost all SELECTs
- BP tuning for data and indexes seems to be needed (some dedicated
bufferpools seem to be under-utilized)
....and the list goes on!

This looks like a long tuning job. So you see, any help is appreciated!

Thanks

PS. By the way, from your personal experience, how important is it to
understand the application, i.e., what it does on a daily basis. I have no
idea how the data is refreshed (updated/inserted/deleted) and how it is
used. Just wondering!