Below stored procedure functionality searches the catalog table SYSROUTINES for a row matching the input parameters from the client program. The two input parameters contain values for NAME and SCHEMA.
Searches the DB2® catalog table SYSTABLES for all tables in which the value of the CREATOR matches the value of input parameter SCHEMA. The stored procedure uses a cursor to return the table names. Here procedure name is: GETPRML
Passing two input values and getting two output values. Usually, IBM Data studio will be used to develop stored procedures.
Once you create a stored procedure in DB2, it is open to access by different operating systems of Mainframe-Zo/s, i-series (AS400), Windows, Linux.
Since it is a database object, and it does not belong to any particular operating system.
Stored Procedure Key Syntax Elements
- Language: It is COBOL. Since we are writing stored procedure in COBOL.
- Deterministic: This is an instruction to DB2, how it responses output values. Read more.
- Reads SQL Data: It means it does not modify SQL data. It only reads SQL data. Read more.
- External name: This definition tells it is for the external stored procedure.
- COLLID : This is the collection id. Related to DBRM of this procedure bound to a package collection id.
- ASUTIME: Total amount of processor time.
- Parameter Style: General- Passing input parameters. Read more.
- Stay Resident: If the stored procedure to remain in memory after the procedure ends.
- RUN Options: You need to pass run-time options. Read more.
- Program type: This can be the MAIN (main program) or SUB (sub-program)
- Security: This means security at DB2 level.
- Result sets: Specifies the maximum number of query result sets that the procedure can return. The default is DYNAMIC RESULT SETS 0, which indicates that the procedure can return no result sets. The value of integer must be between 0 and 32767.
- Commit on return: NO/YES, if DB2 to issue commit after procedure ends.
You May Also Like: Powerful steps to create Stored procedure
Sample Stored Procedure Syntax
CREATE PROCEDURE GETPRML(PROCNM CHAR(18) IN, SCHEMA CHAR(8) IN,
OUTCODE INTEGER OUT, PARMLST VARCHAR(254) OUT)
LANGUAGE COBOL
DETERMINISTIC
READS SQL DATA
EXTERNAL NAME “GETPRML”
COLLID GETPRML
ASUTIME NO LIMIT
PARAMETER STYLE GENERAL
STAY RESIDENT NO
RUN OPTIONS “MSGFILE(OUTFILE),RPTSTG(ON),RPTOPTS(ON)”
WLM ENVIRONMENT SAMPPROG
PROGRAM TYPE MAIN
SECURITY DB2
RESULT SETS 2
COMMIT ON RETURN NO;
CBL RENT
IDENTIFICATION DIVISION.
PROGRAM-ID. GETPRML.
AUTHOR. EXAMPLE.
DATE-WRITTEN. 10/03/2015.
ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
DATA DIVISION.
FILE SECTION.
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
***************************************************
* DECLARE A HOST VARIABLE TO HOLD INPUT SCHEMA
***************************************************
01 INSCHEMA PIC X(8).
***************************************************
* DECLARE CURSOR FOR RETURNING RESULT SETS
***************************************************
*
EXEC SQL DECLARE C1 CURSOR WITH RETURN FOR
SELECT NAME FROM SYSIBM.SYSTABLES WHERE CREATOR=:INSCHEMA
END-EXEC.
*
LINKAGE SECTION.
***************************************************
* DECLARE THE INPUT PARAMETERS FOR THE PROCEDURE
***************************************************
01 PROCNM PIC X(18).
01 SCHEMA PIC X(8).
*******************************************************
* DECLARE THE OUTPUT PARAMETERS FOR THE PROCEDURE
*******************************************************
01 OUT-CODE PIC S9(9) USAGE BINARY.
01 PARMLST.
49 PARMLST-LEN PIC S9(4) USAGE BINARY.
49 PARMLST-TEXT PIC X(254).
PROCEDURE DIVISION USING PROCNM, SCHEMA,
OUT-CODE, PARMLST.
*******************************************************
* Issue the SQL SELECT against the SYSIBM.SYSROUTINES
* DB2 catalog table.
*******************************************************
EXEC SQL
SELECT RUNOPTS INTO :PARMLST
FROM SYSIBM.ROUTINES
WHERE NAME=:PROCNM AND
SCHEMA=:SCHEMA
END-EXEC.
*******************************************************
* COPY SQLCODE INTO THE OUTPUT PARAMETER AREA
*******************************************************
MOVE SQLCODE TO OUT-CODE.
*******************************************************
* OPEN CURSOR C1 TO CAUSE DB2 TO RETURN A RESULT SET
* TO THE CALLER.
*******************************************************
EXEC SQL OPEN C1
END-EXEC.
PROG-END.
GOBACK.
Related Posts
Srinimf 