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.
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.
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.
- 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.
Can a Stored Procedure’s parameters be used / called in a SQL query ?
Say my OUT parameter from DB2 Stored Proc is Var1.
Can this be used as below:
Select Col1, Var1, from Table1