I changed the packagesize. The problem is still happening.

I just took a dynamic SQL snapshot. There are 5k distinct "statement
text" entries. Another interesting detail: there are only 94 statements
that use "?". Seems like my app is using a lot of static statements.

I also looked at other counters:

# Very few statements had a 'worst preparation' time (above 1000ms). In
average, <10 ms.
# Only two of these statements had more than 1 number of compilations.

I am still confused. Why have this problem begin after a simple V8
client bind ...

Thanks again.

Michel Esber wrote:
> I changed the packagesize. The problem is still happening.
>
> I just took a dynamic SQL snapshot. There are 5k distinct "statement
> text" entries. Another interesting detail: there are only 94 statements
> that use "?". Seems like my app is using a lot of static statements.
>
> I also looked at other counters:
>
> # Very few statements had a 'worst preparation' time (above 1000ms). In
> average, <10 ms.
> # Only two of these statements had more than 1 number of compilations.
>
> I am still confused. Why have this problem begin after a simple V8
> client bind ...
Hmph.. V7 server you said...
Is this AIX? kill -36 <busyprocessid>
should tell you where the beast is hanging out...
But this turns quickly into open a PMR IMHO

Cheers
Serge

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

This is a Linux server. Is there any similar kill signal in Linux ?
'kill -l' shows SIGRTMIN+4.

All db2sysc processes are consuming a small chunk of CPU. But as a
group, cpu usage is very high.

The problem really comes and goes. System is stable for 20 minutes,
then busy for some time.

I heard IBM discontinued support for V7 servers. Is it still possible
to open a PMR in this situation ? We do have a support contract.

Thanks,

Michel Esber wrote:
> This is a Linux server. Is there any similar kill signal in Linux ?
> 'kill -l' shows SIGRTMIN+4.
>
> All db2sysc processes are consuming a small chunk of CPU. But as a
> group, cpu usage is very high.
>
> The problem really comes and goes. System is stable for 20 minutes,
> then busy for some time.
>
> I heard IBM discontinued support for V7 servers. Is it still possible
> to open a PMR in this situation ? We do have a support contract.
You mean you have an extended support contract? Sure, of course you can
open a PMR.
Not to pur salt into the wound, but: DB2 V8 has a whole slew of cool
problem determination features which would help here.
Especially the db2pd tool (aka Informix onstat) woudl be useful.

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

Yes, DB2 migration is being taken seriously now. I could open a PMR
today, but it will take some time until IBM provides support (I totally
understand this). I need to study a workaround for the case ASAP.

I have ran another event monitor on the DB and here is another
interesting detail:

OPERATION EXECUTIONTIME
------------------------------ --------------------
Close 16.318283
Prepare 2.536525
Close 2.053398
Prepare 2.775977
Close 2.737252
(...)

Open and Execute statement operations are fast. Close and Prepare are
slow.

Why would DB2 take so long to run such simple operations ... seems like
a nightmare.

Michel Esber wrote:
> Yes, DB2 migration is being taken seriously now. I could open a PMR
> today, but it will take some time until IBM provides support (I totally
> understand this). I need to study a workaround for the case ASAP.
>
> I have ran another event monitor on the DB and here is another
> interesting detail:
>
> OPERATION EXECUTIONTIME
> ------------------------------ --------------------
> Close 16.318283
> Prepare 2.536525
> Close 2.053398
> Prepare 2.775977
> Close 2.737252
> (...)
>
> Open and Execute statement operations are fast. Close and Prepare are
> slow.
>
> Why would DB2 take so long to run such simple operations ... seems like
> a nightmare.
>
Careful, I think you are falling victim to a red herring here.
If I'm not entirely mistaken the time you see is the entire time to
process the cursor, including time spent in the application.
With cursors it's best to look at USER and SYSTEM CPU time only.
Again CLOSE would include OPEN and all the FETCH time.
Cheers
Serge

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

Serge Rielau wrote:
> Michel Esber wrote:
>
>> Yes, DB2 migration is being taken seriously now. I could open a PMR
>> today, but it will take some time until IBM provides support (I totally
>> understand this). I need to study a workaround for the case ASAP.
>>
>> I have ran another event monitor on the DB and here is another
>> interesting detail:
>>
>> OPERATION EXECUTIONTIME
>> ------------------------------ --------------------
>> Close 16.318283
>> Prepare 2.536525
>> Close 2.053398
>> Prepare 2.775977
>> Close 2.737252
>> (...)
>>
>> Open and Execute statement operations are fast. Close and Prepare are
>> slow.
>>
>> Why would DB2 take so long to run such simple operations ... seems like
>> a nightmare.
>>
> Careful, I think you are falling victim to a red herring here.
> If I'm not entirely mistaken the time you see is the entire time to
> process the cursor, including time spent in the application.
> With cursors it's best to look at USER and SYSTEM CPU time only.
> Again CLOSE would include OPEN and all the FETCH time.
BTW, could it be your system is thrashing? Is USER or SYSTEM CPU high?
What about swapping?

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

Memory is not constrained, but CPU usage is heavy.

I will try to reorg and runstats my most often used tables and see if
it gets any better.

Thanks for the help Serge.