Solved

Getting resultsets back from a DB2 sql atomic block

Posted on 2004-10-20
4
1,176 Views
Last Modified: 2008-02-01
Hello
I'm trying to find out a way in which messages like "hi" or "hello" can be returned to the command center/user.
I also would like to know how to return any result sets from atomic block compound statement. Right now i can execute an atomic block like the following in Command Center.

begin atomic
insert into mytable values(10);
select count(*) from mytable;
end

But I dont get back any resultset. All I get in the output area in the command center is

begin atomic
insert into mytable values(10);
select count(*) from mytable;
end


DB20000I  The SQL command completed successfully.
------------------------------------------------------------------------------------------------

When I run the same select statement individually i get

select count(*) from mytable

1          
-----------
        109

  1 record(s) selected.
--------------------------------------------------------------------------------------------------

This is very urgent. Could somebody please help me out with this? Is there any way in which these resultsets can be passed back ..like dbms_output in oracle or soemthing like that. I've seen a UDF online which replicates dbms_output (PUT_LINE) but it is not suitable for the application I'm working on. Are they any other solutions to this problem of mine?
Thank you very much
Vijay



0
Comment
Question by:vijay2320
4 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 12384785
have you tried


BEGIN
DECLARE C1 CURSOR WITH RETURN FOR
SELECT id,name,dept,job
FROM staff;
OPEN C1;
END
0
 

Author Comment

by:vijay2320
ID: 12385774
hello lowfatspread
i think the above declaration of cursor WITH RETURN FOR can only be used within a stored procedure. correct me if I am wrong. Anyways I dont think its possible to return result sets from an atomic block.

the other question i asked
i think the solution can be somehting like this...just in case somebody like me needs it.

select 'hello hi' from sysibm.sysdummy fetch first 1 row only

I get 'hello' as  a result set.


0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12653971
PAQed with points refunded (250)

modulo
Community Support Moderator
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

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…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

815 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

10 Experts available now in Live!

Get 1:1 Help Now