View previous topic :: View next topic
|
Author |
Message |
vishalbshah
New User
Joined: 01 Dec 2006 Posts: 61 Location: Pune
|
|
|
|
Hi,
I am trying to declare below cursor in my cobol program :
Code: |
EXEC SQL
DECLARE FSU-CSR1 CURSOR WITH HOLD FOR
SELECT DISTINCT
CUST_NUM
FROM CUD_CUD_IP_TCL_RTY
WHERE CLASS_REGIME_CDE = :LS-TAX-CLASS-REGIME-CODE
AND RQSTR_ID = :LS-REQUESTOR-ID
AND MSG_STAT_CDE = :LS-MSG-STATUS-CODE
AND INDC_SRCH_RQST_TS < :LS-INDICIA-SRCH-RQST-TS
AND CUST_NUM BETWEEN :WW-DB2-START-CUST-NO
AND :WW-DB2-END-CUST-NO
ORDER BY CUST_NUM ASC
FOR READ ONLY
END-EXEC |
When I am trying to open the same cursor via OPEN statment I am getting below error :
DSNH520I DSNHSMUD LINE 6450 COL 9 THE OPEN STATEMENT FOR CURSOR
"FSU-CSR1" IS INVALID BECAUSE THE CURSOR WAS DEFINED BY AN ALLOCATE
CURSOR STATEMENT
My OPEN Statement is
Code: |
EXEC SQL
OPEN FSU-CSR1
END-EXEC |
Could someone please suggest why Cobol compiler treating this as a ALLOCATE statement rather than DECLARE and why it is giving this error? |
|
Back to top |
|
|
Naish
New User
Joined: 07 Dec 2006 Posts: 82 Location: UK
|
|
|
|
Could you try renaming the cursor-name (removing hyphens) ? |
|
Back to top |
|
|
vishalbshah
New User
Joined: 01 Dec 2006 Posts: 61 Location: Pune
|
|
|
|
Hi,
I have already tried that and infact i could see another successful module having cursor name with hypens.
It should be to do with something else. |
|
Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009 Posts: 1788 Location: Bloomington, IL
|
|
|
|
As the fine manual indicates, the declaration of the cursor is bad, so when you refer to it, DB2 assumes that you must have defined through ALLOCATE. |
|
Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006 Posts: 1353 Location: Israel
|
|
|
|
Apparently, the problem is in the DECLARE:
Quote: |
********************* Text Below Copyright (c) 2012, IBM *********************
DSNH520I
E csectname THE OPEN STATEMENT FOR CURSOR cursor-name IS INVALID BECAUSE THE
CURSOR WAS DEFINED BY AN ALLOCATE CURSOR STATEMENT
Explanation
The cursor was not successfully declared, so it was assumed to be defined by
an ALLOCATE CURSOR statement. A cursor defined by an ALLOCATE CURSOR
statement is open after successful completion of the statement. Attempting to
OPEN an allocated cursor is an error. |
However, I see nothing wrong with your query (but I'm not a DB2 expert).
Did you cut and paste the text ? Did you correct a typo on the way ? |
|
Back to top |
|
|
Naish
New User
Joined: 07 Dec 2006 Posts: 82 Location: UK
|
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
nazishmirekar,
You have pickled your link. You should use the Preview button and click on the link before posting. I know, because I have done it myself. You have to replace the
your_URL.com bit with the URL you want.
I've no idea if your link would be considered on to a "competitor site". |
|
Back to top |
|
|
Naish
New User
Joined: 07 Dec 2006 Posts: 82 Location: UK
|
|
|
|
Moderators,
If this violates any of the rules could you please edit this? Now that its already more than "10 minutes" it doesn't allow me to edit my post. |
|
Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006 Posts: 1353 Location: Israel
|
|
|
|
Competitor or not, it provides a plausible reason for the error
And it reminded me why I always put my DECLARE CURSORs before the PROCEDURE DIVISION. |
|
Back to top |
|
|
vishalbshah
New User
Joined: 01 Dec 2006 Posts: 61 Location: Pune
|
|
|
|
Guys,
I have defined two cursors defined in the same module.
EXEC SQL
DECLARE FSUUSBCD-CSR CURSOR FOR
SELECT PRM_DATA
FROM CUD_PROG_PRM
WHERE PROC_ID = :CUD038-PROC-ID
END-EXEC
EXEC SQL
OPEN FSUUSBCD-CSR
END-EXEC
this is working fine
But
EXEC SQL
DECLARE FSU_CSR1 CURSOR WITH HOLD FOR
SELECT DISTINCT
CUST_NUM
FROM CUD_CUD_IP_TX_RPTY
WHERE CLASS_REGIME_CDE = :WW-DB2-REGIME-CODE
AND RQSTR_ID = :WW-DB2-RQSTR-ID
AND MSG_STAT_CDE = :WW-DB2-MSG-STS-CODE
AND INDC_SRCH_RQST_TS < :WW-DB2-IND-SRCH-TS
AND (CUST_NUM BETWEEN :WW-DB2-START-CUST-NO
AND :WW-DB2-END-CUST-NO)
ORDER BY CUST_NUM ASC
FOR READ ONLY
END-EXEC
gives error whild opening the cursor as below:
EXEC SQL
DSNH520I DSNHSMUD LINE 6452 COL 8 THE OPEN STATEMENT FOR CURSOR
"FSU_CSR1" IS INVALID BECAUSE THE CURSOR WAS DEFINED BY AN ALLOCATE
CURSOR STATEMENT
OPEN FSU_CSR1
END-EXEC |
|
Back to top |
|
|
vishalbshah
New User
Joined: 01 Dec 2006 Posts: 61 Location: Pune
|
|
|
|
Hi Guys,
This cursor was declared in the procedure division.
Looking at a closer look i found that my COBOL program is structured in a way ( incorrect) that FETCH statement for above cursor is coming in the order prior to DECLARE.
The Execution order was proper i.e. DECLARE (which is not executable) then OPEN and Then FETCH.
But the sections were written a way that FETCH statement was prior in COBOL line ordering then DECLARE and OPEN.
Not sure if this was causing issue. Does COBOL compiler need code Liner number of DECLARE and OPEN lesser than that of FETCH ? and does this create similar issue if we not follow it ?
Any suggestions most welcome ! |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
why don't you read the manuals?
yes the order matters,
and it is not for the cobol compiler, (come on at least pretend to think!)
it is for the db2 pre-compiler.
Declare must come first, physically, in a program.
after that, does not matter (open/fetch/close) |
|
Back to top |
|
|
vishalbshah
New User
Joined: 01 Dec 2006 Posts: 61 Location: Pune
|
|
|
|
Hi,
Thanks for the reply.
I meant DB2-precompiler , (sorry, I should have been specific) was referring to the compilation of COBOL-DB2 program.
Could you please, direct me to the manual which says the DECLARE should PHYSICALLY come first for compilation of COBOL-DB2 program if a cursor is defined ? |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
If I can wade through the material in the link that was posted for your benefit, I'm sure you can/should. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
Back to top |
|
|
vishalbshah
New User
Joined: 01 Dec 2006 Posts: 61 Location: Pune
|
|
|
|
Thanks a lot,
It makes sense. a limitation of a single pass COBOL precompiler! |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
by the way vishalbshah,
there are about a dozen regulars here,
that have 20-30 years experience,
which means when we learned (60's, 70's)
there was only hardcopy manuals,
they cost,
so there was usually just 1 copy in the entire organization,
and a rookie had no business with one on his desk,
so we had to hide,and read read read read read
turn pages, read,, and hopefully remember
but we learned how to navigate thru ibm's manuals,
so if we say that the manual states such and such,
it is because we read it while inflicting paper cuts on our fingers
as we turned the pages.
not something you forget.
you need to spend a week-end each on cics, db2, cobol, z/os
just reading the manuals / not so much as to learn all of it
but remember that you saw something about a subject in this manual,
so you can go back later, if you need, and learn about it in detail.
not knowing the syntax for an sql as well as any restrictions,
gotcha's or whatever,
shows me someone who is not prepared to take this skill as a life course seriously,
or well. |
|
Back to top |
|
|
vishalbshah
New User
Joined: 01 Dec 2006 Posts: 61 Location: Pune
|
|
|
|
Thanks a Lot,
it makes sense !
A typical one pass Precompiler limitation for Cursors in COBOL and Fortran programs. |
|
Back to top |
|
|
vishalbshah
New User
Joined: 01 Dec 2006 Posts: 61 Location: Pune
|
|
|
|
Yes Sir,
I agree with you and I was dying to read something on this subject.
I think there was an issue with the link mentioned in the Forum which was not getting opened for some reason so i could not read it through which is why I have asked the link again.
I am only a beginner in this arena (moved out of world of electronics to Mainframe) and want to read and learn more!
I appreciate your help !
Thanks Again! |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
thx,
but you don't have to quote the manual for anyone (especially twice). |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
Since clicking the link didn't work, in that it attempted to take you to a site which doesn't exist, why didn't you copy/paste what you could see, or even retype it in that boxy-looking thing at the top of the screen. The URL is valid, it was just formatted incorrectly in the post.
Don't be afraid to try things. May tell you something. May not. If you don't try it, it definitely won't tell you anything. This has quite general application.
Things not to try included pressing the Emergency Stop on the CPU, stepping off the top of Fire Escapes etc. If it seems highly unlikely to cause any problem (like typing a URL) then give it a go. |
|
Back to top |
|
|
vishalbshah
New User
Joined: 01 Dec 2006 Posts: 61 Location: Pune
|
|
|
|
Hi Bill,
I tried the Copy paste of the link eventually and found the answer to my question but i think our replies are too faster.
As I am in the Office premises, so when the link didn't work while clicking on it I thought the link might be of one of the "blocked" sites and so I should not open it.
Anyway, I am appearing for COBOL and JCL exam in a month's time. so I want to read more and more documents on these subjects.
I thank you both ! |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
I don't know if this matters (and i'm not able to test this now), but this is the first time i've noticed a cursor declared WITH HOLD and then later in the same declaration having FOR READ ONLY specified |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
as more and more db2 programmers
finally
learned that they needed to commit read-only cursors, also,
the requirement for WITH HOLD and FOR READ ONLY became common. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Thank you, kind sir
I Will learn more of this. . .
d |
|
Back to top |
|
|
|