Solved

DB2 With statement

Posted on 2010-11-23
4
1,585 Views
Last Modified: 2012-05-10
I want to remove the session temporay table and make it call based so I can quarantee no clashes in concurrent calls to the SP. Can anyone advise how a I change the seesion table to one using the with statement? I would like ot be able to reference the table from theclient code ( as a returned cursor I guess).
0
Comment
Question by:10Pints
  • 2
4 Comments
 

Author Comment

by:10Pints
ID: 34195891
Ooops! sorry for got the code snippet ...
DROP PROCEDURE GetNVPairs <<

CREATE PROCEDURE GetNVPairs(str CLOB(64K)) 

  LANGUAGE SQL

  RESULT SETS 1



 P1:BEGIN

    DECLARE X integer;

    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

    DECLARE v_a VARCHAR(250);

    DECLARE v_b VARCHAR(250);



    DECLARE GLOBAL TEMPORARY TABLE SESSION.ITEMS

    (

       ordinal integer,

       name   VarChar(50),

       val    VarChar(50)

    )

    WITH REPLACE 

    ON COMMIT

    PRESERVE ROWS

    NOT LOGGED;

  

  BEGIN

    DECLARE iordinal integer;

    DECLARE ia integer;

    DECLARE ib integer;

    DECLARE ic integer;

    DECLARE cur CURSOR FOR SELECT * FROM TABLE(elemIdx( str)) ORDER BY ordinal;

 

    DECLARE cur2 CURSOR  WITH RETURN TO CALLER FOR SELECT * FROM SESSION.ITEMS;

    DELETE FROM SESSION.ITEMS;



    OPEN cur;

    FETCH FROM cur INTO iordinal, ia, ib, ic;



    WHILE(SQLSTATE = '00000') DO

         IF ((ia <> 0) AND ( ib <> 0) AND ( ic <> 0)) then

    	     INSERT INTO SESSION.items(ordinal, name, val) VALUES(iordinal,  SUBSTR( str, ia, ib-ia), SUBSTR( str, ib+1, ic-ib-1));

         END IF;



         FETCH FROM cur INTO iordinal, ia, ib, ic;

    END WHILE;



    CLOSE cur;



    OPEN cur2;



END;

END P1<<

Open in new window

0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 250 total points
ID: 34195921
you can use the temporary table since every session gets its own copy, so you won't have colisions, but you can also use

with a as (
SELECT * FROM TABLE(elemIdx( str))
)
select iordinal,  SUBSTR( str, ia, ib-ia), SUBSTR( str, ib+1, ic-ib-1)
from a
order by ordinal
0
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 250 total points
ID: 34196184
Hi 10pints,

The two techniques have their own usage.

The common table expression that Momi shows is often used when a query needs to process the same set of data more than once or when recursion is needed.  It can also make a query a lot easier to write and understand.

The temporary table that you show is often used to simplify a complex query, when multiple queries need to process the same data, or when one job step (i.e. the stored procedure) needs to hand results to another step in a job.

Unless there are performs reasons to do otherwise, I'll choose the CTE whenever possible.


Good Luck,
Kent
0
 

Author Closing Comment

by:10Pints
ID: 34211619
thanks guys - BRILLIANT!  this cuts my code in less than half and will avid any potential temporary table clashes as the server is multithreaded.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Exclude numbers that start with 7 4 237
Using select statement from input file with DB2 EXPORT 1 529
iSeries DB2 - Query with Sub Query? 7 103
AS400 BRMS - none enrolled tape 6 33
November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now