Anonymous Block in DB2

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sybot_uk - 24 Oct 2007 10:37 GMT

Hi there, I am writing a query in DB2 which needs to return a result
set, i want to be able to use a couple of variables in query to speed
things up.

Please can anyone tell me how i may do this? Is it possible to do this
as part of some sort of anonymous block.

The query will actually be submitted by Crsytal reports, I have no
access to create procedures or functions within the database so
whatever i do it must be in the form of a suubmitable query.

Hope somone can help, many thanks,

Toby

Reply to this Message

jefftyzzer - 24 Oct 2007 17:48 GMT

> Hi there, I am writing a query in DB2 which needs to return a result
> set, i want to be able to use a couple of variables in query to speed
[quoted text clipped - 10 lines]
>
> Toby

>From your use of the phrase "anonymous block," I take it you've got

experience with Oracle. The closest thing in in DB2 is the use of
BEGIN ATOMIC. The only problem is that "[i]f you enclose SELECT
statements inside compound SQL statements, the results of the SELECT
statement are not be displayed to the screen. This is because the
SELECT statement "never happens" as far as the shell is concerned (DB2
only returns the success or failure of the compound atomic statement
as a whole)."

I've taken the above quote from the following link, which I hope will
help you:

http://www-128.ibm.com/developerworks/db2/library/techarticle/0203yip/0203yip.html

--Jeff

Reply to this Message

Serge Rielau - 25 Oct 2007 16:17 GMT

>> Hi there, I am writing a query in DB2 which needs to return a result
>> set, i want to be able to use a couple of variables in query to speed
[quoted text clipped - 24 lines]
>
> http://www-128.ibm.com/developerworks/db2/library/techarticle/0203yip/0203yip.html

Toby,

If you post your PL/SQL we can help you translate it to DB2.
From what you write it may be all you need is a WITH clause (common
table expression).

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message