IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Advantages of stored procedures


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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: 1210
Location: India

PostPosted: Sun Mar 13, 2005 11:04 am
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Invoke stored procedure via batch JCL. DB2 2
No new posts Infosphere Optim - unable to save Col... IBM Tools 0
No new posts Calling COBOL DB2 program from a COBO... COBOL Programming 2
No new posts user exits in Column Map procedures IBM Tools 0
This topic is locked: you cannot edit posts or make replies. Internal Autonomous Stored Procedure ... DB2 6
Search our Forums:

Back to Top