2

MS SQL dev here, this is my first attempt to optimize a DB2 query on iSeries. I was asked to help improve a huge query which consists entirely of dozens of joined subselects. Some of those subselects include unions and where exists/not exists clauses were used extensively. Among other things there are many where clauses that specify column <> 0 etc. Does iSeries DB2 efficiently use index with where column <> 0, <> '' etc?

Improve this question
4
  • What do you mean by "efficiently using" in this case?
    – mustaccio
    Oct 28, 2013 at 20:20
  • Ex: In MS SQL <> may cause optimizer not use an index a column is a part of.
    – ajeh
    Oct 28, 2013 at 20:35
  • 1
    There are a lot of things about your description that may cause an optimizer not use an index, particularly if the column is not the leading one in the index, so your question as stated does not have an answer I'm afraid. Or, as other folks often respond, "it depends".
    – mustaccio
    Oct 28, 2013 at 21:09
  • 1
    I've been taught (specifically as it relates to DB2-more LUW though- and I would assume is also good advice for other RDBMS's) that one should always ask the database engine for what you want, not for what you don't want. The more you can get specific with what you want, the more the optimizer can use to obtain your results.
    – Chris Aldrich
    Oct 29, 2013 at 13:16

1 Answer 1

Reset to default
1

Using System i Navigator, you can run your queries in there to see if there are more indexes that may be recommended. The system tries it best to create the right indexes before running and caches those access paths.

One thing I did in my last project with some larger queries was to create procedures. Then the system stores those access paths and speeds up your queries even more.

Improve this answer
1
  • Along those lines, deterministic functions can sometimes help as well.
    – WarrenT
    Oct 30, 2013 at 0:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.