asked on

DB2 With statement

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

10Pints

ASKER

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

ASKER CERTIFIED SOLUTION

momi_sabag

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION