regarding stored procedure

ravi kumar hassan

regarding stored procedure
Hi Studds,

I need an help with stored procedure..... in our shop we are using
DB2 V6 on OS390. i red about stored procedure in the manual but i am getting
confused with that... we need to write a store procedure using cobol......
can anybody explain how write a stored procedure using cobol.... on os390 to
execute a application. what is the advantage using this stored procedure????
and how would i call the stored procedure to execute the application???? if
somebody has a example of stored procedure it will be helpful....

Thanks
Kumar.
_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.



David Seibert

Re: regarding stored procedure
(in response to ravi kumar hassan)
Hi Ravi,
Who is Studds?

Below is a very very basic Stored procedure. All it does is accept a parm
from the caller and
insert a row into a table using the data from this parm.
There are lots of things missing: debugging code, error handling, return
communication to the caller....
But it works.

Here is code that could call it:

exec sql
CALL Userid.SPISRT2('DJS')
end-exec

I'm a tad puzzled why you say you have to write a Stored Procedure and then
ask what is the advantage of using a Stored Procedure. What is the business
problem your S.P. will solve? Stored procedures have lots of value.

Here are a few excerpts from an excellent Redbook:
Getting Started with DB2 Stored Procedures:
Give Them a Call through the Network >> SG24-4693-01
(C) Copyright International Business Machines Corporation 1996 1998. All
rights reserved.

"Stored procedures enable you to encapsulate many of your application¢ s SQL
statements into a
program that is stored at the DB2 server. The client can invoke the stored
procedure by using only one
SQL statement, thus reducing the network traffic to a single send and
receive operation for a series of
SQL statements. It is also easier to manage and maintain programs that run
at the server than it is to
manage and maintain many copies at the client machines.

Stored procedures enable you to split the application logic between the
client and the server. You can
use this technique to prevent the client application from manipulating the
contents of sensitive server
data. You can also use it to encapsulate business logic into programs at the
server. Figure 2 on
page 3 shows an example of the processing for a client/server application
with stored procedures.

Stored procedures also enable access to features that exist only on the
database server. These
features include commands that run only on the server, software installed
only on the server that can
be accessed by the stored procedure, and the computing resources of the
server, such as memory and
disk space.

Because stored procedures are defined in DRDA, they also take advantage of
DRDA features, such as
data transformation between platforms, database security and accounting, and
two-phase commit
support."

There are other great Stored Procedure Redbooks. See
http://www.redbooks.ibm.com

ID DIVISION.
PROGRAM-ID. SPISRT2.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
77 FILLER PIC X(12) VALUE 'SPISRT WS:'.

EXEC SQL INCLUDE SQLCA END-EXEC.



LINKAGE SECTION.
01 IN-TEXT.
49 IN-TEXT-L PIC S9(4) COMP.
49 IN-TEXT-D PIC X(03).
PROCEDURE DIVISION USING IN-TEXT.


EXEC SQL INSERT INTO SPINSERT (TEXT)
VALUES (:IN-TEXT)
END-EXEC.


GOBACK.

Hope this answers a few questions and helps a bit.

David Seibert
Compuware Corporation File-AID Product Architect
[login to unmask email]