Solved

Getting resultsets back from a DB2 sql atomic block

Posted on 2004-10-20
4
1,187 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

740 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