IBM Mainframe Forum

Posted: **Fri Oct 31, 2014 4:13 pm**

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.

Posted: **Sat Nov 01, 2014 6:38 pm**

Can anyone please respond on this ?

Posted: **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?

Posted: **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:

Select all

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.

Running programs results in:

Select all

A OPEN <+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

IBM Mainframe Forum

Same Cursor name used in Program and Subprogram

Same Cursor name used in Program and Subprogram

Re: Same Cursor name used in Program and Subprogram

Re: Same Cursor name used in Program and Subprogram

Re: Same Cursor name used in Program and Subprogram