View previous topic :: View next topic
|
Author |
Message |
kedianirmal
New User
Joined: 08 Feb 2008 Posts: 82 Location: Bangalore, India
|
|
|
|
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 |
|
|
acevedo
Active User
Joined: 11 May 2005 Posts: 344 Location: Spain
|
|
|
|
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 |
|
|
kedianirmal
New User
Joined: 08 Feb 2008 Posts: 82 Location: Bangalore, India
|
|
|
|
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 |
|
|
acevedo
Active User
Joined: 11 May 2005 Posts: 344 Location: Spain
|
|
|
|
and what is the value of the SQLCODE after the OPEN CURSOR? |
|
Back to top |
|
|
kedianirmal
New User
Joined: 08 Feb 2008 Posts: 82 Location: Bangalore, India
|
|
|
|
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 |
|
|
acevedo
Active User
Joined: 11 May 2005 Posts: 344 Location: Spain
|
|
|
|
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 |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007 Posts: 792 Location: Chennai, India
|
|
Back to top |
|
|
kedianirmal
New User
Joined: 08 Feb 2008 Posts: 82 Location: Bangalore, India
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007 Posts: 792 Location: Chennai, India
|
|
|
|
Have you opened the cursor, as Acevedo asked? |
|
Back to top |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007 Posts: 2146 Location: At my coffee table
|
|
|
|
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 |
|
|
ntmartins
New User
Joined: 03 Mar 2008 Posts: 11 Location: Lisboa, Portugal
|
|
|
|
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 |
|
|
|