Environment: DB2 V7 + FP 14 running Linux.

One of my systems is performing poorly. CPU is 100% busy.

I activated an Event Monitor and the prepared statements sent by
applications are taking very long to execute. I can see a bottleneck
during the PREPARE. When the application issues an OPEN, response is
quick.

When I run a "list applications show detail", I can see dozens of
applications in Compiling status.

Diaglevel 3, DFT_QUERYOPT = 5, DFT_DEGREE= ANY

Can anyone suggest anything that can be looked at in order to fix this
issue?

Thanks,

In article <1139501686.818969.307400@z14g2000cwz.googlegroups .com>,
Michel Esber (michel@us.automatos.com) says...
> Environment: DB2 V7 + FP 14 running Linux.
>
> One of my systems is performing poorly. CPU is 100% busy.
>
> I activated an Event Monitor and the prepared statements sent by
> applications are taking very long to execute. I can see a bottleneck
> during the PREPARE. When the application issues an OPEN, response is
> quick.
>
> When I run a "list applications show detail", I can see dozens of
> applications in Compiling status.
>
> Diaglevel 3, DFT_QUERYOPT = 5, DFT_DEGREE= ANY
>
> Can anyone suggest anything that can be looked at in order to fix this
> issue?
>
> Thanks,
>
>

(I don't think you have the same problem but just in case...)
I've seen this kind of problem when some tables were changed and
automatic rebinding occured. Rebinding the packages solved the
problem.

I just executed a db2rbind DB all and my system is now stable again.
CPU consumption is back to reasonable levels.

Thank you for your fast response and great hint.

-Michel

Unbelievable. After running smooth for approx 20 minutes, my system is
again very slow.

Hundreds of applications in Compiling status. And CPU again at 100%.

Any ideas ?

Thanks

Michel Esber wrote:
> Unbelievable. After running smooth for approx 20 minutes, my system is
> again very slow.
>
> Hundreds of applications in Compiling status. And CPU again at 100%.
>
> Any ideas ?
>
> Thanks
>
Are you swamping your package cache? Check your snapshots!

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

Serge Rielau wrote:
> Michel Esber wrote:
>
>> Unbelievable. After running smooth for approx 20 minutes, my system is
>> again very slow.
>>
>> Hundreds of applications in Compiling status. And CPU again at 100%.
>>
>> Any ideas ?
>>
>> Thanks
>>
> Are you swamping your package cache? Check your snapshots!
select PKG_CACHE_NUM_OVERFLOWS, PKG_CACHE_SIZE_TOP, PKG_CACHE_LOOKUPS,
PKG_CACHE_INSERTS
from table(snapshot_database(cast(NULL as varchar(250)),-1)) as t;

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

Serge, thanks. Unfortunately I am still running DB2 V7.

Here is what I can see from database snapshots:

Package cache lookups = 11860267
Package cache inserts = 1267129
Package cache overflows = 3
Package cache high water mark (Bytes) = 24602066
Application section lookups = 23711267
Application section inserts = 11558385

It seems like this problem started to happen after we have tried to
bind (@db2ubind.lst @db2cli.lst) from a V8 client to this V7 servers.
During this process, we got many warning. After that, the server was
never the same. I am not sure if this has something to do with the
server behaving so sluggish.

Any other snapshots or parameters I can take a look at?

Thanks

Michel Esber wrote:
> Serge, thanks. Unfortunately I am still running DB2 V7.
>
> Here is what I can see from database snapshots:
>
> Package cache lookups = 11860267
> Package cache inserts = 1267129
> Package cache overflows = 3
> Package cache high water mark (Bytes) = 24602066
> Application section lookups = 23711267
> Application section inserts = 11558385
>
> It seems like this problem started to happen after we have tried to
> bind (@db2ubind.lst @db2cli.lst) from a V8 client to this V7 servers.
> During this process, we got many warning. After that, the server was
> never the same. I am not sure if this has something to do with the
> server behaving so sluggish.
>
> Any other snapshots or parameters I can take a look at?
>
> Thanks
>
How big is your package cache? Seems like you need to get it up past
8000 (*4k).
Do you really have 1.2M Distinct SQL Statements?
Or are you feeding in constants?
Take a look at the dynamic SQL snapshot

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

My package cache is currently at 6000. MAXAPPLS=1200.

I was reading the documentation and it recommends MAXAPPLS*8. I will
change that and see what happens.

No, the applications send prepared statements and feeds constants. The
number of distinct statements executed in this DB is certainly small.

Thanks,

Michel Esber wrote:
> My package cache is currently at 6000. MAXAPPLS=1200.
>
> I was reading the documentation and it recommends MAXAPPLS*8. I will
> change that and see what happens.
>
> No, the applications send prepared statements and feeds constants. The
> number of distinct statements executed in this DB is certainly small.
You said this is V7? If it were V8.2 I'd wonder if you bound CLI with
REOPT(ALWAYS)
Take a look at the dynamic SQL snapshot and check number of executions,
etc... Also verify that you really get the parameter markers in the text.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab