Solved

DB2 Optimizer HINTS ?

Posted on 2004-04-20
8
2,402 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
8 Comments
 
LVL 13

Accepted Solution

by:
ghp7000 earned 25 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 25 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
 
LVL 13

Expert Comment

by:ghp7000
ID: 10895092
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
DB2 what is copybook? 4 557
Find Value column 2 does not work 1 194
RPG to c# 3 375
DB2 iSeries Combine Results of 2 Selects (nested Join?) ? 2 78
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

766 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