Re: Where can I get a complete list of all SQL Hints?

> It's a database configuration parameter.

They can be configured at session level. I guess there must be some reasons for IBM lets you do so, right?

> ...and neither this nor that, rarely but often, perhaps or perhaps not
> I guess you mean?

I mean you are making claims based on totally unsupported claims.

Look at what you said:
> DB2 doesn't have hints. At least, not DB2 UDB For Multiplatforms
> (UNIX, Linux, Windows, OS/2)... i.e. the common code base flavour
> that's gobbling market share. DB2 UDB is widely recognised as having
> the leading optimizer though.

The DB2 market share is still below that of Oracle even after the Informix acquistion (if you just count DB2 for UDB, that would be even far smaller).

Widely recognized? Who? You and your boss? At least not in this newsgroup I guess and not for all those DBAs that I came across crying for help on their so-called "Universal"-DB (haha, common code base, I can't believe someone really believe this crab, they don't even have the same syntax (for some very rare features) for christ sake and just for your information, UDB on different platform are developed almost independently by different labs across the world, look at the Explain tables in different UDB platform and see their naming convention mess you will see what I mean!)

> Actually affects optimization post-rewrite. It's basically like
> telling a chess computer how long to spend enumerating possibilities.

So how long is good enough? DB2 doesn't dare to take this responsibility to determine for you (as this would make it seem 'so dumb just like that of Oracle'). So it puts the responsibility on you, the DBA. Hey, this is the optimizer level you choose so if I make a wrong decision, then it must be you haven't given me enough time on such a complex query. Your fault, not DB2's. Woooolala.

> Default level 5 is generally fine and good for mixed load
> environments. I'd recommend 7 for DSS-only environments running very
> complex ad-hoc queries. If you are doing OLTP only then lower levels
> may save a little time. This is all in the documentation. You choose
> an appropriate value and then forget about it... people don't go
> changing optimization level from query to query in practice.

Forget about it and yes, that's what IBM wants you to think (and it seems they really work in your case). In practice, they do change them from level to level. Ask a DB2 DBA that finds his query performs poorly and he tries different level like gambling and only finds the DB2 optimizer is so rigid, even in Level 0 it's trying to do a view merging, a predicate pushing or etc, only after using some very ugly trick that it starts behaving properly

It's the same case. Under normal and ideal case, Oracle DBA don't use HINTS and DB2 DBA don't change level but in case of need, they come into play to rectify poor optimizer decision. Neither is smarter nor dumber.

"TM" <tonym101_at_hotmail.com> wrote in message news:7b718442.0306190949.7429abf3_at_posting.google.com...
> boscong_at_leccotech.com (Bosco Ng) wrote in message
>
> >> DB2 doesn't have hints
> > [snipped]
> >
> > Then what's DB2's Optimizer Level (1 to 9 if I remember right)?
>
> It's a database configuration parameter.
>
> > DB2's optimizer is neither smarter nor dumber, but more rigid in its
> > behavior and more abstract.
>
> ...and neither this nor that, rarely but often, perhaps or perhaps not
> I guess you mean?
>
> > For a SQL statement, you can only specify
> > level 1 to level 9 to adjust the level of rewrite done by DB2's
> > optimizer.
>
> Actually affects optimization post-rewrite. It's basically like
> telling a chess computer how long to spend enumerating possibilities.
>
> > But which level's best? God knows.
>
> Default level 5 is generally fine and good for mixed load
> environments. I'd recommend 7 for DSS-only environments running very
> complex ad-hoc queries. If you are doing OLTP only then lower levels
> may save a little time. This is all in the documentation. You choose
> an appropriate value and then forget about it... people don't go
> changing optimization level from query to query in practice.
>
>
> TM
Received on Fri Jun 20 2003 - 09:49:42 CDT