Getting resultsets back from a DB2 sql atomic block

Posted on 2004-10-20
Last Modified: 2008-02-01
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;

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;

DB20000I  The SQL command completed successfully.

When I run the same select statement individually i get

select count(*) from mytable


  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 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

Question by:vijay2320
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
LVL 50

Expert Comment

ID: 12384785
have you tried

SELECT id,name,dept,job
FROM staff;

Author Comment

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.


Accepted Solution

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

Community Support Moderator

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ODBC connect from ms access to DB2 3 107
Catalog Index 9 131
The Event Monitor "DB2DETAILDEADLOCK" has reached its file 7 438
Fuzzy search functionality DB2 UDB 3 64
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…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit If you want to manage em…
In a recent question ( here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Suggested Courses

752 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