Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Advantages of stored procedures

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
ganmain

New User


Joined: 19 Feb 2005
Posts: 28

PostPosted: Sat Mar 12, 2005 5:13 pm    Post subject: stored procedures
Reply with quote

Hai,

I want to know the advantages of stored procedures.

How to execute the stored procedures.(process involved).

WHETHER STORED PROCEDURES WILL OCCUPY SPACE OR NOT
(INTERVIEW QUESTIONS I HAVE FACED)

PLEASE CLARIFY THIS DOUBT
Back to top
View user's profile Send private message

mcmillan

Site Admin


Joined: 18 May 2003
Posts: 1202
Location: India

PostPosted: Sun Mar 13, 2005 11:04 am    Post subject: Re
Reply with quote

Quote:
advantages of stored procedures


Stored procedures can encapsulate many of your application's SQL statements into a single message to the DB2 server, reducing network traffic to a single send and receive operation for a series of SQL statements.

Stored procedures allow static SQL authorization from a dynamic environment.

Stored procedures remove SQL applications from the workstation, which prevents workstation users from manipulating the contents of sensitive SQL statements and host variables.


Quote:
execute the stored procedures.(process involved).


Write a stored procedure program that can execute the necessary SQL statements. If your stored procedure is a COBOL program, you must compile it with the option NODYNAM.

Write the application program used to CALL the procedure.

Bind a package for the stored procedure. Stored procedures require only a package at the server. You do not need to bind a plan.

Bind a Plan for the calling application Program.

Define the stored procedure in the catalog table SYSIBM.SYSPROCEDURES.

Use GRANT EXECUTE to authorize the appropriate users to use the stored procedure.


An Example of CREATE PROCEDURE Statement:

Quote:

 The name is B.

 It takes two parameters:

- An integer input parameter named V1
- A character output parameter of length 9 named V2

 It is written in the C language.

 It contains no SQL statements.

 The same input always produces the same output.

 The load module name is SUMMOD.

 The package collection name is SUMCOLL.

 It should run for no more than 900 CPU service units.

 The parameters can have null values.

 It should be deleted from memory when it completes.

 The Language Environment run-time options it needs are:

MSGFILE(OUTFILE),RPTSTG(ON),RPTOPTS(ON)

 It is part of the WLM application environment named PAYROLL.

 It runs as a main program.

 It does not access non-DB2 resources, so it does not need a special RACF environment.

 It can return at most 10 result sets.

 When control returns to the client program, DB2 should not commit updates automatically.


This CREATE PROCEDURE statement defines the stored procedure to DB2:

CREATE PROCEDURE B(IN V1 INTEGER, OUT V2 CHAR(9))
LANGUAGE C
DETERMINISTIC
NO SQL
EXTERNAL NAME SUMMOD
COLLID SUMCOLL
ASUTIME LIMIT 900
PARAMETER STYLE GENERAL WITH NULLS
STAY RESIDENT NO
RUN OPTIONS 'MSGFILE(OUTFILE),RPTSTG(ON),RPTOPTS(ON)'
WLM ENVIRONMENT PAYROLL
PROGRAM TYPE MAIN
SECURITY DB2
DYNAMIC RESULT SETS 10
COMMIT ON RETURN NO;



Quote:
WHETHER STORED PROCEDURES WILL OCCUPY SPACE OR NOT


If it's an Interview question, then the answer is "Yes".
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts Access SQLCA fields inside a stored p... gangapd DB2 6 Thu Oct 27, 2016 10:20 am
No new posts Is possible to call subprogram from c... cmsmoon COBOL Programming 3 Fri Aug 05, 2016 6:46 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us