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

From: TM <tonym101_at_hotmail.com>
Date: 19 Jun 2003 10:49:25 -0700
Message-ID: <7b718442.0306190949.7429abf3@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 Thu Jun 19 2003 - 12:49:25 CDT