Cursors can be declared in both working-storage & procedure
division, Agreed.
But is there any difference? If could you please suggest
what is the difference.
TIA
Answers were Sorted based on User's Feedback
Answer / ns
There is no difference. But it is always better to declare
Cursor in Working-Storage Section because you will not code
Open Cursor before Declare Cursor by mistake. It is just a
standard to declare Cursor in WSS. As best practice to
avoid oversight.
Is This Answer Correct ? | 28 Yes | 6 No |
Answer / arthi
Please refer the answer by Marcus_A here:
http://www.dbforums.com/db2/896592-cursor-declaration.html
This is the most concise answer I could find on the net, and it proves that there is no difference in the placement of the declare cursor.
Quoting from the page:
==============
The Declare Cursor SQL statement is not a procedural SQL statement. It is actually part of the Open statement for the Cursor it refers to, and can be placed anywhere in program source code as long as it is physically before the Open SQL statement. It can actually be placed in the Identification Division, although traditionally it is placed in the Working Storage Section.
If you were to examine the compile listing of your program, you will see that the DB2 pre-compiler did not generate a separate call to DB2 for the Declare Cursor (since it is really part of the Open statement for that Cursor).
The problem with placing the Declare Cursor in the Procedure Division is that programmers invariably place code right after the Declare Cursor to check the SQL return code. So what ends up happening is that the SQL code of a previously executed SQL statement gets checked by the program, and if a non-zero return code is acceptable (not an abend condition) for a previous Select statement (such as return code +100), then the program will get confused and often think an abend condition has occurred.
So, if you do put the Declare cursor in the Procedure Division (not a good idea in my opinion) then make absolutely sure that no user code is executed to check the SQL return code for Declare Cursor.
==============
Is This Answer Correct ? | 11 Yes | 4 No |
Answer / krishna
Since declare cursor is not a executable statement hence it
might be declared in WSS.I am not sure...
Is This Answer Correct ? | 11 Yes | 7 No |
Answer / sathish
This can be declared in both the sections. But not sure
what is the diff.
Is This Answer Correct ? | 8 Yes | 5 No |
Answer / bud gobbel
Actually, there is a reason for declaring the cursor in the Procedure Division, however, it is slight.
In CICS programs, the communications area (CA) is passed via the linkage section. If a cursor WHERE clause is predicated on a field in the CA area, then to avoid using a working storage variable and a MOVE statement prior to the opening of the cursor, you should code the cursor declaration in the Procedure Division, prior to the opening logic, etc.
If you don't mind using extra variables, and coding the required MOVE statements, then keep your cursor dec in WS.
In your DB2 pre-compiler, you can test this logic. Put the cursor above the linkage section, and try to use a field in the linkage section as a host variable in the where clause.
Then move it to the very early part of the Procedure Division. The pre-compiler will then be happy.
Good Luck!
Is This Answer Correct ? | 2 Yes | 1 No |
In CICS programs, data is passed in the Communications Area (CA) via the Linkage Section.
If your cursor is predicated (WHERE clause) on a field in the CA, you could code your cursor early in the Procedure Division prior to any open cursor logic. This will avoid using additional variables, and MOVE statements.
If you keep your cursor in the Working Storage area, you cannot use fields in the Linkage section as host variables, because the DB2 pre-compiler will see the cursor first, and NOT have a resolution for the host variable (it hasn't seen the Linkage Section, yet).
It mostly is up to you, unless your shop is a stickler on variables and locations, etc.
Also, over the years I have seen shops become more code-it-and-load-it in philosophy. Very sad.
Is This Answer Correct ? | 1 Yes | 1 No |
Answer / sam
yes there sre diffrence.
If we declare cursor in working storage section then it is
called as static cursor without parameters from cobol.
If we declare cursor in procedure division then we can use
COBOL variables as parameters to the cursor. See the
examples for both the cases...
Eg: STATIC CURSOR
WORKING-STORAGE SECTION.
01 HV-EMPNO PIC 9(4) value 7934.
EXEC SQL
DEFINE C1 CURSOR FOR SELECT * FROM EMP WHERE EMPNO=HV-EMPNO
END-EXEC.
In the above case EMPNO is static. We cannot move a value
or read a value in to empno, because it is in working
storage section.
DYNAMIC CURSOR:
PROCEDURE DIVISION.
ACCEPT HV-EMPNO.
EXEC SQL
DECLARE C1 CURSOR FOR SELECT * FROM EMP WHERE EMPNO=:HV-
EMPNO
END-EXEC.
In the above example we can read EMPNO from another table
or file or we can take it from user. Now cursor becomes
dynamic.
I hope now its clear for you. If you need more information
let me know.
Is This Answer Correct ? | 6 Yes | 24 No |
Answer / lu
I never see the cursor declared in WSS..only in Procedure
division...
Is This Answer Correct ? | 4 Yes | 45 No |
What is meant by dclgen?
Can we update view in db2?
could you give me an example how, where i code CHECKPOINT and restart...I need and example....thanks..
How can you display the current date & current time ?
is it compulsory commitment control in journal?
What is the use of reorg in db2?
what is the process of precompiler?
Explain how can you do the explain of a dynamic sql statement?
How do you do the EXPLAIN of a dynamic SQL statement?
What is a DB2 access path?
I have 3 cursors declared. Cursor1 retieves some data. Based on this curso2 will also fetches some data. In cursor3 (using for some updation) I'm using the data retrieved by the above 2 cursor. My question is, while working with cursor3, periodically if I give commit, will all the three crsors will be closed or only cursor3 will be closed?
What is the usage of open cursor command?