mcmillan
Site Admin
Joined: 18 May 2003
Posts: 923
Location: India
|
|
|
|
| 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". |
|
