What is the diff between declaring a cursor in WSS or PD?

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
mkk157
Member
Posts: 37
Joined: Wed Feb 07, 2007 2:00 pm
Location: Hyderabad

What is the diff between declaring a cursor in WSS or PD?

Post by mkk157 » Wed Mar 21, 2007 5:02 pm

Please answer me the most important question in the interviews, What is the difference between declaring the cursor in Working storage section and Procedure division?
Kumar
If u can dream it, U can do it.

User avatar
guptae
Member
Posts: 8
Joined: Thu Nov 23, 2006 11:28 am
Location: Bangalore,India

Post by guptae » Thu Mar 22, 2007 10:46 am

Hi There,

A static cursor needs to be declared before it is opened. The DB2 preprocesser needs to see it before it generates the code for accessing it. .........the declaration generates no executable code in itself so it is usually placed in the data division, that helps keep the procedure division cleaner and easier to see the logic flow.

Veera
Moderator
Posts: 111
Joined: Wed Feb 22, 2006 2:59 pm

Post by Veera » Fri Mar 23, 2007 1:55 am

Hi

I am not 100 sure if there is any big difference in terms of effective processing or cost-optimisation,

You can declare the cursor anywhere but if yoy declare in the working storage it will reduce the precompiler's work but it will not make any difference for the precompiler in terms of compilation process.Actual CUSROR processing is done by DB2 sybsystem when the CURSOR will be referenced first time in the PROCEDURE DIVISION.

Assumption: If u declare the cursor in PROCEDURE DIVISION may we one more extra call is being made to DB2 subsytem which can be avoided by declaring it in working storage section.

Just as Info : Not pertinent to ur question
**********************************

Long back there was some discussion on CURSOR declaration before or after INCLUDE statements (INCLUDE -> I mean Table declaraion i.e.. DCLGEN statements and other DB2 includes) when the compilation process was TOP-DOWN approach but now its no longer valid i blive...not sure on this part...if we run a simple test shd be able to figure out..

Thanks
Veera

User avatar
ibmlearner
Member
Posts: 13
Joined: Sat Dec 02, 2006 5:57 pm

Post by ibmlearner » Tue Apr 03, 2007 7:44 pm

hi kumar ,

if u decalred a CURSOR in WORKING-STORAGE SECTION , the CURSOR can be used anywhere ( any paragraphs) in your souce program.

if u declared in PROCEDURE DIVISION , the CURSOR can be used only that particular paragraph.

correct me if im wrong.
Thanks & Regards
-------------------------
Learner

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Tue Apr 03, 2007 8:35 pm

Learner you are wrong.

I have always coded the DECLARE immediately before the OPEN (same paragraph), the FETCH and CLOSE were always in there own paragraphs. The DECLARE was in the OPEN paragraph for documentation purposes and ease of maintenance (the FETCH was always the next SECTION - i never use paragraphs) - or simply a page down in the listing when columns were to be added or removed.

If anything, the DECLARE probably has to come (physically within the source file) before the OPEN; but as veera said, that was before, no telling what you can do in DB2 V9.

the only significant thing i ever noticed about putting the DECLARE in the procedure division was that i had to continually remove the SQL code check (after the DECLARE) that the Rookies would code.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

Post Reply