It is always better to use joins than :
- self-programmed stuff
since unions use at least twice the time, and self programmed stuff only uses nested loop technology, or merge scan technology. DB2 can use both, depending on statistics. And, DB2 can change the table order and perhaps use the star join, too. The last reason is, that you have to close and reopen cursors very often, while DB2 can handle this internally much faster.
Use Explain with the STATEMENT-Table created and see, how much service units the join would use. Then, calculate the service units consumed, when opening the inner cursor as often, as the outer cursor will give results. I cannot belive, that this would be faster....
But still, keep statistics up to date and read the explain-table wisely.
Originally posted by antodomnic
How about using joins in db2 running on OS/390 v 6.1. Will it increase the performance?.