470,821 Members | 2,024 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,821 developers. It's quick & easy.

Long compiling of SQL statements

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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
Feb 21 '06 #1
5 4551
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
Feb 21 '06 #2
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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
Feb 21 '06 #3
>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.

Feb 21 '06 #4
>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

Feb 21 '06 #5
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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
Feb 22 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Arnab Nandi | last post: by
17 posts views Thread by Michel Esber | last post: by
35 posts views Thread by aNt17017 | last post: by
8 posts views Thread by WebSnozz | last post: by
9 posts views Thread by vadivel.ks | last post: by
2 posts views Thread by Michel Esber | last post: by
reply views Thread by Troels Arvin | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.