Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

DB2 Optimizer HINTS ?

Posted on 2004-04-20
8
Medium Priority
?
2,700 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Screencast - Getting to Know the Pipeline

877 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