Does iSeries DB2 have good optimizer?

1
votes

MS SQL developer wading into iSeries DB2 here.

I was asked to re-write some report SQL queries taking forever to run and they are enormous, joining dozens of tables etc.

However when I approached it by using typical MS SQL optimization steps, such as replacing NOT EXISTS with joins, I did not initially see any improvement. Should I bother or the optimizer is taking care of the obvious things like that?

2
ibm-midrangedb2-400
FWIW, on this site we try to [focus on specific programming problems][stackoverflow.com/about]. We generally prefer that broader database questions be posted on our [DBA site][dba.stackexchange.com/]. Many of the same people are there, but you may hear from someone who has more specialized technical expertise. - WarrenT
Good to know, thanks! - ajeh

2 Answers

2
votes

DB/2 is completely different than MS SQL and I wouldn't expect many hand optimizations to make the same impact.

There are a number of factors related to DB/2 performance and optimization. I would suggest starting with the documentation about optimizing query performance using query optimization tools.

Running your query through Visual Explain, for example, should provide a clearer answer.

IBM i Access for Windows ODBC performance
0
votes

Perhaps the most obvious thing to check is to make sure you have good indexes for your joins. If not then create them. Indexes have much less performance impact on DB2 for i.