Solved

DB2 Optimizer HINTS ?

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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 (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…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

920 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

16 Experts available now in Live!

Get 1:1 Help Now