CURSOR WITH RETURN AND UPDATE?
in [DB2]
|
Prev: Have you registered yet for Episodes 2 & 3 of The DB2Night Show on Oct 1 and 15th
Next: DB2 position function (again!)
From: esmith2112 on 28 Sep 2009 12:41 Within the confines of a stored procedure how do I declare a cursor to return a result set to the client application, and also update a non- returned attribute of each row? For example using pseudo-SQL, my non- functioning attempt: ================= declare curs1 cursor with return for select t_id, t_name from mytable where sent_timestamp is null; --perform update on each row open curs1; while (<not at end of cursor>) fetch curs1 into my_t_id, my_t_name; update mytable set sent_timestamp = current timestamp where t_id = my_id; end while; --return results to client open curs1; ================ This reminds me of the Halloween problem a little. But evening googling didn't provide an ready answer. Using 9.5 on AIX 5.3. Thanks, Evan P.S. Anyone know how to get Google Groups to search this archive past the most recent 30 days?
From: jefftyzzer on 28 Sep 2009 14:00 On Sep 28, 9:41 am, esmith2112 <esmith2...(a)gmail.com> wrote: > Within the confines of a stored procedure how do I declare a cursor to > return a result set to the client application, and also update a non- > returned attribute of each row? For example using pseudo-SQL, my non- > functioning attempt: > > ================= > declare curs1 cursor with return for > select t_id, t_name > from mytable > where sent_timestamp is null; > > --perform update on each row > open curs1; > while (<not at end of cursor>) > fetch curs1 into my_t_id, my_t_name; > > update mytable > set sent_timestamp = current timestamp > where t_id = my_id; > end while; > > --return results to client > open curs1; > > ================ > > This reminds me of the Halloween problem a little. But evening > googling didn't provide an ready answer. > > Using 9.5 on AIX 5.3. > > Thanks, > Evan > > P.S. Anyone know how to get Google Groups to search this archive past > the most recent 30 days? Why do you need a cursor at all? Why not just do update mytable set sent_timestamp = current timestamp where sent_timestamp is null; and then open curs1; If you really do need to loop, I think you're better off using a cursor FOR loop (much faster than open...while...fetch), like so: FOR l_mytab as c_mytab cursor for select t_id, t_name from mytable where sent_timestamp is null DO update mytable set sent_timestamp = current timestamp where t_id = l_mytab.id;-- end for;-- As to returning the cursor, the easiest thing to do would be to simply declare it as before and open it after the update, i.e., put the "open curs1;" after the "end for;" For something even crazier (and likely faster), you could do this: declare curs1 cursor with return for select t_id, t_name from final table (update mytable set sent_timestamp = current timestamp where sent_timestamp is null );-- open curs1;-- --Jeff
From: esmith2112 on 28 Sep 2009 14:30 On Sep 28, 2:00 pm, jefftyzzer <jefftyz...(a)sbcglobal.net> wrote: > On Sep 28, 9:41 am, esmith2112 <esmith2...(a)gmail.com> wrote: > > > > > Why do you need a cursor at all? Why not just do > > update mytable > set sent_timestamp = current timestamp > where sent_timestamp is null; > > and then > > open curs1; > > If you really do need to loop, I think you're better off using a > cursor FOR loop (much faster than open...while...fetch), like so: > > FOR l_mytab as c_mytab cursor for > select t_id, t_name > from mytable > where sent_timestamp is null > > DO > update mytable > set sent_timestamp = current timestamp > where t_id = l_mytab.id;-- > > end for;-- > > As to returning the cursor, the easiest thing to do would be to simply > declare it as before and open it after the update, i.e., put the "open > curs1;" after the "end for;" > > For something even crazier (and likely faster), you could do this: > > declare curs1 cursor with return for > select t_id, t_name > from > final table > (update mytable > set sent_timestamp = current timestamp > where sent_timestamp is null > );-- > > open curs1;-- > > --Jeff Thanks, Jeff. I'll give your suggestions a shot. I haven't done application development in a long while, so my skills are definitely rusty. I knew I was probably missing a fundamental concept here.
From: Mark A on 28 Sep 2009 17:05 > "esmith2112" <esmith2112(a)gmail.com> wrote in message > news:aae8aab2-00b0-405e-930b-84a33241f175(a)g23g2000yqh.googlegroups.com... > Thanks, Jeff. I'll give your suggestions a shot. I haven't done > application development in a long while, so my skills are definitely > rusty. I knew I was probably missing a fundamental concept here. Don't be too hard on yourself. There are some potential problems doing the update in one statement, such as filling up the transaction log and/or holding a lot of locks on the table--thereby decreasing concurrency. With a cursor, you can commit every N updates, and if the cursor is defined WITH HOLD, then it will not close when a commit is issued. I don't now if these issues apply in your circumstance, but there are legitimate reasons for using a cursor, even if the elapsed time is a bit slower.
From: jefftyzzer on 28 Sep 2009 19:26
On Sep 28, 11:30 am, esmith2112 <esmith2...(a)gmail.com> wrote: > On Sep 28, 2:00 pm, jefftyzzer <jefftyz...(a)sbcglobal.net> wrote: > > > > > On Sep 28, 9:41 am, esmith2112 <esmith2...(a)gmail.com> wrote: > > > Why do you need a cursor at all? Why not just do > > > update mytable > > set sent_timestamp = current timestamp > > where sent_timestamp is null; > > > and then > > > open curs1; > > > If you really do need to loop, I think you're better off using a > > cursor FOR loop (much faster than open...while...fetch), like so: > > > FOR l_mytab as c_mytab cursor for > > select t_id, t_name > > from mytable > > where sent_timestamp is null > > > DO > > update mytable > > set sent_timestamp = current timestamp > > where t_id = l_mytab.id;-- > > > end for;-- > > > As to returning the cursor, the easiest thing to do would be to simply > > declare it as before and open it after the update, i.e., put the "open > > curs1;" after the "end for;" > > > For something even crazier (and likely faster), you could do this: > > > declare curs1 cursor with return for > > select t_id, t_name > > from > > final table > > (update mytable > > set sent_timestamp = current timestamp > > where sent_timestamp is null > > );-- > > > open curs1;-- > > > --Jeff > > Thanks, Jeff. I'll give your suggestions a shot. I haven't done > application development in a long while, so my skills are definitely > rusty. I knew I was probably missing a fundamental concept here. No problem--we're all still learning. I've got no issues with cursors (some of my beast friends....), I just tend to prefer cursor FOR loops over the "old-fashioned" kind. Regards, --Jeff |