You need an index on SMART_PCS. You are table scanning it in both cases. Because, for the fast query you only join to the other table on its index, it uses a has join. The bad query, you want other columns from the other table, so it uses a loop join. A table scan (SMART_PCS) with a loop join will always take a while.
I added an index on SMART_PCS and now my query runs in 100 seconds, great improvement!
However, I still have a problem.
If I run the same query on SQL Server the first time it takes 110 seconds, but the second time it takes only 2 seconds!!
If I run the same query on DB2 it always takes 100 seconds.
I expected data to be already present in bufferpool so to have the same results as SQL Server, I also tried enlarging the bufferpool size (now it's 27000), the result is the same...
I've got only one big bufferpool, the default one, and increased it to
with the command
db2 "alter bufferpool ibmdefaultbp immediate size xxx automatic".
The query is not affected at all by any of these change