Simple SQL question

Jobi M B225 Augustine

Simple SQL question
Hi List,

I have a very simple question. Is it possible to give ORDER BY in a cursor
declaration with FOR UPDATE option as follows in OS390 DB2 V6.

EXEC SQL DECLARE C3 CURSOR WITH HOLD FOR
SELECT WORKDEPT, PHONENO, JOB, EDLEVEL, SALARY FROM
DSN8410.EMP
ORDER BY WORKDEPT
FOR UPDATE OF PHONENO;

Is there any limitations for this !!

A quick reply will be really appreciated.

Thanks and best regards,

Jobi Augustine
CIGNA Systems - ES IM &T
* Tel (860) 226-5466 (6-5466)
* Fax (860) 226-5344 (6-5344)
* Mail [login to unmask email] <mailto:[login to unmask email]>

Confidential, unpublished property of CIGNA. Do not duplicate or distribute.
Use and distribution limited solely to authorized personnel. Copyright2001
CIGNA.


------------------------------------------------------------------------------
CONFIDENTIALITY NOTICE: If you have received this e-mail in error, please immediately notify the sender by e-mail at the address shown. This e-mail transmission may contain confidential information. This information is intended only for the use of the individual(s) or entity to whom it is intended even if addressed incorrectly. Please delete it from your files if you are not the intended recipient. Thank you for your compliance.© Copyright 2001 CIGNA

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++



John Hardy

Re: Simple SQL question
(in response to Jobi M B225 Augustine)
Sorry, no. The 'ORDER BY' clause makes the result table 'Read Only'. The
result table in a 'CURSOR FOR UPDATE' cannot be read only.


On Wed, 26 Dec 2001 17:06:50 -0500, Augustine, Jobi M B225
<[login to unmask email]> wrote:

>Hi List,
>
>I have a very simple question. Is it possible to give ORDER BY in a cursor
>declaration with FOR UPDATE option as follows in OS390 DB2 V6.
>
>EXEC SQL DECLARE C3 CURSOR WITH HOLD FOR
> SELECT WORKDEPT, PHONENO, JOB, EDLEVEL, SALARY FROM
>DSN8410.EMP
>ORDER BY WORKDEPT
> FOR UPDATE OF PHONENO;
>
>Is there any limitations for this !!
>
>A quick reply will be really appreciated.
>
>Thanks and best regards,
>
>Jobi Augustine
>CIGNA Systems - ES IM &T
>* Tel (860) 226-5466 (6-5466)
>* Fax (860) 226-5344 (6-5344)
>* Mail [login to unmask email] <mailto:[login to unmask email]>
>
>Confidential, unpublished property of CIGNA. Do not duplicate or
distribute.
>Use and distribution limited solely to authorized personnel. Copyright2001
>CIGNA.
>
>
>---------------------------------------------------------------------------
---
>CONFIDENTIALITY NOTICE: If you have received this e-mail in error, please
immediately notify the sender by e-mail at the address shown. This e-mail
transmission may contain confidential information. This information is
intended only for the use of the individual(s) or entity to whom it is
intended even if addressed incorrectly. Please delete it from your files
if you are not the intended recipient. Thank you for your compliance.©
Copyright 2001 CIGNA
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>
>





David Seibert

Re: Simple SQL question
(in response to John Hardy)
Hi Jobi,

This is documented in the SQL reference under the DECLARE CURSOR clause.
See
http://publib.boulder.ibm.com:80/cgi-bin/bookmgr/BOOKS/dsnsqh11/5.40#HDRRDCU
R
where it tells your that as of Version 7 on z/OS and OS/390, the Order By
clause still makes the cursor read-only and therefore not a candidate for
Update where current of.
This is true in V6 and prior also.

This is the case unless you are using Scrollable cursors which are a feature
new to V7.

David Seibert
Compuware Corporation File-AID Product Architect
[login to unmask email]