What's the significance of Cursor in Stored Procedures

anu2 · New User Joined: 10 Jun 2007 Posts: 36 Location: Chennai

Hi,

How Stored Procedures are different from normal COBOl + DB2 pgm ?
What's the significance of Cursor in Stored Procedures ?

Anu

dr_te_z · Posted: Thu Sep 20, 2007 12:24 pm

2 significant diff's:

1 - inter program communitation. A stored procedure can be called from any other application on any other platform, as long as they have database connection (db2 client or JDBC level 4)
2 - output. output can be represented as parameters (analogy with linkage storage)

or output can be represented as a database-table.
Just imagine a cursor in COBOL: code the SQL in "declare cursor", build the result set with "open cursor" and obtain the rows with "fetch cursor" and clean up your mess with "close cursor".

familiar stuff, right? When you DO NOT code the fetch and close after the open cursor, and just return to the caller by means of "GOBACK" the resulst-set is passed to the caller and can be processed on the client.

akumar · New User Joined: 12 Jan 2007 Posts: 8 Location: bangalore

one more thing to add on. Stored procedures are helpful in reducing network traffic. In a normal cobol-db2 program if we have 10 sql staments in a cobol-db2 program,there will be 10 I/0 across the network.
but using by stored procedure with some logic we can embbed all the 10 sql in a single sp,and can execute in a single I/o thus reducing the network traffic.