IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SQL Code -501


IBM Mainframe Forums -> COBOL Programming
Post new topic   Reply to topic
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
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
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
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
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
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
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: 792
Location: Chennai, India

PostPosted: Thu Apr 17, 2008 6:11 pm
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...
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
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
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: 792
Location: Chennai, India

PostPosted: Thu Apr 17, 2008 6:34 pm
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: 2146
Location: At my coffee table

PostPosted: Thu Apr 17, 2008 7:04 pm
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: 11
Location: Lisboa, Portugal

PostPosted: Fri Apr 18, 2008 4:33 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> COBOL Programming

 


Similar Topics
Topic Forum Replies
No new posts run rexx code with jcl CLIST & REXX 15
No new posts Compile rexx code with jcl CLIST & REXX 6
No new posts REXX code to expand copybook in a cob... CLIST & REXX 2
No new posts VSAM return code 23 - for a Random read COBOL Programming 4
No new posts Monitoring production job progress. N... JCL & VSAM 4
Search our Forums:

Back to Top