sql stored procedure questions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Roger - 25 Jul 2006 22:50 GMT

I am trying to create a stored procedure with following functionality.
I need to select a table where status = 'G' and return the row to the
application and then update all those rows that I just selected to
change status to 'P'. This table is being inserted all the time.

can i do this :

create a procedure sp1
...
RESULT SETS 1
...
declare cursor c1 with return for
select ....
from tb1
where status = 'G' for update of status
...
...
open c1
fetch c1 into outvariable
while .. do
update tb1 set status = 'P'
where current of c1
fetch c1 into outvariable
end while
...

Will this work ? or is there any other way to accomplish this.

Thanks

Reply to this Message

jefftyzzer - 25 Jul 2006 23:39 GMT

Looking at your pseudocode, I believe that you can absolutely do this
in an SP, but you might want also to consider a more esoteric feature
of DB2: modiying table functions, to wit:

SELECT
    *
FROM
    OLD TABLE
    (
    UPDATE
        TB1
    SET
        STATUS = 'P
    WHERE
        STATUS = 'G'
    )

This allows you to 1) update the value, and 2) return the "before
image" of the data. This should be markedly faster than a procedural
implementation. Let us know if this fits the bill.

Further reading: www.vldb.org/conf/2004/IND1P1.PDF

--Jeff

> I am trying to create a stored procedure with following functionality.
> I need to select a table where status = 'G' and return the row to the
[quoted text clipped - 25 lines]
>
> Thanks

Reply to this Message

Brian Tkatch - 26 Jul 2006 12:58 GMT

> I am trying to create a stored procedure with following functionality.
> I need to select a table where status = 'G' and return the row to the
[quoted text clipped - 25 lines]
>
> Thanks

No reason to FETCH. The stored PROCEDURE can RETURN the entire CURSOR
to the CLIENT, if it is OPENed before the PROCEDURE ends.

Here's an example:

CREATE PROCEDURE Example()
BEGIN
DECLARE List CURSOR WITH RETURN TO CLIENT FOR SELECT * FROM MyTable;
OPEN List;
END

B.

Reply to this Message

Roger - 26 Jul 2006 13:13 GMT

Thanks for the input...but brian, I need to update the rows i selected.
Thats why I tried to fetch and update the rows with current of. That
does not seem to work.

Thanks

> > I am trying to create a stored procedure with following functionality.
> > I need to select a table where status = 'G' and return the row to the
[quoted text clipped - 38 lines]
>
> B.

Reply to this Message

Serge Rielau - 26 Jul 2006 13:34 GMT

Use Jeff's SELECT FROM UPDATE proposal as the cursor.
DB2 will update the rows and pass you back the changes as a result set.

DECLARE cur CURSOR WITH RETURN FOR SELECT ... FROM OLD TABLE(UPDATE ...);
OPEN cur;

.. which of course begs the question why you bother with the procedure,
but that's performance.. next chapter.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message

Roger - 26 Jul 2006 14:58 GMT

OK...may be I should have mentioned I am trying to execute this
procedure in DB2 V 7 on Z/os. The select from old table (update...)
doesn't work in Db2 v7 on mainframe.

Any suggestions...

Thanks'

> Use Jeff's SELECT FROM UPDATE proposal as the cursor.
> DB2 will update the rows and pass you back the changes as a result set.
[quoted text clipped - 14 lines]
> IOD Conference
> http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message

Serge Rielau - 26 Jul 2006 15:31 GMT

> OK...may be I should have mentioned I am trying to execute this
> procedure in DB2 V 7 on Z/os. The select from old table (update...)
> doesn't work in Db2 v7 on mainframe.

True.. that should be in DB2 9 for zOS.

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message

Brian Tkatch - 26 Jul 2006 14:38 GMT

> Thanks for the input...but brian, I need to update the rows i selected.
> Thats why I tried to fetch and update the rows with current of. That
[quoted text clipped - 44 lines]
> >
> > B.

Oh. I just figured you could run the UPDATE first.

B.

Reply to this Message