Serge Rielau wrote:
[color=blue]
> Gregor Kovač wrote:[color=green]
>> Hi!
>>
>> I have a problem on one of my databases.
>> I have a Java application that issues SQL statements to that database.
>> Some of the SQL statements make DB2 go to into a compile state for hours.
>> I see Compiling if I do "db2 list applications show detail".
>>
>> How can I see what is causing this behaviour ? What do to ?
>>
>> Best regards,
>> Kovi
>>[/color]
> Is this complex SQL? What's your optimization level?
> Note that for OLTP the recommended level is 3.
> For BI typically 5 or 7. 9 should only ever be used ad-hoc for
> experiments. What does the package cache information say? Are there
> overflows?
>
> In a well behaved system the package cache should have a very near 100%
> hit ratio with a stable number of "package cache inserts". If that's not
> teh case you have soemone firing queries without using parameter markers
> or yoru cache is too small (thrashing).
>
> Cheers
> Serge
>[/color]
Our application if OLTP and I always use default optimization level.
None of the SQLs is complex, just couple of JOINs and that's it.
If I do "select * from table(snapshot_database(cast(NULL as varchar(1)),-1))
as f" I see that there are about 0.6 % of sort overflows, 5 catalog cache
overflows out of over 200000 lookups, 0 hash join overflows and 1 package
cache overflow.
In db2diag.log I can see some lock escalations, but I know what those are
there for, and the tables involved in lock escalation don't have anything
to do with SQLs that are compiling.
The only difference that I can see is that we have an online backup to TSM
and the logs are being archived.
Hmm...
Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac |
Gregor.Kovac@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
