Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2005
    Posts
    118

    Unanswered: Alternative to results sets

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Feb 2005
    Posts
    118
    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) ?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Feb 2005
    Posts
    118
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •