Many applications in Compiling status

[Serge Rielau]

Re: Many applications in Compiling status

Michel Esber wrote:[color=blue]
> 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.
>[/color]
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]

Re: Many applications in Compiling status

Serge Rielau wrote:[color=blue]
> Michel Esber wrote:
>[color=green]
>> 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.
>>[/color]
> 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.[/color]
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

[Michel Esber]

Re: Many applications in Compiling status

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.