View previous topic :: View next topic
|
Author |
Message |
Majid Hussain
New User
Joined: 18 Aug 2007 Posts: 20 Location: Bangalore
|
|
|
|
Hello
I am writing a DB2 native stored procedure which will
1. get multiple rows from TABLE1
2. for each record fetched, update another table TABLE2
3. for each update, send a row in result set with a success message.
1 and 2 are pretty easy. But for 3, we don't have a table and we cannot open a cursor WITH RETURN.
One solution could be to define a temporary table. Insert all the messages and then open a cursor on the table. I have coded a few COBOL-DB2 stored procedure with very good response times.
I was just wondering if there is another way to achieve this in native stored procedures that I have just started to write. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3053 Location: NYC,USA
|
|
|
|
If I understood correctly, you can declare 2 cursors instead of working with temp tables,
1) for successful message:
Code: |
SELECT 'Successfully updated the row'
from sysibm.sysdummy1 |
2)For unsuccessful message:
Code: |
SELECT 'ERROR updating the table'
from sysibm.sysdummy1 |
Now open anyone cursor based on the SQLCODE of your update query. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
DB2 V11 supports arrays but I don't know much about them yet. |
|
Back to top |
|
|
|