sql stored procedure questions

[Roger]

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

[jefftyzzer]

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

Roger wrote:

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

[Brian Tkatch]

Roger wrote:

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

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.

[Roger]

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

Brian Tkatch wrote:

Roger wrote:

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

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.

[Serge Rielau]

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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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

[Brian Tkatch]

Roger wrote:

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

Brian Tkatch wrote:

Roger wrote:

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

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.

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

B.

[Roger]

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'
Serge Rielau wrote:

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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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

[Serge Rielau]

Roger wrote:

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.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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