How To Set Optimization Level In JDBC Connection

[Schnaggy]

Hi DB2 Freaks,

I have a big problem with the optimizer in DB2/UDBV5.2 on AIX and
SUN/OS:
The access plans for an dynamic sql statement are horribly different,
so on AIX it works within 1sec and on SUN/OS it finally ends up with a
database manager error because the file system for the system managed
tablespace is full.

The default optimization level on DB2 AIX is 3 and on SUN it is 5. We
changed the default opt.Level on SUN to 3 and it worked. But this maybe
leads to other performance problems within other programs working
against the same dbinst.
So I search for a method to change the optimization level within an jdbc
db2 connection, like the transaction isolation level, but I didn't found
anything.

Anyone knows anything usefull?

Thanks in advance,

Schnaggy :-)

--
mwi hamburg gmbh
Dipl.-Ing. Elektrotechnik - SoftwareEngineering (FH)
Carsten Wagner
mailto:c.wa...@mwi-online.de
http://www.mwi-online.de
Tel: 07000 694 1007
Fax: 07000 694 9999

[David Sharpe]

Hi Schnaggy,
Does the sql statement:
SET CURRENT QUERY OPTIMIZATION = 3;
work in v5.2? If it does, that will set the special register for that uow.
Check
out the SQL Refr. for more details.

Hope this helps,
David.

[Schnaggy]

Hi David,

YES! v5.2 accepts the "SET CURRENT...". I think this is what I searched for.
Many Thanks!
BTW: Are there any optimizer Fixes or updates available, or is this problem a
WCS based. I know the optimizer's KI can't always decide the best way, but
I'm interested in the reason why it fails.

Again many thanks,

Schnaggy :-)

[David Sharpe]

That is a very open ended question, before answering, you have
to ask your self:
- does it run out of memory in the compile stage or the execute stage ?
- is the access plan at opt level 5 'reasonable'?
- is the exact same data present on both databases?
- do both data bases have current runstats?

In general, the optimizer gets smarter and smarter from release to release ...
so you may get a better access plan with a newer release.

[Schnaggy]

David Sharpe wrote:

> That is a very open ended question, before answering, you have
> to ask your self:
> - does it run out of memory in the compile stage or the execute stage ?
>

I don't know how to examin.

> - is the access plan at opt level 5 'reasonable'?

Yes the access plan seems to be reasonable.

>
> - is the exact same data present on both databases?

No, the faster database has ten times more data.

>
> - do both data bases have current runstats?

Yes of course, its been the first thing I checked out and ran a new runstats.

>
>
> In general, the optimizer gets smarter and smarter from release to release ...
> so you may get a better access plan with a newer release.
>

Do you know any documentation 'bout the db2 optimizer or any dbms optimizers. Its
seems to be a funny and interesting thing:-)

Many thanks for your patience and answers,

Schnaggy :-)

Still raining in Hamburg, Germany.

[David Sharpe]

Schnaggy wrote:

> David Sharpe wrote:
>
> > That is a very open ended question, before answering, you have
> > to ask your self:
> > - does it run out of memory in the compile stage or the execute stage ?
> >
>
> I don't know how to examin.
>

If it has an access plan, then it got through the compile stage OK.

>
> > - is the access plan at opt level 5 'reasonable'?
>
> Yes the access plan seems to be reasonable.
>

Are they the same on both boxes? Below you say that the successful
box has ten times more data. The optimizer will consider that and may
choose different plans.

>
> >
> > - is the exact same data present on both databases?
>
> No, the faster database has ten times more data.
>
> >
> > - do both data bases have current runstats?
>
> Yes of course, its been the first thing I checked out and ran a new runstats.
>
> >
> >
> > In general, the optimizer gets smarter and smarter from release to release ...
> > so you may get a better access plan with a newer release.
> >
>
> Do you know any documentation 'bout the db2 optimizer or any dbms optimizers. Its
> seems to be a funny and interesting thing:-)
>

The release notes and the "whats new" document a number of optimizer improvements
from release to release. The Admin Guide is our 'official' documentation. There are
courses on optimizing db2 offered by IBM and others. And there are a number of
papers and articles published on our optimizer.

Those are some of the few places you could start to look for more info on
the db2 optimizer.