DB2 Stored Procedures in Mainframes

DB2 stored procedures mainframe–>  Stored procedures are the programs that contains one or more SQL statements to perform a business functionality and can be called by any application with SQL call statements. These are ALSO considered as the programs that are compiled and stored in a DB2 server which can be called/INVOKED from any application program. Stored procedures are DB2 objects and must be defined to DB2 using CREATE PROCEDURE Statement.

Good thing about Stored procedures are, when it gets compiled and called for the first time the execution plan gets stored in the database which gets used in further invocations of this SP. It is a data base Object which performs certain business logic so it is also considered as a program.

Once the Stored procedure is created, a row gets created in SYSROUTINES table. This table contains this SP details in columns like Specific Schema, Specific Name(Procedure name), Routine Type, Routine Body, SQL Data Access, Path & External Name Etc.

Where to Code?
Can be coded just like any other cobol db2 program with the required SQL statements inside it.

Types:
Can be a Native SP or External SP.
Native Stored procedures are written in SQL language.
External Stored procedures are written along with the host language like COBOL.

Creating External St. Procedures:
Creating external stored procedures involves in defining/CREATING the SP to DB2 and coding of SP logic in Language defined. In our case it is COBOL.

Defining SP to DB2:
This process gives a name, declares IN & OUT parameters, Sets the external Language etc.

Example:

CREATE PROCEDURE STRD001(PROCNM CHAR(18) IN, SCHEMA CHAR(8) IN,
OUTCODE INTEGER OUT, PARMLST VARCHAR(254) OUT)
LANGUAGE COBOL
DETERMINISTIC
READS SQL DATA
EXTERNAL NAME "STRD001"
COLLID STRD001
ASUTIME NO LIMIT
PARAMETER STYLE GENERAL
STAY RESIDENT NO
RUN OPTIONS "MSGFILE(OUTFILE),RPTSTG(ON),RPTOPTS(ON)"
WLM ENVIRONMENT SAMPPRG
PROGRAM TYPE MAIN
SECURITY DB2
RESULT SETS 2
COMMIT ON RETURN NO;

Invoking Stored Procedures:
In mainframes environment, A stored procedure can be invoked from an application program USING A CALL or it can be invoked from the Command Line.

EXEC-SQL
    CALL STRD001
END-EXEC

Parameters can be supplied to the above SP if they are declared while creating SP.

Features:

• Supports languages like SQL, COBOL, C, REXX, PLI, ASSE, JAVA.
• It is compiled once and used many a times.
• Parameters can be passed.
• Gives status back to the calling module.
• Returns values of the parameters passed.
• Cursors can be used, if one by one row needs to be processed.