Right now we have a calling program that calls Stored Procedure (lets say SP1) that returns a results set.
Therefore the body of SP1 looks as follows:
DECLARE cur1 CURSOR WITH RETURN TO CALLER FOR
FROM tab1 JOIN tab2 ON...
values c1 and c2 are from tab1 ;
values c3 and c4 are from tab2 ;
lets say I want to create a results set where I get values c1,c2,c3,c4 but not using a join.
Instead I want two SQLs
one SQL to get c1 and c2
second SQL to get c3 and c4
and each row of the results set is concat of o/p from SQL1 and o/p opf SQL2.
The results sets in used for reports. And since results are used, everything is transformed into one big complex sql (with many joins).
Thereby resulting in poor performance.
by breaking up the complex sql, and doing cursor processing, we could get the same results.
but then how do you pass back the results to the reports (or results sets) ?
What I noticed is that sometimes the business logic can better decide the filters than leaving it to the db2 optimizer.
therefore, by spliting of the complex join, what we achive is
- direct the order of processing (which could become very tricky if told to db2)
- perform functions outside the realm of db2. right now the functions are invoked in the select clause. To make it worse, sometimes the functions are invoked in the WHERE clause