Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Cursor in DB2.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Anil Khanna

New User


Joined: 06 Sep 2013
Posts: 15
Location: Cube

PostPosted: Tue Jul 15, 2014 12:04 pm    Post subject: Cursor in DB2.
Reply with quote

Am trying to bind a package in IBM DB2 (mainframe) involving a SENSITIVE STATIC SCROLL cursor with ORDER BY and FOR UPDATE clause in it , but it fails with SQLCODE -243 whereas the same bind sucessfull without the ORDER BY clause. Any idea what could be the reason for this? We are using DB2 v10.0.

Output of the BIND is

Code:
DSNX200I -T1G2 BIND SQL ERROR

USING AIDABPTU AUTHORITY

PLAN=(NOT APPLICABLE)

DBRM=DBAMAGIX

STATEMENT=4586

SQLCODE=-243

SQLSTATE=36001

TOKENS=

CSECT NAME=DSNXEPP

RDS CODE=-190

SQL Statement causing this issue:

EXEC SQL

DECLARE CAGIX723 SENSITIVE STATIC SCROLL CURSOR FOR

SELECT PART_ID

,COLUMN1
,COLUMN2
,COLUMN3
,.
,.
,.
,COLUMNn
WHERE column1 = :host-var1
and column2 = ;host-var2
ORDER BY COLUMN2, COLUMN3 ,COLUMN4
FOR UPDATE
END-EXEC.
Back to top
View user's profile Send private message

chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Tue Jul 15, 2014 2:25 pm    Post subject: Reply to: Cursor in DB2.
Reply with quote

Hi,

You cant use FOR UPDATE and ORDER BY simultaneously.

When you mention ORDER BY clause resultant table of query is a read only table and you can not use FOR UPDATE clause for that table

Regards,
Chandan
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Tue Jul 15, 2014 2:35 pm    Post subject:
Reply with quote

Below is the link for similar topic discussed on forum

http://ibmmainframes.com/about45670.html

Also below what DB2 manual says for V10

Quote:
The ORDER BY clause cannot be used in an outermost fullselect that contains a FOR UPDATE clause

I also have same doubt as per link why you need order by for performing updates?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Getting -504 Cursor Name GTT-ARTS-CUR... Robin Sulsona DB2 2 Fri Mar 17, 2017 1:43 am
No new posts Release of Adabas Cursor mohitsethi All Other Mainframe Topics 1 Mon Feb 06, 2017 8:36 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Variable for cursor names ravikumar15 DB2 5 Sun Jul 17, 2016 7:08 am
No new posts DB2 Stored Procedure - Dynamic SQL - ... GuyC DB2 2 Wed Feb 17, 2016 5:10 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us