Long compiling of SQL statements

[Gregor Kovaè]

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

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

[Serge Rielau]

Gregor Kova� wrote:

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

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

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Gregor Kovaè]

Serge Rielau wrote:

Gregor Kovaè wrote:

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

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

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 | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

[Eugene F]

>From my past experience with java stored V8 procedures (dynamic jdbc,
not sqlj) on AIX 5, I noticed the application snapshots sometimes shown
COMPILE state when long running update or insert embedded in java was
indeed executing. Because the procedures were always succeeding with a
result, we didn't bother to call IBM support though. If you own the
java proc's code, to pinpoint that issue, you can use SET EXPLAIN
MODE=EXPLAIN SQL statement in your code and have the SQL statements to
only compile but not execute. Otherwise grab the SQL statements from
your batch using SQL snapshots and try to compile them separately using
any of the DB2 explain facility available features.

[Eugene F]

>From my past experience with java stored V8 procedures (dynamic jdbc,
not sqlj) on AIX 5, I noticed the application snapshots sometimes shown
COMPILE state when long running update or insert embedded in java was
indeed executing. Because the procedures were always succeeding with a
result, we didn't bother to call IBM support though. If you own the
java proc's code, to pinpoint that issue, you can use SET EXPLAIN
MODE=EXPLAIN SQL statement in your code and have the SQL statements to
only compile but not execute. Otherwise grab the SQL statements from
your batch using SQL snapshots and try to compile them separately using
any of the DB2 explain facility available features.

-Eugene

[Gregor Kovaè]

Eugene F wrote:

From my past experience with java stored V8 procedures (dynamic jdbc,

not sqlj) on AIX 5, I noticed the application snapshots sometimes shown
COMPILE state when long running update or insert embedded in java was
indeed executing. Because the procedures were always succeeding with a
result, we didn't bother to call IBM support though. If you own the
java proc's code, to pinpoint that issue, you can use SET EXPLAIN
MODE=EXPLAIN SQL statement in your code and have the SQL statements to
only compile but not execute. Otherwise grab the SQL statements from
your batch using SQL snapshots and try to compile them separately using
any of the DB2 explain facility available features.

-Eugene

Hi!

We are on Windows 2003 and the SQLs in question are not in a procedure. They
are simple dynamic SQLs that are send from the application.

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~