Improve company productivity with a Business Account.Sign Up

x
?
Solved

DB2 Optimizer HINTS ?

Posted on 2004-04-20
8
Medium Priority
?
2,867 Views
Last Modified: 2012-08-13
Does DB2 (if so which versions) have the ability of optimizer Hints?
Ex:  Select */ FIRST_ROWS /* column from table ?

If so, How are they used and what are they
0
Comment
Question by:strongs120
5 Comments
 
LVL 13

Accepted Solution

by:
ghp7000 earned 100 total points
ID: 10879895
no, this is not Oracle where the optimizer is so poor that you need to give it 'hints'.
In DB2, you influence the optimizer by writing your sql statements in such a way that you get the execution plan you want, in conjunction with indexes, statistics, query optimization levels, CPU speed, dynamic of static sql, parameter markers or host variables,there are many factors, but no hints as you may be used to.
0
 
LVL 18

Assisted Solution

by:BigSchmuh
BigSchmuh earned 100 total points
ID: 10886630
Yes and No...
You can not have hints in a query.
You can set DB2 parameters that acts as hints on the DB2 instance.
==> Examples are (I am not sure of the exact syntax):
db2set DB2_PIPELINE_PLAN = ON (To give a priority to query plan which has no consolidation steps)
db2qset DB2_HASHJOIN = OFF (To avoid the optimizer to use the "Hash Join" join method)

Last, you can manually update a statistics to "fake" db2 optimizer about the cardinality of a table/index/column.

Hope this helps.
0
 
LVL 4

Expert Comment

by:bondtrader
ID: 10892294
strongs120,

Try tacking an optimize clause onto your select statements.  This is similar to Oracle's hints.  Not the exact same thing, but  similar.

For instance, if you know you're only going to process 20 rows at a time try:

select col1, col2 from schema.yourtable optimize for 20 rows

Hope this helps
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10893883
you can also set the optimisation level that you want UDB to perform upon your query...

the method of specifiying optimiser "hints" differs between the DB2/UDB versions and Platforms...

what sort of optimsations where you actually thinking of?

if the want only 1 row returned

then its

select ....
from
fetch first N rows only
optimize for Y ROWS



 
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Watch the video to know the process of migration of Exchange or Office 365 mailboxes in absence of MS Outlook. It is an eminent tool which can easily migrate Public, Archive user mailboxes from one another Exchange server and Office 365. Kernel Migr…
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question