Hi All!

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:
BEGIN
DECLARE cur1 CURSOR WITH RETURN TO CALLER FOR
SELECT c1,c2,c3,c4
FROM tab1 JOIN tab2 ON...
WHERE cond;
OPEN cur1;
END
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.

Any idea how that can be done ?

thank you.
Anil

You could use a Declared temp table to store the intermediate results, then return the contents of the temp table. Why do you want to break the join?

Andy

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) ?

You SP would look like this:

BEGIN
DECLARE GLOBAL TEMPORARY TABLE fred (column defs) ON COMMIT PRESERVE ROWS NOT LOGGED;

INSERT INTO session.fred select ....

....


BEGIN
DECLARE CURSOR1 CURSOR for select * from session.fred;

OPEN CURSOR1;
END;

END

IMHO this may not necessarily help in decreasing the elapsed time, unless the optimizer cannot optimize the SQL becuase of its complexity ..

Just wondering if you have done physical/SQL tuning to support the complex piece of SQL ?
Runstats/Reorg etc ?
Numfreqvalues, numquantiles etc in the RUNSTATS command ?

Cheers
Sathyaram

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

Anil