Solved

DB2 Optimizer HINTS ?

Posted on 2004-04-20
8
2,290 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql is not working correctly 23 353
the encoding configuration of DB2 V8.02 3 418
DB2 tablespace disk full error 33 1,030
DB2 return first match 3 91
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…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now