?
Solved

Getting resultsets back from a DB2 sql atomic block

Posted on 2004-10-20
4
Medium Priority
?
1,206 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

CHALLENGE LAB: Troubleshooting Connectivity Issues

Goal: Fix the connectivity issue in the lab's AWS environment so that you can SSH into the provided EC2 instance.  

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 (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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

719 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