• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1490
  • Last Modified:

DB2 CURSORS

Hi
I want to pass - and handle -"variable arguments" in a DB2 stored procedure.
I can pas them as a string and parse the string OK so get a row-set of attribute name/value pairs .

thing is I now wnat to be able to set fields in a table like
when the name is 'x' set field y to value.

I guess i need a cursor and a case statement - but I am having real problems with this - can't seem to find a decent example where the cursor is a rowset not an individual field.
drop procedure testx<<

create procedure testx()
   LANGUAGE SQL 
   BEGIN
	DECLARE TABLE x AS
	(
	ordinal integer,
	nam VARCAHR(50),
	val(50),
	);

	DECLARE SQLCODE INTEGER DEFAULT 0;
	DECLARE cursor CURSOR WITH RETURN TO CALLER FOR
	-- example of a possible variable input 
	-- GetNVPairs(string) is my UDF which will parse the string into a table of (nam, val) pairs
	select * from TABLE(GetNVPairs('field_1:val_1|field_2:val_2x|field_3:val_3y'));
	-- want to do something like 'dynamically' insert into table_y ( field_1, field_2, field_3) values (val_1, val_2, val_3)
	-- but how?


	OPEN cursor; 
  	fetch cursor into x;
  	while SQLCODE <> 100
   	do
      		fetch cursor into x;
  	end while;
  	
	close cursor;
     END

Open in new window

0
10Pints
Asked:
10Pints
  • 5
  • 3
1 Solution
 
momi_sabagCommented:
you will have to set all fields, something like

update
set col1 = case when str1='field1' the str2 else col1 end
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi 10pints,

A cursor represents a single row within a result set.  If I understand your question, I think that you want multiple columns in the set?


Kent
DECLARE time1 timestamp;
DECLARE time2 timestamp;

DECLARE mycursor CURSOR FOR
  SELECT start_time, end_time FROM mytable;

OPEN mycursor;

FETCH mycursor INTO time1, time2;

CLOSE mycursor;

Open in new window

0
 
10PintsAuthor Commented:

thanks gentlemen:

I am really struggling with this one.

From Kent's input I see something I missed:  so you can select multiple fields into a cursor
and assign them to multiple variables.
Right that gets me over the first hurdle so I can define a pair of variable  for the name and the value data

But then I need to iterate the rows dont I and "switch" on the data: especial the field name

My scenario is:
I have a stored procedure (SP) that gets called from c++ code and it has to create a parent item and any number of child depenendncies.
the parent file item will be  8 fields or so including a unique ID, the child items will just be the child file names.
The parent item will be added to a file table,
the child items are added to another table: a simple link table that will relate the parent to its child items.
Now the number of children is variable, and our SP handler code will go to max of 8 parameters.

So when iterating the parameter string I will get a table of name value pairs from the strin parser UDF: GetNVPairs( params string).

so I need to run a select on this and look at each attribute name and decide what to do with it:

So in the SP I thought I could declare a cursor on the name value table returned by GetNVPairs
iterate each row ( field name. value)
if it is the parent filename : set the parent name field to the value
else if it say the id set the ID field and so on for the 8 parent fields

Then the remaining parameters will be added as separate ros to the child dependency table along with the parent Id - to save the dependency information.

Also I would like to use this variable args idea will be used for other SPs.


Thanks for your help - much appreciated!

T
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
Kent OlsenData Warehouse Architect / DBACommented:
Hi 10Pints,

My thought is that you're making this harder than it has to be, and possibly affecting performance as well.

From your description, it sounds like you need two tables (parent and child) with a foreign key relationship between them.  Each row in the child table should contain the primary key of the parent row that it is associated with.

As far as the stored procedure goes, loop on the input string to find the name/value pairs.  That's a lot less complicated and more efficient than having another SP parse the string into a table or even having a UDF process the same string repeatedly looking for successive items.


Kent
0
 
10PintsAuthor Commented:
Interesting - the samples I found for parsing strings all use a table and recursion - which is a right pain
si I can use a function or SP and do a procedural style processing of the string - thta sounds too good to be true!  So could use Locate() and substr in loop then?
0
 
10PintsAuthor Commented:
Got it running - using the recursive approach - only just fathomed how to debug DB2 SPs using VS2088
That is a GREAT help!
All I need to do now is to call one SP from another and handle the return result set. (Any tips would be good!).
I find the IBM help verbose and mot very helpful - seems a sever lack of good examples - unless you are lucky in your search. Many thanks

T
0
 
10PintsAuthor Commented:
Right, i think the approach will be to parse the varaible name/value pairs from a string parameter into create a temporary table using a function like;
CREATE FUNCTION elemIdx ( string CLOB(64K) )
   RETURNS TABLE ( ordinal INTEGER, a INTEGER,  b INTEGER, c INTEGER)
   LANGUAGE SQL
   CONTAINS SQL
BEGIN ATOMIC
   RETURN
      WITH t(ordinal, a, b, c) AS
         (
         VALUES ( 0,       1,           LOCATE(':', string,   1), LOCATE('|', string, 1  ))
           UNION ALL
           SELECT ordinal+1, c+1, LOCATE(':', string, c+1), COALESCE( NULLIF( LOCATE('|', string, c+1), 0), LENGTH(string)+1)
           FROM   t
           WHERE  ordinal < 10000 AND LOCATE(':', string, c+1) <> 0  -- terminate if there are no further delimiters remaining
         )
      SELECT ordinal, a, b, c
      FROM   t;
END

then to have an SP use this like;
REATE 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<<

That all works BUT...
I really would like to make the table returned really temporary so I don't get any clashes - i.deally. thread specific so ~i was thinging of using a with statement in the GetNVPairs, but I am having difficulty with that!
any tip/ - then I reckon I can close this one
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi 10pints,

Here's an article on using DB2 recursive SQL to do that:

  http://www.experts-exchange.com/Database/DB2/A_3629-Recursive-SQL-in-DB2-Converting-Columns-to-Rows.html

If you want to use this as a learning exercise, great!  Otherwise, I suggest that it's overkill.

If you're trying to parse a parameter string that looks something like this:

  A=1,B=2,C=3

All you really want to do is loop on the string.

The code below is 90% of it.  It needs some sanity checks and tests of the last name/value pair (as there is always 1 pair that doesn't have a comma after it).


Good Luck,
Kent


DECLARE pstart       integer;
  DECLARE vstart       integer;
  DECLARE cpos         integer;
  DECLARE searchstring varchar (200) = 'A=1,B=2,C=3';
  DECLARE Name         varchar (50);
  DECLARE Value        varchar (50);

  SET pstart = 1;

  WHILE (pstart > 0)
  LOOP
    SET cpos = locate (search_string, ',');   -- find the next ','
    SET vstart = locate (search_string, '='); -- find the next '='
    SET Name = substr (searchstring, 1, vstart-1);    
    SET Value = substr (searchstring, vstart+1, cpos-vstart-1);
  END LOOP;

Open in new window

0
 
10PintsAuthor Commented:
Thanks Kent: as always you are a great help!
Clear concise and very helpful.
I guess the only solutions I found for DB2 were recursion based, so  I 'ASSUMED' it was the way to go...
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now