Hi All,
I have two Programs - Program A and Program B . In Program A, I have 1 cursor with name Cursor1.
I declared the cursor and then opened it, after opening the cursor I called Program B[my cursor1 of Program A is still open]. In Program B also 1 declared and open another cursor named Cursor1.{Program A and Program B cursors are on different tables but unknowingly they have same names}.
As I have seen in same program we can't open two cursors with same name, will the above scenario give Sqlcode -502 or it will execute without any issue or we will get error during precompile time ?
Please share your thoughts.
Note: I don't have terminal now to test this scenario that's I want to discuss with you guys.
Same Cursor name used in Program and Subprogram
Previous topic • Next topic • 4 posts
• Page 1 of 1
Re: Same Cursor name used in Program and Subprogram
by gouravar » Sat Nov 01, 2014 6:38 pm
Can anyone please respond on this ?
Re: Same Cursor name used in Program and Subprogram
by NicC » Sat Nov 01, 2014 6:45 pm
Obviously not. Especially as for most helpers this is the weekend and they are probably carryng in with their lives.
What have you studied so far to try and reach your own conclusion?
What have you studied so far to try and reach your own conclusion?
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
Regards
Nic
- NicC
- Global moderator
- Posts: 3025
- Joined: Sun Jul 04, 2010 12:13 am
- Location: Pushing up the daisies (almost)
- Has thanked: 4 times
- Been thanked: 136 times
Re: Same Cursor name used in Program and Subprogram
by alexm » Wed Nov 26, 2014 11:17 pm
Hi gouravar,
after a long weekend, I found some time to answer your question
When program 'A' gets compiled, a DBRM 'A' is being generated. It includes all the SQL - and additional information like host variables etc - exctracted from your program 'A' source code. At this time, the DB2 precompiler has no knowledge of the existence of a program 'B'. When program 'B' gets compiled, a DBRM 'B' is being generated - here also, DB2 has no knowledge of a program 'A', it only sees program 'B'.
If you look at the DB2 precompiler output listing, you may have a look at those places where the precompiler replaced your EXEC SQL ... END-EXEC statements. You'll find new structures (SQL-PLISTn) and calls to routines (PERFORM SQL-INITIAL, ...) but none of those places have a reference to your cursor name. So if the cursor name is not known to DB2 either at precompile nor at runtime, DB2 does not care about cursor names...
I've created A and B samples. Both use cursor name 'C1' and access different tables:
Running programs results in:
I'm now back enjoying the next weekend
after a long weekend, I found some time to answer your question
When program 'A' gets compiled, a DBRM 'A' is being generated. It includes all the SQL - and additional information like host variables etc - exctracted from your program 'A' source code. At this time, the DB2 precompiler has no knowledge of the existence of a program 'B'. When program 'B' gets compiled, a DBRM 'B' is being generated - here also, DB2 has no knowledge of a program 'A', it only sees program 'B'.
If you look at the DB2 precompiler output listing, you may have a look at those places where the precompiler replaced your EXEC SQL ... END-EXEC statements. You'll find new structures (SQL-PLISTn) and calls to routines (PERFORM SQL-INITIAL, ...) but none of those places have a reference to your cursor name. So if the cursor name is not known to DB2 either at precompile nor at runtime, DB2 does not care about cursor names...
I've created A and B samples. Both use cursor name 'C1' and access different tables:
IDENTIFICATION DIVISION. | IDENTIFICATION DIVISION.
PROGRAM-ID. A. <-> PROGRAM-ID. B.
ENVIRONMENT DIVISION. | ENVIRONMENT DIVISION.
CONFIGURATION SECTION. | CONFIGURATION SECTION.
DATA DIVISION. | DATA DIVISION.
WORKING-STORAGE SECTION. | WORKING-STORAGE SECTION.
01 TEMP PIC X(1). | 01 TEMP PIC X(1).
01 DISCOMP PIC +9(9). | 01 DISCOMP PIC +9(9).
EXEC SQL INCLUDE SQLCA END-EXEC. | EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL DECLARE C1 CURSOR FOR SELECT <-> EXEC SQL DECLARE C1 CURSOR FOR SELECT
IBMREQD FROM SYSIBM.SYSDUMMY1 END-EXEC. <-> 'X' FROM SYSIBM.SYSDUMMYE END-EXEC.
PROCEDURE DIVISION. | PROCEDURE DIVISION.
MAIN SECTION. | MAIN SECTION.
EXEC SQL OPEN C1 END-EXEC. | EXEC SQL OPEN C1 END-EXEC.
MOVE SQLCODE TO DISCOMP | MOVE SQLCODE TO DISCOMP
DISPLAY 'A OPEN <' DISCOMP '/' SQLERRMC '>' | DISPLAY 'B OPEN <' DISCOMP '/' SQLERRMC '>'
CALL 'B' <|
EXEC SQL FETCH C1 INTO :TEMP END-EXEC. | EXEC SQL FETCH C1 INTO :TEMP END-EXEC.
MOVE SQLCODE TO DISCOMP | MOVE SQLCODE TO DISCOMP
DISPLAY 'A FETCH <' DISCOMP '/' SQLERRMC | DISPLAY 'B FETCH <' DISCOMP '/' SQLERRMC
'/' TEMP '>' | '/' TEMP '>'
EXEC SQL CLOSE C1 END-EXEC. | EXEC SQL CLOSE C1 END-EXEC.
MOVE SQLCODE TO DISCOMP | MOVE SQLCODE TO DISCOMP
DISPLAY 'A CLOSE <' DISCOMP '/' SQLERRMC '>' | DISPLAY 'B CLOSE <' DISCOMP '/' SQLERRMC '>'
GOBACK | GOBACK
. | .
MAIN-EXIT. EXIT. | MAIN-EXIT. EXIT.
PROGRAM-ID. A. <-> PROGRAM-ID. B.
ENVIRONMENT DIVISION. | ENVIRONMENT DIVISION.
CONFIGURATION SECTION. | CONFIGURATION SECTION.
DATA DIVISION. | DATA DIVISION.
WORKING-STORAGE SECTION. | WORKING-STORAGE SECTION.
01 TEMP PIC X(1). | 01 TEMP PIC X(1).
01 DISCOMP PIC +9(9). | 01 DISCOMP PIC +9(9).
EXEC SQL INCLUDE SQLCA END-EXEC. | EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL DECLARE C1 CURSOR FOR SELECT <-> EXEC SQL DECLARE C1 CURSOR FOR SELECT
IBMREQD FROM SYSIBM.SYSDUMMY1 END-EXEC. <-> 'X' FROM SYSIBM.SYSDUMMYE END-EXEC.
PROCEDURE DIVISION. | PROCEDURE DIVISION.
MAIN SECTION. | MAIN SECTION.
EXEC SQL OPEN C1 END-EXEC. | EXEC SQL OPEN C1 END-EXEC.
MOVE SQLCODE TO DISCOMP | MOVE SQLCODE TO DISCOMP
DISPLAY 'A OPEN <' DISCOMP '/' SQLERRMC '>' | DISPLAY 'B OPEN <' DISCOMP '/' SQLERRMC '>'
CALL 'B' <|
EXEC SQL FETCH C1 INTO :TEMP END-EXEC. | EXEC SQL FETCH C1 INTO :TEMP END-EXEC.
MOVE SQLCODE TO DISCOMP | MOVE SQLCODE TO DISCOMP
DISPLAY 'A FETCH <' DISCOMP '/' SQLERRMC | DISPLAY 'B FETCH <' DISCOMP '/' SQLERRMC
'/' TEMP '>' | '/' TEMP '>'
EXEC SQL CLOSE C1 END-EXEC. | EXEC SQL CLOSE C1 END-EXEC.
MOVE SQLCODE TO DISCOMP | MOVE SQLCODE TO DISCOMP
DISPLAY 'A CLOSE <' DISCOMP '/' SQLERRMC '>' | DISPLAY 'B CLOSE <' DISCOMP '/' SQLERRMC '>'
GOBACK | GOBACK
. | .
MAIN-EXIT. EXIT. | MAIN-EXIT. EXIT.
Running programs results in:
A OPEN <+000000000/ >
B OPEN <+000000000/ >
B FETCH <+000000000/ /X>
B CLOSE <+000000000/ >
A FETCH <+000000000/ /Y>
A CLOSE <+000000000/ >
B OPEN <+000000000/ >
B FETCH <+000000000/ /X>
B CLOSE <+000000000/ >
A FETCH <+000000000/ /Y>
A CLOSE <+000000000/ >
I'm now back enjoying the next weekend
-
alexm - Posts: 35
- Joined: Wed Oct 13, 2010 6:40 pm
- Has thanked: 0 time
- Been thanked: 0 time
Previous topic • Next topic • 4 posts
• Page 1 of 1
-
- Related topics
- Replies
- Views
- Last post
-
- Can we declare same Cursor more than once in a program
by Mann_B » Fri Jun 15, 2012 3:02 pm - 7 Replies
- 9116 Views
- Last post by NicC
Fri Apr 03, 2015 10:32 pm
- Can we declare same Cursor more than once in a program
-
- DB2 Cursor in COBOL-DB2 Program
by mbattu » Tue Mar 07, 2017 5:49 pm - 0 Replies
- 1968 Views
- Last post by mbattu
Tue Mar 07, 2017 5:49 pm
- DB2 Cursor in COBOL-DB2 Program
-
- getting abend s322 executing a cursor program
1, 2by kirangentlebreeze » Fri Oct 29, 2010 7:33 am - 11 Replies
- 5668 Views
- Last post by NicC
Sun Oct 31, 2010 8:33 pm
- getting abend s322 executing a cursor program
-
- converting dynamic cursor into static cursor
by rupa888 » Mon Jun 17, 2013 5:36 pm - 5 Replies
- 2993 Views
- Last post by dick scherrer
Wed Jun 19, 2013 6:43 pm
- converting dynamic cursor into static cursor
-
- END OF CURSOR AND NO ROWS FOUND IN CURSOR.
by sinmani » Fri Oct 04, 2013 5:28 pm - 5 Replies
- 6184 Views
- Last post by Akatsukami
Mon Oct 07, 2013 10:36 pm
- END OF CURSOR AND NO ROWS FOUND IN CURSOR.