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
 

 

SQL Code -501

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
kedianirmal

New User


Joined: 08 Feb 2008
Posts: 82
Location: Bangalore, India

PostPosted: Thu Apr 17, 2008 5:23 pm    Post subject: SQL Code -501
Reply with quote

hi,

I am facing problem in cobol program which is failing with SQL code -501 when a cursor is trying to fetch some data from a table. Actually the output for the cursor is a huge data. And the programmer has user the option HOLD for fetching the rows one by one.

The program fails with -501 only when there is a huge data to be processed.

Is it a normal thing. What can be the limit for the fetch condition.

Please help,
Nirmal
Back to top
View user's profile Send private message

acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Thu Apr 17, 2008 5:32 pm    Post subject:
Reply with quote

Code:
DSNT408I SQLCODE = -501, ERROR:  THE CURSOR IDENTIFIED IN A FETCH OR CLOSE STATEMENT IS NOT OPEN                                                     
DSNT415I SQLERRP    =  SQL PROCEDURE DETECTING ERROR                     


you said something about HOLD...

AFAIK: if the program has COMMIT and the cursor is not declared as WITH HOLD... the commit will close the cursor.
Back to top
View user's profile Send private message
kedianirmal

New User


Joined: 08 Feb 2008
Posts: 82
Location: Bangalore, India

PostPosted: Thu Apr 17, 2008 5:34 pm    Post subject:
Reply with quote

The Program has used the oprtion of CURSOR WITH HOLD FOR. So as i think the cursor should not be closed by the commit.
And even the program is giving the return code -501 at the start of the run also.

Regards,
Nirmal
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Thu Apr 17, 2008 5:41 pm    Post subject:
Reply with quote

and what is the value of the SQLCODE after the OPEN CURSOR?
Back to top
View user's profile Send private message
kedianirmal

New User


Joined: 08 Feb 2008
Posts: 82
Location: Bangalore, India

PostPosted: Thu Apr 17, 2008 5:54 pm    Post subject:
Reply with quote

The error message is as follows:

Code:
DSNT408I SQLCODE = -501, ERROR:  THE CURSOR IDENTIFIED IN A FETCH OR 
         CLOSE STATEMENT IS NOT OPEN                                 
DSNT418I SQLSTATE   = 24501 SQLSTATE RETURN CODE                     
DSNT415I SQLERRP    = DSNXERT SQL PROCEDURE DETECTING ERROR           
DSNT416I SQLERRD    = -240  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD    = X'FFFFFF10'  X'00000000'  X'00000000'           
         X'FFFFFFFF'  X'00000000'  X'00000000' SQL DIAGNOSTIC         
         INFORMATION   
[/code]
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Thu Apr 17, 2008 6:02 pm    Post subject:
Reply with quote

before the fetch you must issue

Quote:
EXEC SQL
OPEN cursornamehere
END-EXEC


and evaluate the SQLCODE for this db2 sentence.... I asked for that SQLCODE not for the FETCH statement sqlcode.
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Thu Apr 17, 2008 6:11 pm    Post subject:
Reply with quote

A cursor declared with WITH HOLD is closed at commit time if one of the following is true:

1. The connection associated with the cursor is in the release pending status.

2. The bind option DISCONNECT(AUTOMATIC) is in effect.

3. The environment is one in which the option WITH HOLD is ignored.

More on...
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/DSNSQJ10/5.42
Back to top
View user's profile Send private message
kedianirmal

New User


Joined: 08 Feb 2008
Posts: 82
Location: Bangalore, India

PostPosted: Thu Apr 17, 2008 6:26 pm    Post subject:
Reply with quote

The option given with bind are as follows:

Code:
VALIDATE(BIND) -   
ISOLATION(CS) -     
ACQUIRE(USE) -     
RELEASE(COMMIT) -   
EXPLAIN(YES)       



But the issue is its giving SQLCODE -501 in the beginning of the program; when its the first fetch and no commit statement is used till the time.
Hence i think its no where linked with COMMIT.
Let me know if more information is required.

Thanks,
Nirmal
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Apr 17, 2008 6:30 pm    Post subject:
Reply with quote

I realize that it is thursday, but you people seem to think that it is sat night at some bar.

acevedo gave the answer. OPEN the cursor before you fetch it.....


and people wonder why I am sarcastic with my posts.
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Thu Apr 17, 2008 6:34 pm    Post subject:
Reply with quote

Have you opened the cursor, as Acevedo asked?
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Thu Apr 17, 2008 7:04 pm    Post subject:
Reply with quote

dbzTHEdinosauer wrote:
and people wonder why I am sarcastic with my posts.
Actually, I wonder why you are so diplomatic with so many of your responses......Way more than I can manage.....
Back to top
View user's profile Send private message
ntmartins

New User


Joined: 03 Mar 2008
Posts: 12
Location: Lisboa, Portugal

PostPosted: Fri Apr 18, 2008 4:33 pm    Post subject:
Reply with quote

Well,

Either you did not opened the cursor, or you opened it and did not testet the sqlcode.

But there is a third option: When you open the cursor in the begining of the program and start treatinf large ammount of data without using the cursor it can fall down by timeout. Meaning that DB2 closes the cursor automaticaly.

First of all verify the two first possibilities.
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 -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to Capture the Max return code of... anilkumar922 All Other Mainframe Topics 0 Thu Jan 12, 2017 12:02 pm
This topic is locked: you cannot edit posts or make replies. Calling REXX code from ISPF panel sanchita dey TSO/ISPF 4 Mon Jan 02, 2017 5:13 pm
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
No new posts What is the code in CLIST to enable t... jackzhang75 CLIST & REXX 1 Fri Dec 02, 2016 3:02 am
No new posts IDEAL - Code Retrieval yugendran CA Products 0 Fri Nov 25, 2016 3:27 pm


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