What is STORED PROCEDURE in a DB2? How will you create a new stored procedure?

The DB2 STORED PROCEDURE are the programs which are directly managed by DBMS. The STORED PROCEDURE generally contains SQLs and they can be called by application programs. The STORED PROCEDURE processes the query and returns the result to the application program. The STORED PROCEDURES can be used for the SQLs which are very often used, so instead of using the same SQL query again and again, we can simply use STORED PROCEDURE.

The other benefit of STORED PROCEDURE is that they are fast and have good performance as compared to static SQLs used in application programs. The STORED PROCEDURE can be written in many languages like COBOL, JAVA, C++, etc. Once we have written a STORED PROCEDURE in any of the languages, we have to register this STORED PROCEDURE in DB2 using below command.

Example

CREATE PROCEDURE ORDERSTAT (IN ORDER_ID CHAR(8),
   OUT ORDER_STATUS CHAR(3))

A STORED PROCEDURE with the name ORDERSTAT will be created using the above command which will take the input as ORDER_ID and gives ORDER_STATUS in output.